Bug #92020 Introduce new SQL mode rejecting queries with results depending on query plan

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