Details
-
Bug
-
Status: Closed (View Workflow)
-
Minor
-
Resolution: Fixed
-
10.3.12
-
None
Description
Here what's shown in the warnings of the EXPLAIN EXTENDED for the query
select * from t2 where a < 5 |
intersect
|
select * from t3 where a < 5 |
union all |
select * from t1 where a > 4; |
when create and populate t1,t2,t3 with the following statements:
create table t1 (a int); |
insert into t1 values (3), (1), (7), (3), (2), (7), (4); |
create table t2 (a int); |
insert into t2 values (4), (5), (9), (1), (8), (9); |
create table t3 (a int); |
insert into t3 values (8), (1), (8), (2), (3), (7), (2); |
MariaDB [test]> explain extended select * from t2 where a < 5 intersect select * from t3 where a < 5 union all select * from t1 where a > 4; +------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | PRIMARY | t2 | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where |
|
| 2 | INTERSECT | t3 | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
|
| 3 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
|
| NULL | UNIT RESULT | <unit1,2,3> | ALL | NULL | NULL | NULL | NULL | NULL | NULL | |
|
+------+-------------+-------------+------+---------------+------+---------+------+------+----------+-------------+
|
4 rows in set, 1 warning (0.002 sec)
|
 |
MariaDB [test]> show warnings;
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect /* select#2 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union /* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 |
|
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
For comparison EXPLAIN EXTENDED for the query
explain extended select * from t3 where a < 5 union all select * from t1 where a > 4; |
shows everything as expected
MariaDB [test]> explain extended select * from t3 where a < 5 union all select * from t1 where a > 4;
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
| 1 | PRIMARY | t3 | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
|
| 2 | UNION | t1 | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where |
|
+------+-------------+-------+------+---------------+------+---------+------+------+----------+-------------+
|
2 rows in set, 1 warning (0.001 sec)
|
 |
MariaDB [test]> show warnings;
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Level | Code | Message |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Note | 1003 | /* select#1 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union all /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 |
|
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|