revision-id: 09bd2138522787a4e0b015695c462903f4a9e728 (mariadb-10.3.12-65-g09bd213) parent(s): 4946eb7b54323f98fa2e399e981467733c3b0914 author: Igor Babaev committer: Igor Babaev timestamp: 2019-02-22 21:38:55 -0800 message: MDEV-18700 EXPLAIN EXTENDED shows a wrong operation for query with UNION ALL after INTERSECT EXPLAIN EXTENDED erroneously showed UNION instead of UNION ALL in the warning if UNION ALL followed INTERSECT or EXCEPT operations. The bug was in the function st_select_lex_unit::print() that printed the text of the query used in the warning. --- mysql-test/main/union.result | 37 +++++++++++++++++++++++++++++++++++++ mysql-test/main/union.test | 30 ++++++++++++++++++++++++++++++ sql/sql_lex.cc | 4 ++-- 3 files changed, 69 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/union.result b/mysql-test/main/union.result index da99d65..ef767b1 100644 --- a/mysql-test/main/union.result +++ b/mysql-test/main/union.result @@ -2568,5 +2568,42 @@ c1 -10 drop table t1,t2; # +# MDEV-18700: EXPLAIN EXTENDED for query with UNION ALL +# after INTERSECT/EXCEPT operations +# +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); +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 +Warnings: +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 all /* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 +explain extended +select * from t2 where a < 5 +except +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 EXCEPT 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 +Warnings: +Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 except /* select#2 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5 union all /* select#3 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 +drop table t1,t2,t3; +# # End of 10.3 tests # diff --git a/mysql-test/main/union.test b/mysql-test/main/union.test index e7543ba..9d9194e 100644 --- a/mysql-test/main/union.test +++ b/mysql-test/main/union.test @@ -1812,5 +1812,35 @@ SELECT c1 FROM t1 UNION SELECT - @a FROM t2; drop table t1,t2; --echo # +--echo # MDEV-18700: EXPLAIN EXTENDED for query with UNION ALL +--echo # after INTERSECT/EXCEPT operations +--echo # + +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); + +explain extended +select * from t2 where a < 5 +intersect +select * from t3 where a < 5 +union all +select * from t1 where a > 4; + +explain extended +select * from t2 where a < 5 +except +select * from t3 where a < 5 +union all +select * from t1 where a > 4; + +drop table t1,t2,t3; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 430bd2b..4282a8f 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2897,8 +2897,6 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type) str->append(STRING_WITH_LEN(" union ")); if (union_all) str->append(STRING_WITH_LEN("all ")); - else if (union_distinct == sl) - union_all= TRUE; break; case INTERSECT_TYPE: str->append(STRING_WITH_LEN(" intersect ")); @@ -2907,6 +2905,8 @@ void st_select_lex_unit::print(String *str, enum_query_type query_type) str->append(STRING_WITH_LEN(" except ")); break; } + if (sl == union_distinct) + union_all= TRUE; } if (sl->braces) str->append('(');