revision-id: 42f1eac205b12e51a6b3d6d87bfcc2a2e384e8ef (mariadb-10.4.3-53-g42f1eac205b) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-11 17:35:13 +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 | 19 +++++++----------- sql/sql_lex.h | 2 +- 5 files changed, 69 insertions(+), 13 deletions(-) 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..89b330f263a 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); @@ -8838,19 +8839,10 @@ void st_select_lex_unit::reset_distinct() } -void st_select_lex_unit::fix_distinct(st_select_lex_unit *new_unit) +void st_select_lex_unit::fix_distinct() { - if (union_distinct) - { - if (this != union_distinct->master_unit()) - { - DBUG_ASSERT(new_unit == union_distinct->master_unit()); - new_unit->union_distinct= union_distinct; - reset_distinct(); - } - else - new_unit->reset_distinct(); - } + if (union_distinct && this != union_distinct->master_unit()) + reset_distinct(); } @@ -9089,6 +9081,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->fix_distinct(); } push_select(unit->fake_select_lex); return false; @@ -9236,6 +9229,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->fix_distinct(); } sel1->first_nested= last->first_nested; } @@ -9273,6 +9267,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->fix_distinct(); } push_select(unit->fake_select_lex); return false; diff --git a/sql/sql_lex.h b/sql/sql_lex.h index b78a010d4b7..61816bee803 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -995,7 +995,7 @@ class st_select_lex_unit: public st_select_lex_node { unit_common_op common_op(); void reset_distinct(); - void fix_distinct(st_select_lex_unit *new_unit); + void fix_distinct(); void register_select_chain(SELECT_LEX *first_sel);