MySQL Bugs: #100164: resolve problem in group by and order by field with 2 tables in 2 schema

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:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	8.0.20	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
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:
b
2
5

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.

Hi Mr. SHENG,

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.

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

It is actually a correct and expected behaviour, as described in our Reference Manual.