Details
Critical 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 |