- commits - lists.mariadb.org

[Commits] 42f1eac205b: MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL
by Oleksandr Byelkin 11 Mar '19

11 Mar '19

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);

1 0

[Commits] 0219e8e4132: c
by Oleksandr Byelkin 11 Mar '19

11 Mar '19

revision-id: 0219e8e4132b48d3932c9273be5c497d85e6b01d (mariadb-10.4.3-54-g0219e8e4132) parent(s): 3b2e6076b274455ffcf06fefda569a5d92762fc2 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-11 17:35:02 +0100 message: c --- sql/sql_lex.cc | 7 ++----- 1 file changed, 2 insertions(+), 5 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 7f2499d4dff..89b330f263a 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -8841,11 +8841,8 @@ void st_select_lex_unit::reset_distinct() void st_select_lex_unit::fix_distinct() { - if (union_distinct) - { - if (this != union_distinct->master_unit()) - reset_distinct(); - } + if (union_distinct && this != union_distinct->master_unit()) + reset_distinct(); }

1 0

[Commits] 3b2e6076b27: MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL
by Oleksandr Byelkin 11 Mar '19

11 Mar '19

revision-id: 3b2e6076b274455ffcf06fefda569a5d92762fc2 (mariadb-10.4.3-53-g3b2e6076b27) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-11 17:25:24 +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 | 12 +++++------ sql/sql_lex.h | 2 +- 5 files changed, 67 insertions(+), 8 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..7f2499d4dff 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,18 +8839,12 @@ 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(); } } @@ -9089,6 +9084,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 +9232,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 +9270,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);

1 0

[Commits] 3d83b4324f7: c
by Oleksandr Byelkin 11 Mar '19

11 Mar '19

revision-id: 3d83b4324f7222c03e1d7f84f430218414ad5707 (mariadb-10.4.3-54-g3d83b4324f7) parent(s): 1271aacaeed48df45b26b97748cbe6f683c6b465 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-03-11 17:25:14 +0100 message: c --- sql/sql_lex.cc | 14 ++++---------- sql/sql_lex.h | 2 +- 2 files changed, 5 insertions(+), 11 deletions(-) diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 64096e5be86..7f2499d4dff 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -8839,18 +8839,12 @@ 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(); } } @@ -9090,7 +9084,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(); + unit->fix_distinct(); } push_select(unit->fake_select_lex); return false; @@ -9238,7 +9232,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(); + unit->fix_distinct(); } sel1->first_nested= last->first_nested; } @@ -9276,7 +9270,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(); + 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);

1 0

[Commits] 1271aacaeed: MDEV-18701: Wrong result from query that uses INTERSECT after UNION ALL
by Oleksandr Byelkin 11 Mar '19

11 Mar '19

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;

1 0

[Commits] d071e75: MDEV-18431: Select max + row_number giving incorrect result
by varun 11 Mar '19

11 Mar '19

