- commits - lists.mariadb.org

[Commits] 88a80e92dc4: MDEV-9959: A serious MariaDB server performance bug
by Varun 27 Apr '19

27 Apr '19

revision-id: 88a80e92dc444ce30718f3e08d3ab66fb02bcea4 (mariadb-10.3.10-284-g88a80e92dc4) parent(s): 3032cd8e91f1e1ead8b6f941e75cd29e473e7eaa author: Varun Gupta committer: Varun Gupta timestamp: 2019-04-24 13:31:24 +0530 message: MDEV-9959: A serious MariaDB server performance bug Step #2: If any field in the select list of the derived tables is present in the group by list also , then we are again guaranteed that ref access to the derived table would always produce one row per key. --- mysql-test/main/cte_nonrecursive.result | 6 +- mysql-test/main/derived.result | 43 +++++++++ mysql-test/main/derived.test | 26 +++++ mysql-test/main/derived_cond_pushdown.result | 106 ++++++++++----------- mysql-test/main/derived_opt.result | 2 +- mysql-test/main/derived_split_innodb.result | 4 +- mysql-test/main/derived_view.result | 34 +++---- mysql-test/main/join_cache.result | 6 +- mysql-test/main/selectivity.result | 4 +- mysql-test/main/selectivity_innodb.result | 4 +- mysql-test/main/subselect_extra.result | 2 +- mysql-test/main/subselect_extra_no_semijoin.result | 2 +- sql/sql_lex.cc | 62 ++++++++++++ sql/sql_lex.h | 1 + sql/table.cc | 3 + 15 files changed, 220 insertions(+), 85 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index d80d34ecc7f..b04b0335289 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -85,14 +85,14 @@ with t as (select a, count(*) from t1 where b >= 'c' group by a) select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort explain select * from t2, (select a, count(*) from t1 where b >= 'c' group by a) as t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort # specivication of t contains having with t as (select a, count(*) from t1 where b >= 'c' @@ -597,7 +597,7 @@ explain select * from v2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived3> ref key0 key0 5 test.t2.c 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary; Using filesort # with clause in the specification of a view that whose definition # table alias for a with table diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index 857246d68b4..e19b80678be 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1249,3 +1249,46 @@ a a 4 4 6 6 drop table t1,t2,t3; +create table t1(a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (7,1),(8,2), (1,3); +# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +a b a b c +1 1 1 2 5 +# expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case +# involves use of multiple equalities to check the prefix condition for rows=1 +explain select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +a b a b +# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using where; Using temporary; Using filesort +select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +a b a b +# rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where +1 PRIMARY <derived2> ref key0 key0 10 test.t1.a,test.t1.b 2 +2 DERIVED t2 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +a b a b c +1 1 8 1 1 +2 2 7 2 2 +drop table t1,t2,t3; diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 990f955450a..494500a1c8a 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1062,3 +1062,29 @@ analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; drop table t1,t2,t3; + +create table t1(a int, b int); +insert into t1 values (1,1),(2,2),(3,3); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (7,2,2),(8,1,1),(1,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (7,1),(8,2), (1,3); + +--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.a; + +--echo # expects rows=1 for derived table as group by fields are a prefix of the keyparts involved in ref access, this case +--echo # involves use of multiple equalities to check the prefix condition for rows=1 +explain select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; +select * from t1 , ((select a,b from t2 where a=c group by c))q where t1.a=q.a; + +--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; +select * from t1 , ((select a,b from t2 where b=c group by c))q where t1.a=q.a; + +--echo # rows should not be 1 for the derived table as group by is not a prefix of the keyparts for ref access +explain select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +select * from t1 , ((select a,b,c from t2 group by a))q where t1.a=q.b and t1.b=q.c; +drop table t1,t2,t3; + diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 8086c4480f6..2f46e7eeeed 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -1551,7 +1551,7 @@ a b max_c avg_c a b c d explain select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 2 +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 1 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort explain format=json select * from v1,t2 where (v1.a=v1.b) and (v1.a=t2.a); EXPLAIN @@ -1573,7 +1573,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -1839,7 +1839,7 @@ explain select * from v_decimal as v,t2_decimal as t where (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 6 test.t.b,test.t.b 2 +1 PRIMARY <derived2> ref key0 key0 6 test.t.b,test.t.b 1 2 DERIVED t1_decimal ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort explain format=json select * from v_decimal as v,t2_decimal as t where (v.a=v.b) and (v.b=t.b) and ((t.b>1) or (v.a=1)); @@ -1862,7 +1862,7 @@ EXPLAIN "key_length": "6", "used_key_parts": ["a", "b"], "ref": ["test.t.b", "test.t.b"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -2157,7 +2157,7 @@ explain select * from v_double as v,t2_double as t where (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1)); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 18 test.t.c,test.t.c 2 Using where +1 PRIMARY <derived2> ref key0 key0 18 test.t.c,test.t.c 1 Using where 2 DERIVED t1_double ALL NULL NULL NULL NULL 9 Using where; Using temporary; Using filesort explain format=json select * from v_double as v,t2_double as t where (v.b=v.c) and (v.c=t.c) and ((t.c>10) or (v.a=1)); @@ -2180,7 +2180,7 @@ EXPLAIN "key_length": "18", "used_key_parts": ["b", "c"], "ref": ["test.t.c", "test.t.c"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "t.c > 10 or v.a = 1", "materialized": { @@ -2380,7 +2380,7 @@ where t1.a>5 group by a,b having max_c < 707) v1, t2 where (v1.a=t2.a) and (v1.max_c>300) and (v1.a=v1.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort explain format=json select * from (select a, b, max(c) as max_c, avg(c) as avg_c from t1 @@ -2405,7 +2405,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.max_c > 300", "materialized": { @@ -2506,7 +2506,7 @@ a b max_c avg_c a b c d explain select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.b 2 +1 PRIMARY <derived2> ref key0 key0 10 test.t2.a,test.t2.b 1 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using temporary; Using filesort explain format=json select * from v1,t2 where (v1.a=t2.a) and (v1.b=t2.b); EXPLAIN @@ -2528,7 +2528,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -3204,7 +3204,7 @@ explain select * from v1,v2,t2 where (v1.a=t2.a) and (v1.a=v1.b) and (v1.a=v2.a) and (v2.max_c<300); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 9 Using where -1 PRIMARY <derived2> ref key1 key1 10 test.t2.a,test.t2.a 2 +1 PRIMARY <derived2> ref key1 key1 10 test.t2.a,test.t2.a 1 1 PRIMARY <derived3> ref key0 key0 5 test.t2.a 2 Using where 3 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort @@ -3229,7 +3229,7 @@ EXPLAIN "key_length": "10", "used_key_parts": ["a", "b"], "ref": ["test.t2.a", "test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -7207,7 +7207,7 @@ SELECT d FROM v4 WHERE s > a id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(t1) -3 DEPENDENT SUBQUERY <derived5> index_subquery key0 key0 5 func 2 Using where +3 DEPENDENT SUBQUERY <derived5> index_subquery key0 key0 5 func 1 Using where 5 DERIVED t4 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort explain format=json SELECT * FROM t1 WHERE a IN ( SELECT b FROM v2 WHERE b < a OR b IN ( @@ -7245,7 +7245,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["d"], "ref": ["func"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8514,7 +8514,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8564,7 +8564,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8848,9 +8848,21 @@ EXPLAIN "query_block": { "select_id": 1, "table": { - "table_name": "<derived2>", + "table_name": "t1", "access_type": "ALL", - "rows": 3, + "rows": 4, + "filtered": 100, + "attached_condition": "t1.id2 is not null" + }, + "table": { + "table_name": "<derived2>", + "access_type": "ref", + "possible_keys": ["key0"], + "key": "key0", + "key_length": "5", + "used_key_parts": ["id2"], + "ref": ["test.t1.id2"], + "rows": 1, "filtered": 100, "attached_condition": "vc.ct > 0", "materialized": { @@ -8870,18 +8882,6 @@ EXPLAIN } } } - }, - "block-nl-join": { - "table": { - "table_name": "t1", - "access_type": "ALL", - "rows": 4, - "filtered": 100 - }, - "buffer_type": "flat", - "buffer_size": "256Kb", - "join_type": "BNL", - "attached_condition": "t1.id2 = vc.id2" } } } @@ -14986,7 +14986,7 @@ on t1.a=t.a where t1.b < 3; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 range idx_b idx_b 5 NULL 4 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 2 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`c`) AS `m` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` < 3 @@ -15019,7 +15019,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15096,7 +15096,7 @@ on t1.a=t.a where t1.b <= 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 9 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 2 DERIVED t2 ALL idx_a NULL NULL NULL 90 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`s` AS `s`,`t`.`m` AS `m` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`a` AS `a`,sum(`test`.`t2`.`b`) AS `s`,min(`test`.`t2`.`b`) AS `m` from `test`.`t2` group by `test`.`t2`.`a`) `t` where `t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`b` <= 5 @@ -15125,7 +15125,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 9, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15200,7 +15200,7 @@ from t1 left join on t1.a=t.a; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 4 100.00 -1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 100.00 Using where 2 LATERAL DERIVED t2 ref idx_a idx_a 5 test.t1.a 2 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t1` left join (/* select#2 */ select `test`.`t2`.`a` AS `a`,max(`test`.`t2`.`b`) AS `max`,min(`test`.`t2`.`b`) AS `min` from `test`.`t2` where `test`.`t2`.`a` = `test`.`t1`.`a` group by `test`.`t2`.`a`) `t` on(`t`.`a` = `test`.`t1`.`a` and `test`.`t1`.`a` is not null) where 1 @@ -15227,7 +15227,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "trigcond(trigcond(t1.a is not null))", "materialized": { @@ -15289,7 +15289,7 @@ on t3.a=t.a and t3.c=t.c where t3.b > 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 range idx_b idx_b 5 NULL 3 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15 @@ -15322,7 +15322,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15367,7 +15367,7 @@ on t3.a=t.a and t3.c=t.c where t3.b <= 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 DERIVED t4 ALL idx NULL NULL NULL 40 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`a`,`test`.`t4`.`c`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15 @@ -15396,7 +15396,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 4, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15441,7 +15441,7 @@ on t3.a=t.a and t3.c=t.c where t3.b > 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 range idx_b idx_b 5 NULL 3 100.00 Using index condition; Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx idx 133 test.t3.a,test.t3.c 1 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`a` = `test`.`t3`.`a` and `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` > 15 @@ -15474,7 +15474,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15519,7 +15519,7 @@ on t3.a=t.a and t3.c=t.c where t3.b <= 15; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL idx_b NULL NULL NULL 12 75.00 Using where -1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 4 100.00 +1 PRIMARY <derived2> ref key0 key0 133 test.t3.a,test.t3.c 1 100.00 2 DERIVED t4 ALL idx NULL NULL NULL 40 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t3`.`a` AS `a`,`test`.`t3`.`c` AS `c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t3` join (/* select#2 */ select `test`.`t4`.`a` AS `a`,`test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`,`test`.`t4`.`a`) `t` where `t`.`a` = `test`.`t3`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t3`.`b` <= 15 @@ -15548,7 +15548,7 @@ EXPLAIN "key_length": "133", "used_key_parts": ["a", "c"], "ref": ["test.t3.a", "test.t3.c"], - "rows": 4, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15603,7 +15603,7 @@ where t2.b between 80 and 85 and t2.c in ('y','z') and t2.a=t3.a and t3.c=t.c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 range idx idx 133 NULL 2 100.00 Using index condition; Using where 1 PRIMARY t3 ref idx_a idx_a 5 test.t2.a 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 2 100.00 +1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 1 100.00 2 LATERAL DERIVED t4 ref idx_c idx_c 128 test.t3.c 3 100.00 Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` where `test`.`t4`.`c` = `test`.`t3`.`c` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` between 80 and 85 and `test`.`t2`.`c` in ('y','z') @@ -15646,7 +15646,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t3.c"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -15750,7 +15750,7 @@ where t2.b < 40 and t2.a=t3.a and t3.c=t.c; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 90 100.00 Using where 1 PRIMARY t3 ref idx_a idx_a 5 test.t2.a 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 10 100.00 +1 PRIMARY <derived2> ref key0 key0 128 test.t3.c 1 100.00 2 DERIVED t4 ALL idx_c NULL NULL NULL 160 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`a` AS `a`,`test`.`t2`.`b` AS `b`,`test`.`t2`.`c` AS `c`,`t`.`c` AS `t_c`,`t`.`max` AS `max`,`t`.`min` AS `min` from `test`.`t2` join `test`.`t3` join (/* select#2 */ select `test`.`t4`.`c` AS `c`,max(`test`.`t4`.`b`) AS `max`,min(`test`.`t4`.`b`) AS `min` from `test`.`t4` group by `test`.`t4`.`c`) `t` where `test`.`t3`.`a` = `test`.`t2`.`a` and `t`.`c` = `test`.`t3`.`c` and `test`.`t2`.`b` < 40 @@ -15788,7 +15788,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t3.c"], - "rows": 10, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -16344,7 +16344,7 @@ a c explain extended SELECT * FROM t4 WHERE c IN ( SELECT c FROM v1 ) and a < 2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t4 range a a 5 NULL 1 100.00 Using index condition; Using where -1 PRIMARY <derived3> ref key0 key0 128 test.t4.c 2 100.00 FirstMatch(t4) +1 PRIMARY <derived3> ref key0 key0 128 test.t4.c 1 100.00 FirstMatch(t4) 3 LATERAL DERIVED t3 ref c c 128 test.t4.c 2 100.00 3 LATERAL DERIVED <subquery4> eq_ref distinct_key distinct_key 4 func 1 100.00 4 MATERIALIZED t1 ALL NULL NULL NULL NULL 3 100.00 @@ -16376,7 +16376,7 @@ EXPLAIN "key_length": "128", "used_key_parts": ["c"], "ref": ["test.t4.c"], - "rows": 2, + "rows": 1, "filtered": 100, "first_match": "t4", "materialized": { @@ -16498,7 +16498,7 @@ pk1 f pk2 cnt EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN v2 ON pk1 = pk2 WHERE f <> 5; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL PRIMARY NULL NULL NULL 2 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 4 test.t1.pk1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 4 test.t1.pk1 1 100.00 2 LATERAL DERIVED t2 eq_ref PRIMARY PRIMARY 4 test.t1.pk1 1 100.00 Using index Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`f` AS `f`,`v2`.`pk2` AS `pk2`,`v2`.`cnt` AS `cnt` from `test`.`t1` join `test`.`v2` where `v2`.`pk2` = `test`.`t1`.`pk1` and `test`.`t1`.`f` <> 5 @@ -16523,7 +16523,7 @@ EXPLAIN "key_length": "4", "used_key_parts": ["pk2"], "ref": ["test.t1.pk1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -16737,7 +16737,7 @@ left join on u.id=auditlastlogin.userid; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY u ALL NULL NULL NULL NULL 2 -1 PRIMARY <derived2> ref key0 key0 5 test.u.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.u.id 1 2 DERIVED au ALL NULL NULL NULL NULL 4 Using temporary; Using filesort select * from t1 as u left join diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result index 48ac7e62653..40e034e5c61 100644 --- a/mysql-test/main/derived_opt.result +++ b/mysql-test/main/derived_opt.result @@ -535,7 +535,7 @@ ON t2.id=t.id WHERE t2.id < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 3 Using index condition -1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using temporary; Using filesort set join_cache_level=default; set optimizer_switch= @save_optimizer_switch; diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index b9ed016429b..6625bd7df0d 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -16,7 +16,7 @@ EXPLAIN SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 index c1,n1_c1_n2 n1_c1_n2 9 NULL 2 Using where; Using index -1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 2 +1 PRIMARY <derived2> ref key0 key0 8 test.t1.n1,test.t1.n2 1 2 LATERAL DERIVED t1 ref c1,n1_c1_n2 n1_c1_n2 4 test.t1.n1 1 Using where; Using index SELECT t1.n1 FROM t1, (SELECT n1, n2 FROM t1 WHERE c1 = 'a' GROUP BY n1) as t WHERE t.n1 = t1.n1 AND t.n2 = t1.n2 AND c1 = 'a' GROUP BY n1; @@ -95,7 +95,7 @@ ON t2.id=t.id WHERE t2.id < 3; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 range PRIMARY PRIMARY 4 NULL 2 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.id 1 2 LATERAL DERIVED t1 eq_ref PRIMARY PRIMARY 4 test.t2.id 1 set join_cache_level=default; DROP TABLE t1,t2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 30831e75341..242f8514bf4 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -213,7 +213,7 @@ explain extended select * from t1 join (select * from t2 group by f2) tt on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`tt`.`f2` AS `f2`,`tt`.`f22` AS `f22` from `test`.`t1` join (/* select#2 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`t2` group by `test`.`t2`.`f2`) `tt` where `tt`.`f2` = `test`.`t1`.`f1` @@ -227,7 +227,7 @@ flush status; explain select * from t1 join (select * from t2 group by f2) tt on f1=f2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort show status like 'Handler_read%'; Variable_name Value @@ -287,7 +287,7 @@ explain showing created indexes explain extended select * from t1 join v2 on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 100.00 2 DERIVED t2 ALL NULL NULL NULL NULL 11 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`f1` AS `f1`,`test`.`t1`.`f11` AS `f11`,`v2`.`f2` AS `f2`,`v2`.`f22` AS `f22` from `test`.`t1` join `test`.`v2` where `v2`.`f2` = `test`.`t1`.`f1` @@ -338,7 +338,7 @@ flush status; explain select * from t1 join v2 on f1=f2; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort show status like 'Handler_read%'; Variable_name Value @@ -371,7 +371,7 @@ Handler_read_rnd_next 36 explain extended select * from v1 join v4 on f1=f2; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.f2 2 100.00 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.f2 1 100.00 2 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v1`.`f1` AS `f1`,`v1`.`f11` AS `f11`,`test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22` from `test`.`v1` join `test`.`t2` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` in (2,3) @@ -395,7 +395,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["test.t2.f2"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -529,7 +529,7 @@ join on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived5> ref key0 key0 5 tt.f1 2 100.00 +1 PRIMARY <derived5> ref key0 key0 5 tt.f1 1 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 3 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: @@ -575,7 +575,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["tt.f1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -652,7 +652,7 @@ join on x.f1 = z.f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived4> ref key0 key0 5 x.f1 2 100.00 +1 PRIMARY <derived4> ref key0 key0 5 x.f1 1 100.00 4 DERIVED <derived5> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort 2 DERIVED <derived3> ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort @@ -719,7 +719,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["x.f1"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -863,7 +863,7 @@ join of above two explain extended select * from v6 join v7 on f2=f1; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 11 100.00 Using where -1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 2 100.00 +1 PRIMARY <derived5> ref key0 key0 5 test.t2.f2 1 100.00 5 DERIVED t1 ALL NULL NULL NULL NULL 11 100.00 Using where; Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `test`.`t2`.`f2` AS `f2`,`test`.`t2`.`f22` AS `f22`,`v1`.`f1` AS `f1`,`v1`.`f11` AS `f11` from `test`.`t2` join `test`.`v1` where `v1`.`f1` = `test`.`t2`.`f2` and `test`.`t2`.`f2` < 7 and `test`.`t2`.`f2` in (2,3) @@ -887,7 +887,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["f1"], "ref": ["test.t2.f2"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -917,7 +917,7 @@ test two keys explain select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 11 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t1.f1 1 1 PRIMARY xx ALL NULL NULL NULL NULL 11 Using where; Using join buffer (flat, BNL join) 2 DERIVED t2 ALL NULL NULL NULL NULL 11 Using temporary; Using filesort select * from t1 join (select * from t2 group by f2) tt on t1.f1=tt.f2 join t1 xx on tt.f22=xx.f1; @@ -1106,7 +1106,7 @@ SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 system NULL NULL NULL NULL 1 1 PRIMARY t2 ref a a 4 const 1 Using index -1 PRIMARY <derived2> ref key0 key0 8 const,const 1 +1 PRIMARY <derived2> ref key1 key1 8 func,func 1 2 DERIVED t3 ALL NULL NULL NULL NULL 12 Using temporary; Using filesort SELECT * FROM t1, t2, v1 WHERE t2.a=t1.a AND t2.a=v1.a AND t2.a=v1.b; a a a b @@ -1139,7 +1139,7 @@ SELECT * FROM t3 WHERE t3.a IN (SELECT v1.a FROM v1, t2 WHERE t2.a = v1.b); id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY t3 ALL NULL NULL NULL NULL 4 100.00 Using where -2 DEPENDENT SUBQUERY <derived3> ref key1 key1 5 func 2 100.00 +2 DEPENDENT SUBQUERY <derived3> ref key1 key1 5 func 1 100.00 2 DEPENDENT SUBQUERY t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join) 3 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using temporary; Using filesort Warnings: @@ -1505,7 +1505,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b @@ -1932,7 +1932,7 @@ EXPLAIN SELECT v1.a FROM v1,v2 WHERE v2.b = v1.b ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 Using where; Using filesort -1 PRIMARY <derived3> ref key0 key0 4 v1.b 2 +1 PRIMARY <derived3> ref key0 key0 4 v1.b 1 3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort DROP VIEW v1,v2; diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index fde6e0fec6b..23396d22876 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5197,7 +5197,7 @@ SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary -1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 2 End temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); @@ -5208,8 +5208,8 @@ EXPLAIN SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY <derived2> ALL NULL NULL NULL NULL 3 -1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary; End temporary; Using join buffer (flat, BNL join) +1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where; Start temporary +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 End temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 3 Using temporary SELECT * FROM (SELECT DISTINCT * FROM t1) t WHERE t.a IN (SELECT t2.a FROM t2); diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 00907235ecc..69f4be54414 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -140,7 +140,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 268 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort @@ -161,7 +161,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier ALL PRIMARY NULL NULL NULL 10 100.00 Using filesort -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 268 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 268 100.00 Using where; Using temporary; Using filesort diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 93917065722..be5348a2a15 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -143,7 +143,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 229 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort @@ -164,7 +164,7 @@ and total_revenue = (select max(total_revenue) from revenue0) order by s_suppkey; id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY supplier index PRIMARY PRIMARY 4 NULL 10 100.00 -1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 10 100.00 Using where +1 PRIMARY <derived3> ref key0 key0 5 dbt3_s001.supplier.s_suppkey 1 100.00 Using where 3 DERIVED lineitem range i_l_shipdate,i_l_suppkey i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort 2 SUBQUERY <derived4> ALL NULL NULL NULL NULL 228 100.00 4 DERIVED lineitem range i_l_shipdate i_l_shipdate 4 NULL 229 100.00 Using where; Using temporary; Using filesort diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result index dbcf00268c2..6bcea113759 100644 --- a/mysql-test/main/subselect_extra.result +++ b/mysql-test/main/subselect_extra.result @@ -389,7 +389,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result index 49a1431eb9b..7e257cb9291 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.result +++ b/mysql-test/main/subselect_extra_no_semijoin.result @@ -391,7 +391,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v1); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 2 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 1 Using where 3 DERIVED t2 ALL NULL NULL NULL NULL 6 Using temporary; Using filesort SELECT * FROM v2; a b diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index c52005e7683..aa645ac00ca 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -7617,6 +7617,68 @@ Item *st_select_lex::build_cond_for_grouping_fields(THD *thd, Item *cond, } +/** + Check if any any item in the group by list is also present in the select_list + @retval true: All elements common between select and group by list +*/ + +void st_select_lex::is_group_by_prefix(KEY *keyinfo) +{ + uint key_parts= keyinfo->usable_key_parts; + KEY_PART_INFO *key_part_info= keyinfo->key_part; + bool found= FALSE; + + if (key_parts < group_list.elements) + return; + + uint matched_fields=0, i, j; + Item *item; + + for (i= 0; i < key_parts; key_part_info++, i++) + { + uint fld_idx= key_part_info->fieldnr - 1; + item= join->fields_list.elem(fld_idx); + for (ORDER *order= group_list.first; order; order= order->next) + { + Item *ord_item= order->item[0]->real_item(); + Item_equal *item_equal= ord_item->get_item_equal(); + + if (item_equal) + { + Item_equal_fields_iterator it(*item_equal); + Item *equal_item; + while ((equal_item= it++)) + { + if (equal_item->eq(item, 0)) + { + matched_fields++; + found= TRUE; + break; + } + } + } + else + { + if (item->eq(ord_item, 0)) + { + matched_fields++; + found= TRUE; + } + } + if (found) + break; + } + + if (matched_fields == group_list.elements) + { + for (j=matched_fields - 1; j < key_parts; j++) + keyinfo->rec_per_key[j]= 1; + return; + } + found= FALSE; + } +} + int set_statement_var_if_exists(THD *thd, const char *var_name, size_t var_name_length, ulonglong value) { diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 4eaec7d062b..6f5d289f3be 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1387,6 +1387,7 @@ class st_select_lex: public st_select_lex_node bool cond_pushdown_is_allowed() const { return !olap && !explicit_limit && !tvc; } + void is_group_by_prefix(KEY *keyinfo); private: bool m_non_agg_field_used; diff --git a/sql/table.cc b/sql/table.cc index c4494c9ae4b..a4d050748ba 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7287,6 +7287,9 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, derived->check_distinct_in_union()) keyinfo->rec_per_key[key_parts - 1]= 1; } + + if (!first->is_part_of_union() && first->group_list.elements) + first->is_group_by_prefix(keyinfo); } set_if_bigger(s->max_key_length, keyinfo->key_length);

