MySQL Bugs: #113358: Wrong functional dependency in ONLY_FULL_GROUP

Bug #113358	Wrong functional dependency in ONLY_FULL_GROUP_BY mode
Submitted:	7 Dec 2023 7:09	Modified:	7 Dec 2023 8:41
Reporter:	Xinhao Zhao	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0, 8.0.35	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
subquery_to_derived=on cause some wrong query to be executed.

How to repeat:
create table t1(a int, b int c int);
create table t2(a int, b int);
insert into t1 values (1, 1, 1), (2, 1, 1);
insert into t2 values (1), (1), (2);
set optimizer_switch='subquery_to_derived=off';
select b, (select count(t.a) from t2 as t where t.a = t1.a) as c from t1;
select b, (select count(t.a) from t2 as t where t.a = t1.a) as c from t1 group by b;
set optimizer_switch='subquery_to_derived=on';
select b, (select count(t.a) from t2 as t where t.a = t1.a) as c from t1 group by b;

mysql> SET optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    1 |    1 |
+------+------+------+
2 rows in set (0.05 sec)

mysql> select * from t2;
+------+
| a    |
+------+
|    1 |
|    2 |
|    1 |
+------+
3 rows in set (0.05 sec)

mysql> SET optimizer_switch='subquery_to_derived=on';
Query OK, 0 rows affected (0.00 sec)

mysql> select b, (select count(t.a) from t2 as t where t.a = t1.a) as c from t1 group by b;
+------+---+
| b    | c |
+------+---+
|    1 | 2 |
+------+---+
1 row in set (0.12 sec)

mysql> select b, (select count(t.a) from t2 as t where t.a = t1.a) as c from t1;
+------+---+
| b    | c |
+------+---+
|    1 | 2 |
|    1 | 1 |
+------+---+
2 rows in set (0.11 sec)

I think the bug comes from Group_check::do_ident_check, where functional dependency check is mistakenly skipped when the table is derived table converted from scalar_query.

The code is 

if (i->type() == Item::FIELD_ITEM && down_cast<Item_field *>(i)->table_ref->m_was_scalar_subquery)
    return false;

Hello Xinhao Zhao,

Thank you for the report and testcase.

regards,
Umesh