Bug #118080 The execution plan for the view with UNION ALL uses a full table scan instead of an index.

• Submitted: 29 Apr 6:51
• Modified: 29 Apr 7:45
• Reporter: haizhen xue
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.42
• OS: Any
• CPU Architecture: Any

[29 Apr 6:51] haizhen xue

Description:
create table t1(id bigint, `c` varchar(1), PRIMARY KEY (`id`));
insert into t1 values(1,'a');
create table t2(id float(19,0), `c` varchar(1), PRIMARY KEY (`id`));
insert into t2 values(1.0,'a');

create view v_1 as select id from t1 where c is not null  union all select id from t2 where c is not null;

select * from v_1  where id=1 ;

mysql> explain select * from v_1  where id=1 ;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 8       | const |    1 |   100.00 | Using index |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | Using where |
|  3 | UNION       | t2         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

How to repeat:
create table t1(id bigint, `c` varchar(1), PRIMARY KEY (`id`));
insert into t1 values(1,'a');
create table t2(id float(19,0), `c` varchar(1), PRIMARY KEY (`id`));
insert into t2 values(1.0,'a');

create view v_1 as select id from t1 where c is not null  union all select id from t2 where c is not null;

8.0.30:
mysql> explain select * from v_1  where id=1 ;
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL  |    2 |   100.00 | NULL  |
|  2 | DERIVED     | t1         | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
|  3 | UNION       | t2         | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
3 rows in set, 1 warning (0.00 sec)

8.0.39:
mysql> explain select * from v_1  where id=1 ;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
| id | select_type | table      | partitions | type | possible_keys | key         | key_len | ref   | rows | filtered | Extra       |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ref  | <auto_key0>   | <auto_key0> | 8       | const |    1 |   100.00 | Using index |
|  2 | DERIVED     | t1         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | Using where |
|  3 | UNION       | t2         | NULL       | ALL  | NULL          | NULL        | NULL    | NULL  |    1 |   100.00 | Using where |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

[29 Apr 7:45] MySQL Verification Team

Hello haizhen xue,

Thank you for the report and test case.

regards,
Umesh