2 1

[Commits] b9dd8f8: MDEV-19324 Wrong results from query, using brackets with ORDER BY ..LIMIT
by IgorBabaev 27 Apr '19

27 Apr '19

revision-id: b9dd8f8fbd7eb63970e372fb6c3d34595022ade0 (mariadb-10.4.4-37-gb9dd8f8) parent(s): baadbe96019b205164167928d80e836ebbb6bcfe author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-26 17:55:12 -0700 message: MDEV-19324 Wrong results from query, using brackets with ORDER BY ..LIMIT If a select query was of the form (SELECT ... ORDER BY ...) LIMIT ... then in most cases it returned incorrect result. It happened because SELECT ... ORDER BY ... was wrapped into a select with materialized derived table: SELECT ... ORDER BY ... => SELECT * FROM (SELECT ... ORDER BY ...) dt. Yet for any materialized derived table ORDER BY without LIMIT is ignored. This patch resolves the problem by the conversion (SELECT ... ORDER BY ...) LIMIT ... => SELECT ... ORDER BY ... LIMIT ... at the parser stage. Similarly ((SELECT ... UNION ...) ORDER BY ...) LIMIT ... is converted to (SELECT ... UNION ...) ORDER BY ... LIMIT ... This conversion optimizes execution of the query because the result of (SELECT ... UNION ...) ORDER BY ... is not materialized into a temporary table anymore. --- mysql-test/main/brackets.result | 150 ++++++++++++++++++++++++++++++++++++++++ mysql-test/main/brackets.test | 33 +++++++++ sql/sql_lex.cc | 13 +++- 3 files changed, 194 insertions(+), 2 deletions(-) diff --git a/mysql-test/main/brackets.result b/mysql-test/main/brackets.result index e789cde..3cf3468 100644 --- a/mysql-test/main/brackets.result +++ b/mysql-test/main/brackets.result @@ -243,4 +243,154 @@ a a 1 DROP TABLE t1,t2; +# +# MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n +# +create table t1 (a int); +insert into t1 values (10),(20),(30); +select a from t1 order by a desc limit 1; +a +30 +explain extended select a from t1 order by a desc limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1 +explain format=json select a from t1 order by a desc limit 1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } +} +(select a from t1 order by a desc) limit 1; +a +30 +explain extended (select a from t1 order by a desc) limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 100.00 Using filesort +Warnings: +Note 1003 (select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `test`.`t1`.`a` desc limit 1) +explain format=json (select a from t1 order by a desc) limit 1; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "read_sorted_file": { + "filesort": { + "sort_key": "t1.a desc", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } +} +(select a from t1 where a=20 union select a from t1) order by a desc limit 1; +a +30 +explain extended (select a from t1 where a=20 union select a from t1) order by a desc limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20 union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 +explain format=json (select a from t1 where a=20 union select a from t1) order by a desc limit 1; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a = 20" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + ] + } + } +} +((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +a +30 +explain extended ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +2 UNION t1 ALL NULL NULL NULL NULL 3 100.00 +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 20) union /* select#2 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` order by `a` desc limit 1 +explain format=json ((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +EXPLAIN +{ + "query_block": { + "union_result": { + "table_name": "<union1,2>", + "access_type": "ALL", + "query_specifications": [ + { + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100, + "attached_condition": "t1.a = 20" + } + } + }, + { + "query_block": { + "select_id": 2, + "operation": "UNION", + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + ] + } + } +} +drop table t1; # End of 10.4 tests diff --git a/mysql-test/main/brackets.test b/mysql-test/main/brackets.test index 0eaa3bf..54f7d27 100644 --- a/mysql-test/main/brackets.test +++ b/mysql-test/main/brackets.test @@ -106,5 +106,38 @@ INSERT INTO t2 VALUES (4),(5),(6),(7); DROP TABLE t1,t2; +--echo # +--echo # MDEV-19324: ((SELECT ...) ORDER BY col ) LIMIT n +--echo # + +create table t1 (a int); +insert into t1 values (10),(20),(30); + +let $q1= +select a from t1 order by a desc limit 1; +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +let $q2= +(select a from t1 order by a desc) limit 1; +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +let $q1= +(select a from t1 where a=20 union select a from t1) order by a desc limit 1; +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +let $q2= +((select a from t1 where a=20 union select a from t1) order by a desc) limit 1; +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +drop table t1; + --echo # End of 10.4 tests diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index 8ea0bc5..ee483ac 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -9111,7 +9111,13 @@ SELECT_LEX *LEX::parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l) l->set_to(unit->fake_select_lex); else { - sel= wrap_unit_into_derived(unit); + if (!l->order_list && !unit->fake_select_lex->explicit_limit) + { + sel= unit->fake_select_lex; + l->order_list= &sel->order_list; + } + else + sel= wrap_unit_into_derived(unit); if (!sel) return NULL; l->set_to(sel); @@ -9126,7 +9132,10 @@ SELECT_LEX *LEX::parsed_select(SELECT_LEX *sel, Lex_order_limit_lock * l) SELECT_LEX_UNIT *unit= create_unit(sel); if (!unit) return NULL; - sel= wrap_unit_into_derived(unit); + if (!l->order_list && !sel->explicit_limit) + l->order_list= &sel->order_list; + else + sel= wrap_unit_into_derived(unit); if (!sel) return NULL; l->set_to(sel);

