MySQL Bugs: #92020: Introduce new SQL mode rejecting queries with results depending on query plan

Bug #92020	Introduce new SQL mode rejecting queries with results depending on query plan
Submitted:	15 Aug 2018 11:25	Modified:	15 Aug 2018 13:35
Reporter:	Sveta Smirnova (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	5.7.23, 8.0.12	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Even if SQL mode ONLY_FULL_GROUP_BY used some queries which potentially return different results depending on the optimizer plan are accepted.

How to repeat:
Run the query for bug #91878 with MySQL Server and SQL mode ONLY_FULL_GROUP_BY, with PostgreSQL and Oracle. Only MySQL will allow such a query.

PostgreSQL rejects it with:

postgres=# SELECT t.f1 AS T_FP, COUNT(t.f1) as  Test_Value FROM t1 AS t JOIN t2 AS tv ON(t.f1 = tv.t1_f1) join t1 as tt ON tt.f1 = t.f1 where t.f1 = 731834939448428685 group by tv.f1 having COUNT(t.f1) > 0;
ERROR:  column "t.f1" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT t.f1 AS T_FP, COUNT(t.f1) as  Test_Value FROM t1 AS t...

Oracle with:

ORA-00979: not a GROUP BY expression

MySQL returns wrong results as described in bug #91878

Suggested fix:
Since changing ONLY_FULL_GROUP_BY to something stricter may break existing applications introduce new mode, say, STRICT_ONLY_FULL_GROUP_BY which will reject such queries too.

Hi Sveta,

Verified as reported. Thanks for the report.

kind regards
Bogdan