- commits - lists.mariadb.org

[Commits] 167a96b5157: MDEV-9959: A serious MariaDB server performance bug
by varunraiko1803＠gmail.com 20 Apr '19

20 Apr '19

revision-id: 167a96b5157049408a6ad4bca7abcd376af93fb5 (mariadb-10.3.0-644-g167a96b5157) parent(s): 4359c6b4806605c78987e50cab3a6b42016b7603 author: Varun Gupta committer: Varun Gupta timestamp: 2018-03-27 16:45:46 +0530 message: MDEV-9959: A serious MariaDB server performance bug 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. Added handling for multiple selects in the derived table --- mysql-test/r/cte_nonrecursive.result | 8 +-- mysql-test/r/cte_recursive.result | 6 +- mysql-test/r/derived_cond_pushdown.result | 82 ++++++++++++------------- mysql-test/r/derived_view.result | 2 +- mysql-test/r/join_cache.result | 6 +- mysql-test/r/mdev9959.result | 46 ++++++++++++++ mysql-test/r/subselect_extra.result | 2 +- mysql-test/r/subselect_extra_no_semijoin.result | 2 +- mysql-test/t/mdev9959.test | 25 ++++++++ sql/sql_lex.h | 2 + sql/sql_union.cc | 60 ++++++++++++++++++ sql/sql_yacc.yy | 3 + sql/table.cc | 19 ++++++ 13 files changed, 209 insertions(+), 54 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 53334512b20..a3eb81b0747 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/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/r/cte_recursive.result b/mysql-test/r/cte_recursive.result index 15d4fc1a01f..a7305046ba3 100644 --- a/mysql-test/r/cte_recursive.result +++ b/mysql-test/r/cte_recursive.result @@ -1238,9 +1238,9 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 12 2 DERIVED folks ALL NULL NULL NULL NULL 12 Using where 3 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 -3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +3 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 1 4 RECURSIVE UNION p ALL PRIMARY NULL NULL NULL 12 -4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 2 +4 RECURSIVE UNION <derived2> ref key0 key0 5 test.p.id 1 NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL with recursive ancestors @@ -3049,7 +3049,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 16 100.00 2 DERIVED a ALL NULL NULL NULL NULL 16 100.00 Using where 3 RECURSIVE UNION b ALL NULL NULL NULL NULL 16 100.00 Using where -3 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 2 100.00 +3 RECURSIVE UNION <derived2> ref key0 key0 35 test.b.departure 1 100.00 4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 16 100.00 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL Warnings: diff --git a/mysql-test/r/derived_cond_pushdown.result b/mysql-test/r/derived_cond_pushdown.result index 32d3c88cc8d..3723e25a494 100644 --- a/mysql-test/r/derived_cond_pushdown.result +++ b/mysql-test/r/derived_cond_pushdown.result @@ -5187,7 +5187,7 @@ explain select * from v2_union as v,t2 where ((v.a=6) or (v.a=8)) and (v.c>200) and (v.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 5 test.t2.a 6 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 4 UNION t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort @@ -5213,7 +5213,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 6, + "rows": 1, "filtered": 100, "attached_condition": "v.c > 200", "materialized": { @@ -5358,7 +5358,7 @@ a b c a b c d explain select * from v3_union as v,t2 where (v.a=t2.a) and (v.c>6); 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 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5382,7 +5382,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "v.c > 6", "materialized": { @@ -5476,7 +5476,7 @@ a b c a b c d explain select * from v3_union as v,t2 where (v.a=t2.a) and ((t2.a>1) or (v.b<20)); 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 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5500,7 +5500,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "t2.a > 1 or v.b < 20", "materialized": { @@ -5561,7 +5561,7 @@ explain select * from v3_union as v,t2 where (v.a=t2.a) and ((v.b=19) or (v.b=21)) and ((v.c<3) or (v.c>600)); 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 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5586,7 +5586,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "(v.b = 19 or v.b = 21) and (v.c < 3 or v.c > 600)", "materialized": { @@ -5645,7 +5645,7 @@ a b c a b c d explain select * from v4_union as v,t2 where (v.a=t2.a) and (v.b<20); 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 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5669,7 +5669,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "v.b < 20", "materialized": { @@ -5752,7 +5752,7 @@ explain select * from v4_union as v,t2 where (v.a=t2.a) and ((t2.a<3) or (v.b<40)) and (v.c>500); 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 5 test.t2.a 4 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 20 Using where NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -5777,7 +5777,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 4, + "rows": 1, "filtered": 100, "attached_condition": "(t2.a < 3 or v.b < 40) and v.c > 500", "materialized": { @@ -7952,7 +7952,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5); 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 5 test.t2.a 2 +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -7976,7 +7976,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { @@ -8226,7 +8226,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.c>500); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8250,7 +8250,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 500", "materialized": { @@ -8317,7 +8317,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<5) and (v1.c>500); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8341,7 +8341,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 500", "materialized": { @@ -8411,7 +8411,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and ((v1.b>27) or (v1.b<19)); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8435,7 +8435,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.b > 27 or v1.b < 19", "materialized": { @@ -8508,7 +8508,7 @@ explain select * from v1,t2 where (v1.a=t2.a) and ((v1.c<400) or (v1.c>800)); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8533,7 +8533,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 400 or v1.c > 800", "materialized": { @@ -8762,7 +8762,7 @@ where ((d1.a>4) and (d1.c>500))); 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 5 test.t2.b 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -8796,7 +8796,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "t2.c = 988 and t2.b > 13 or d1.a > 4 and d1.c > 500", "materialized": { @@ -8872,7 +8872,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); 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 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 UNION <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -8899,7 +8899,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 200", "materialized": { @@ -9138,7 +9138,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>5) and (v1.c>200); 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 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 4 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9163,7 +9163,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 200", "materialized": { @@ -9260,7 +9260,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<200); 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 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 4 UNION t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9285,7 +9285,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 200", "materialized": { @@ -9498,7 +9498,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9525,7 +9525,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 130", "materialized": { @@ -9643,7 +9643,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a>4) and (v1.c<130); 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 5 test.t2.a 3 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT <derived4> ALL NULL NULL NULL NULL 18 Using where 4 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort @@ -9671,7 +9671,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 3, + "rows": 1, "filtered": 100, "attached_condition": "v1.c < 130", "materialized": { @@ -10143,7 +10143,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and (v1.a<2) and (v1.b<30) and (v1.c>450); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -10167,7 +10167,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.b < 30 and v1.c > 450", "materialized": { @@ -10243,7 +10243,7 @@ a b c a b c explain select * from v1,t2 where (v1.a=t2.a) and ((v1.a<2) or (v1.a<5)) and (v1.c>450); 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 5 test.t2.a 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.a 1 Using where 2 DERIVED t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort 3 EXCEPT t1 ALL NULL NULL NULL NULL 18 Using where; Using temporary; Using filesort NULL EXCEPT RESULT <except2,3> ALL NULL NULL NULL NULL NULL @@ -10267,7 +10267,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["a"], "ref": ["test.t2.a"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.c > 450", "materialized": { @@ -10636,7 +10636,7 @@ a b c a b c explain select * from v1,t2 where (v1.b=t2.b) and (v1.a<3); 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 5 test.t2.b 2 Using where +1 PRIMARY <derived2> ref key0 key0 5 test.t2.b 1 Using where 2 DERIVED t3 range i1 i1 5 NULL 1 Using index condition 3 UNION t3 ALL NULL NULL NULL NULL 20 Using where; Using temporary; Using filesort NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL @@ -10660,7 +10660,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t2.b"], - "rows": 2, + "rows": 1, "filtered": 100, "attached_condition": "v1.a < 3", "materialized": { @@ -12333,7 +12333,7 @@ EXPLAIN "key_length": "5", "used_key_parts": ["b"], "ref": ["test.t1.a"], - "rows": 2, + "rows": 1, "filtered": 100, "materialized": { "query_block": { diff --git a/mysql-test/r/derived_view.result b/mysql-test/r/derived_view.result index 85e56ff176e..3a0fde7b053 100644 --- a/mysql-test/r/derived_view.result +++ b/mysql-test/r/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/r/join_cache.result b/mysql-test/r/join_cache.result index eea397402ad..cc185b640bc 100644 --- a/mysql-test/r/join_cache.result +++ b/mysql-test/r/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/r/mdev9959.result b/mysql-test/r/mdev9959.result new file mode 100644 index 00000000000..049e0350cca --- /dev/null +++ b/mysql-test/r/mdev9959.result @@ -0,0 +1,46 @@ +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 +# multiple selects in derived table +# NO UNION ALL +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select 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 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,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b 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,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; +a a b +3 3 1 +4 4 2 +6 6 2 +drop table t1,t2,t3; diff --git a/mysql-test/r/subselect_extra.result b/mysql-test/r/subselect_extra.result index 73642c09324..a743f30d00a 100644 --- a/mysql-test/r/subselect_extra.result +++ b/mysql-test/r/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/r/subselect_extra_no_semijoin.result b/mysql-test/r/subselect_extra_no_semijoin.result index fc8d5759945..2646bc51bac 100644 --- a/mysql-test/r/subselect_extra_no_semijoin.result +++ b/mysql-test/r/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/mysql-test/t/mdev9959.test b/mysql-test/t/mdev9959.test new file mode 100644 index 00000000000..36f364261c9 --- /dev/null +++ b/mysql-test/t/mdev9959.test @@ -0,0 +1,25 @@ +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; + +--echo # multiple selects in derived table +--echo # NO UNION ALL +analyze select * from t1 , ( (select t2.a,t2.b from t2 order by c) union (select t2.a,t2.b 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,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; + +select * from t1 , ( (select t2.a,t2.b from t2 order by c) union all (select t2.a,t2.b from t2 order by c) except(select t3.a, t3.b from t3 order by b))q where t1.a=q.a; + +drop table t1,t2,t3; + diff --git a/sql/sql_lex.h b/sql/sql_lex.h index f0241a32acf..16d5e55d251 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -761,6 +761,7 @@ class st_select_lex_unit: public st_select_lex_node { Procedure *last_procedure; /* Pointer to procedure, if such exists */ bool columns_are_renamed; + bool union_all; /* TRUE if there is a UNION ALL operation */ void init_query(); st_select_lex* outer_select(); @@ -800,6 +801,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 857c9a117f5..6dac0cb0458 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -1960,3 +1960,63 @@ void st_select_lex_unit::set_unique_exclude() } } } + +/* + Check if the selects in the derived table can give distinct rows irrespective + of the data given for the tables. + + for example: + select * from + ((select t1.a from t1) op (select t2.a from t2) op (select t3.a from t3)); + the op here being UNION/INTERSECT/EXCEPT + + so this function would check if the derived table like the case above + can give distinct rows or not irrespective of the data in the tables. + + So what the function is handling: + - If there is no UNION ALL, we are guarenteed distinct rows. + + Example + ((select t1.a from t1) UNION (select t2.a from t2); + + - If there is UNION ALL, we can still guarantee distinct rows if + the last operation of the selects does not involve UNION ALL or + INTERSECT is not there + + Example: + ((select t1.a from t1) UNION ALL (select t2.a from t2) UNION/EXCEPT + (select t3.a from t3)): guarantees distinct rows, UNION and EXCEPT + have the same precedence. + + Example: + ((select t1.a from t1) UNION ALL (select t2.a from t2) INTERSECT + (select t3.a from t3)): does not guarantee distinct rows because + INTERSECT has higher precedence than UNION so we would evaluate + the INTERSECT part first and then do UNION ALL, so there we can + end up with duplicates, so distinct rows are not guaranteed. + + @retval false Distinct rows are not guaranteed + @retval true Distinct rows are guanranteed irrespective of the data + in the tables + +*/ + +bool st_select_lex_unit::check_distinct_in_union() +{ + bool is_intersect_present=FALSE; + st_select_lex* first= first_select(); + for(st_select_lex *sl=first; sl ; sl=sl->next_select()) + is_intersect_present|= (sl->linkage == INTERSECT_TYPE); + + if (!union_all) + return true; + else + { + if (union_distinct) + { + if (!is_intersect_present && !union_distinct->next_select()) + return true; + } + } + return false; +} diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 35ec2d29d21..112873e8362 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -571,7 +571,10 @@ bool LEX::add_select_to_union_list(bool is_union_distinct, current_select; } else + { DBUG_ASSERT(type == UNION_TYPE); + current_select->master_unit()->union_all= true; + } return FALSE; } diff --git a/sql/table.cc b/sql/table.cc index 4f90d429ce5..d2938a29ea6 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -7113,6 +7113,25 @@ bool TABLE::add_tmp_key(uint key, uint key_parts, if (!keyinfo->rec_per_key) return TRUE; bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts); + /* + 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 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) + { + /* + This handles the case when we have a single select in the derived table + */ + st_select_lex* first= derived->first_select(); + if ((first && !first->is_part_of_union() && + first->options & SELECT_DISTINCT) || + derived->check_distinct_in_union()) + keyinfo->rec_per_key[key_parts-1]=1; + } + keyinfo->read_stats= NULL; keyinfo->collected_stats= NULL;

2 2

[Commits] 793b74b: MDEV-19255 Server crash in st_join_table::save_explain_data or assertion
by IgorBabaev 19 Apr '19

19 Apr '19

revision-id: 793b74ba759845db885ceb1b9cbc5f8e99fd2cc3 (mariadb-10.4.4-30-g793b74b) parent(s): 878ca5ca4f8c5598f2e289a753327c87b5a8818c author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-19 16:17:51 -0700 message: MDEV-19255 Server crash in st_join_table::save_explain_data or assertion `sel->quick' failure in JOIN::make_range_rowid_filters upon query with rowid_filter=ON Index ranges can be defined using conditions with inexpensive subqueries. Such a subquery is evaluated when some representation of a possible range sequence is built. After the evaluation the JOIN structure of the subsquery is distroyed. Any attempt to build the above representation may fail because the function that checks whether a subquery is inexpensive in some cases uses the join structure of the subquery. When a range rowid filter is built by a range sequence constructed out of a range condition that uses an inexpensive subquery the representation of the the sequence is built twice. Building the second representation fails due to the described problem with the execution of Item_subselect::is_expensive(). The function was corrected to return the result of the last its invocation if the Item_subselect object has been already evaluated. --- mysql-test/main/rowid_filter.result | 92 ++++++++++++++++++++++++++++++ mysql-test/main/rowid_filter.test | 39 +++++++++++++ mysql-test/main/rowid_filter_innodb.result | 92 ++++++++++++++++++++++++++++++ sql/item_subselect.cc | 20 ++++--- sql/item_subselect.h | 2 + 5 files changed, 237 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/rowid_filter.result b/mysql-test/main/rowid_filter.result index 280ced7..efe914f 100644 --- a/mysql-test/main/rowid_filter.result +++ b/mysql-test/main/rowid_filter.result @@ -2012,4 +2012,96 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter.test b/mysql-test/main/rowid_filter.test index 9c53367..6f26e81 100644 --- a/mysql-test/main/rowid_filter.test +++ b/mysql-test/main/rowid_filter.test @@ -300,4 +300,43 @@ SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3); DROP TABLE t1,t2; +--echo # +--echo # MDEV-19255: rowid range filter built for range condition +--echo # that uses in expensive subquery +--echo # + +CREATE TABLE t1 ( + pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); + +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; + +let $q= +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) + WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); + +eval $q; +eval EXPLAIN EXTENDED $q; +eval EXPLAIN FORMAT=JSON $q; + +DROP TABLE t1,t2; + set @@use_stat_tables=@save_use_stat_tables; diff --git a/mysql-test/main/rowid_filter_innodb.result b/mysql-test/main/rowid_filter_innodb.result index c877e5a..54c7e03 100644 --- a/mysql-test/main/rowid_filter_innodb.result +++ b/mysql-test/main/rowid_filter_innodb.result @@ -1941,6 +1941,98 @@ id select_type table type possible_keys key key_len ref rows filtered Extra Warnings: Note 1003 /* select#1 */ select `test`.`t1`.`pk` AS `pk` from `test`.`t1` having 0 DROP TABLE t1,t2; +# +# MDEV-19255: rowid range filter built for range condition +# that uses in expensive subquery +# +CREATE TABLE t1 ( +pk1 INT PRIMARY KEY, a1 INT, b1 VARCHAR(1), KEY(b1) +) ENGINE=MyISAM; +INSERT INTO t1 VALUES +(10,0,'z'),(11,3,'j'),(12,8,'f'),(13,8,'p'),(14,6,'w'),(15,0,'c'),(16,1,'j'), +(17,1,'f'),(18,5,'v'),(19,3,'f'),(20,2,'q'),(21,8,'y'),(22,0,'a'),(23,9,'w'), +(24,3,'e'),(25,1,'b'),(26,9,'r'),(27,2,'k'),(28,5,'c'),(29,3,'k'),(30,9,'b'), +(31,8,'j'),(32,1,'t'),(33,8,'n'),(34,3,'z'),(35,0,'u'),(36,3,'a'),(37,3,'g'), +(38,1,'f'),(39,6,'p'),(40,6,'m'),(41,6,'t'),(42,7,'i'),(43,4,'h'),(44,3,'d'), +(45,2,'b'),(46,1,'o'),(47,2,'j'),(48,6,'s'),(49,5,'q'),(50,6,'l'),(51,9,'j'), +(52,6,'y'),(53,0,'i'),(54,7,'x'),(55,2,'u'),(56,6,'t'),(57,4,'b'),(58,5,'m'), +(59,4,'x'),(60,8,'x'),(61,6,'v'),(62,8,'m'),(63,4,'j'),(64,8,'z'),(65,2,'a'), +(66,9,'i'),(67,4,'g'),(68,8,'h'),(69,1,'p'),(70,8,'a'),(71,0,'x'),(72,2,'s'), +(73,6,'k'),(74,0,'m'),(75,6,'e'),(76,9,'y'),(77,7,'d'),(78,7,'w'),(79,6,'y'), +(80,9,'s'),(81,9,'x'),(82,6,'l'),(83,9,'f'),(84,8,'x'),(85,1,'p'),(86,7,'y'), +(87,6,'p'),(88,1,'g'),(89,3,'c'),(90,5,'h'),(91,3,'p'),(92,2,'b'),(93,1,NULL), +(94,3,NULL),(95,2,'y'),(96,7,'s'),(97,7,'x'),(98,6,'i'),(99,9,'t'),(100,5,'j'), +(101,0,'u'),(102,7,'r'),(103,2,'x'),(104,8,'e'),(105,8,'i'),(106,5,'q'), +(107,8,'z'),(108,3,'k'),(109,65,NULL); +CREATE TABLE t2 (pk2 INT PRIMARY KEY, a2 INT, b2 VARCHAR(1)) ENGINE=MyISAM; +INSERT INTO t2 VALUES (1,1,'x'); +INSERT INTO t2 SELECT * FROM t1; +SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +pk1 a1 b1 pk2 a2 b2 +65 2 a 109 65 NULL +EXPLAIN EXTENDED SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 101 100.00 Using where +1 PRIMARY t1 eq_ref|filter PRIMARY,b1 PRIMARY|b1 4|4 test.t2.a2 1 (87%) 87.00 Using where; Using rowid filter +2 SUBQUERY t2 range PRIMARY PRIMARY 4 NULL 1 100.00 Using index condition +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`pk1` AS `pk1`,`test`.`t1`.`a1` AS `a1`,`test`.`t1`.`b1` AS `b1`,`test`.`t2`.`pk2` AS `pk2`,`test`.`t2`.`a2` AS `a2`,`test`.`t2`.`b2` AS `b2` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`pk1` = `test`.`t2`.`a2` and `test`.`t1`.`b1` <= (/* select#2 */ select max(`test`.`t2`.`b2`) from `test`.`t2` where `test`.`t2`.`pk2` <= 1) and `test`.`t2`.`a2` <> `test`.`t2`.`pk2` +EXPLAIN FORMAT=JSON SELECT * FROM t1 INNER JOIN t2 ON ( pk1 <> pk2 AND pk1 = a2 ) +WHERE b1 <= ( SELECT MAX(b2) FROM t2 WHERE pk2 <= 1 ); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 101, + "filtered": 100, + "attached_condition": "t2.a2 <> t2.pk2 and t2.a2 is not null" + }, + "table": { + "table_name": "t1", + "access_type": "eq_ref", + "possible_keys": ["PRIMARY", "b1"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk1"], + "ref": ["test.t2.a2"], + "rowid_filter": { + "range": { + "key": "b1", + "used_key_parts": ["b1"] + }, + "rows": 87, + "selectivity_pct": 87 + }, + "rows": 1, + "filtered": 87, + "attached_condition": "t1.b1 <= (subquery#2)" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "table_name": "t2", + "access_type": "range", + "possible_keys": ["PRIMARY"], + "key": "PRIMARY", + "key_length": "4", + "used_key_parts": ["pk2"], + "rows": 1, + "filtered": 100, + "index_condition": "t2.pk2 <= 1" + } + } + } + ] + } +} +DROP TABLE t1,t2; set @@use_stat_tables=@save_use_stat_tables; # # MDEV-18755: possible RORI-plan and possible plan with range filter diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 4fb9abc..2b1c4c1 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -54,7 +54,8 @@ Item_subselect::Item_subselect(THD *thd_arg): value_assigned(0), own_engine(0), thd(0), old_engine(0), have_to_be_excluded(0), inside_first_fix_fields(0), done_first_fix_fields(FALSE), - expr_cache(0), forced_const(FALSE), substitution(0), engine(0), eliminated(FALSE), + expr_cache(0), forced_const(FALSE), expensive_fl(FALSE), + substitution(0), engine(0), eliminated(FALSE), changed(0), is_correlated(FALSE), with_recursive_reference(0) { DBUG_ENTER("Item_subselect::Item_subselect"); @@ -585,6 +586,9 @@ bool Item_subselect::is_expensive() double examined_rows= 0; bool all_are_simple= true; + if (!expensive_fl && is_evaluated()) + return false; + /* check extremely simple select */ if (!unit->first_select()->next_select()) // no union { @@ -595,7 +599,7 @@ bool Item_subselect::is_expensive() SELECT_LEX *sl= unit->first_select(); JOIN *join = sl->join; if (join && !join->tables_list && !sl->first_inner_unit()) - return false; + return (expensive_fl= false); } @@ -605,14 +609,14 @@ bool Item_subselect::is_expensive() /* not optimized subquery */ if (!cur_join) - return true; + return (expensive_fl= true); /* If the subquery is not optimised or in the process of optimization it supposed to be expensive */ if (cur_join->optimization_state != JOIN::OPTIMIZATION_DONE) - return true; + return (expensive_fl= true); if (!cur_join->tables_list && !sl->first_inner_unit()) continue; @@ -634,7 +638,7 @@ bool Item_subselect::is_expensive() considered optimized if it has a join plan. */ if (!cur_join->join_tab) - return true; + return (expensive_fl= true); if (sl->first_inner_unit()) { @@ -642,15 +646,15 @@ bool Item_subselect::is_expensive() Subqueries that contain subqueries are considered expensive. @todo: accumulate the cost of subqueries. */ - return true; + return (expensive_fl= true); } examined_rows+= cur_join->get_examined_rows(); } // here we are sure that subquery is optimized so thd is set - return !all_are_simple && - (examined_rows > thd->variables.expensive_subquery_limit); + return (expensive_fl= !all_are_simple && + (examined_rows > thd->variables.expensive_subquery_limit)); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index 0e771ba..bbc24d3 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -73,6 +73,8 @@ class Item_subselect :public Item_result_field, to substitute 'this' with a constant item. */ bool forced_const; + /* Set to the result of the last call of is_expensive() */ + bool expensive_fl; #ifndef DBUG_OFF /* Count the number of times this subquery predicate has been executed. */ uint exec_counter;

1 0

[Commits] 8bcec84e8c5: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt
by Galina 18 Apr '19

18 Apr '19

revision-id: 8bcec84e8c5780a7c322357fd1deac75a5f2da70 (mariadb-10.4.4-25-g8bcec84e8c5) parent(s): ee4a2fef18136165a3267b4429e5921fc306cc20 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-04-19 00:03:14 +0300 message: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt The bug occurs because is_jtbm_const_tab field is not reset after the first execution of statement. It remains in the second execution when pushdown into IN subquery is made. That’s why pushdown for the second execution of statement is not made. To fix it is_jtbm_const_tab is reset for each statement execution. --- mysql-test/main/in_subq_cond_pushdown.result | 77 ++++++++++++++++++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 24 +++++++++ sql/opt_subselect.cc | 1 + 3 files changed, 102 insertions(+) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index eef320d2d04..53355fb7b4c 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3887,3 +3887,80 @@ i1 2 1 DROP TABLE t1,t2,t3; +# +# MDEV-19269: pushdown into IN subquery is not made +# on the second execution of stmt +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); +PREPARE stmt FROM " +EXPLAIN FORMAT=JSON +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);"; +set @a=2; +execute stmt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "message": "Impossible WHERE noticed after reading const tables" + }, + "subqueries": [ + { + "query_block": { + "select_id": 2, + "table": { + "message": "Impossible WHERE" + } + } + } + ] + } +} +set @a=1; +execute stmt; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.b = t1.a and t1.a is not null and t1.a is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "12", + "used_key_parts": ["x", "COUNT(t2.y)"], + "ref": ["test.t1.a", "test.t1.a"], + "rows": 1, + "filtered": 100, + "attached_condition": "t1.a = `<subquery2>`.`COUNT(t2.y)`", + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "having_condition": "`COUNT(t2.y)` = t2.x", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 3, + "filtered": 100 + } + } + } + } + } + } +} +DROP TABLE t1,t2; diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 7763201cda1..a4bcbaca97b 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -860,3 +860,27 @@ SELECT t3.i1 FROM t3 GROUP BY i1 HAVING t.i1 < 3)); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-19269: pushdown into IN subquery is not made +--echo # on the second execution of stmt +--echo # + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); + +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); + +PREPARE stmt FROM " +EXPLAIN FORMAT=JSON +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE @a=1 GROUP BY t2.x);"; + +set @a=2; +execute stmt; +set @a=1; +execute stmt; + +DROP TABLE t1,t2; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 32b70b41eb3..d0fd8c5ee55 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -6028,6 +6028,7 @@ bool setup_degenerate_jtbm_semi_joins(JOIN *join, if ((subq_pred= table->jtbm_subselect)) { + subq_pred->is_jtbm_const_tab= FALSE; JOIN *subq_join= subq_pred->unit->first_select()->join; if (!subq_join->tables_list || !subq_join->table_count)

1 0

[Commits] 056b6fe1d59: MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command
by Sergei Petrunia 18 Apr '19

18 Apr '19

revision-id: 056b6fe1d59b515a6380e50783b3c4ad0f93959f (mariadb-10.1.38-110-g056b6fe1d59) parent(s): 0bb924e18c338fa2dc901041aad09998b399efc1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-18 23:12:43 +0300 message: MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command Archive storage engine assumed that any query that attempts to read from the table will call ha_archive::info() beforehand. ha_archive would flush un-written data in that call (this would make it visible for the reads). Break this assumption. Flush the data when the table is opened for reading. This way, one can do multiple write statements without causing a flush, but as soon as we might need the data, we flush it. --- mysql-test/suite/archive/archive_eits.result | 24 +++++++++++++ mysql-test/suite/archive/archive_eits.test | 32 ++++++++++++++++++ storage/archive/ha_archive.cc | 50 ++++++++++++++++++---------- storage/archive/ha_archive.h | 3 ++ 4 files changed, 92 insertions(+), 17 deletions(-) diff --git a/mysql-test/suite/archive/archive_eits.result b/mysql-test/suite/archive/archive_eits.result new file mode 100644 index 00000000000..e077c2e4954 --- /dev/null +++ b/mysql-test/suite/archive/archive_eits.result @@ -0,0 +1,24 @@ +drop table if exists t1; +# +# MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command +# +CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive; +INSERT INTO t1 VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); +set @tmp1= @@optimizer_use_condition_selectivity; +set @tmp2= @@use_stat_tables; +set optimizer_use_condition_selectivity=4; +set use_stat_tables=PREFERABLY; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +select * from mysql.table_stats where table_name='t1' and db_name=database(); +db_name table_name cardinality +test t1 4 +drop table t1; +set optimizer_use_condition_selectivity=@tmp1; +set use_stat_tables=@tmp2; diff --git a/mysql-test/suite/archive/archive_eits.test b/mysql-test/suite/archive/archive_eits.test new file mode 100644 index 00000000000..04c4ccdb709 --- /dev/null +++ b/mysql-test/suite/archive/archive_eits.test @@ -0,0 +1,32 @@ +-- source include/have_archive.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +--echo # +--echo # MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command +--echo # + +CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive; +INSERT INTO t1 VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); + +set @tmp1= @@optimizer_use_condition_selectivity; +set @tmp2= @@use_stat_tables; + +set optimizer_use_condition_selectivity=4; +set use_stat_tables=PREFERABLY; +ANALYZE TABLE t1; + +select * from mysql.table_stats where table_name='t1' and db_name=database(); + +drop table t1; + +set optimizer_use_condition_selectivity=@tmp1; +set use_stat_tables=@tmp2; + + diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc index d70757e8142..e985f75d646 100644 --- a/storage/archive/ha_archive.cc +++ b/storage/archive/ha_archive.cc @@ -1650,7 +1650,6 @@ void ha_archive::update_create_info(HA_CREATE_INFO *create_info) DBUG_VOID_RETURN; } - /* Hints for optimizer, see ha_tina for more information */ @@ -1658,22 +1657,7 @@ int ha_archive::info(uint flag) { DBUG_ENTER("ha_archive::info"); - mysql_mutex_lock(&share->mutex); - if (share->dirty) - { - DBUG_PRINT("ha_archive", ("archive flushing out rows for scan")); - DBUG_ASSERT(share->archive_write_open); - azflush(&(share->archive_write), Z_SYNC_FLUSH); - share->dirty= FALSE; - } - - /* - This should be an accurate number now, though bulk and delayed inserts can - cause the number to be inaccurate. - */ - stats.records= share->rows_recorded; - mysql_mutex_unlock(&share->mutex); - + flush_and_clear_pending_writes(); stats.deleted= 0; DBUG_PRINT("ha_archive", ("Stats rows is %d\n", (int)stats.records)); @@ -1716,6 +1700,38 @@ int ha_archive::info(uint flag) } +int ha_archive::external_lock(THD *thd, int lock_type) +{ + if (lock_type == F_RDLCK) + { + // We are going to read from the table. Flush any pending writes that we + // may have + flush_and_clear_pending_writes(); + } + return 0; +} + + +void ha_archive::flush_and_clear_pending_writes() +{ + mysql_mutex_lock(&share->mutex); + if (share->dirty) + { + DBUG_PRINT("ha_archive", ("archive flushing out rows for scan")); + DBUG_ASSERT(share->archive_write_open); + azflush(&(share->archive_write), Z_SYNC_FLUSH); + share->dirty= FALSE; + } + + /* + This should be an accurate number now, though bulk and delayed inserts can + cause the number to be inaccurate. + */ + stats.records= share->rows_recorded; + mysql_mutex_unlock(&share->mutex); +} + + /* This method tells us that a bulk insert operation is about to occur. We set a flag which will keep write_row from saying that its data is dirty. This in diff --git a/storage/archive/ha_archive.h b/storage/archive/ha_archive.h index 56ff566db8c..a74374a340f 100644 --- a/storage/archive/ha_archive.h +++ b/storage/archive/ha_archive.h @@ -169,5 +169,8 @@ class ha_archive: public handler int unpack_row(azio_stream *file_to_read, uchar *record); unsigned int pack_row(uchar *record, azio_stream *writer); bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); + int external_lock(THD *thd, int lock_type); +private: + void flush_and_clear_pending_writes(); };

1 0

[Commits] 633c42cc662: MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command
by Sergei Petrunia 18 Apr '19

18 Apr '19

revision-id: 633c42cc662c2af410f774ae22dcf24d3c5d2085 (mariadb-10.0.37-51-g633c42cc662) parent(s): 2a0f1d613219ad7962c3394b9c1996ece40926df author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-18 20:56:56 +0300 message: MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command Archive storage engine assumed that any query that attempts to read from the table will call ha_archive::info() beforehand. ha_archive would flush un-written data in that call (this would make it visible for the reads). Break this assumption. Flush the data when the table is opened for reading. This way, one can do multiple write statements without causing a flush, but as soon as we might need the data, we flush it. --- mysql-test/suite/archive/archive_eits.result | 24 +++++++++++++ mysql-test/suite/archive/archive_eits.test | 32 ++++++++++++++++++ storage/archive/ha_archive.cc | 50 ++++++++++++++++++---------- storage/archive/ha_archive.h | 3 ++ 4 files changed, 92 insertions(+), 17 deletions(-) diff --git a/mysql-test/suite/archive/archive_eits.result b/mysql-test/suite/archive/archive_eits.result new file mode 100644 index 00000000000..e077c2e4954 --- /dev/null +++ b/mysql-test/suite/archive/archive_eits.result @@ -0,0 +1,24 @@ +drop table if exists t1; +# +# MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command +# +CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive; +INSERT INTO t1 VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); +set @tmp1= @@optimizer_use_condition_selectivity; +set @tmp2= @@use_stat_tables; +set optimizer_use_condition_selectivity=4; +set use_stat_tables=PREFERABLY; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze note The storage engine for the table doesn't support analyze +select * from mysql.table_stats where table_name='t1' and db_name=database(); +db_name table_name cardinality +test t1 4 +drop table t1; +set optimizer_use_condition_selectivity=@tmp1; +set use_stat_tables=@tmp2; diff --git a/mysql-test/suite/archive/archive_eits.test b/mysql-test/suite/archive/archive_eits.test new file mode 100644 index 00000000000..04c4ccdb709 --- /dev/null +++ b/mysql-test/suite/archive/archive_eits.test @@ -0,0 +1,32 @@ +-- source include/have_archive.inc + +--disable_warnings +drop table if exists t1; +--enable_warnings + +--echo # +--echo # MDEV-17297: stats.records=0 for a table of Archive engine when it has rows, when we run ANALYZE command +--echo # + +CREATE TABLE t1 (fid INTEGER PRIMARY KEY AUTO_INCREMENT, g POINT)engine=archive; +INSERT INTO t1 VALUES +(101, PointFromText('POINT(10 10)')), +(102, PointFromText('POINT(20 10)')), +(103, PointFromText('POINT(20 20)')), +(104, PointFromWKB(AsWKB(PointFromText('POINT(10 20)')))); + +set @tmp1= @@optimizer_use_condition_selectivity; +set @tmp2= @@use_stat_tables; + +set optimizer_use_condition_selectivity=4; +set use_stat_tables=PREFERABLY; +ANALYZE TABLE t1; + +select * from mysql.table_stats where table_name='t1' and db_name=database(); + +drop table t1; + +set optimizer_use_condition_selectivity=@tmp1; +set use_stat_tables=@tmp2; + + diff --git a/storage/archive/ha_archive.cc b/storage/archive/ha_archive.cc index fd0a6b7d4fb..d14a3030a5d 100644 --- a/storage/archive/ha_archive.cc +++ b/storage/archive/ha_archive.cc @@ -1652,7 +1652,6 @@ void ha_archive::update_create_info(HA_CREATE_INFO *create_info) DBUG_VOID_RETURN; } - /* Hints for optimizer, see ha_tina for more information */ @@ -1660,22 +1659,7 @@ int ha_archive::info(uint flag) { DBUG_ENTER("ha_archive::info"); - mysql_mutex_lock(&share->mutex); - if (share->dirty) - { - DBUG_PRINT("ha_archive", ("archive flushing out rows for scan")); - DBUG_ASSERT(share->archive_write_open); - azflush(&(share->archive_write), Z_SYNC_FLUSH); - share->dirty= FALSE; - } - - /* - This should be an accurate number now, though bulk and delayed inserts can - cause the number to be inaccurate. - */ - stats.records= share->rows_recorded; - mysql_mutex_unlock(&share->mutex); - + flush_and_clear_pending_writes(); stats.deleted= 0; DBUG_PRINT("ha_archive", ("Stats rows is %d\n", (int)stats.records)); @@ -1718,6 +1702,38 @@ int ha_archive::info(uint flag) } +int ha_archive::external_lock(THD *thd, int lock_type) +{ + if (lock_type == F_RDLCK) + { + // We are going to read from the table. Flush any pending writes that we + // may have + flush_and_clear_pending_writes(); + } + return 0; +} + + +void ha_archive::flush_and_clear_pending_writes() +{ + mysql_mutex_lock(&share->mutex); + if (share->dirty) + { + DBUG_PRINT("ha_archive", ("archive flushing out rows for scan")); + DBUG_ASSERT(share->archive_write_open); + azflush(&(share->archive_write), Z_SYNC_FLUSH); + share->dirty= FALSE; + } + + /* + This should be an accurate number now, though bulk and delayed inserts can + cause the number to be inaccurate. + */ + stats.records= share->rows_recorded; + mysql_mutex_unlock(&share->mutex); +} + + /* This method tells us that a bulk insert operation is about to occur. We set a flag which will keep write_row from saying that its data is dirty. This in diff --git a/storage/archive/ha_archive.h b/storage/archive/ha_archive.h index 56ff566db8c..a74374a340f 100644 --- a/storage/archive/ha_archive.h +++ b/storage/archive/ha_archive.h @@ -169,5 +169,8 @@ class ha_archive: public handler int unpack_row(azio_stream *file_to_read, uchar *record); unsigned int pack_row(uchar *record, azio_stream *writer); bool check_if_incompatible_data(HA_CREATE_INFO *info, uint table_changes); + int external_lock(THD *thd, int lock_type); +private: + void flush_and_clear_pending_writes(); };

1 0

[Commits] 59ed5f3: MDEV-19164 Assertion `fixed' failed in Item_func_inet_aton::val_int
by IgorBabaev 18 Apr '19

18 Apr '19

revision-id: 59ed5f3aa4bc5a02a65f93b1d054ccc0fb2cd248 (mariadb-10.4.4-26-g59ed5f3) parent(s): e7029e864f4b2c8fa88362677ee8150cc479f07f author: Igor Babaev committer: Igor Babaev timestamp: 2019-04-17 21:37:29 -0700 message: MDEV-19164 Assertion `fixed' failed in Item_func_inet_aton::val_int When pushing a condition from HAVING into WHERE the function st_select_lex::pushdown_from_having_into_where() transforms column references in the pushed condition then performs cleanup of items of the condition and finally calls fix_fields() for the condition items. The cleanup is performed by a call of the method walk() with cleanup_processor as the first parameter. Unfortunately this sequence of calls does not work if the condition contains cached items, because fix_fields() cannot go through Item_cache items and this leaves underlying items unfixed. The solution of this problem used in this patch is just does not allow to process Item_cache objects when performing cleanup of the pushed condition. In order to let the traversal procedure walk() not to process Item_cache objects the third parameter of the used call of walk() is set to a fictitious pointer (void *) 1. And Item_cache::walk() is changed to prevent any action when it gets such value as the third parameter. --- mysql-test/main/having_cond_pushdown.result | 76 +++++++++++++++++++++++++++++ mysql-test/main/having_cond_pushdown.test | 30 ++++++++++++ sql/item.h | 10 +++- sql/sql_lex.cc | 6 +-- 4 files changed, 117 insertions(+), 5 deletions(-) diff --git a/mysql-test/main/having_cond_pushdown.result b/mysql-test/main/having_cond_pushdown.result index 85ca034..d7c9d93 100644 --- a/mysql-test/main/having_cond_pushdown.result +++ b/mysql-test/main/having_cond_pushdown.result @@ -4657,3 +4657,79 @@ GROUP BY v1.pk HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; +# +# MDEV-19164: pushdown of condition with cached items +# +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); +select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +d1 +explain extended select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 4 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26') group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 4, + "filtered": 100, + "attached_condition": "t1.d1 between <cache>(inet_aton('1978-04-27')) and <cache>('2018-08-26')" + } + } + } + } +} +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); +select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +d1 +2018-01-15 +2018-02-20 +Warnings: +Warning 1292 Truncated incorrect datetime value: '1' +explain extended select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where; Using temporary; Using filesort +Warnings: +Note 1003 select `test`.`t1`.`d1` AS `d1` from `test`.`t1` where `test`.`t1`.`d1` not between <cache>(0) and <cache>(exp(0)) group by `test`.`t1`.`d1` having 1 +explain format=json select d1 from t1 +group by d1 +having d1 not between 0 AND exp(0); +EXPLAIN +{ + "query_block": { + "select_id": 1, + "filesort": { + "sort_key": "t1.d1", + "temporary_table": { + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 2, + "filtered": 100, + "attached_condition": "t1.d1 not between <cache>(0) and <cache>(exp(0))" + } + } + } + } +} +drop table t1; diff --git a/mysql-test/main/having_cond_pushdown.test b/mysql-test/main/having_cond_pushdown.test index 257e5cb..5088dad 100644 --- a/mysql-test/main/having_cond_pushdown.test +++ b/mysql-test/main/having_cond_pushdown.test @@ -1340,3 +1340,33 @@ HAVING (v1.pk = 1); DROP TABLE t1,t2,tmp1; DROP VIEW v1; + +--echo # +--echo # MDEV-19164: pushdown of condition with cached items +--echo # + +create table t1 (d1 date); +insert into t1 values (null),('1971-03-06'),('1993-06-05'),('1998-07-08'); + +let $q1= +select d1 from t1 + group by d1 + having d1 between (inet_aton('1978-04-27')) and '2018-08-26'; + +eval $q1; +eval explain extended $q1; +eval explain format=json $q1; + +delete from t1; +insert into t1 values ('2018-01-15'),('2018-02-20'); + +let $q2= +select d1 from t1 + group by d1 + having d1 not between 0 AND exp(0); + +eval $q2; +eval explain extended $q2; +eval explain format=json $q2; + +drop table t1; diff --git a/sql/item.h b/sql/item.h index 97d31e6..0d40735 100644 --- a/sql/item.h +++ b/sql/item.h @@ -714,6 +714,8 @@ class Item_const /****************************************************************************/ +#define STOP_PTR ((void *) 1) + class Item: public Value_source, public Type_all_attributes { @@ -1829,8 +1831,10 @@ class Item: public Value_source, /*========= Item processors, to be used with Item::walk() ========*/ virtual bool remove_dependence_processor(void *arg) { return 0; } virtual bool cleanup_processor(void *arg); - virtual bool cleanup_excluding_fields_processor(void *arg) { return cleanup_processor(arg); } - virtual bool cleanup_excluding_const_fields_processor(void *arg) { return cleanup_processor(arg); } + virtual bool cleanup_excluding_fields_processor (void *arg) + { return cleanup_processor(arg); } + virtual bool cleanup_excluding_const_fields_processor (void *arg) + { return cleanup_processor(arg); } virtual bool collect_item_field_processor(void *arg) { return 0; } virtual bool collect_outer_ref_processor(void *arg) {return 0; } virtual bool check_inner_refs_processor(void *arg) { return 0; } @@ -6535,6 +6539,8 @@ class Item_cache: public Item, virtual void set_null(); bool walk(Item_processor processor, bool walk_subquery, void *arg) { + if (arg == STOP_PTR) + return FALSE; if (example && example->walk(processor, walk_subquery, arg)) return TRUE; return (this->*processor)(arg); diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index f74541b..8ea0bc5 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -10112,7 +10112,7 @@ Item *remove_pushed_top_conjuncts_for_having(THD *thd, Item *cond) Multiple equalities are not removed but marked with DELETION_FL flag. They will be deleted later in substitite_for_best_equal_field() called for the HAVING condition. - 5. Unwrap fields wrapped in Item_ref wrappers contain in the condition + 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition of attach_to_conds so the condition could be pushed into WHERE. @note @@ -10203,7 +10203,7 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) join->having_equal= 0; /* - 5. Unwrap fields wrapped in Item_ref wrappers contain in the condition + 5. Unwrap fields wrapped in Item_ref wrappers contained in the condition of attach_to_conds so the condition could be pushed into WHERE. */ it.rewind(); @@ -10213,7 +10213,7 @@ Item *st_select_lex::pushdown_from_having_into_where(THD *thd, Item *having) &Item::field_transformer_for_having_pushdown, (uchar *)this); - if (item->walk(&Item::cleanup_processor, 0, 0) || + if (item->walk(&Item:: cleanup_processor, 0, STOP_PTR) || item->fix_fields(thd, NULL)) { attach_to_conds.empty();

1 0

[Commits] bed6eb20889: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt
by Galina 17 Apr '19

17 Apr '19

revision-id: bed6eb20889f9f5274818c323b4b386fda3ccb6b (mariadb-10.4.4-25-gbed6eb20889) parent(s): ee4a2fef18136165a3267b4429e5921fc306cc20 author: Galina Shalygina committer: Galina Shalygina timestamp: 2019-04-17 19:58:29 +0300 message: MDEV-19269 Pushdown into IN subquery is not made on the second execution of stmt The bug occurs because is_jtbm_const_tab field is not reset after the first execution of statement. It remains in the second execution when pushdown into IN subquery is made. That’s why pushdown for the second execution of statement is not made. To fix it is_jtbm_const_tab is reset for each statement execution. --- mysql-test/main/in_subq_cond_pushdown.result | 25 ++++++++++++++++++++++++ mysql-test/main/in_subq_cond_pushdown.test | 29 ++++++++++++++++++++++++++++ sql/opt_subselect.cc | 1 + 3 files changed, 55 insertions(+) diff --git a/mysql-test/main/in_subq_cond_pushdown.result b/mysql-test/main/in_subq_cond_pushdown.result index eef320d2d04..5a5b52f73d4 100644 --- a/mysql-test/main/in_subq_cond_pushdown.result +++ b/mysql-test/main/in_subq_cond_pushdown.result @@ -3887,3 +3887,28 @@ i1 2 1 DROP TABLE t1,t2,t3; +# +# MDEV-19269: pushdown into IN subquery is not made +# on the second execution of stmt +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 GROUP BY t2.x HAVING 1=2);"; +EXECUTE stmt; +a b +EXECUTE stmt; +a b +DROP TABLE t1,t2; diff --git a/mysql-test/main/in_subq_cond_pushdown.test b/mysql-test/main/in_subq_cond_pushdown.test index 7763201cda1..91423f6f053 100644 --- a/mysql-test/main/in_subq_cond_pushdown.test +++ b/mysql-test/main/in_subq_cond_pushdown.test @@ -860,3 +860,32 @@ SELECT t3.i1 FROM t3 GROUP BY i1 HAVING t.i1 < 3)); DROP TABLE t1,t2,t3; + +--echo # +--echo # MDEV-19269: pushdown into IN subquery is not made +--echo # on the second execution of stmt +--echo # + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (x int, y int); + +INSERT INTO t1 VALUES (1,1),(2,2); +INSERT INTO t2 VALUES (1,1),(2,2),(2,3); + +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +EXECUTE stmt; +EXECUTE stmt; + +PREPARE stmt FROM " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 GROUP BY t2.x HAVING 1=2);"; + +EXECUTE stmt; +EXECUTE stmt; + +DROP TABLE t1,t2; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 32b70b41eb3..d0fd8c5ee55 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -6028,6 +6028,7 @@ bool setup_degenerate_jtbm_semi_joins(JOIN *join, if ((subq_pred= table->jtbm_subselect)) { + subq_pred->is_jtbm_const_tab= FALSE; JOIN *subq_join= subq_pred->unit->first_select()->join; if (!subq_join->tables_list || !subq_join->table_count)

1 0

[Commits] 8adcb55df8e: MDEV-19266: Crash in EITS code when enabling 128 indexes
by Sergei Petrunia 17 Apr '19

17 Apr '19

revision-id: 8adcb55df8e892f866ec930f3e6df30bd5a1d9ad (mariadb-10.4.4-22-g8adcb55df8e) parent(s): 682b360c343c5a6f61282d07a4a904b5bb281b12 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2019-04-17 13:56:54 +0300 message: MDEV-19266: Crash in EITS code when enabling 128 indexes Do not attempt to set param->table->with_impossible_ranges if the range optimizer is using pseudo-indexes (which is true when we are computing EITS selectivity estimates or doing partition pruning). --- sql/opt_range.cc | 6 +++++- 1 file changed, 5 insertions(+), 1 deletion(-) diff --git a/sql/opt_range.cc b/sql/opt_range.cc index 5ab3d70214d..ed9cd541f70 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -9044,7 +9044,11 @@ int and_range_trees(RANGE_OPT_PARAM *param, SEL_TREE *tree1, SEL_TREE *tree2, if (key && key->type == SEL_ARG::IMPOSSIBLE) { result->type= SEL_TREE::IMPOSSIBLE; - param->table->with_impossible_ranges.set_bit(param->real_keynr[key_no]); + if (param->using_real_indexes) + { + param->table->with_impossible_ranges.set_bit(param-> + real_keynr[key_no]); + } DBUG_RETURN(1); } result_keys.set_bit(key_no);

1 0

[Commits] e1cca292936: postreview changes
by Oleksandr Byelkin 16 Apr '19

16 Apr '19

revision-id: e1cca2929362e0197f4f0e12ef7e6c8e5979ba73 (mariadb-10.4.4-18-ge1cca292936) parent(s): 17cba93aa83b620fb1b771ea287bcd5b173d2776 author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-16 09:25:04 +0200 message: postreview changes --- mysql-test/main/subselect_innodb.result | 37 ++++++++++++++++++++++----------- mysql-test/main/subselect_innodb.test | 34 ++++++++++++++++++------------ 2 files changed, 46 insertions(+), 25 deletions(-) diff --git a/mysql-test/main/subselect_innodb.result b/mysql-test/main/subselect_innodb.result index 518158e3a04..8e09be9b705 100644 --- a/mysql-test/main/subselect_innodb.result +++ b/mysql-test/main/subselect_innodb.result @@ -621,18 +621,31 @@ DROP TABLE t1,t2,t3,t4; # failed in Item_equal::fix_fields, server crashes after 2nd execution # of PS # -CREATE TABLE t1 (pk int primary key, i1 int, i2 int, v1 varchar(1), v2 varchar(1), KEY i1 (i1), KEY v2 (v2,i1)) ENGINE=InnoDB ; -INSERT INTO t1 VALUES (12,1,1,'r','r'); -CREATE TABLE t2 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ; -CREATE TABLE t3 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ; -INSERT INTO t3 VALUES (19,1,9,NULL,NULL),(20,5,6,'r','r'); +create table t1 (a int, b int); +create table t2 (x int, y int); +insert into t1 values (1,1),(2,2); +insert into t2 values (1,1),(2,2),(2,3); +# here we can see conditions pushdown (see HAVING): prepare stmt from " -SELECT 1 FROM t1 -WHERE t1.pk = t1.i1 - AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3 - WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));"; +explain extended +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; execute stmt; -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 Impossible WHERE noticed after reading const tables +2 MATERIALIZED NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from <materialize> (/* select#2 */ select `test`.`t2`.`x`,count(`test`.`t2`.`y`) from `test`.`t2` where 0 group by `test`.`t2`.`x` having `COUNT(t2.y)` = `test`.`t2`.`x`) join `test`.`t1` where 0 +# here re-execution of the pushdown does not crash: +prepare stmt from " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; execute stmt; -1 -drop table t1,t2,t3; +a b +execute stmt; +a b +execute stmt; +a b +drop table t1,t2; diff --git a/mysql-test/main/subselect_innodb.test b/mysql-test/main/subselect_innodb.test index 214d692e793..b8d12d04a5e 100644 --- a/mysql-test/main/subselect_innodb.test +++ b/mysql-test/main/subselect_innodb.test @@ -617,21 +617,29 @@ DROP TABLE t1,t2,t3,t4; --echo # of PS --echo # -CREATE TABLE t1 (pk int primary key, i1 int, i2 int, v1 varchar(1), v2 varchar(1), KEY i1 (i1), KEY v2 (v2,i1)) ENGINE=InnoDB ; -INSERT INTO t1 VALUES (12,1,1,'r','r'); - -CREATE TABLE t2 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ; - -CREATE TABLE t3 (pk int, i1 int, i2 int, v1 varchar(1), v2 varchar(1)) ENGINE=InnoDB ; -INSERT INTO t3 VALUES (19,1,9,NULL,NULL),(20,5,6,'r','r'); +create table t1 (a int, b int); +create table t2 (x int, y int); +insert into t1 values (1,1),(2,2); +insert into t2 values (1,1),(2,2),(2,3); + +--echo # here we can see conditions pushdown (see HAVING): prepare stmt from " -SELECT 1 FROM t1 -WHERE t1.pk = t1.i1 - AND ((t1.pk,t1.i1) IN (SELECT t3.pk, COUNT(t3.pk) FROM t3 - WHERE EXISTS (SELECT 1 FROM (t1 JOIN t3 ON (t3.i2 = t1.i1)) WHERE t3.v1 = t1.v1)));"; - +explain extended +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +execute stmt; + +--echo # here re-execution of the pushdown does not crash: +prepare stmt from " +SELECT * FROM t1 +WHERE a = b + AND (a,b) IN (SELECT t2.x, COUNT(t2.y) FROM t2 WHERE 1=2 GROUP BY t2.x);"; + +execute stmt; execute stmt; execute stmt; -drop table t1,t2,t3; +drop table t1,t2;

1 0

[Commits] 74ce152dca3: cc
by Oleksandr Byelkin 16 Apr '19

16 Apr '19

revision-id: 74ce152dca39685cf534d86a9b2c553f4bb1551c (mariadb-5.5.63-19-g74ce152dca3) parent(s): d85bf9f2d9bad832db72f62a942e38173bbf31ed author: Oleksandr Byelkin committer: Oleksandr Byelkin timestamp: 2019-04-16 08:55:51 +0200 message: cc --- sql/item_subselect.cc | 36 +++++++++++++++++++++++++++++++----- sql/item_subselect.h | 19 ++++--------------- sql/opt_subselect.cc | 5 ++++- 3 files changed, 39 insertions(+), 21 deletions(-) diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index c578beb9fc9..c7e638c1772 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -3043,13 +3043,23 @@ bool subselect_union_engine::no_rows() } +// constructor can assign THD because it will be called after JOIN::prepare +subselect_uniquesubquery_engine::subselect_uniquesubquery_engine(THD *thd_arg, + st_join_table *tab_arg, Item_subselect *subs, Item *where, uint8* uncache) + :subselect_engine(subs, 0), tab(tab_arg), cond(where), + uncacheable_ref(uncache) +{ + Item_in_subselect *in_subs= (Item_in_subselect *) item; + DBUG_ASSERT(item->unit->first_select()->next_select() == 0); + *uncacheable_ref= (in_subs->left_expr->const_item() ? + 0 : + UNCACHEABLE_DEPENDENT_INJECTED); +} + void subselect_uniquesubquery_engine::cleanup() { DBUG_ENTER("subselect_uniquesubquery_engine::cleanup"); Item_in_subselect *in_subs= (Item_in_subselect *) item; - tab->select->uncacheable= (in_subs->left_expr->const_item() ? - 0 : - UNCACHEABLE_DEPENDENT_INJECTED); /* Note for mergers: we don't have to, and actually must not de-initialize tab->table->file here. @@ -3475,7 +3485,7 @@ bool subselect_uniquesubquery_engine::copy_ref_key(bool skip_constants) uint8 subselect_uniquesubquery_engine::uncacheable() { - return tab->select->uncachable; + return *uncacheable_ref; } @@ -3616,6 +3626,17 @@ subselect_uniquesubquery_engine::~subselect_uniquesubquery_engine() //psergey-merge: don't need this after all: tab->table->file->ha_index_end(); } +// constructor can assign THD because it will be called after JOIN::prepare +subselect_indexsubquery_engine::subselect_indexsubquery_engine + (THD *thd_arg, st_join_table *tab_arg, Item_subselect *subs, Item *where, + Item *having_arg, bool chk_null) + :subselect_uniquesubquery_engine(thd_arg, tab_arg, subs, where, + &subs->unit->first_select()->uncacheable), + check_null(chk_null), + having(having_arg) +{ + DBUG_ASSERT(subs->unit->first_select()->next_select() == 0); +} /** Execute subselect via unique index lookup @@ -4590,8 +4611,13 @@ subselect_hash_sj_engine::make_unique_engine() tab->preread_init_done= FALSE; tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE); + DBUG_ASSERT(item->unit->first_select()->next_select() == 0); + DBUG_RETURN(new subselect_uniquesubquery_engine(thd, tab, item, - semi_join_conds)); + semi_join_conds, + &item->unit-> + first_select()-> + uncacheable)); } diff --git a/sql/item_subselect.h b/sql/item_subselect.h index ebb2445fc4f..f315491d46c 100644 --- a/sql/item_subselect.h +++ b/sql/item_subselect.h @@ -887,19 +887,13 @@ class subselect_uniquesubquery_engine: public subselect_engine TRUE<=> last execution produced empty set. Valid only when left expression is NULL. */ + uint8 *uncacheable_ref; bool empty_result_set; public: - // constructor can assign THD because it will be called after JOIN::prepare subselect_uniquesubquery_engine(THD *thd_arg, st_join_table *tab_arg, - Item_subselect *subs, Item *where) - :subselect_engine(subs, 0), tab(tab_arg), cond(where) - { - Item_in_subselect *in_subs= (Item_in_subselect *) item; - tab->select->uncacheable= (in_subs->left_expr->const_item() ? - 0 : - UNCACHEABLE_DEPENDENT_INJECTED); - } + Item_subselect *subs, Item *where, + uint8 *uncach_ref); ~subselect_uniquesubquery_engine(); void cleanup(); int prepare(THD *); @@ -958,14 +952,9 @@ class subselect_indexsubquery_engine: public subselect_uniquesubquery_engine Item *having; public: - // constructor can assign THD because it will be called after JOIN::prepare subselect_indexsubquery_engine(THD *thd_arg, st_join_table *tab_arg, Item_subselect *subs, Item *where, - Item *having_arg, bool chk_null) - :subselect_uniquesubquery_engine(thd_arg, tab_arg, subs, where), - check_null(chk_null), - having(having_arg) - {} + Item *having_arg, bool chk_null); int exec(); virtual void print (String *str, enum_query_type query_type); virtual enum_engine_type engine_type() { return INDEXSUBQUERY_ENGINE; } diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 62a5b0793e9..9266ccc0d9f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -5102,7 +5102,10 @@ int rewrite_to_index_subquery_engine(JOIN *join) subselect_uniquesubquery_engine(thd, join_tab, unit->item, - where))); + where, + &join-> + select_lex-> + uncacheable))); } else if (join_tab[0].type == JT_REF && join_tab[0].ref.items[0]->name == in_left_expr_name)

1 0