1 0

[Commits] 3032cd8e91f: step #1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the
by Varun 26 Apr '19

26 Apr '19

revision-id: 3032cd8e91f1e1ead8b6f941e75cd29e473e7eaa (mariadb-10.3.10-283-g3032cd8e91f) parent(s): f4019f5b3544a18f3ddf32df2c5214c3f8dabdce author: Varun Gupta committer: Varun Gupta timestamp: 2019-04-22 18:19:25 +0530 message: step #1: if a derived table has SELECT DISTINCT, provide index statistics for it so that the join optimizer in the upper select knows that ref access to the table will produce one row. --- mysql-test/main/cte_nonrecursive.result | 8 ++-- mysql-test/main/derived.result | 54 ++++++++++++++++++++++ mysql-test/main/derived.test | 30 ++++++++++++ mysql-test/main/derived_view.result | 2 +- mysql-test/main/subselect_extra.result | 2 +- mysql-test/main/subselect_extra_no_semijoin.result | 2 +- sql/sql_lex.h | 1 + sql/sql_union.cc | 40 ++++++++++++++++ sql/table.cc | 20 ++++++++ 9 files changed, 152 insertions(+), 7 deletions(-) diff --git a/mysql-test/main/cte_nonrecursive.result b/mysql-test/main/cte_nonrecursive.result index b846ec2d8ac..d80d34ecc7f 100644 --- a/mysql-test/main/cte_nonrecursive.result +++ b/mysql-test/main/cte_nonrecursive.result @@ -244,7 +244,7 @@ with t as (select distinct a from t1 where b >= 'c') select * from t as r1, t as r2 where r1.a=r2.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary explain @@ -253,7 +253,7 @@ select * from (select distinct a from t1 where b >= 'c') as r1, where r1.a=r2.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 8 Using where -1 PRIMARY <derived3> ref key0 key0 5 r1.a 2 +1 PRIMARY <derived3> ref key0 key0 5 r1.a 1 3 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where; Using temporary # two references to t specified by a query @@ -369,7 +369,7 @@ select c as a from t2 where c < 4) select * from t2,t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -381,7 +381,7 @@ select c as a from t2 where c < 4) as t where t2.c=t.a; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 ALL NULL NULL NULL NULL 4 Using where -1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.c 1 2 DERIVED t1 ALL NULL NULL NULL NULL 8 Using where 3 UNION t2 ALL NULL NULL NULL NULL 4 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/main/derived.result b/mysql-test/main/derived.result index f0d0289c1ce..857246d68b4 100644 --- a/mysql-test/main/derived.result +++ b/mysql-test/main/derived.result @@ -1195,3 +1195,57 @@ drop table t1,t2,t3; # # End of 10.2 tests # +# +# MDEV-9959: A serious MariaDB server performance bug +# +create table t1(a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); +table "<derived2>" should have type=ref and rows=1 +one select in derived table +with distinct +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort +analyze select * from t1 , ((select distinct t2.a, t2.b from t2 order by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 2 1.00 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using temporary; Using filesort +# multiple selects in derived table +# NO UNION ALL +analyze select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 1.00 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL 6.00 NULL NULL +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; +a a +1 1 +2 2 +3 3 +4 4 +5 5 +6 6 +# UNION ALL and EXCEPT +analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; +id select_type table type possible_keys key key_len ref rows r_rows filtered r_filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t1.a 1 0.50 100.00 100.00 +2 DERIVED t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +3 UNION t2 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +4 EXCEPT t3 ALL NULL NULL NULL NULL 6 6.00 100.00 100.00 +NULL UNIT RESULT <unit2,3,4> ALL NULL NULL NULL NULL NULL 3.00 NULL NULL +select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; +a a +3 3 +4 4 +6 6 +drop table t1,t2,t3; diff --git a/mysql-test/main/derived.test b/mysql-test/main/derived.test index 6c51f23c51e..990f955450a 100644 --- a/mysql-test/main/derived.test +++ b/mysql-test/main/derived.test @@ -1032,3 +1032,33 @@ drop table t1,t2,t3; --echo # --echo # End of 10.2 tests --echo # + +--echo # +--echo # MDEV-9959: A serious MariaDB server performance bug +--echo # + +create table t1(a int); +insert into t1 values (1),(2),(3),(4),(5),(6); +create table t2(a int, b int,c int); +insert into t2(a,b,c) values (1,1,2),(2,2,3),(3,1,4),(4,2,2),(5,1,1),(6,2,5); +create table t3(a int, b int); +insert into t3(a,b) values (1,1),(2,2),(2,1),(1,2),(5,1),(9,2); + +--echo table "<derived2>" should have type=ref and rows=1 +--echo one select in derived table + +--echo with distinct +analyze select * from t1 , ((select distinct t2.a from t2 order by c))q where t1.a=q.a; +analyze select * from t1 , ((select distinct t2.a, t2.b from t2 order by c))q where t1.a=q.a; + +--echo # multiple selects in derived table +--echo # NO UNION ALL +analyze select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; +select * from t1 , ( (select t2.a from t2 order by c) union (select t2.a from t2 order by c))q where t1.a=q.a; + +--echo # UNION ALL and EXCEPT +analyze select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; + +select * from t1 , ( (select t2.a from t2 order by c) union all (select t2.a from t2 order by c) except(select t3.a from t3 order by b))q where t1.a=q.a; + +drop table t1,t2,t3; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 86dd73f5733..30831e75341 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1525,7 +1525,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result index a3a0f1f9a15..dbcf00268c2 100644 --- a/mysql-test/main/subselect_extra.result +++ b/mysql-test/main/subselect_extra.result @@ -409,7 +409,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 2 FirstMatch(t1) +1 PRIMARY <derived3> ref key0 key0 10 test.t1.a,test.t1.b 1 FirstMatch(t1) 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/mysql-test/main/subselect_extra_no_semijoin.result b/mysql-test/main/subselect_extra_no_semijoin.result index ec9ddb0452e..49a1431eb9b 100644 --- a/mysql-test/main/subselect_extra_no_semijoin.result +++ b/mysql-test/main/subselect_extra_no_semijoin.result @@ -411,7 +411,7 @@ EXPLAIN SELECT a FROM t1 WHERE (a,b) IN (SELECT * FROM v2); id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t1 ALL NULL NULL NULL NULL 2 Using where -2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 2 Using where +2 DEPENDENT SUBQUERY <derived3> index_subquery key0 key0 10 func,func 1 Using where 3 DERIVED t2 ALL NULL NULL NULL NULL 6 4 UNION t3 ALL NULL NULL NULL NULL 4 NULL UNION RESULT <union3,4> ALL NULL NULL NULL NULL NULL diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 72ca4ac0b43..4eaec7d062b 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -896,6 +896,7 @@ class st_select_lex_unit: public st_select_lex_node { bool union_needs_tmp_table(); void set_unique_exclude(); + bool check_distinct_in_union(); friend struct LEX; friend int subselect_union_engine::exec(); diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 87fbbebe4ba..3fb5552c77a 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -2049,3 +2049,43 @@ void st_select_lex_unit::set_unique_exclude() } } } + +/** + @brief + Check if the derived table is guaranteed to have distinct rows because of + UNION operations used to populate it. + + @detail + UNION operation removes duplicate rows from its output. That is, a query like + + select * from t1 UNION select * from t2 + + will not produce duplicate rows in its output, even if table t1 (and/or t2) + contain duplicate rows. EXCEPT and INTERSECT operations also have this + property. + + On the other hand, UNION ALL operation doesn't remove duplicates. (The SQL + standard also defines EXCEPT ALL and INTERSECT ALL, but we don't support + them). + + st_select_lex_unit computes its value left to right. That is, if there is + a st_select_lex_unit object describing + + (select #1) OP1 (select #2) OP2 (select #3) + + then ((select #1) OP1 (select #2)) is computed first, and OP2 is computed + second. + + How can one tell if st_select_lex_unit is guaranteed to have distinct + output rows? This depends on whether the last operation was duplicate- + removing or not: + - UNION ALL is not duplicate-removing + - all other operations are duplicate-removing +*/ + +bool st_select_lex_unit::check_distinct_in_union() +{ + if (union_distinct && !union_distinct->next_select()) + return true; + return false; +} diff --git a/sql/table.cc b/sql/table.cc index 80995abc1f9..c4494c9ae4b 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7269,6 +7269,26 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, key_part_info++; } + /* + For the case when there is a derived table that would give distinct rows, + the index statistics are passed to the join optimizer to tell that a ref + access to all the fields of the derived table will produce only one row. + */ + + st_select_lex_unit* derived= pos_in_table_list ? + pos_in_table_list->derived: NULL; + if (derived) + { + st_select_lex* first= derived->first_select(); + uint select_list_items= first->get_item_list()->elements; + if (key_parts == select_list_items) + { + if ((!first->is_part_of_union() && (first->options & SELECT_DISTINCT)) || + derived->check_distinct_in_union()) + keyinfo->rec_per_key[key_parts - 1]= 1; + } + } + set_if_bigger(s->max_key_length, keyinfo->key_length); s->keys++; return FALSE;

