Bug #99273 Item_ref in Having Clause points to invalid outer table field

• Submitted: 16 Apr 2020 6:02
• Modified: 16 Apr 2020 7:47
• Reporter: Shanshan Ying
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.19, 5.7.29
• OS: Any
• CPU Architecture: Any
• Tags: regression

[16 Apr 2020 6:02] Shanshan Ying

Description:
Item_ref in having clause with subquery refers to an invlaid field in outer table

How to repeat:
mysql> CREATE TABLE t1 (a INT, b INT);
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3);
Query OK, 6 rows affected (0.02 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql>
mysql> SET SQL_MODE = 'ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)

mysql>
mysql> SELECT a
    -> FROM t1 as t1
    -> GROUP BY a
    ->   HAVING (
    ->       SELECT t1.a
    ->       FROM t1 AS t2
    ->       GROUP BY b LIMIT 1
    ->   );
+------+
| a    |
+------+
|    1 |
|    2 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+
6 rows in set (0.01 sec)

mysql> INSERT INTO t1 values (null, 4);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT a
    -> FROM t1 as t1
    -> GROUP BY a
    ->   HAVING (
    ->       SELECT t1.a
    ->       FROM t1 AS t2
    ->       GROUP BY b LIMIT 1
    ->   );
Empty set (0.00 sec)

The Item_ref(t1.a)'s value is copied from T1.a, not from the tmp table on T1.
That's why after inserting a NULL values into T1 leading to empty result set.

[16 Apr 2020 7:47] MySQL Verification Team

Hello Shanshan Ying,

Thank you for the report and test case.
Observed that 5.7.29 and 8.0.19 builds are affected with the provided test case.

regards,
Umesh