revision-id: d071e75403fc8d26721aab520258a4d6a3647a3c (mariadb-10.2.22-47-gd071e75) parent(s): ab7e2b048d7d0835ae7473873169b7606114348e author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 19:05:14 +0530 message: MDEV-18431: Select max + row_number giving incorrect result The issue here was when we had a subquery and a window function in an expression in the select list then subquery was getting computed after window function computation. This resulted in incorrect results because the subquery was correlated and the fields in the subquery was pointing to the base table instead of the temporary table. The approach to fix this was to have an additional field in the temporary table for the subquery and to execute the subquery before window function execution. After execution the values for the subquery were stored in the temporary table and then when we needed to calcuate the expression, all we do is read the values from the temporary table for the subquery. --- mysql-test/r/win.result | 17 +++++++++++++++++ mysql-test/t/win.test | 14 ++++++++++++++ sql/item.cc | 1 - 3 files changed, 31 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/win.result b/mysql-test/r/win.result index 2e80c2c..ed1d350 100644 --- a/mysql-test/r/win.result +++ b/mysql-test/r/win.result @@ -3488,3 +3488,20 @@ ifnull(max(n1) over (partition by n1),'aaa') 4 drop table t1; drop view v1; +# +# MDEV-18431: Select max + row_number giving incorrect result +# +create table t1 (id int, v int); +insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2); +select e.id, +(select max(t1.v) from t1 where t1.id=e.id) as a, +row_number() over (partition by e.id order by e.v) as b, +(select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b +from t1 e; +id a b sum_a_b +1 3 1 4 +1 3 2 5 +1 3 3 6 +2 2 1 3 +2 2 2 4 +drop table t1; diff --git a/mysql-test/t/win.test b/mysql-test/t/win.test index 18bdfa3..8c9bf3e 100644 --- a/mysql-test/t/win.test +++ b/mysql-test/t/win.test @@ -2240,3 +2240,17 @@ explain select * from v1; select * from v1; drop table t1; drop view v1; + +--echo # +--echo # MDEV-18431: Select max + row_number giving incorrect result +--echo # + +create table t1 (id int, v int); +insert into t1 values (1, 1), (1,2), (1,3), (2, 1), (2, 2); + +select e.id, + (select max(t1.v) from t1 where t1.id=e.id) as a, + row_number() over (partition by e.id order by e.v) as b, + (select max(t1.v) from t1 where t1.id=e.id) + (row_number() over (partition by e.id order by e.v)) as sum_a_b +from t1 e; +drop table t1; diff --git a/sql/item.cc b/sql/item.cc index 46420fd..6c9496c 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1996,7 +1996,6 @@ void Item::split_sum_func2(THD *thd, Ref_ptr_array ref_pointer_array, } if (unlikely((!(used_tables() & ~PARAM_TABLE_BIT) || - type() == SUBSELECT_ITEM || (type() == REF_ITEM && ((Item_ref*)this)->ref_type() != Item_ref::VIEW_REF)))) return;

1 0

[Commits] fd0d355: MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly
by varun 11 Mar '19

11 Mar '19