2 1

[Commits] 9a5a86f293b: MDEV-17260: Memory leaks in mysqlbinlog
by sujatha 26 Apr '19

26 Apr '19

revision-id: 9a5a86f293b6fe40ad606de43c04a2d8ba6b60b1 (mariadb-10.2.23-80-g9a5a86f293b) parent(s): 4e01bc8c963d9513625dd984cd1aca24b8a7b516 author: Sujatha Sivakumar committer: Sujatha Sivakumar timestamp: 2019-04-26 11:37:00 +0530 message: MDEV-17260: Memory leaks in mysqlbinlog Problem: ======== The mysqlbinlog tool is leaking memory, causing failures in various tests when compiling and testing with AddressSanitizer or LeakSanitizer like this: cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source make -j$(nproc) cd mysql-test ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto Analysis: ========= Two types of leaks were observed during above execution. 1) Leak in Log_event::read_log_event(char const*, unsigned int, char const**, Format_description_log_event const*, char) File: sql/log_event.cc:2150 For all row based replication events the memory which is allocated during read_log_event is not freed after the event is processed. The event specific memory has to be retained only when flashback option is enabled with mysqlbinlog tool. In this case all the events are retained till the end statement is received and they are processed in reverse order and they are destroyed. But in the existing code all events are retained irrespective of flashback mode. Hence the memory leaks are observed. 2) read_remote_annotate_event(unsigned char*, unsigned long, char const**) File: client/mysqlbinlog.cc:194 In general the Annotate event is not printed immediately because all subsequent rbr-events can be filtered away. Instead it will be printed together with the first not filtered away Table map or the last rbr will be processed. While reading remote annotate events memory is allocated for event buffer and event's temp_buf is made to point to the allocated buffer as shown below. The TRUE flag is used for doing proper cleanup using free_temp_buf(). i.e at the time of deletion of annotate event its destructor takes care of clearing the temp_buf. /* Ensure the event->temp_buf is pointing to the allocated buffer. (TRUE = free temp_buf on the event deletion) */ event->register_temp_buf((char*)event_buf, TRUE); But existing code does the following when it receives a remote annotate_event. if (remote_opt) ev->temp_buf= 0; That is code immediately sets temp_buf=0, because of which free_temp_buf() call will return empty handed as it has lost the reference to the allocated temporary buffer. This results in memory leak Fix: ==== 1) If not in flashback mode, destroy the memory for events once they are processed. 2) Remove the ev->temp_buf=0 code for remote option. Let the proper cleanup to be done as part of free_temp_buf(). --- client/mysqlbinlog.cc | 9 +++------ mysql-test/suite/binlog/r/flashback.result | 18 ++++++++++++++++++ mysql-test/suite/binlog/t/flashback.test | 30 +++++++++++++++++++++++++++++- 3 files changed, 50 insertions(+), 7 deletions(-) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index cfc05cbf794..3dad4fef67b 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info) if (annotate_event) { annotate_event->print(result_file, print_event_info); - delete annotate_event; // the event should not be printed more than once - annotate_event= 0; + free_annotate_event(); } } @@ -1465,7 +1464,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1478,7 +1477,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Old_rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1539,8 +1538,6 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, } } - if (remote_opt) - ev->temp_buf= 0; if (destroy_evt) /* destroy it later if not set (ignored table map) */ delete ev; } diff --git a/mysql-test/suite/binlog/r/flashback.result b/mysql-test/suite/binlog/r/flashback.result index c96eaebe838..0d189b735a3 100644 --- a/mysql-test/suite/binlog/r/flashback.result +++ b/mysql-test/suite/binlog/r/flashback.result @@ -674,6 +674,24 @@ world.city 563256876 DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; +# < CASE 7 > +# Test Case for MDEV-17260 +# +RESET MASTER; +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +# 6- Rows must be present +SELECT COUNT(*) FROM t1; +COUNT(*) +6 +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +# 0- Rows must be present +include/assert.inc [Table t1 should have 0 rows.] +# 6- Rows must be present upon restoring from flashback +include/assert.inc [Table t1 should have six rows.] +DROP TABLE t1; SET binlog_format=statement; Warnings: Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test index 3fc8c44c60c..9782fa4ec83 100644 --- a/mysql-test/suite/binlog/t/flashback.test +++ b/mysql-test/suite/binlog/t/flashback.test @@ -335,8 +335,36 @@ DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; -## Clear +--echo # < CASE 7 > +--echo # Test Case for MDEV-17260 +--echo # + +RESET MASTER; + +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +--echo # 6- Rows must be present +SELECT COUNT(*) FROM t1; +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +--echo # 0- Rows must be present +--let $assert_cond= COUNT(*) = 0 FROM t1 +--let $assert_text= Table t1 should have 0 rows. +--source include/assert.inc + +--exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql +--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" + +--echo # 6- Rows must be present upon restoring from flashback +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Table t1 should have six rows. +--source include/assert.inc + +DROP TABLE t1; + +## Clear SET binlog_format=statement; --error ER_FLASHBACK_NOT_SUPPORTED SET GLOBAL binlog_format=statement;

1 0

[Commits] 9a5a86f293b: MDEV-17260: Memory leaks in mysqlbinlog
by sujatha 26 Apr '19

26 Apr '19

revision-id: 9a5a86f293b6fe40ad606de43c04a2d8ba6b60b1 (mariadb-10.2.23-80-g9a5a86f293b) parent(s): 4e01bc8c963d9513625dd984cd1aca24b8a7b516 author: Sujatha Sivakumar committer: Sujatha Sivakumar timestamp: 2019-04-26 11:37:00 +0530 message: MDEV-17260: Memory leaks in mysqlbinlog Problem: ======== The mysqlbinlog tool is leaking memory, causing failures in various tests when compiling and testing with AddressSanitizer or LeakSanitizer like this: cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source make -j$(nproc) cd mysql-test ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto Analysis: ========= Two types of leaks were observed during above execution. 1) Leak in Log_event::read_log_event(char const*, unsigned int, char const**, Format_description_log_event const*, char) File: sql/log_event.cc:2150 For all row based replication events the memory which is allocated during read_log_event is not freed after the event is processed. The event specific memory has to be retained only when flashback option is enabled with mysqlbinlog tool. In this case all the events are retained till the end statement is received and they are processed in reverse order and they are destroyed. But in the existing code all events are retained irrespective of flashback mode. Hence the memory leaks are observed. 2) read_remote_annotate_event(unsigned char*, unsigned long, char const**) File: client/mysqlbinlog.cc:194 In general the Annotate event is not printed immediately because all subsequent rbr-events can be filtered away. Instead it will be printed together with the first not filtered away Table map or the last rbr will be processed. While reading remote annotate events memory is allocated for event buffer and event's temp_buf is made to point to the allocated buffer as shown below. The TRUE flag is used for doing proper cleanup using free_temp_buf(). i.e at the time of deletion of annotate event its destructor takes care of clearing the temp_buf. /* Ensure the event->temp_buf is pointing to the allocated buffer. (TRUE = free temp_buf on the event deletion) */ event->register_temp_buf((char*)event_buf, TRUE); But existing code does the following when it receives a remote annotate_event. if (remote_opt) ev->temp_buf= 0; That is code immediately sets temp_buf=0, because of which free_temp_buf() call will return empty handed as it has lost the reference to the allocated temporary buffer. This results in memory leak Fix: ==== 1) If not in flashback mode, destroy the memory for events once they are processed. 2) Remove the ev->temp_buf=0 code for remote option. Let the proper cleanup to be done as part of free_temp_buf(). --- client/mysqlbinlog.cc | 9 +++------ mysql-test/suite/binlog/r/flashback.result | 18 ++++++++++++++++++ mysql-test/suite/binlog/t/flashback.test | 30 +++++++++++++++++++++++++++++- 3 files changed, 50 insertions(+), 7 deletions(-) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index cfc05cbf794..3dad4fef67b 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info) if (annotate_event) { annotate_event->print(result_file, print_event_info); - delete annotate_event; // the event should not be printed more than once - annotate_event= 0; + free_annotate_event(); } } @@ -1465,7 +1464,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1478,7 +1477,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Old_rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1539,8 +1538,6 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, } } - if (remote_opt) - ev->temp_buf= 0; if (destroy_evt) /* destroy it later if not set (ignored table map) */ delete ev; } diff --git a/mysql-test/suite/binlog/r/flashback.result b/mysql-test/suite/binlog/r/flashback.result index c96eaebe838..0d189b735a3 100644 --- a/mysql-test/suite/binlog/r/flashback.result +++ b/mysql-test/suite/binlog/r/flashback.result @@ -674,6 +674,24 @@ world.city 563256876 DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; +# < CASE 7 > +# Test Case for MDEV-17260 +# +RESET MASTER; +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +# 6- Rows must be present +SELECT COUNT(*) FROM t1; +COUNT(*) +6 +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +# 0- Rows must be present +include/assert.inc [Table t1 should have 0 rows.] +# 6- Rows must be present upon restoring from flashback +include/assert.inc [Table t1 should have six rows.] +DROP TABLE t1; SET binlog_format=statement; Warnings: Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test index 3fc8c44c60c..9782fa4ec83 100644 --- a/mysql-test/suite/binlog/t/flashback.test +++ b/mysql-test/suite/binlog/t/flashback.test @@ -335,8 +335,36 @@ DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; -## Clear +--echo # < CASE 7 > +--echo # Test Case for MDEV-17260 +--echo # + +RESET MASTER; + +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +--echo # 6- Rows must be present +SELECT COUNT(*) FROM t1; +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +--echo # 0- Rows must be present +--let $assert_cond= COUNT(*) = 0 FROM t1 +--let $assert_text= Table t1 should have 0 rows. +--source include/assert.inc + +--exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql +--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" + +--echo # 6- Rows must be present upon restoring from flashback +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Table t1 should have six rows. +--source include/assert.inc + +DROP TABLE t1; + +## Clear SET binlog_format=statement; --error ER_FLASHBACK_NOT_SUPPORTED SET GLOBAL binlog_format=statement;

1 0

[Commits] 9a5a86f293b: MDEV-17260: Memory leaks in mysqlbinlog
by sujatha 26 Apr '19

26 Apr '19

