revision-id: 1271aacaeed48df45b26b97748cbe6f683c6b465 (mariadb-10.4.3-53-g1271aacaeed) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-11 17:10:20 +0100 message: MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL Recalculate distinct pointer if we cut chain of SELECTs --- mysql-test/main/intersect.result | 33 +++++++++++++++++++++++++++++++ mysql-test/main/intersect.test | 27 +++++++++++++++++++++++++ mysql-test/main/table_value_constr.result | 1 + sql/sql_lex.cc | 4 ++++ 4 files changed, 65 insertions(+) diff --git a/mysql-test/main/intersect.result b/mysql-test/main/intersect.result index f2d36e1d0e9..bd88243f151 100644 --- a/mysql-test/main/intersect.result +++ b/mysql-test/main/intersect.result @@ -833,3 +833,36 @@ c1 3 drop table t12,t13,t234; # End of 10.3 tests +# +# MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL +# +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); +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; +a +7 +7 +1 +explain extended +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 7 100.00 Using where +4 UNION <derived2> ALL NULL NULL NULL NULL 6 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 100.00 Using where +3 INTERSECT t3 ALL NULL NULL NULL NULL 7 100.00 Using where +NULL INTERSECT RESULT <intersect2,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` > 4 union all /* select#4 */ select `__4`.`a` AS `a` from (/* select#2 */ select `test`.`t2`.`a` AS `a` from `test`.`t2` where `test`.`t2`.`a` < 5 intersect /* select#3 */ select `test`.`t3`.`a` AS `a` from `test`.`t3` where `test`.`t3`.`a` < 5) `__4` +drop table t1,t2,t3; diff --git a/mysql-test/main/intersect.test b/mysql-test/main/intersect.test index 0a68cab21f0..616a833ea3c 100644 --- a/mysql-test/main/intersect.test +++ b/mysql-test/main/intersect.test @@ -321,3 +321,30 @@ select * from t13 union select * from t234 intersect select * from t12; drop table t12,t13,t234; --echo # End of 10.3 tests + +--echo # +--echo # MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL +--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); + + +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; + +explain extended +select * from t1 where a > 4 +union all +select * from t2 where a < 5 +intersect +select * from t3 where a < 5; + +drop table t1,t2,t3; diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 454356bb5fb..1d485af4a4d 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -366,6 +366,7 @@ values (1,2); 1 2 1 2 3 4 +1 2 # combination of different structures that uses VALUES structures : UNION + UNION ALL values (1,2),(3,4) union all diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c204f96f303..64096e5be86 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -5427,6 +5427,7 @@ SELECT_LEX *LEX::wrap_select_chain_into_derived(SELECT_LEX *sel) DBUG_RETURN(NULL); Name_resolution_context *context= &dummy_select->context; dummy_select->automatic_brackets= FALSE; + sel->distinct= TRUE; // First select has not this attribute (safety) if (!(unit= dummy_select->attach_selects_chain(sel, context))) DBUG_RETURN(NULL); @@ -9089,6 +9090,7 @@ bool LEX::parsed_unit_in_brackets(SELECT_LEX_UNIT *unit) /* There is a priority jump starting from first_in_nest */ if (create_priority_nest(first_in_nest) == NULL) return true; + unit->reset_distinct(); } push_select(unit->fake_select_lex); return false; @@ -9236,6 +9238,7 @@ SELECT_LEX_UNIT *LEX::parsed_select_expr_cont(SELECT_LEX_UNIT *unit, /* There is a priority jump starting from first_in_nest */ if ((last= create_priority_nest(first_in_nest)) == NULL) return NULL; + unit->reset_distinct(); } sel1->first_nested= last->first_nested; } @@ -9273,6 +9276,7 @@ bool LEX::parsed_body_unit(SELECT_LEX_UNIT *unit) /* There is a priority jump starting from first_in_nest */ if (create_priority_nest(first_in_nest) == NULL) return true; + unit->reset_distinct(); } push_select(unit->fake_select_lex); return false;