revision-id: fd0d355e7c9a6756bc66946bf76080623d128c2d (mariadb-10.3.6-226-gfd0d355) parent(s): 88d89ee0bae24b71416c2af4f4c2f2be7b6a033a author: Varun Gupta committer: Varun Gupta timestamp: 2019-03-11 18:36:36 +0530 message: MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly Keys with multi-key parts were not being printed correctly, only the first key part was getting printed. Fixed it by making sure append_range_all_keyparts function is called for the remaining keyparts. --- mysql-test/main/opt_trace.result | 425 +++++++++++++++++++++++++++++++++++++++ mysql-test/main/opt_trace.test | 17 ++ sql/opt_range.cc | 3 +- 3 files changed, 443 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/opt_trace.result b/mysql-test/main/opt_trace.result index 4c3e2b3..307a81c 100644 --- a/mysql-test/main/opt_trace.result +++ b/mysql-test/main/opt_trace.result @@ -6022,3 +6022,428 @@ t_outer_2.a in (select t_inner_3.a from t2 t_inner_3, t1 t_inner_4) { set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; +# +# MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly +# +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a", "c"], + "key": "c", + "key_length": "10", + "used_key_parts": ["c", "d"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 98.9, + "attached_condition": "t1.a > 10 and t1.b < 10" + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 where t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a > 10 and t1.b < 10 and t1.c = 0 and t1.d = 1", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "t1.a > 10 and t1.b < 10 and multiple equal(0, t1.c) and multiple equal(1, t1.d)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "c", + "equals": "0", + "null_rejecting": false + }, + { + "table": "t1", + "field": "d", + "equals": "1", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 208.25 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": true, + "key_parts": ["c", "d"] + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 < a AND NULL < b < 10"], + "rowid_ordered": false, + "using_mrr": false, + "index_only": false, + "rows": 989, + "cost": 1272.8, + "chosen": false, + "cause": "cost" + }, + { + "index": "c", + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "c", + "rows": 1, + "ranges": ["0 <= c <= 0 AND 1 <= d <= 1"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "c", + "selectivity_from_index": 0.001 + }, + { + "index_name": "a", + "selectivity_from_index": 0.989 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 9.9e-4 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "c", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.c = 0 and t1.d = 1 and t1.a > 10 and t1.b < 10", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": "t1.a > 10 and t1.b < 10" + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +explain format=json select * from t1 force index(a) where a=10 and b=20; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ref", + "possible_keys": ["a"], + "key": "a", + "key_length": "10", + "used_key_parts": ["a", "b"], + "ref": ["const", "const"], + "rows": 1, + "filtered": 100 + } + } +} +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +QUERY TRACE MISSING_BYTES_BEYOND_MAX_MEM_SIZE INSUFFICIENT_PRIVILEGES +explain format=json select * from t1 force index(a) where a=10 and b=20 { + "steps": [ + { + "join_preparation": { + "select_id": 1, + "steps": [ + { + "expanded_query": "select t1.a AS a,t1.b AS b,t1.c AS c,t1.d AS d from t1 FORCE INDEX (a) where t1.a = 10 and t1.b = 20" + } + ] + } + }, + { + "join_optimization": { + "select_id": 1, + "steps": [ + { + "condition_processing": { + "condition": "WHERE", + "original_condition": "t1.a = 10 and t1.b = 20", + "steps": [ + { + "transformation": "equality_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "constant_propagation", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + }, + { + "transformation": "trivial_condition_removal", + "resulting_condition": "multiple equal(10, t1.a) and multiple equal(20, t1.b)" + } + ] + } + }, + { + "table_dependencies": [ + { + "table": "t1", + "row_may_be_null": false, + "map_bit": 0, + "depends_on_map_bits": [] + } + ] + }, + { + "ref_optimizer_key_uses": [ + { + "table": "t1", + "field": "a", + "equals": "10", + "null_rejecting": false + }, + { + "table": "t1", + "field": "b", + "equals": "20", + "null_rejecting": false + } + ] + }, + { + "rows_estimation": [ + { + "table": "t1", + "range_analysis": { + "table_scan": { + "rows": 1000, + "cost": 2e308 + }, + "potential_range_indexes": [ + { + "index": "a", + "usable": true, + "key_parts": ["a", "b"] + }, + { + "index": "c", + "usable": false, + "cause": "not applicable" + } + ], + "setup_range_conditions": [], + "group_index_range": { + "chosen": false, + "cause": "no group by or distinct" + }, + "analyzing_range_alternatives": { + "range_scan_alternatives": [ + { + "index": "a", + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"], + "rowid_ordered": true, + "using_mrr": false, + "index_only": false, + "rows": 1, + "cost": 2.3783, + "chosen": true + } + ], + "analyzing_roworder_intersect": { + "cause": "too few roworder scans" + }, + "analyzing_index_merge_union": [] + }, + "chosen_range_access_summary": { + "range_access_plan": { + "type": "range_scan", + "index": "a", + "rows": 1, + "ranges": ["10 <= a <= 10 AND 20 <= b <= 20"] + }, + "rows_for_plan": 1, + "cost_for_plan": 2.3783, + "chosen": true + } + } + }, + { + "selectivity_for_indexes": [ + { + "index_name": "a", + "selectivity_from_index": 0.001 + } + ], + "selectivity_for_columns": [], + "cond_selectivity": 0.001 + } + ] + }, + { + "considered_execution_plans": [ + { + "plan_prefix": [], + "table": "t1", + "best_access_path": { + "considered_access_paths": [ + { + "access_type": "ref", + "index": "a", + "used_range_estimates": true, + "rows": 1, + "cost": 2, + "chosen": true + }, + { + "type": "scan", + "chosen": false, + "cause": "cost" + } + ] + } + } + ] + }, + { + "attaching_conditions_to_tables": { + "original_condition": "t1.a = 10 and t1.b = 20", + "attached_conditions_computation": [], + "attached_conditions_summary": [ + { + "table": "t1", + "attached": null + } + ] + } + } + ] + } + }, + { + "join_execution": { + "select_id": 1, + "steps": [] + } + } + ] +} 0 0 +drop table t1,t0,one_k; diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index e59a11f..86f2a82 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -374,3 +374,20 @@ select * from INFORMATION_SCHEMA.OPTIMIZER_TRACE; set @@optimizer_switch= @save_optimizer_switch; drop table t1,t2; set optimizer_trace='enabled=off'; + +--echo # +--echo # MDEV-18471: Optimizer trace: multi-part key ranges are printed incorrectly +--echo # + +create table t0(a int); +insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table one_k (a int); +insert into one_k select A.a + B.a*10 + C.a*100 from t0 A, t0 B, t0 C; +create table t1 ( a int, b int, c int, d int, key (a,b), key(c,d)); +insert into t1 select a,a,a,a from one_k; +set optimizer_trace=1; +explain format=json select * from t1 where a > 10 and b < 10 and c=0 and d=1; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +explain format=json select * from t1 force index(a) where a=10 and b=20; +select * from INFORMATION_SCHEMA.OPTIMIZER_TRACe; +drop table t1,t0,one_k; diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 1e60bb9..92161f7 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -15812,8 +15812,7 @@ static void append_range_all_keyparts(Json_writer_array *range_trace, if (keypart_range->next_key_part && keypart_range->next_key_part->part == - keypart_range->part + 1 && - keypart_range->is_singlepoint()) + keypart_range->part + 1) { append_range_all_keyparts(range_trace, range_string, range_so_far, keypart_range->next_key_part, key_parts);

1 0

[Commits] 33bf0a34a15: Update test.
by jan 11 Mar '19

11 Mar '19

revision-id: 33bf0a34a15fa02ed3a656d372fe7d7b168bff8e (mariadb-10.2.22-49-g33bf0a34a15) parent(s): 1e4344ec86f26e35a6c400d5a02d4e981cbe45c2 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-11 12:20:09 +0200 message: Update test. --- mysql-test/suite/galera/r/galera_kill_smallchanges.result | 5 ++--- mysql-test/suite/galera/t/galera_kill_smallchanges.test | 5 +---- 2 files changed, 3 insertions(+), 7 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_kill_smallchanges.result b/mysql-test/suite/galera/r/galera_kill_smallchanges.result index 7542727b100..09f3921f022 100644 --- a/mysql-test/suite/galera/r/galera_kill_smallchanges.result +++ b/mysql-test/suite/galera/r/galera_kill_smallchanges.result @@ -7,9 +7,8 @@ connection node_1; INSERT INTO t1 VALUES (1); connection node_2; connection node_2a; -call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); -SELECT COUNT(*) = 1 FROM t1; -COUNT(*) = 1 +SELECT COUNT(*) FROM t1; +COUNT(*) 1 SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; VARIABLE_VALUE = 2 diff --git a/mysql-test/suite/galera/t/galera_kill_smallchanges.test b/mysql-test/suite/galera/t/galera_kill_smallchanges.test index fa726094671..b9c8f3b428d 100644 --- a/mysql-test/suite/galera/t/galera_kill_smallchanges.test +++ b/mysql-test/suite/galera/t/galera_kill_smallchanges.test @@ -3,7 +3,6 @@ # --source include/galera_cluster.inc ---source include/have_innodb.inc --connection node_1 @@ -28,9 +27,7 @@ INSERT INTO t1 VALUES (1); --source include/galera_connect.inc --connection node_2a -call mtr.add_suppression("WSREP: Last Applied Action message in non-primary configuration from member .*"); - -SELECT COUNT(*) = 1 FROM t1; +SELECT COUNT(*) FROM t1; SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --connection node_1