revision-id: 9a5a86f293b6fe40ad606de43c04a2d8ba6b60b1 (mariadb-10.2.23-80-g9a5a86f293b) parent(s): 4e01bc8c963d9513625dd984cd1aca24b8a7b516 author: Sujatha Sivakumar committer: Sujatha Sivakumar timestamp: 2019-04-26 11:37:00 +0530 message: MDEV-17260: Memory leaks in mysqlbinlog Problem: ======== The mysqlbinlog tool is leaking memory, causing failures in various tests when compiling and testing with AddressSanitizer or LeakSanitizer like this: cmake -DCMAKE_BUILD_TYPE=Debug -DWITH_ASAN:BOOL=ON /path/to/source make -j$(nproc) cd mysql-test ASAN_OPTIONS=abort_on_error=1 ./mtr --parallel=auto Analysis: ========= Two types of leaks were observed during above execution. 1) Leak in Log_event::read_log_event(char const*, unsigned int, char const**, Format_description_log_event const*, char) File: sql/log_event.cc:2150 For all row based replication events the memory which is allocated during read_log_event is not freed after the event is processed. The event specific memory has to be retained only when flashback option is enabled with mysqlbinlog tool. In this case all the events are retained till the end statement is received and they are processed in reverse order and they are destroyed. But in the existing code all events are retained irrespective of flashback mode. Hence the memory leaks are observed. 2) read_remote_annotate_event(unsigned char*, unsigned long, char const**) File: client/mysqlbinlog.cc:194 In general the Annotate event is not printed immediately because all subsequent rbr-events can be filtered away. Instead it will be printed together with the first not filtered away Table map or the last rbr will be processed. While reading remote annotate events memory is allocated for event buffer and event's temp_buf is made to point to the allocated buffer as shown below. The TRUE flag is used for doing proper cleanup using free_temp_buf(). i.e at the time of deletion of annotate event its destructor takes care of clearing the temp_buf. /* Ensure the event->temp_buf is pointing to the allocated buffer. (TRUE = free temp_buf on the event deletion) */ event->register_temp_buf((char*)event_buf, TRUE); But existing code does the following when it receives a remote annotate_event. if (remote_opt) ev->temp_buf= 0; That is code immediately sets temp_buf=0, because of which free_temp_buf() call will return empty handed as it has lost the reference to the allocated temporary buffer. This results in memory leak Fix: ==== 1) If not in flashback mode, destroy the memory for events once they are processed. 2) Remove the ev->temp_buf=0 code for remote option. Let the proper cleanup to be done as part of free_temp_buf(). --- client/mysqlbinlog.cc | 9 +++------ mysql-test/suite/binlog/r/flashback.result | 18 ++++++++++++++++++ mysql-test/suite/binlog/t/flashback.test | 30 +++++++++++++++++++++++++++++- 3 files changed, 50 insertions(+), 7 deletions(-) diff --git a/client/mysqlbinlog.cc b/client/mysqlbinlog.cc index cfc05cbf794..3dad4fef67b 100644 --- a/client/mysqlbinlog.cc +++ b/client/mysqlbinlog.cc @@ -227,8 +227,7 @@ void print_annotate_event(PRINT_EVENT_INFO *print_event_info) if (annotate_event) { annotate_event->print(result_file, print_event_info); - delete annotate_event; // the event should not be printed more than once - annotate_event= 0; + free_annotate_event(); } } @@ -1465,7 +1464,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1478,7 +1477,7 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, if (print_row_event(print_event_info, ev, e->get_table_id(), e->get_flags(Old_rows_log_event::STMT_END_F))) goto err; - if (!is_stmt_end) + if (opt_flashback && !is_stmt_end) destroy_evt= FALSE; break; } @@ -1539,8 +1538,6 @@ Exit_status process_event(PRINT_EVENT_INFO *print_event_info, Log_event *ev, } } - if (remote_opt) - ev->temp_buf= 0; if (destroy_evt) /* destroy it later if not set (ignored table map) */ delete ev; } diff --git a/mysql-test/suite/binlog/r/flashback.result b/mysql-test/suite/binlog/r/flashback.result index c96eaebe838..0d189b735a3 100644 --- a/mysql-test/suite/binlog/r/flashback.result +++ b/mysql-test/suite/binlog/r/flashback.result @@ -674,6 +674,24 @@ world.city 563256876 DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; +# < CASE 7 > +# Test Case for MDEV-17260 +# +RESET MASTER; +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +# 6- Rows must be present +SELECT COUNT(*) FROM t1; +COUNT(*) +6 +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +# 0- Rows must be present +include/assert.inc [Table t1 should have 0 rows.] +# 6- Rows must be present upon restoring from flashback +include/assert.inc [Table t1 should have six rows.] +DROP TABLE t1; SET binlog_format=statement; Warnings: Warning 1105 MariaDB Galera and flashback do not support binlog format: STATEMENT diff --git a/mysql-test/suite/binlog/t/flashback.test b/mysql-test/suite/binlog/t/flashback.test index 3fc8c44c60c..9782fa4ec83 100644 --- a/mysql-test/suite/binlog/t/flashback.test +++ b/mysql-test/suite/binlog/t/flashback.test @@ -335,8 +335,36 @@ DROP TABLE test.test; DROP TABLE world.city; DROP DATABASE world; -## Clear +--echo # < CASE 7 > +--echo # Test Case for MDEV-17260 +--echo # + +RESET MASTER; + +CREATE TABLE t1 ( f INT PRIMARY KEY ) ENGINE=innodb; +INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6); +--echo # 6- Rows must be present +SELECT COUNT(*) FROM t1; +FLUSH LOGS; +DELETE FROM t1; +FLUSH LOGS; +--echo # 0- Rows must be present +--let $assert_cond= COUNT(*) = 0 FROM t1 +--let $assert_text= Table t1 should have 0 rows. +--source include/assert.inc + +--exec $MYSQL_BINLOG -vv -B --read-from-remote-server --user=root --host=127.0.0.1 --port=$MASTER_MYPORT master-bin.000002> $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql +--exec $MYSQL -e "SET binlog_format= ROW; source $MYSQLTEST_VARDIR/tmp/mysqlbinlog_row_flashback_7.sql;" + +--echo # 6- Rows must be present upon restoring from flashback +--let $assert_cond= COUNT(*) = 6 FROM t1 +--let $assert_text= Table t1 should have six rows. +--source include/assert.inc + +DROP TABLE t1; + +## Clear SET binlog_format=statement; --error ER_FLASHBACK_NOT_SUPPORTED SET GLOBAL binlog_format=statement;

1 0

[Commits] 0beb0ed: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(),
by IgorBabaev 26 Apr '19

26 Apr '19

