Bug #100164 resolve problem in group by and order by field with 2 tables in 2 schema
Submitted: 9 Jul 2020 1:45 Modified: 9 Jul 2020 12:21
Reporter: SUMMER SHENG Email Updates:
Status: Not a Bug Impact on me:
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0.20 OS:Any
Assigned to: CPU Architecture:Any

[9 Jul 2020 1:45] SUMMER SHENG
With two identical tables in two schema, change the table order in from clause lead to different results.

I think it does not work as expected.

How to repeat:
1. create data
create database test1;
use test1;
create table t1 (a int,b int);
insert into t1 values(1,1),(4,4);
create database test2;
create table test2.t1 (a int, b int ,c int);
insert into test2.t1 values (1,2,10),(2,3,11),(3,4,12),(4,5,13);

2. do the query
Query 1:
select test2.t1.b from t1 join test2.t1 using(a) group by t1.b order by t1.b;
Result 1:

Query 2:
select test2.t1.b from test2.t1 join t1 using(a) group by t1.b order by t1.b;
Result 2:
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test2.t1.b' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The only different between the two query is the swap of two tables in the from clause.
[9 Jul 2020 12:09] MySQL Verification Team

Thank you for your bug report.

However, this is not a bug.

Simply, if you are using `test1` schema and not fully qualified names in the query, and since only_full_group_by is set, then this is expected behaviour.

Not a bug.
[9 Jul 2020 12:21] SUMMER SHENG
I didn't mean that the error is a bug.

I was meaning that the two query I provide should have the same result.

It seems that in Query 1, t1.b is resolved to test1.t1.b; in Query 2, it is resolved to test2.t1.b

This is not as expected, I think
[9 Jul 2020 13:19] MySQL Verification Team
It is actually a correct and expected behaviour, as described in our Reference Manual.