Description:
There is a discrepancy in how the ONLY_FULL_GROUP_BY SQL mode is enforced. When a query involves a non-aggregated column that is not present in the GROUP BY clause, MySQL correctly rejects the query if it targets a physical table. However, if the same logic is applied to a derived table (subquery), the validation fails to trigger, and the query is executed.
How to repeat:
CREATE TABLE t1 (c2 BIT);
INSERT INTO t1 (c2) VALUES (b'0');
INSERT INTO t1 (c2) VALUES (b'1');
-- Query 1: Using a Derived Table (Incorrectly Succeeds)
SELECT ta1.ca4, ta1.ca5
FROM (SELECT c2 AS ca4, c2 AS ca5 FROM t1) AS ta1
WHERE (! ta1.ca5)
GROUP BY ta1.ca5;
-- Result of Query 1:
-- +-------+-------+
-- | ca4 | ca5 |
-- +-------+-------+
-- | 0x00 | 0x00 |
-- +-------+-------+
-- Query 2: Using a Physical Table (Correctly Fails)
CREATE TABLE ta1 AS SELECT c2 AS ca4, c2 AS ca5 FROM t1;
SELECT ca4, ca5 FROM ta1 WHERE ((! ca5)) GROUP BY ca5;
-- Result of Query 2:
-- Error: Expression #1 of SELECT list is not in GROUP BY clause and contains
-- nonaggregated column 'debug.ta1.ca4' which is not functionally dependent
-- on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