revision-id: 0beb0edf54a889c4db29d052cd3ca14877302c0b (mariadb-10.3.12-86-g0beb0ed) parent(s): 51e48b9f8981986257a1cfbdf75e4fc29a5959c1 author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-25 20:27:24 -0700 message: MDEV-17894 Assertion `(thd->lex)->current_select' failed in MYSQLparse(), query with VALUES() A table value constructor can be used in all contexts where a select can be used. In particular an ORDER BY clause or a LIMIT clause or both of them can be attached to a table value constructor to produce a new query. Unfortunately execution of such queries was not supported. This patch fixes the problem. --- mysql-test/main/table_value_constr.result | 333 ++++++++++++++++++++++++++++++ mysql-test/main/table_value_constr.test | 149 +++++++++++++ sql/item_subselect.cc | 2 +- sql/item_subselect.h | 2 +- sql/sql_lex.cc | 16 ++ sql/sql_lex.h | 6 + sql/sql_tvc.cc | 203 +++++++++++++++--- sql/sql_tvc.h | 5 + sql/sql_union.cc | 19 +- sql/sql_yacc.yy | 11 +- sql/sql_yacc_ora.yy | 12 +- 11 files changed, 715 insertions(+), 43 deletions(-) diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index 1d485af..082f2b4 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2189,3 +2189,336 @@ EXECUTE stmt; 1 + 1 2 abc 2 2 abc DEALLOCATE PREPARE stmt; +# +# MDEV-17894: tvc with ORDER BY ... LIMIT +# +values (5), (7), (1), (3), (4) limit 2; +5 +5 +7 +explain extended values (5), (7), (1), (3), (4) limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 2 +values (5), (7), (1), (3), (4) limit 2 offset 1; +5 +7 +1 +explain extended values (5), (7), (1), (3), (4) limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 values (5),(7),(1),(3),(4) limit 1,2 +values (5), (7), (1), (3), (4) order by 1 limit 2; +5 +1 +3 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 2 +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +5 +3 +4 +explain extended values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 limit 1,2 +values (5), (7), (1), (3), (4) order by 1; +5 +1 +3 +4 +5 +7 +explain extended values (5), (7), (1), (3), (4) order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNIT RESULT <unit1> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 values (5),(7),(1),(3),(4) order by 1 +select 2 union (values (5), (7), (1), (3), (4) limit 2); +2 +2 +5 +7 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 2) +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +2 +2 +7 +1 +explain extended select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (values (5),(7),(1),(3),(4) limit 1,2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +2 +2 +1 +3 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +2 +2 +3 +4 +explain extended select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 2 AS `2` union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) +(values (5), (7), (1), (3), (4) limit 2) union select 2; +5 +5 +7 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +5 +7 +1 +2 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +5 +1 +3 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union /* select#2 */ select 2 AS `2` +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +5 +3 +4 +2 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,2) union /* select#2 */ select 2 AS `2` +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +3 +3 +3 +4 +explain extended select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (values (5),(7),(1),(3),(4) limit 3,2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +5 +3 +4 +3 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +3 +3 +1 +3 +explain extended select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select 3 AS `3` union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +5 +1 +3 +3 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) union all /* select#2 */ select 3 AS `3` +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +5 +7 +1 +1 +3 +explain extended ( values (5), (7), (1), (3), (4) limit 2 offset 1 ) +union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +3 UNION <derived2> ALL NULL NULL NULL NULL 5 100.00 Using filesort +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,3> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 1,2) union all (/* select#3 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 2) +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +5 +3 +3 +4 +explain extended (values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (values (5),(7),(1),(3),(4) limit 3,2) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +5 +3 +3 +4 +5 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +5 +3 +4 +explain extended (values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 +order by 1 limit 2 offset 1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SUBQUERY <derived3> ALL NULL NULL NULL NULL 5 100.00 Using filesort +3 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used +2 UNION NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL NULL Using filesort +Warnings: +Note 1003 (/* select#1 */ select `tvc_0`.`5` AS `5` from (values (5),(7),(1),(3),(4)) `tvc_0` order by 1 limit 1,3) union all /* select#2 */ select 3 AS `3` order by 1 limit 1,2 +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +2 +2 +5 +7 +execute stmt; +2 +2 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +2 +2 +1 +3 +execute stmt; +2 +2 +1 +3 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +3 +3 +5 +7 +execute stmt; +3 +3 +5 +7 +deallocate prepare stmt; +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +3 +3 +1 +3 +execute stmt; +3 +3 +1 +3 +deallocate prepare stmt; diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index 0dd0a7a..a6d0d47 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1123,3 +1123,152 @@ PREPARE stmt FROM "SELECT * FROM (VALUES(1 + 1,2,'abc')) t"; EXECUTE stmt; EXECUTE stmt; DEALLOCATE PREPARE stmt; + +--echo # +--echo # MDEV-17894: tvc with ORDER BY ... LIMIT +--echo # + +let $q= +values (5), (7), (1), (3), (4) limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +let $q= +values (5), (7), (1), (3), (4) order by 1; +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) limit 2 offset 1); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1); +eval $q; +eval explain extended $q; + + +let $q= +(values (5), (7), (1), (3), (4) limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union select 2; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2 offset 1) union select 2; +eval $q; +eval explain extended $q; + + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) limit 2 offset 3); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 2) union all select 3; +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +( values (5), (7), (1), (3), (4) limit 2 offset 1 ) + union all +( values (5), (7), (1), (3), (4) order by 1 limit 2 ); +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) limit 2 offset 3) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 order by 1; +eval $q; +eval explain extended $q; + +let $q= +(values (5), (7), (1), (3), (4) order by 1 limit 3 offset 1) union all select 3 + order by 1 limit 2 offset 1; +eval $q; +eval explain extended $q; + +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 2 union (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +prepare stmt from " +select 3 union all (values (5), (7), (1), (3), (4) order by 1 limit 2) +"; +execute stmt; +execute stmt; +deallocate prepare stmt; diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 207aa9a..475e74d 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -269,7 +269,7 @@ bool Item_subselect::fix_fields(THD *thd_param, Item **ref) { if (sl->tvc) { - wrap_tvc_in_derived_table(thd, sl); + wrap_tvc_into_select(thd, sl); } } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 363dbba..5a9968b 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -267,7 +267,7 @@ class Item_subselect :public Item_result_field, Item* build_clone(THD *thd) { return 0; } Item* get_copy(THD *thd) { return 0; } - bool wrap_tvc_in_derived_table(THD *thd, st_select_lex *tvc_sl); + bool wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl); friend class select_result_interceptor; friend class Item_in_optimizer; diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index d6cc62c..638530d 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2275,6 +2275,7 @@ void st_select_lex_unit::init_query() with_element= 0; columns_are_renamed= false; intersect_mark= NULL; + with_wrapped_tvc= false; } void st_select_lex::init_query() @@ -3411,6 +3412,19 @@ bool st_select_lex_unit::union_needs_tmp_table() { if (with_element && with_element->is_recursive) return true; + if (!with_wrapped_tvc) + { + for (st_select_lex *sl= first_select(); sl; sl=sl->next_select()) + { + if (sl->tvc && sl->tvc->to_be_wrapped_as_with_tail()) + { + with_wrapped_tvc= true; + break; + } + } + } + if (with_wrapped_tvc) + return true; return union_distinct != NULL || global_parameters()->order_list.elements != 0 || thd->lex->sql_command == SQLCOM_INSERT_SELECT || @@ -8236,6 +8250,8 @@ bool LEX::tvc_finalize() current_select->options)))) return true; many_values.empty(); + if (!current_select->master_unit()->fake_select_lex) + current_select->master_unit()->add_fake_select_lex(thd); return false; } diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 926b09e..4123275 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -806,6 +806,12 @@ class st_select_lex_unit: public st_select_lex_node { */ Item_int *intersect_mark; /** + TRUE if the unit contained TVC at the top level that has been wrapped + into SELECT: + VALUES (v1) ... (vn) => SELECT * FROM (VALUES (v1) ... (vn)) as tvc + */ + bool with_wrapped_tvc; + /** Pointer to 'last' select, or pointer to select where we stored global parameters for union. diff --git a/sql/sql_tvc.cc b/sql/sql_tvc.cc index a5085fd..c400264 100644 --- a/sql/sql_tvc.cc +++ b/sql/sql_tvc.cc @@ -344,6 +344,7 @@ bool table_value_constr::exec(SELECT_LEX *sl) DBUG_ENTER("table_value_constr::exec"); List_iterator_fast<List_item> li(lists_of_values); List_item *elem; + ha_rows send_records= 0; if (select_options & SELECT_DESCRIBE) DBUG_RETURN(false); @@ -357,7 +358,13 @@ bool table_value_constr::exec(SELECT_LEX *sl) while ((elem= li++)) { - result->send_data(*elem); + if (send_records >= sl->master_unit()->select_limit_cnt) + break; + int rc= result->send_data(*elem); + if (!rc) + send_records++; + else if (rc > 0) + DBUG_RETURN(true); } if (result->send_eof()) @@ -436,6 +443,12 @@ void table_value_constr::print(THD *thd, String *str, print_list_item(str, list, query_type); } + if (select_lex->order_list.elements) + { + str->append(STRING_WITH_LEN(" order by ")); + select_lex->print_order(str, select_lex->order_list.first, query_type); + } + select_lex->print_limit(thd, str, query_type); } @@ -533,7 +546,8 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select, char buff[6]; alias->length= my_snprintf(buff, sizeof(buff), - "tvc_%u", parent_select->curr_tvc_name); + "tvc_%u", + parent_select ? parent_select->curr_tvc_name : 0); alias->str= thd->strmake(buff, alias->length); if (!alias->str) return true; @@ -542,19 +556,57 @@ static bool create_tvc_name(THD *thd, st_select_lex *parent_select, } -bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, - st_select_lex *tvc_sl) +/** + @brief + Check whether TVC used in unit is to be wrapped into select + + @details + TVC used in unit that contains more than one members is to be wrapped + into select if it is tailed with ORDER BY ... LIMIT n [OFFSET m] + + @retval + true if TVC is to be wrapped + false otherwise +*/ + +bool table_value_constr::to_be_wrapped_as_with_tail() +{ + return select_lex->master_unit()->first_select()->next_select() && + select_lex->order_list.elements && select_lex->explicit_limit; +} + + +/** + @brief + Wrap table value constructor into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + @parent_select The parent select if tvc_sl used in a subquery + + @details + The function wraps the TVC tvc_sl into a select: + the function transforms the TVC of the form VALUES (v1), ... (vn) into + the select of the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + + @retval pointer to the result of of the transformation if successful + NULL - otherwise +*/ + +static +st_select_lex *wrap_tvc(THD *thd, st_select_lex *tvc_sl, + st_select_lex *parent_select) { LEX *lex= thd->lex; - /* SELECT_LEX object where the transformation is performed */ - SELECT_LEX *parent_select= lex->current_select; + select_result *save_result= thd->lex->result; uint8 save_derived_tables= lex->derived_tables; + thd->lex->result= NULL; Query_arena backup; Query_arena *arena= thd->activate_stmt_arena_if_needed(&backup); - /* - Create SELECT_LEX of the subquery SQ used in the result of transformation + Create SELECT_LEX of the select used in the result of transformation */ lex->current_select= tvc_sl; if (mysql_new_select(lex, 0, NULL)) @@ -562,15 +614,15 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, mysql_init_select(lex); /* Create item list as '*' for the subquery SQ */ Item *item; - SELECT_LEX *sq_select; // select for IN subquery; - sq_select= lex->current_select; - sq_select->linkage= tvc_sl->linkage; - sq_select->parsing_place= SELECT_LIST; - item= new (thd->mem_root) Item_field(thd, &sq_select->context, + SELECT_LEX *wrapper_sl; + wrapper_sl= lex->current_select; + wrapper_sl->linkage= tvc_sl->linkage; + wrapper_sl->parsing_place= SELECT_LIST; + item= new (thd->mem_root) Item_field(thd, &wrapper_sl->context, NULL, NULL, &star_clex_str); if (item == NULL || add_item_to_list(thd, item)) goto err; - (sq_select->with_wild)++; + (wrapper_sl->with_wild)++; /* Exclude SELECT with TVC */ tvc_sl->exclude(); @@ -585,11 +637,11 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, derived_unit= tvc_select->master_unit(); tvc_select->linkage= DERIVED_TABLE_TYPE; - lex->current_select= sq_select; + lex->current_select= wrapper_sl; /* Create the name of the wrapping derived table and - add it to the FROM list of the subquery SQ + add it to the FROM list of the wrapper */ Table_ident *ti; LEX_CSTRING alias; @@ -598,35 +650,120 @@ bool Item_subselect::wrap_tvc_in_derived_table(THD *thd, create_tvc_name(thd, parent_select, &alias)) goto err; if (!(derived_tab= - sq_select->add_table_to_list(thd, - ti, &alias, 0, - TL_READ, MDL_SHARED_READ))) + wrapper_sl->add_table_to_list(thd, + ti, &alias, 0, + TL_READ, MDL_SHARED_READ))) goto err; - sq_select->add_joined_table(derived_tab); - sq_select->add_where_field(derived_unit->first_select()); - sq_select->context.table_list= sq_select->table_list.first; - sq_select->context.first_name_resolution_table= sq_select->table_list.first; - sq_select->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; + wrapper_sl->add_joined_table(derived_tab); + wrapper_sl->add_where_field(derived_unit->first_select()); + wrapper_sl->context.table_list= wrapper_sl->table_list.first; + wrapper_sl->context.first_name_resolution_table= wrapper_sl->table_list.first; + wrapper_sl->table_list.first->derived_type= DTYPE_TABLE | DTYPE_MATERIALIZE; lex->derived_tables|= DERIVED_SUBQUERY; - sq_select->where= 0; - sq_select->set_braces(false); + wrapper_sl->where= 0; + wrapper_sl->set_braces(false); derived_unit->set_with_clause(0); - if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) - ((subselect_single_select_engine *) engine)->change_select(sq_select); - if (arena) thd->restore_active_arena(arena, &backup); - lex->current_select= sq_select; - return false; + thd->lex->result= save_result; + return wrapper_sl; err: if (arena) thd->restore_active_arena(arena, &backup); + thd->lex->result= save_result; lex->derived_tables= save_derived_tables; - lex->current_select= parent_select; - return true; + return 0; +} + + +/** + @brief + Wrap TVC with ORDER BY ... LIMIT tail into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + + @details + The function wraps the TVC tvc_sl into a select: + the function transforms the TVC with tail of the form + VALUES (v1), ... (vn) ORDER BY ... LIMIT n [OFFSET m] + into the select with the same tail of the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + ORDER BY ... LIMIT n [OFFSET m] + + @retval pointer to the result of of the transformation if successful + NULL - otherwise +*/ + +st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl) +{ + st_select_lex *wrapper_sl= wrap_tvc(thd, tvc_sl, NULL); + if (!wrapper_sl) + return NULL; + + wrapper_sl->order_list= tvc_sl->order_list; + wrapper_sl->select_limit= tvc_sl->select_limit; + wrapper_sl->offset_limit= tvc_sl->offset_limit; + wrapper_sl->braces= tvc_sl->braces; + wrapper_sl->explicit_limit= tvc_sl->explicit_limit; + tvc_sl->order_list.empty(); + tvc_sl->select_limit= NULL; + tvc_sl->offset_limit= NULL; + tvc_sl->braces= 0; + tvc_sl->explicit_limit= false; + if (tvc_sl->select_number == 1) + { + tvc_sl->select_number= wrapper_sl->select_number; + wrapper_sl->select_number= 1; + } + if (tvc_sl->master_unit()->union_distinct == tvc_sl) + { + wrapper_sl->master_unit()->union_distinct= wrapper_sl; + } + thd->lex->current_select= wrapper_sl; + return wrapper_sl; +} + + +/** + @brief + Wrap TVC in a subselect into a select + + @param thd The context handler + @param tvc_sl The TVC to wrap + + @details + The function wraps the TVC tvc_sl used in a subselect into a select + the function transforms the TVC of the form VALUES (v1), ... (vn) + into the select the form + SELECT * FROM (VALUES (v1), ... (vn)) tvc_x + and replaces the subselect with the result of the transformation. + + @retval false if successfull + true otherwise +*/ + +bool Item_subselect::wrap_tvc_into_select(THD *thd, st_select_lex *tvc_sl) +{ + LEX *lex= thd->lex; + /* SELECT_LEX object where the transformation is performed */ + SELECT_LEX *parent_select= lex->current_select; + SELECT_LEX *wrapper_sl= wrap_tvc(thd, tvc_sl, parent_select); + if (wrapper_sl) + { + if (engine->engine_type() == subselect_engine::SINGLE_SELECT_ENGINE) + ((subselect_single_select_engine *) engine)->change_select(wrapper_sl); + lex->current_select= wrapper_sl; + return false; + } + else + { + lex->current_select= parent_select; + return true; + } } diff --git a/sql/sql_tvc.h b/sql/sql_tvc.h index 128cc88..594a77a 100644 --- a/sql/sql_tvc.h +++ b/sql/sql_tvc.h @@ -57,6 +57,8 @@ class table_value_constr : public Sql_alloc select_result *tmp_result, st_select_lex_unit *unit_arg); + bool to_be_wrapped_as_with_tail(); + int save_explain_data_intern(THD *thd_arg, Explain_query *output); bool optimize(THD *thd_arg); @@ -64,4 +66,7 @@ class table_value_constr : public Sql_alloc void print(THD *thd_arg, String *str, enum_query_type query_type); }; + +st_select_lex *wrap_tvc_with_tail(THD *thd, st_select_lex *tvc_sl); + #endif /* SQL_TVC_INCLUDED */ diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 7b0e796..bcca27c 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -831,7 +831,8 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, bool is_union_select; bool have_except= FALSE, have_intersect= FALSE; bool instantiate_tmp_table= false; - bool single_tvc= !first_sl->next_select() && first_sl->tvc; + bool single_tvc= !first_sl->next_select() && first_sl->tvc && + !fake_select_lex; DBUG_ENTER("st_select_lex_unit::prepare"); DBUG_ASSERT(thd == current_thd); @@ -986,7 +987,21 @@ bool st_select_lex_unit::prepare(TABLE_LIST *derived_arg, { if (sl->tvc) { - if (sl->tvc->prepare(thd, sl, tmp_result, this)) + if (sl->tvc->to_be_wrapped_as_with_tail()) + { + st_select_lex *wrapper_sl= wrap_tvc_with_tail(thd, sl); + if (!wrapper_sl) + goto err; + + if (sl == first_sl) + first_sl= wrapper_sl; + sl= wrapper_sl; + + if (prepare_join(thd, sl, tmp_result, additional_options, + is_union_select)) + goto err; + } + else if (sl->tvc->prepare(thd, sl, tmp_result, this)) goto err; } else if (prepare_join(thd, sl, tmp_result, additional_options, diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 98ead67..649ceee 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -9192,7 +9192,7 @@ select_paren: { Lex->current_select->set_braces(true); } - table_value_constructor + table_value_constructor select_part3 { DBUG_ASSERT(Lex->current_select->braces); } @@ -9212,6 +9212,12 @@ select_paren: | '(' select_paren ')' ; +select_parent_union_query_term_proper: + SELECT_SYM select_options_and_item_list select_part3_union_query_term + opt_select_lock_type + | table_value_constructor select_part3_union_query_term + ; + select_paren_union_query_term: { /* @@ -9220,8 +9226,7 @@ select_paren_union_query_term: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_union_query_term - opt_select_lock_type + select_parent_union_query_term_proper { DBUG_ASSERT(Lex->current_select->braces); } diff --git a/sql/sql_yacc_ora.yy b/sql/sql_yacc_ora.yy index f7aa1c9..d3bb4fe 100644 --- a/sql/sql_yacc_ora.yy +++ b/sql/sql_yacc_ora.yy @@ -9125,11 +9125,12 @@ union_list_part2: | '(' select_paren_union_query_term ')' union_order_or_limit ; + select_paren: { Lex->current_select->set_braces(true); } - table_value_constructor + table_value_constructor select_part3 { DBUG_ASSERT(Lex->current_select->braces); } @@ -9149,6 +9150,12 @@ select_paren: | '(' select_paren ')' ; +select_parent_union_query_term_proper: + SELECT_SYM select_options_and_item_list select_part3_union_query_term + opt_select_lock_type + | table_value_constructor select_part3_union_query_term + ; + select_paren_union_query_term: { /* @@ -9157,8 +9164,7 @@ select_paren_union_query_term: */ Lex->current_select->set_braces(true); } - SELECT_SYM select_options_and_item_list select_part3_union_query_term - opt_select_lock_type + select_parent_union_query_term_proper { DBUG_ASSERT(Lex->current_select->braces); }

1 0

[Commits] 647c22692ad: MDEV-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave
by sachin.setiya＠mariadb.com 25 Apr '19

25 Apr '19

revision-id: 647c22692ad1fe466eb14b749520cc520807d4fa (mariadb-10.1.38-114-g647c22692ad) parent(s): 6c5e4c9bc0d9ac30f7ec7ee334630bacb58687ba author: Sachin committer: Sachin timestamp: 2019-04-23 17:58:03 +0530 message: MDEV-17588 replicate-do filters cause errors when creating filtered-out tables on master with syntax unsupported on slave Prototype Commit. Delay throwing of error in parsing stage untill we know the result from rpl filter. --- mysql-test/suite/rpl/r/rpl_mdev_17588.result | 26 ++++++++++++++++++++++ mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt | 1 + mysql-test/suite/rpl/t/rpl_mdev_17588.test | 29 +++++++++++++++++++++++++ sql/sql_class.h | 1 + sql/sql_parse.cc | 7 ++++++ sql/sql_yacc.yy | 12 +++++++--- 6 files changed, 73 insertions(+), 3 deletions(-) diff --git a/mysql-test/suite/rpl/r/rpl_mdev_17588.result b/mysql-test/suite/rpl/r/rpl_mdev_17588.result new file mode 100644 index 00000000000..976efde2bdd --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_mdev_17588.result @@ -0,0 +1,26 @@ +include/master-slave.inc +[connection master] +set sql_log_bin= 0; +install soname 'ha_tokudb'; +set sql_log_bin= 1; +create table t1 (a int) engine=TokuDB; +create table t2 (a int); +create table t3 (a int) engine=TokuDB; +include/wait_for_slave_sql_error.inc [errno=1286] +show create table t1; +ERROR 42S02: Table 'test.t1' doesn't exist +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +show create table t3; +ERROR 42S02: Table 'test.t3' doesn't exist +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +drop table t1, t2, t3; +set sql_log_bin= 0; +uninstall soname 'ha_tokudb'; +set sql_log_bin= 1; +CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286'); +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt new file mode 100644 index 00000000000..19497afd22a --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mdev_17588-slave.opt @@ -0,0 +1 @@ +--replicate-do-table=test.t2 --replicate-do-table=test.t3 --sql-mode='NO_ENGINE_SUBSTITUTION' diff --git a/mysql-test/suite/rpl/t/rpl_mdev_17588.test b/mysql-test/suite/rpl/t/rpl_mdev_17588.test new file mode 100644 index 00000000000..c636077e4d1 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_mdev_17588.test @@ -0,0 +1,29 @@ +--source include/master-slave.inc + +--connection master +set sql_log_bin= 0; +install soname 'ha_tokudb'; +set sql_log_bin= 1; + +create table t1 (a int) engine=TokuDB; +create table t2 (a int); +create table t3 (a int) engine=TokuDB; + +--connection slave +let $slave_sql_errno= 1286; +source include/wait_for_slave_sql_error.inc; +--error ER_NO_SUCH_TABLE +show create table t1; +show create table t2; +--error ER_NO_SUCH_TABLE +show create table t3; +SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; +START SLAVE; +--connection master +drop table t1, t2, t3; +set sql_log_bin= 0; +uninstall soname 'ha_tokudb'; +set sql_log_bin= 1; +--sync_slave_with_master +CALL mtr.add_suppression('Slave: Unknown storage engine .* Error_code: 1286'); +--source include/rpl_end.inc diff --git a/sql/sql_class.h b/sql/sql_class.h index 6640e02147a..4361700ee6c 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -2802,6 +2802,7 @@ class THD :public Statement, uint8 password; /* 0, 1 or 2 */ uint8 failed_com_change_user; bool slave_thread; + char *rpl_unavailable_storage_plugin= NULL; bool extra_port; /* If extra connection */ bool no_errors; diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc index 6649c60f827..5f8c833c76f 100644 --- a/sql/sql_parse.cc +++ b/sql/sql_parse.cc @@ -2656,6 +2656,13 @@ mysql_execute_command(THD *thd) /* we warn the slave SQL thread */ my_message(ER_SLAVE_IGNORED_TABLE, ER_THD(thd, ER_SLAVE_IGNORED_TABLE), MYF(0)); + thd->rpl_unavailable_storage_plugin= NULL; + DBUG_RETURN(0); + } + if (thd->rpl_unavailable_storage_plugin) + { + my_error(ER_UNKNOWN_STORAGE_ENGINE, MYF(0), thd->rpl_unavailable_storage_plugin); + thd->rpl_unavailable_storage_plugin= NULL; DBUG_RETURN(0); } /* diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 5111f0690ab..0756028c34a 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -2476,7 +2476,7 @@ create: LEX *lex= thd->lex; lex->current_select= &lex->select_lex; if ((lex->create_info.used_fields & HA_CREATE_USED_ENGINE) && - !lex->create_info.db_type) + !lex->create_info.db_type && !thd->rpl_unavailable_storage_plugin) { lex->create_info.use_default_db_type(thd); push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, @@ -5791,9 +5791,15 @@ storage_engines: else { if (thd->variables.sql_mode & MODE_NO_ENGINE_SUBSTITUTION) - my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); + { + if (!thd->slave_thread) + my_yyabort_error((ER_UNKNOWN_STORAGE_ENGINE, MYF(0), $1.str)); + else + thd->rpl_unavailable_storage_plugin= $1.str; + } $$= 0; - push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, + if (!thd->rpl_unavailable_storage_plugin) + push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN, ER_UNKNOWN_STORAGE_ENGINE, ER_THD(thd, ER_UNKNOWN_STORAGE_ENGINE), $1.str);

2 1

[Commits] 4e01bc8c963: MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase
by Oleksandr Byelkin 25 Apr '19

25 Apr '19

revision-id: 4e01bc8c963d9513625dd984cd1aca24b8a7b516 (mariadb-10.2.23-79-g4e01bc8c963) parent(s): 3dffdee667666df9ade9f2c458bf1ea495ffba02 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-25 18:02:31 +0200 message: MDEV-16240: Assertion `0' failed in row_sel_convert_mysql_key_to_innobase Set table in row ID position mode before using this function. --- mysql-test/r/multi_update_innodb.result | 40 ++++++++++++++++++++++ mysql-test/t/multi_update_innodb.test | 49 +++++++++++++++++++++++++++ sql/handler.cc | 15 +++++++-- sql/item_subselect.cc | 9 +++-- sql/item_subselect.h | 2 +- sql/log_event.cc | 7 ++++ sql/sql_insert.cc | 60 +++++++++++++++++++++++++++------ sql/sql_load.cc | 7 ++++ sql/sql_select.cc | 16 +++++++++ sql/sql_update.cc | 8 +++++ 10 files changed, 197 insertions(+), 16 deletions(-) diff --git a/mysql-test/r/multi_update_innodb.result b/mysql-test/r/multi_update_innodb.result index 5890fd24f5f..294ebfcebdf 100644 --- a/mysql-test/r/multi_update_innodb.result +++ b/mysql-test/r/multi_update_innodb.result @@ -151,3 +151,43 @@ create table t2 like t1; insert into t2 select * from t1; delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; +# +# MDEV-16240: Assertion `0' failed in +# row_sel_convert_mysql_key_to_innobase +# +SET @save_sql_mode=@@sql_mode; +set sql_mode=''; +CREATE TABLE `t3` ( +`f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), +`f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), +`f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', +`pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', +`f4` datetime DEFAULT current_timestamp(), +PRIMARY KEY (`pk`), +UNIQUE KEY `f2k` (`f2`), +KEY `f4k` (`f4`) +) ENGINE=InnoDB; +INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); +CREATE VIEW `v1` AS +SELECT `t3`.`pk` AS `pk`, +`t3`.`f3` AS `f3`, +`t3`.`f4` AS `f4`, +`t3`.`f2` AS `f2`, +`t3`.`f1` AS `f1` +FROM `t3`; +CREATE TABLE `t4` ( +`f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), +`f3` timestamp NULL DEFAULT NULL, +`f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), +`pk` int(11) NOT NULL, +`f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), +PRIMARY KEY (`pk`) +) ENGINE=InnoDB; +INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-1 8 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); +UPDATE `v1` t1, `t4` t2 +SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; +ERROR 23000: Duplicate entry '0000-00-00 00:00:00' for key 'f2k' +DROP VIEW v1; +DROP TABLE t3,t4; +SET @@sql_mode=@save_sql_mode; +# End of 10.2 tests diff --git a/mysql-test/t/multi_update_innodb.test b/mysql-test/t/multi_update_innodb.test index f5f8f91edb8..2e46ee06d4d 100644 --- a/mysql-test/t/multi_update_innodb.test +++ b/mysql-test/t/multi_update_innodb.test @@ -173,3 +173,52 @@ delete t1,t2 from t2,t1 where t1.a<'B' and t2.b=t1.b; drop table t1,t2; +--echo # +--echo # MDEV-16240: Assertion `0' failed in +--echo # row_sel_convert_mysql_key_to_innobase +--echo # + +SET @save_sql_mode=@@sql_mode; +set sql_mode=''; + +CREATE TABLE `t3` ( + `f1` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE current_timestamp(), + `f2` datetime DEFAULT '2000-01-01 00:00:00' ON UPDATE current_timestamp(), + `f3` TIMESTAMP NULL DEFAULT '2000-01-01 00:00:00', + `pk` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', + `f4` datetime DEFAULT current_timestamp(), + PRIMARY KEY (`pk`), + UNIQUE KEY `f2k` (`f2`), + KEY `f4k` (`f4`) + ) ENGINE=InnoDB; + +INSERT INTO `t3` VALUES ('2018-05-18 15:08:07','2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00','0000-00-00 00:00:00'),('0000-00-00 00:00:00','0000-00-00 00:00:00','1999-12-31 23:00:00','2002-07-03 23:04:40','0000-00-00 00:00:00'); + +CREATE VIEW `v1` AS +SELECT `t3`.`pk` AS `pk`, + `t3`.`f3` AS `f3`, + `t3`.`f4` AS `f4`, + `t3`.`f2` AS `f2`, + `t3`.`f1` AS `f1` +FROM `t3`; + +CREATE TABLE `t4` ( + `f1` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(), + `f3` timestamp NULL DEFAULT NULL, + `f2` timestamp NULL DEFAULT '1999-12-31 23:00:00' ON UPDATE current_timestamp(), + `pk` int(11) NOT NULL, + `f4` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), + PRIMARY KEY (`pk`) +) ENGINE=InnoDB; + +INSERT INTO `t4` VALUES ('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,1,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,3,'2018-05-18 15:08:06'),('0000-00-00 00:00:00','0000-00-00 00:00:00',NULL,1976,'0000-00-00 00:00:00'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2000,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2001,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2002,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2003,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00',NULL,2004,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2005,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2018,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2019,'2018-05-1 8 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','2018-05-18 15:08:06',2024,'2018-05-18 15:08:06'),('2018-05-18 17:08:06','0000-00-00 00:00:00','1999-12-31 23:00:00',2025,'2018-05-18 15:08:06'),('0000-00-00 00:00:00',NULL,'2018-05-18 15:08:06',2026,'2018-05-18 15:08:06'),('2018-05-18 17:08:07','0000-00-00 00:00:00','0000-00-00 00:00:00',2027,'0000-00-00 00:00:00'); + +--error ER_DUP_ENTRY +UPDATE `v1` t1, `t4` t2 +SET t1.`f2` = 6452736 WHERE t1.`f4` = 6272000; + +DROP VIEW v1; +DROP TABLE t3,t4; +SET @@sql_mode=@save_sql_mode; + +--echo # End of 10.2 tests diff --git a/sql/handler.cc b/sql/handler.cc index ad1bad59efa..e5b452f9649 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -2649,8 +2649,7 @@ int handler::ha_rnd_pos(uchar *buf, uchar *pos) DBUG_ENTER("handler::ha_rnd_pos"); DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE || m_lock_type != F_UNLCK); - /* TODO: Find out how to solve ha_rnd_pos when finding duplicate update. */ - /* DBUG_ASSERT(inited == RND); */ + DBUG_ASSERT(inited == RND); TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_FETCH_ROW, MAX_KEY, 0, { result= rnd_pos(buf, pos); }) @@ -3308,6 +3307,10 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, ulonglong nr; int error; MY_BITMAP *old_read_set; + bool rnd_inited= (inited == RND); + + if (rnd_inited && ha_rnd_end()) + return; old_read_set= table->prepare_for_keyread(table->s->next_number_index); @@ -3317,6 +3320,10 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, DBUG_ASSERT(0); (void) extra(HA_EXTRA_NO_KEYREAD); *first_value= ULONGLONG_MAX; + if (rnd_inited && ha_rnd_init_with_error(0)) + { + //TODO: it would be nice to return here an error + } return; } @@ -3363,6 +3370,10 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, ha_index_end(); table->restore_column_maps_after_keyread(old_read_set); *first_value= nr; + if (rnd_inited && ha_rnd_init_with_error(0)) + { + //TODO: it would be nice to return here an error + } return; } diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 8a9dd083911..8cff8f3a5c4 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -5855,12 +5855,16 @@ Ordered_key::cmp_keys_by_row_data_and_rownum(Ordered_key *key, } -void Ordered_key::sort_keys() +bool Ordered_key::sort_keys() { + if (tbl->file->ha_rnd_init_with_error(0)) + return TRUE; my_qsort2(key_buff, (size_t) key_buff_elements, sizeof(rownum_t), (qsort2_cmp) &cmp_keys_by_row_data_and_rownum, (void*) this); /* Invalidate the current row position. */ cur_key_idx= HA_POS_ERROR; + tbl->file->ha_rnd_end(); + return FALSE; } @@ -6313,7 +6317,8 @@ subselect_rowid_merge_engine::init(MY_BITMAP *non_null_key_parts, /* Sort the keys in each of the indexes. */ for (uint i= 0; i < merge_keys_count; i++) - merge_keys[i]->sort_keys(); + if (merge_keys[i]->sort_keys()) + return TRUE; if (init_queue(&pq, merge_keys_count, 0, FALSE, subselect_rowid_merge_engine::cmp_keys_by_cur_rownum, NULL, diff --git a/sql/item_subselect.h b/sql/item_subselect.h index bd6a1bdc498..5366c759795 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -1287,7 +1287,7 @@ class Ordered_key : public Sql_alloc ++cur_key_idx; } - void sort_keys(); + bool sort_keys(); double null_selectivity(); /* diff --git a/sql/log_event.cc b/sql/log_event.cc index 8990e1953b6..7ebc75dd1bf 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -12845,6 +12845,12 @@ Rows_log_event::write_row(rpl_group_info *rgi, if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { DBUG_PRINT("info",("Locating offending record using rnd_pos()")); + + if ((error= table->file->ha_rnd_init_with_error(0))) + { + DBUG_RETURN(error); + } + error= table->file->ha_rnd_pos(table->record[1], table->file->dup_ref); if (error) { @@ -12854,6 +12860,7 @@ Rows_log_event::write_row(rpl_group_info *rgi, table->file->print_error(error, MYF(0)); DBUG_RETURN(error); } + table->file->ha_rnd_end(); } else { diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index c623336fdba..4005153cb64 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -881,7 +881,12 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, #endif /* EMBEDDED_LIBRARY */ { if (duplic != DUP_ERROR || ignore) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + goto abort; + } /** This is a simple check for the case when the table has a trigger that reads from it, or when the statement invokes a stored function @@ -942,7 +947,10 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, { DBUG_PRINT("info", ("iteration %llu", iteration)); if (iteration && bulk_parameters_set(thd)) - goto abort; + { + error= 1; + goto values_loop_end; + } while ((values= its++)) { @@ -1094,7 +1102,11 @@ bool mysql_insert(THD *thd,TABLE_LIST *table_list, error=1; } if (duplic != DUP_ERROR || ignore) + { table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS) + table->file->ha_rnd_end(); + } transactional_table= table->file->has_transactions(); @@ -1705,6 +1717,7 @@ int write_record(THD *thd, TABLE *table,COPY_INFO *info) goto err; if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { + DBUG_ASSERT(table->file->inited == handler::RND); if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref)) goto err; } @@ -3177,6 +3190,9 @@ bool Delayed_insert::handle_inserts(void) max_rows= ULONG_MAX; // Do as much as possible } + if (table->file->ha_rnd_init_with_error(0)) + goto err; + /* We can't use row caching when using the binary log because if we get a crash, then binary log will contain rows that are not yet @@ -3352,6 +3368,8 @@ bool Delayed_insert::handle_inserts(void) } } + table->file->ha_rnd_end(); + if (WSREP((&thd))) thd_proc_info(&thd, "insert done"); else @@ -3649,7 +3667,12 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) thd->cuted_fields=0; if (info.ignore || info.handle_duplicates != DUP_ERROR) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + DBUG_RETURN(1); + } if (info.handle_duplicates == DUP_REPLACE && (!table->triggers || !table->triggers->has_delete_triggers())) table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); @@ -3818,6 +3841,9 @@ bool select_insert::prepare_eof() if (!error && thd->is_error()) error= thd->get_stmt_da()->sql_errno(); + if (info.ignore || info.handle_duplicates != DUP_ERROR) + if (table->file->ha_table_flags() & HA_DUPLICATE_POS) + table->file->ha_rnd_end(); table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); @@ -3929,6 +3955,11 @@ void select_insert::abort_result_set() { if (thd->locked_tables_mode <= LTM_LOCK_TABLES) table->file->ha_end_bulk_insert(); + if (table->file->inited) + table->file->ha_rnd_end(); + table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); + table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); + /* If at least one row has been inserted/modified and will stay in the table (the table doesn't have transactions) we must write to @@ -4341,7 +4372,12 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u) restore_record(table,s->default_values); // Get empty record thd->cuted_fields=0; if (info.ignore || info.handle_duplicates != DUP_ERROR) + { table->file->extra(HA_EXTRA_IGNORE_DUP_KEY); + if (table->file->ha_table_flags() & HA_DUPLICATE_POS && + table->file->ha_rnd_init_with_error(0)) + DBUG_RETURN(1); + } if (info.handle_duplicates == DUP_REPLACE && (!table->triggers || !table->triggers->has_delete_triggers())) table->file->extra(HA_EXTRA_WRITE_CAN_REPLACE); @@ -4522,9 +4558,6 @@ bool select_create::send_eof() */ exit_done= 1; // Avoid double calls - table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); - table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); - send_ok_packet(); if (m_plock) @@ -4600,13 +4633,6 @@ void select_create::abort_result_set() thd->locked_tables_list.unlock_locked_table(thd, create_info->mdl_ticket); } - if (m_plock) - { - mysql_unlock_tables(thd, *m_plock); - *m_plock= NULL; - m_plock= NULL; - } - if (table) { bool tmp_table= table->s->tmp_table; @@ -4617,9 +4643,21 @@ void select_create::abort_result_set() thd->restore_tmp_table_share(saved_tmp_table_share); } + if (table->file->inited && + (info.ignore || info.handle_duplicates != DUP_ERROR) && + (table->file->ha_table_flags() & HA_DUPLICATE_POS)) + table->file->ha_rnd_end(); table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY); table->file->extra(HA_EXTRA_WRITE_CANNOT_REPLACE); table->auto_increment_field_not_null= FALSE; + + if (m_plock) + { + mysql_unlock_tables(thd, *m_plock); + *m_plock= NULL; + m_plock= NULL; + } + drop_open_table(thd, table, create_table->db, create_table->table_name); table=0; // Safety if (thd->log_current_statement && mysql_bin_log.is_open()) diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 49558f8b694..0fcc4efbccd 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -660,6 +660,10 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, thd->abort_on_warning= !ignore && thd->is_strict_mode(); + if ((table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) && + (error= table_list->table->file->ha_rnd_init_with_error(0))) + goto err; + thd_progress_init(thd, 2); if (table_list->table->validate_default_values_of_unset_fields(thd)) { @@ -679,6 +683,9 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, set_fields, set_values, read_info, *ex->enclosed, skip_lines, ignore); + if (table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) + table_list->table->file->ha_rnd_end(); + thd_proc_info(thd, "End bulk insert"); if (!error) thd_progress_next_stage(thd); diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 3c1cea6be51..291560dc098 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -20318,6 +20318,15 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), table->file->print_error(error,MYF(0)); /* purecov: inspected */ DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } + /* Prepare table for random positioning */ + bool rnd_inited= (table->file->inited == handler::RND); + if (!rnd_inited && + ((error= table->file->ha_index_end()) || + (error= table->file->ha_rnd_init(0)))) + { + table->file->print_error(error, MYF(0)); + DBUG_RETURN(NESTED_LOOP_ERROR); + } if (table->file->ha_rnd_pos(table->record[1],table->file->dup_ref)) { table->file->print_error(error,MYF(0)); /* purecov: inspected */ @@ -20331,6 +20340,13 @@ end_unique_update(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)), table->file->print_error(error,MYF(0)); /* purecov: inspected */ DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */ } + if (!rnd_inited && + ((error= table->file->ha_rnd_end()) || + (error= table->file->ha_index_init(0, 0)))) + { + table->file->print_error(error, MYF(0)); + DBUG_RETURN(NESTED_LOOP_ERROR); + } } if (join->thd->check_killed()) { diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 80ecd820046..1da265d7c16 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -211,6 +211,14 @@ static void prepare_record_for_error_message(int error, TABLE *table) bitmap_union(table->read_set, &unique_map); /* Tell the engine about the new set. */ table->file->column_bitmaps_signal(); + + if ((error= table->file->ha_index_or_rnd_end()) || + (error= table->file->ha_rnd_init(0))) + { + table->file->print_error(error, MYF(0)); + DBUG_VOID_RETURN; + } + /* Read record that is identified by table->file->ref. */ (void) table->file->ha_rnd_pos(table->record[1], table->file->ref); /* Copy the newly read columns into the new record. */

