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