Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.2, 10.2(EOL)
-
None
-
Ubuntu 16.04
-
10.2.11
Description
Based on StackOverflow question posted by Joyce Babu
https://stackoverflow.com/questions/45146830/non-grouping-field-used-in-having-clause-with-sub-query
With condition_pushdown_for_derived=on (which is default in 10.2) and sql_mode=ONLY_FULL_GROUP_BY, the query from the test case below returns an error:
Non-grouping field 'ct' is used in HAVING clause
|
The query appears to be valid, and without ONLY_FULL_GROUP_BY and/or with condition_pushdown_for_derived=off it works.
The error message is also incorrect, as there was no HAVING clause in query.
SET sql_mode = 'ONLY_FULL_GROUP_BY'; |
|
|
create table t1 (id int,id2 int); |
insert into t1 values (1,1), (2,3),(3,4), (7,2); |
|
|
create table t2(id2 int); |
insert t2 values (1),(2),(3); |
|
|
SELECT * FROM t1 |
LEFT OUTER JOIN |
(SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2) |
WHERE (vc.ct>0); |
Attachments
Issue Links
- links to
Activity
The patch for bug mdev-14368 that was pushed into 5.5 fixes the problem.
I've checked this when applying the patch to to 10.2.
This bug is a consequence of the bug mdev-14368 fixed in 5.5.59.
Fixed after a upstream merge from 5.5 where the cause of the bug was removed.
MariaDB [test]> select version();+----------------+| version() |+----------------+| 10.2.2-MariaDB |+----------------+1 row in set (0.00 sec)MariaDB [test]> SET sql_mode = 'ONLY_FULL_GROUP_BY';Query OK, 0 rows affected (0.00 sec)MariaDB [test]> explain extended SELECT * FROM t1-> LEFT OUTER JOIN-> (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)-> WHERE (vc.ct>0);ERROR 1463 (42000): Non-grouping field 'ct' is used in HAVING clauseMariaDB [test]> SET optimizer_switch='condition_pushdown_for_derived=off';Query OK, 0 rows affected (0.00 sec)MariaDB [test]> explain extended SELECT * FROM t1-> LEFT OUTER JOIN-> (SELECT id2, COUNT(*) as ct FROM t2 GROUP BY id2) vc USING (id2)-> WHERE (vc.ct>0);+------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 8 | 100.00 | Using where || 1 | PRIMARY | <derived2> | ref | key0 | key0 | 5 | test.t1.id2 | 2 | 100.00 | Using where || 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using temporary; Using filesort |+------+-------------+------------+------+---------------+------+---------+-------------+------+----------+---------------------------------+3 rows in set, 1 warning (0.00 sec)