1 0

[Commits] a738ed518dd: postreview
by Oleksandr Byelkin 25 Apr '19

25 Apr '19

revision-id: a738ed518dd7d0d0e2d699829e71c3c9fd49c690 (mariadb-10.2.23-80-ga738ed518dd) parent(s): 2babe7a2adb2ed0d57eb7b44bd92113e64413bb6 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-25 18:02:04 +0200 message: postreview --- sql/handler.cc | 8 ++++++-- sql/log_event.cc | 6 ++---- sql/sql_load.cc | 6 ++---- 3 files changed, 10 insertions(+), 10 deletions(-) diff --git a/sql/handler.cc b/sql/handler.cc index 2c7c9d26324..e5b452f9649 100644 --- a/sql/handler.cc +++ b/sql/handler.cc @@ -3321,7 +3321,9 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, (void) extra(HA_EXTRA_NO_KEYREAD); *first_value= ULONGLONG_MAX; if (rnd_inited && ha_rnd_init_with_error(0)) - DBUG_ASSERT(0); + { + //TODO: it would be nice to return here an error + } return; } @@ -3369,7 +3371,9 @@ void handler::get_auto_increment(ulonglong offset, ulonglong increment, table->restore_column_maps_after_keyread(old_read_set); *first_value= nr; if (rnd_inited && ha_rnd_init_with_error(0)) - DBUG_ASSERT(0); + { + //TODO: it would be nice to return here an error + } return; } diff --git a/sql/log_event.cc b/sql/log_event.cc index c17c7ef6e06..7ebc75dd1bf 100644 --- a/sql/log_event.cc +++ b/sql/log_event.cc @@ -12845,11 +12845,9 @@ Rows_log_event::write_row(rpl_group_info *rgi, if (table->file->ha_table_flags() & HA_DUPLICATE_POS) { DBUG_PRINT("info",("Locating offending record using rnd_pos()")); - error= table->file->ha_rnd_init(0); - if (error) + + if ((error= table->file->ha_rnd_init_with_error(0))) { - DBUG_PRINT("info",("rnd_init() returns error %d",error)); - table->file->print_error(error, MYF(0)); DBUG_RETURN(error); } diff --git a/sql/sql_load.cc b/sql/sql_load.cc index 8994b436cab..0fcc4efbccd 100644 --- a/sql/sql_load.cc +++ b/sql/sql_load.cc @@ -661,11 +661,9 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list, thd->abort_on_warning= !ignore && thd->is_strict_mode(); if ((table_list->table->file->ha_table_flags() & HA_DUPLICATE_POS) && - (error= table_list->table->file->ha_rnd_init(0))) - { - table->file->print_error(error, MYF(0)); + (error= table_list->table->file->ha_rnd_init_with_error(0))) goto err; - } + thd_progress_init(thd, 2); if (table_list->table->validate_default_values_of_unset_fields(thd)) {

1 0