1 0

[Commits] 594160778e5: Ignore warning.
by jan 11 Mar '19

11 Mar '19

revision-id: 594160778e518dcc8ffabdbc8b2a5e12a93318de (mariadb-10.2.22-49-g594160778e5) parent(s): f7ebae2b17e1674ec255070814cbe472da1ae41b author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-11 12:16:46 +0200 message: Ignore warning. --- mysql-test/suite/galera/suite.pm | 1 + 1 file changed, 1 insertion(+) diff --git a/mysql-test/suite/galera/suite.pm b/mysql-test/suite/galera/suite.pm index 8b7b7034fac..6e53e6e2e31 100644 --- a/mysql-test/suite/galera/suite.pm +++ b/mysql-test/suite/galera/suite.pm @@ -75,6 +75,7 @@ push @::global_suppressions, qr|WSREP: Protocol violation. JOIN message sender .* is not in state transfer \(JOINED\). Message ignored.|, qr|WSREP: Unsupported protocol downgrade: incremental data collection disabled. Expect abort.|, qr(WSREP: Action message in non-primary configuration from member [0-9]*), + qr(WSREP: Last Applied Action message in non-primary configuration from member [0-9]*), qr(WSREP: discarding established .*), qr|WSREP: .*core_handle_uuid_msg.*|, qr(WSREP: --wsrep-causal-reads=ON takes precedence over --wsrep-sync-wait=0. WSREP_SYNC_WAIT_BEFORE_READ is on),

