Bug #114312 Item_ref::used_tables wrong when ref to a rollup constant
Submitted: 12 Mar 6:30 Modified: 19 Mar 2:26
Reporter: Xinhao Zhao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any

[12 Mar 6:30] Xinhao Zhao
Description:
An Item_ref ref items in base_ref_items, but Item_rollup_group_item only exists in field_list. If an Item_ref reference to a constant with rollup, then the Item_ref's used_tables should be RAND_TABLE_BIT, but now it's 0.

This will cause wrong result.

mysql> CREATE TABLE t1 (a int primary key, b int);
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO t1 VALUES (1, 2);
Query OK, 1 row affected (0.01 sec)

mysql> SELECT 1 as x FROM t1 GROUP BY x WITH ROLLUP HAVING EXISTS (SELECT 1 FROM t1 WHERE x is NULL);
Empty set (0.00 sec)

mysql> EXPLAIN SELECT 1 as x FROM t1 GROUP BY x WITH ROLLUP HAVING EXISTS (SELECT 1 FROM t1 WHERE x is NULL);
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra             |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
|  1 | SIMPLE      | NULL  | NULL       | NULL | NULL          | NULL | NULL    | NULL | NULL |     NULL | Impossible HAVING |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------------+
1 row in set, 2 warnings (0.00 sec)

How to repeat:
CREATE TABLE t1 (a int primary key, b int);
INSERT INTO t1 VALUES (1, 2);
SELECT 1 as x FROM t1 GROUP BY x WITH ROLLUP HAVING EXISTS (SELECT 1 FROM t1 WHERE x is NULL);
EXPLAIN SELECT 1 as x FROM t1 GROUP BY x WITH ROLLUP HAVING EXISTS (SELECT 1 FROM t1 WHERE x is NULL);

Suggested fix:
Find item in field_list for Item_ref and use that item to propagate used_tables
[12 Mar 6:50] MySQL Verification Team
Hello Xinhao Zhao,

Thank you for the report and testcase.

regards,
Umesh
[19 Mar 2:26] Xinhao Zhao
Maybe readd Item_func_rollup_const in 8.0.13 can fix this problem.