Description:
in the mysql the Constant conditions in SQL with derived views are not passed.
How to repeat:
root@localhost:employees>create table c1(id int ,n varchar(10) ) ;
Query OK, 0 rows affected (0.16 sec)
root@localhost:employees>create table c2(id int ,n varchar(10) ) ;
Query OK, 0 rows affected (0.12 sec)
root@localhost:employees>create index idx_c1 on c1(id) ;
Query OK, 0 rows affected (0.10 sec)
Records: 0 Duplicates: 0 Warnings: 0
root@localhost:employees>create index idx_c2 on c2(id) ;
root@localhost:employees>insert into a1 values(1,'a1'),(2,'a2'),(3,'a3');
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost:employees>insert into a2 values(1,'a1'),(2,'a2'),(3,'a3');
Query OK, 3 rows affected (0.06 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@localhost:employees>desc select * from c1 d join c2 e on d.id = e.id where d.id=1 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d | NULL | ref | idx_c1 | idx_c1 | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | e | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.01 sec)
root@localhost:employees>show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`d`.`id` AS `id`,`employees`.`d`.`n` AS `n`,`employees`.`e`.`id` AS `id`,`employees`.`e`.`n` AS `n` from `employees`.`c1` `d` join `employees`.`c2` `e` where ((`employees`.`d`.`id` = 1) and (`employees`.`e`.`id` = 1))
1 row in set (0.00 sec)
root@localhost:employees>desc select * from c1 d join c2 e on d.id = e.id where e.id=1 ;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | d | NULL | ref | idx_c1 | idx_c1 | 4 | const | 1 | 100.00 | NULL |
| 1 | SIMPLE | e | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
2 rows in set, 1 warning (0.00 sec)
root@localhost:employees>show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`d`.`id` AS `id`,`employees`.`d`.`n` AS `n`,`employees`.`e`.`id` AS `id`,`employees`.`e`.`n` AS `n` from `employees`.`c1` `d` join `employees`.`c2` `e` where ((`employees`.`d`.`id` = 1) and (`employees`.`e`.`id` = 1))
1 row in set (0.01 sec)
###################################
root@localhost:employees>desc select * from c1 d join ( select * from c2 e union all select * from c2 e1 ) e on d.id = e.id where d.id=1 ;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | PRIMARY | d | NULL | ref | idx_c1 | idx_c1 | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | e | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 3 | UNION | e1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
4 rows in set, 1 warning (0.00 sec)
root@localhost:employees>show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`d`.`id` AS `id`,`employees`.`d`.`n` AS `n`,`e`.`id` AS `id`,`e`.`n` AS `n` from `employees`.`c1` `d` join (/* select#2 */ select `employees`.`e`.`id` AS `id`,`employees`.`e`.`n` AS `n` from `employees`.`c2` `e` union all /* select#3 */ select `employees`.`e1`.`id` AS `id`,`employees`.`e1`.`n` AS `n` from `employees`.`c2` `e1`) `e` where ((`employees`.`d`.`id` = 1) and (`e`.`id` = 1))
1 row in set (0.00 sec)
root@localhost:employees>desc select * from c1 d join ( select * from c2 e union all select * from c2 e1 ) e on d.id = e.id where e.id=1 ;
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
| 1 | PRIMARY | d | NULL | ALL | idx_c1 | NULL | NULL | NULL | 3 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | employees.d.id | 2 | 100.00 | NULL |
| 2 | DERIVED | e | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
| 3 | UNION | e1 | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+----------------+------+----------+-------+
4 rows in set, 1 warning (0.00 sec)
root@localhost:employees>show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`d`.`id` AS `id`,`employees`.`d`.`n` AS `n`,`e`.`id` AS `id`,`e`.`n` AS `n` from `employees`.`c1` `d` join (/* select#2 */ select `employees`.`e`.`id` AS `id`,`employees`.`e`.`n` AS `n` from `employees`.`c2` `e` where (`employees`.`e`.`id` = 1) union all /* select#3 */ select `employees`.`e1`.`id` AS `id`,`employees`.`e1`.`n` AS `n` from `employees`.`c2` `e1` where (`employees`.`e1`.`id` = 1)) `e` where (`e`.`id` = `employees`.`d`.`id`)
1 row in set (0.00 sec)
Suggested fix:
root@localhost:employees>desc select * from c1 d join ( select * from c2 e union all select * from c2 e1 ) e on d.id = e.id where e.id=1 and d.id=1 ;
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
| 1 | PRIMARY | d | NULL | ref | idx_c1 | idx_c1 | 4 | const | 1 | 100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | const | 1 | 100.00 | NULL |
| 2 | DERIVED | e | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
| 3 | UNION | e1 | NULL | ref | idx_c2 | idx_c2 | 4 | const | 1 | 100.00 | NULL |
+----+-------------+------------+------------+------+---------------+-------------+---------+-------+------+----------+-------+
4 rows in set, 1 warning (0.01 sec)
root@localhost:employees>show warnings \G
*************************** 1. row ***************************
Level: Note
Code: 1003
Message: /* select#1 */ select `employees`.`d`.`id` AS `id`,`employees`.`d`.`n` AS `n`,`e`.`id` AS `id`,`e`.`n` AS `n` from `employees`.`c1` `d` join (/* select#2 */ select `employees`.`e`.`id` AS `id`,`employees`.`e`.`n` AS `n` from `employees`.`c2` `e` where (`employees`.`e`.`id` = 1) union all /* select#3 */ select `employees`.`e1`.`id` AS `id`,`employees`.`e1`.`n` AS `n` from `employees`.`c2` `e1` where (`employees`.`e1`.`id` = 1)) `e` where ((`employees`.`d`.`id` = 1) and (`e`.`id` = 1))
1 row in set (0.00 sec)