1 0

[Commits] 4f8a21caa26: Update results.
by jan 11 Mar '19

11 Mar '19

revision-id: 4f8a21caa269be6e8091fbb93aac01f88d0eb6e0 (mariadb-10.2.22-49-g4f8a21caa26) parent(s): ec96d5ab41e458b6397f5bbe83736f9c1405acf9 author: Jan Lindström committer: Jan Lindström timestamp: 2019-03-11 12:14:09 +0200 message: Update results. --- mysql-test/suite/galera/r/galera_kill_smallchanges.result | 2 -- mysql-test/suite/galera/r/galera_var_slave_threads.result | 13 ++++++++++--- 2 files changed, 10 insertions(+), 5 deletions(-) diff --git a/mysql-test/suite/galera/r/galera_kill_smallchanges.result b/mysql-test/suite/galera/r/galera_kill_smallchanges.result index e4d7161c71a..7542727b100 100644 --- a/mysql-test/suite/galera/r/galera_kill_smallchanges.result +++ b/mysql-test/suite/galera/r/galera_kill_smallchanges.result @@ -1,6 +1,4 @@ connection node_1; -connection node_2; -connection node_1; SET GLOBAL wsrep_provider_options = 'pc.ignore_sb=true'; CREATE TABLE t1 (f1 INTEGER) ENGINE=InnoDB; connection node_2; diff --git a/mysql-test/suite/galera/r/galera_var_slave_threads.result b/mysql-test/suite/galera/r/galera_var_slave_threads.result index c28cc091ae9..eda30755aed 100644 --- a/mysql-test/suite/galera/r/galera_var_slave_threads.result +++ b/mysql-test/suite/galera/r/galera_var_slave_threads.result @@ -12,6 +12,7 @@ Warning 1292 Truncated incorrect wsrep_slave_threads value: '0' SELECT @@wsrep_slave_threads = 1; @@wsrep_slave_threads = 1 1 +# wsrep_slave_threads = 1 SET GLOBAL wsrep_slave_threads = 1; SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE LIKE '%wsrep aborter%'; COUNT(*) @@ -20,15 +21,17 @@ SET GLOBAL wsrep_slave_threads = 64; connection node_1; INSERT INTO t1 VALUES (1); connection node_2; -SELECT COUNT(*) = 1 FROM t1; -COUNT(*) = 1 +SELECT COUNT(*) FROM t1; +COUNT(*) 1 +# wsrep_slave_threads = 64 SET GLOBAL wsrep_slave_threads = 1; connection node_1; connection node_2; SELECT COUNT(*) FROM t2; COUNT(*) -64 +66 +# wsrep_slave_threads = 1 SELECT COUNT(*) FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'system user' AND STATE LIKE '%wsrep aborter%'; COUNT(*) 1 @@ -42,11 +45,15 @@ connection node_1; CREATE TABLE t1 (i INT AUTO_INCREMENT PRIMARY KEY) ENGINE=INNODB; connection node_2; SET GLOBAL wsrep_slave_threads = 4; +# wsrep_slave_threads = 4 SET GLOBAL wsrep_slave_threads = 1; connection node_1; INSERT INTO t1 VALUES (DEFAULT); INSERT INTO t1 VALUES (DEFAULT); INSERT INTO t1 VALUES (DEFAULT); +INSERT INTO t1 VALUES (DEFAULT); +INSERT INTO t1 VALUES (DEFAULT); +INSERT INTO t1 VALUES (DEFAULT); DROP TABLE t1; connection node_2; SELECT NAME FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/t%';

1 0