Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10663

Use of Inline table columns in HAVING clause throws 1463 Error

    Details

    • Sprint:
      10.1.20

      Description

      Use of Inline table columns in Having Clause throws 1463 Error but use of the same column in Group by works fine.

      These queries worked fine with MySQL 5.6.21 but started failing when using in Mariadb.

      Test Case presented below

      MariaDB [bts]> DROP TABLE IF EXISTS `example1463`;
      Query OK, 0 rows affected (0.00 sec)
       
      MariaDB [bts]> select @@Sql_mode;
      +-----------------------------------------------------------------------------------+
      | @@Sql_mode                                                                        |
      +-----------------------------------------------------------------------------------+
      | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      +-----------------------------------------------------------------------------------+
      1 row in set (0.00 sec)
       
      MariaDB [bts]> CREATE TABLE `example1463` (
          ->   `Customer` varchar(255) NOT NULL,
          ->   `DeliveryStatus` varchar(255) NOT NULL,
          ->   `OrderSize` int(11) NOT NULL
          -> ) ;
      Query OK, 0 rows affected (0.02 sec)
      

      MariaDB [bts]>
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Success', 100);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 110);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Charlie', 'Failed', 200);
      Query OK, 1 row affected (0.01 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Success', 100);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('David', 'Unknown', 100);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Success', 150);
      Query OK, 1 row affected (0.00 sec)
       
      MariaDB [bts]> INSERT INTO example1463 VALUES ('Edward', 'Pending', 150);
      Query OK, 1 row affected (0.00 sec)
      

      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
          -> FROM (SELECT Customer,
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
          -> OrderSize
          -> FROM example1463) as subQ
          -> GROUP BY Success, Customer
          -> WITH ROLLUP;
      +----------+---------+----------------+
      | Customer | Success | SUM(OrderSize) |
      +----------+---------+----------------+
      | Charlie  | No      |            200 |
      | David    | No      |            100 |
      | Edward   | No      |            150 |
      | NULL     | No      |            450 |
      | Charlie  | Yes     |            100 |
      | David    | Yes     |            210 |
      | Edward   | Yes     |            150 |
      | NULL     | Yes     |            460 |
      | NULL     | NULL    |            910 |
      +----------+---------+----------------+
      9 rows in set (0.00 sec)
      

      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
          -> FROM (SELECT Customer,
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
          -> OrderSize
          -> FROM example1463) as subQ
          -> GROUP BY Success, Customer
          -> WITH ROLLUP
          -> HAVING Success IS NOT NULL;
      ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause
      

      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
          -> FROM (SELECT Customer,
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
          -> OrderSize
          -> FROM example1463) as subQ
          -> GROUP BY Success, Customer
          -> ;
      +----------+---------+----------------+
      | Customer | Success | SUM(OrderSize) |
      +----------+---------+----------------+
      | Charlie  | No      |            200 |
      | David    | No      |            100 |
      | Edward   | No      |            150 |
      | Charlie  | Yes     |            100 |
      | David    | Yes     |            210 |
      | Edward   | Yes     |            150 |
      +----------+---------+----------------+
      6 rows in set (0.01 sec)
      

      MariaDB [bts]> SELECT Customer, Success, SUM(OrderSize)
          -> FROM (SELECT Customer,
          -> CASE WHEN DeliveryStatus='Success' THEN 'Yes' ELSE 'No' END AS Success,
          -> OrderSize
          -> FROM example1463) as subQ
          -> GROUP BY Success, Customer
          -> HAVING Success IS NOT NULL;
      ERROR 1463 (42000): Non-grouping field 'Success' is used in HAVING clause
      

        Attachments

          Activity

            People

            • Assignee:
              sanja Oleksandr Byelkin
              Reporter:
              kgrandhe@us.ibm.com Kishor Grandhe
            • Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: