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 |
Attachments
Activity
It looks like without ONLY_FULL_GROUP_BY the problem exists but left unreported with the error.
The problem is that the function which lookup in the GROUP BY (find_field_in_group_list) list check:
(*(cur_group->item))>real_item()>type()
and so with merged derived (and probably VIEW) it do not recognize it as an legal field.
revision-id: 2e7c5fc70c571378ac1f5800fae3d59ba7d7bca5 (mariadb-5.5.53-16-g2e7c5fc)
parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8
committer: Oleksandr Byelkin
timestamp: 2016-12-05 18:15:04 +0100
message:
MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error
check for VIEW/DERIVED fields
revision-id: 895309f441e0262229671dcec5d49c4ff8f8ca2c (mariadb-5.5.53-16-g895309f)
parent(s): 18cdff6765b954431934f6e6d0b1e281c8f0e0b8
committer: Oleksandr Byelkin
timestamp: 2016-12-06 14:18:24 +0100
message:
MDEV-10663: Use of Inline table columns in HAVING clause throws 1463 Error
check for VIEW/DERIVED fields
Thanks for the report and test case.