- commits - lists.mariadb.org

[Commits] eb6979ee420: Make the Optimizer Trace of reqular query and PS EXECUTE be identical
by psergey 25 Nov '21

25 Nov '21

revision-id: eb6979ee420475c16b7f172fbde8b5a6a0d0c7ec (mariadb-10.4.22-30-geb6979ee420) parent(s): b53ee760ff17e3d9ed95b75e5ea2de124ae69384 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-25 18:27:56 +0300 message: Make the Optimizer Trace of reqular query and PS EXECUTE be identical Print this piece when we've just made the choice to convert to semi-join. Also, print it when we've already made that choice before: transformation": { "select_id": 2, "from": "IN (SELECT)", "to": "semijoin", "chosen": true } --- sql/opt_subselect.cc | 9 +++++++++ 1 file changed, 9 insertions(+) diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index cef6cd9c160..e0c1d6dac4a 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -714,6 +714,15 @@ int check_and_do_in_subquery_rewrites(JOIN *join) if (arena) thd->restore_active_arena(arena, &backup); in_subs->is_registered_semijoin= TRUE; + } + + /* + Print the transformation into trace. Do it when we've just set + is_registered_semijoin=TRUE above, and also do it when we've already + had it set. + */ + if (in_subs->is_registered_semijoin) + { OPT_TRACE_TRANSFORM(thd, trace_wrapper, trace_transform, select_lex->select_number, "IN (SELECT)", "semijoin");

1 0

[Commits] fbc01355a7e: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by psergey 25 Nov '21

25 Nov '21

revision-id: fbc01355a7eefc2c5c62dfc2fce5e14f34c2ee7f (mariadb-10.3.31-70-gfbc01355a7e) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-25 12:20:29 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are not going to execute those anyway. --- mysql-test/main/derived_cond_pushdown.result | 1 - mysql-test/main/derived_view.result | 1 - mysql-test/main/explain_innodb.result | 15 +++++++++++++++ mysql-test/main/explain_innodb.test | 17 +++++++++++++++++ mysql-test/main/join.result | 1 - mysql-test/main/ps.result | 3 --- mysql-test/main/view.result | 1 - sql/sql_select.cc | 4 +++- 8 files changed, 35 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index d2c116913f4..a21bb48509a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16898,7 +16898,6 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; 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 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 65a1adcaddd..d1ce6c8e623 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1558,7 +1558,6 @@ EXPLAIN EXTENDED SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b; 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 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v2`.`b` AS `b` from `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..948673d836c --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,15 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +id +select 1; +1 +1 +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..3e24a78085c --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,17 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index e667eab7eb9..88f67fb8074 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1499,7 +1499,6 @@ EXPLAIN EXTENDED SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; 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 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where `v2`.`i2` = NULL and `v2`.`a` < `v2`.`b` DROP VIEW v2; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 051f40cfd78..222afd6c1f3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -160,7 +160,6 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -168,7 +167,6 @@ id select_type table type possible_keys key key_len ref rows Extra execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -176,7 +174,6 @@ id select_type table type possible_keys key key_len ref rows Extra explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 34dd8f3c8dc..37678a1b50d 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -5714,7 +5714,6 @@ ON t36.f36 = v60.f60 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..b2c1d1e3db5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26322,6 +26322,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, elimination. (2) they are not merged derived tables (3) they are not hanging CTEs (they are needed for execution) + (4) this is not a derived subquery in a degenerate select. */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || @@ -26329,7 +26330,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, (!tmp_unit->with_element || (tmp_unit->derived && tmp_unit->derived->derived_result && - !tmp_unit->with_element->is_hanging_recursive()))) // (3) + !tmp_unit->with_element->is_hanging_recursive())) && // (3) + !(tmp_unit->derived && zero_result_cause)) // (4) { explain->add_child(tmp_unit->first_select()->select_number); }

1 0

[Commits] 9feee441a53: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by psergey 25 Nov '21

25 Nov '21

revision-id: 9feee441a5363a5a26f858acff1a55cc7c0e8ac7 (mariadb-10.3.31-70-g9feee441a53) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-25 12:10:31 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are not going to execute those anyway. --- mysql-test/main/derived_cond_pushdown.result | 1 - mysql-test/main/derived_view.result | 1 - mysql-test/main/explain_innodb.result | 15 +++++++++++++++ mysql-test/main/explain_innodb.test | 17 +++++++++++++++++ mysql-test/main/join.result | 1 - mysql-test/main/ps.result | 3 --- mysql-test/main/view.result | 1 - sql/sql_select.cc | 4 +++- 8 files changed, 35 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index d2c116913f4..a21bb48509a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16898,7 +16898,6 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; 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 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 65a1adcaddd..d1ce6c8e623 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1558,7 +1558,6 @@ EXPLAIN EXTENDED SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b; 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 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v2`.`b` AS `b` from `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..ca7479a8d6a --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,15 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0; +id +select 1; +1 +1 +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..3e24a78085c --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,17 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; +select 1; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index e667eab7eb9..88f67fb8074 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1499,7 +1499,6 @@ EXPLAIN EXTENDED SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; 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 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where `v2`.`i2` = NULL and `v2`.`a` < `v2`.`b` DROP VIEW v2; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 051f40cfd78..222afd6c1f3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -160,7 +160,6 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -168,7 +167,6 @@ id select_type table type possible_keys key key_len ref rows Extra execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -176,7 +174,6 @@ id select_type table type possible_keys key key_len ref rows Extra explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 34dd8f3c8dc..37678a1b50d 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -5714,7 +5714,6 @@ ON t36.f36 = v60.f60 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..b2c1d1e3db5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26322,6 +26322,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, elimination. (2) they are not merged derived tables (3) they are not hanging CTEs (they are needed for execution) + (4) this is not a derived subquery in a degenerate select. */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || @@ -26329,7 +26330,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, (!tmp_unit->with_element || (tmp_unit->derived && tmp_unit->derived->derived_result && - !tmp_unit->with_element->is_hanging_recursive()))) // (3) + !tmp_unit->with_element->is_hanging_recursive())) && // (3) + !(tmp_unit->derived && zero_result_cause)) // (4) { explain->add_child(tmp_unit->first_select()->select_number); }

1 0

[Commits] cf0e887: MDEV-26553 NOT IN subquery construct crashing 10.1 and up
by IgorBabaev 25 Nov '21

25 Nov '21

revision-id: cf0e8870f157a2e41d0de700e8b1834bbbdeff79 (mariadb-10.2.31-1264-gcf0e887) parent(s): 114e18b8b68a00b3829ac231cc8f84187f529287 author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-24 23:19:22 -0800 message: MDEV-26553 NOT IN subquery construct crashing 10.1 and up This bug was introduced by commit be00e279c6061134a33a8099fd69d4304735d02e The commit was applied for the task MDEV-6480 that allowed to remove top level disjuncts from WHERE conditions if the range optimizer evaluated them as always equal to FALSE/NULL. If such disjuncts are removed the WHERE condition may become an AND formula and if this formula contains multiple equalities the field JOIN::item_equal must be updated to refer to these equalities. The above mentioned commit forgot to do this and it could cause crashes for some queries. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/range.result | 24 ++++++++++++++++++++++++ mysql-test/r/range_mrr_icp.result | 24 ++++++++++++++++++++++++ mysql-test/t/range.test | 25 ++++++++++++++++++++++++- sql/sql_select.cc | 6 ++++++ 4 files changed, 78 insertions(+), 1 deletion(-) diff --git a/mysql-test/r/range.result b/mysql-test/r/range.result index 6a3850c..f1f949a 100644 --- a/mysql-test/r/range.result +++ b/mysql-test/r/range.result @@ -3201,5 +3201,29 @@ pk i v a b 2 2 4 2 4 drop table t1, t2; # +# MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed +# +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); +1 +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or b = NULL); +1 +select c from t2 +where 2 not in (select 1 from t1 +where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); +c +5 +2 +3 +4 +drop table t1,t2; +# # End of 10.2 tests # diff --git a/mysql-test/r/range_mrr_icp.result b/mysql-test/r/range_mrr_icp.result index 24f42f3..645576f 100644 --- a/mysql-test/r/range_mrr_icp.result +++ b/mysql-test/r/range_mrr_icp.result @@ -3213,6 +3213,30 @@ pk i v a b 2 2 4 2 4 drop table t1, t2; # +# MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed +# +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); +1 +select 1 from t1 s1 +where 1 not in (select 1 from t1 +where ((a = 1 or a = 2) and b = 1) or b = NULL); +1 +select c from t2 +where 2 not in (select 1 from t1 +where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); +c +5 +2 +3 +4 +drop table t1,t2; +# # End of 10.2 tests # set optimizer_switch=@mrr_icp_extra_tmp; diff --git a/mysql-test/t/range.test b/mysql-test/t/range.test index 890377e..7369b9d 100644 --- a/mysql-test/t/range.test +++ b/mysql-test/t/range.test @@ -2232,6 +2232,29 @@ select * from t1 inner join t2 on ( t2.b = t1.v or t2.a = t1.pk); drop table t1, t2; --echo # ---echo # End of 10.2 tests +--echo # MDEV-26553: Always FALSE/NULL disjunct on top level of WHERE is removed --echo # +create table t1 (a int, b int, index idx(a,b)); +insert into t1 values (1,1), (1,2), (2,1), (2,2), (3,3); + +create table t2 (c int); +insert into t2 values (5), (2), (3), (4); + +select 1 from t1 s1 + where 1 not in (select 1 from t1 + where ((a = 1 or a = 2) and b = 1) or (b > 5 and b < 1)); + +select 1 from t1 s1 + where 1 not in (select 1 from t1 + where ((a = 1 or a = 2) and b = 1) or b = NULL); + +select c from t2 + where 2 not in (select 1 from t1 + where ((a=1 or a=2) and b = 1) or (b > 5 and b < 1)); + +drop table t1,t2; + +--echo # +--echo # End of 10.2 tests +--echo # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 54a2fac..4742211 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -4504,7 +4504,13 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list, if (*s->on_expr_ref) *s->on_expr_ref= select->cond; else + { join->conds= select->cond; + if (join->conds && join->conds->type() == Item::COND_ITEM && + ((Item_cond*) (join->conds))->functype() == + Item_func::COND_AND_FUNC) + join->cond_equal= &((Item_cond_and*) (join->conds))->m_cond_equal; + } s->quick=select->quick; s->needed_reg=select->needed_reg;

1 0

[Commits] 5643112bd70: Fix
by psergey 23 Nov '21

23 Nov '21

revision-id: 5643112bd7087cdb8984f388cced04947562045e (mariadb-10.6.1-231-g5643112bd70) parent(s): 08231d6e2e7bd37cbc3cb4f5a697ed3dfe831741 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-23 18:24:09 +0300 message: Fix --- mysql-test/main/innodb_ext_key,off.rdiff | 54 ++++++++++++++++---------------- 1 file changed, 27 insertions(+), 27 deletions(-) diff --git a/mysql-test/main/innodb_ext_key,off.rdiff b/mysql-test/main/innodb_ext_key,off.rdiff index 4d6c65c9971..b334d006737 100644 --- a/mysql-test/main/innodb_ext_key,off.rdiff +++ b/mysql-test/main/innodb_ext_key,off.rdiff @@ -265,30 +265,30 @@ drop table t0,t1,t2; # # MDEV-11196: Error:Run-Time Check Failure #2 - Stack around the variable 'key_buff' -@@ -755,11 +755,12 @@ - "select_id": 1, - "table": { - "table_name": "t1", -- "access_type": "range", -+ "access_type": "ref", - "possible_keys": ["f2"], - "key": "f2", -- "key_length": "3070", -- "used_key_parts": ["f2", "pk1"], -+ "key_length": "3066", -+ "used_key_parts": ["f2"], -+ "ref": ["const"], - "rows": 1, - "filtered": 100, - "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", -@@ -788,8 +789,8 @@ - "access_type": "range", - "possible_keys": ["k1"], - "key": "k1", -- "key_length": "3011", -- "used_key_parts": ["pk1", "f2", "pk2"], -+ "key_length": "3007", -+ "used_key_parts": ["pk1", "f2"], - "rows": 1, - "filtered": 100, - "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'", +@@ -768,11 +768,12 @@ + { + "table": { + "table_name": "t1", +- "access_type": "range", ++ "access_type": "ref", + "possible_keys": ["f2"], + "key": "f2", +- "key_length": "3070", +- "used_key_parts": ["f2", "pk1"], ++ "key_length": "3066", ++ "used_key_parts": ["f2"], ++ "ref": ["const"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.pk1 <= 5 and t1.pk2 <= 5 and t1.f2 = 'abc'", +@@ -805,8 +806,8 @@ + "access_type": "range", + "possible_keys": ["k1"], + "key": "k1", +- "key_length": "3011", +- "used_key_parts": ["pk1", "f2", "pk2"], ++ "key_length": "3007", ++ "used_key_parts": ["pk1", "f2"], + "rows": 1, + "filtered": 100, + "index_condition": "t1.f2 <= 5 and t1.pk2 <= 5 and t1.pk1 = 'abc'",

1 0

[Commits] 84b659c2546: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by psergey 23 Nov '21

23 Nov '21

revision-id: 84b659c25467439680a9c161615ee7fe0699842e (mariadb-10.3.31-70-g84b659c2546) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-23 17:55:40 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are not going to execute those anyway. --- mysql-test/main/derived_cond_pushdown.result | 1 - mysql-test/main/derived_view.result | 1 - mysql-test/main/explain_innodb.result | 15 +++++++++++++++ mysql-test/main/explain_innodb.test | 17 +++++++++++++++++ mysql-test/main/join.result | 1 - mysql-test/main/ps.result | 3 --- mysql-test/main/view.result | 1 - sql/sql_select.cc | 4 +++- 8 files changed, 35 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index d2c116913f4..a21bb48509a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16898,7 +16898,6 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; 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 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 65a1adcaddd..d1ce6c8e623 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1558,7 +1558,6 @@ EXPLAIN EXTENDED SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b; 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 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v2`.`b` AS `b` from `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..ca7479a8d6a --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,15 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0; +id +select 1; +1 +1 +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..7282616d3d0 --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,17 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0; +select 1; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index e667eab7eb9..88f67fb8074 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1499,7 +1499,6 @@ EXPLAIN EXTENDED SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; 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 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where `v2`.`i2` = NULL and `v2`.`a` < `v2`.`b` DROP VIEW v2; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 051f40cfd78..222afd6c1f3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -160,7 +160,6 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -168,7 +167,6 @@ id select_type table type possible_keys key key_len ref rows Extra execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -176,7 +174,6 @@ id select_type table type possible_keys key key_len ref rows Extra explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 34dd8f3c8dc..37678a1b50d 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -5714,7 +5714,6 @@ ON t36.f36 = v60.f60 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..b2c1d1e3db5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26322,6 +26322,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, elimination. (2) they are not merged derived tables (3) they are not hanging CTEs (they are needed for execution) + (4) this is not a derived subquery in a degenerate select. */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || @@ -26329,7 +26330,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, (!tmp_unit->with_element || (tmp_unit->derived && tmp_unit->derived->derived_result && - !tmp_unit->with_element->is_hanging_recursive()))) // (3) + !tmp_unit->with_element->is_hanging_recursive())) && // (3) + !(tmp_unit->derived && zero_result_cause)) // (4) { explain->add_child(tmp_unit->first_select()->select_number); }

1 0

[Commits] e29f077e4d8: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log
by psergey 23 Nov '21

23 Nov '21

revision-id: e29f077e4d8d28931b5747299b4a39227d151b73 (mariadb-10.3.31-70-ge29f077e4d8) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-23 17:55:08 +0300 message: MDEV-26249: Crash in Explain_node::print_explain_for_children with slow query log The problem was caused by this: - The select in derived table uses two-phase optimization (due to a possible LATERAL DERIVED). - The primary select has "Impossible where" and so it short-cuts its optimization. - The optimization for the SELECT in the derived table is never finished, and EXPLAIN data structure has a dangling pointer to select #2. Fixed with this: if the select has "Impossible where", do not add links to derived table subselects into the EXPLAIN data structure. We are --- mysql-test/main/derived_cond_pushdown.result | 1 - mysql-test/main/derived_view.result | 1 - mysql-test/main/explain_innodb.result | 15 +++++++++++++++ mysql-test/main/explain_innodb.test | 17 +++++++++++++++++ mysql-test/main/join.result | 1 - mysql-test/main/ps.result | 3 --- mysql-test/main/view.result | 1 - sql/sql_select.cc | 4 +++- 8 files changed, 35 insertions(+), 8 deletions(-) diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index d2c116913f4..a21bb48509a 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -16898,7 +16898,6 @@ EXPLAIN EXTENDED SELECT * FROM v1 JOIN v2 ON v1.f = v2.f; 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 -3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL no matching row in const table Warnings: Note 1003 /* select#1 */ select NULL AS `f`,`v2`.`f` AS `f` from `test`.`t1` `a` straight_join `test`.`t1` `b` join `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 65a1adcaddd..d1ce6c8e623 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1558,7 +1558,6 @@ EXPLAIN EXTENDED SELECT * FROM (SELECT b FROM v2 WHERE b = 0) t WHERE b; 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 -3 DERIVED t1 ALL NULL NULL NULL NULL 5 100.00 Using temporary; Using filesort Warnings: Note 1003 /* select#1 */ select `v2`.`b` AS `b` from `test`.`v2` where 0 DROP VIEW v1,v2; diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result new file mode 100644 index 00000000000..ca7479a8d6a --- /dev/null +++ b/mysql-test/main/explain_innodb.result @@ -0,0 +1,15 @@ +# +# MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +# +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0; +id +select 1; +1 +1 +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test new file mode 100644 index 00000000000..7282616d3d0 --- /dev/null +++ b/mysql-test/main/explain_innodb.test @@ -0,0 +1,17 @@ +--echo # +--echo # MDEV-26249: Crash in in Explain_node::print_explain_for_children while writing to the slow query log +--echo # + +--source include/have_innodb.inc + +set @sql_tmp=@@slow_query_log; +SET GLOBAL slow_query_log = 1; +SET long_query_time = 0.000000; +SET log_slow_verbosity = 'explain'; + +CREATE TABLE t1 ( id varchar(50), KEY (id)) engine=innodb; +SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1>0; +select 1; + +SET GLOBAL slow_query_log = @sql_tmp; +drop table t1; diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result index e667eab7eb9..88f67fb8074 100644 --- a/mysql-test/main/join.result +++ b/mysql-test/main/join.result @@ -1499,7 +1499,6 @@ EXPLAIN EXTENDED SELECT * FROM t1 JOIN v2 ON i1 = i2 WHERE a < b; 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 DERIVED t2 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Note 1003 /* select#1 */ select NULL AS `i1`,`v2`.`i2` AS `i2`,`v2`.`a` AS `a`,`v2`.`b` AS `b` from `test`.`v2` where `v2`.`i2` = NULL and `v2`.`a` < `v2`.`b` DROP VIEW v2; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 051f40cfd78..222afd6c1f3 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -160,7 +160,6 @@ prepare stmt1 from @stmt ; execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -168,7 +167,6 @@ id select_type table type possible_keys key key_len ref rows Extra execute stmt1 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables @@ -176,7 +174,6 @@ id select_type table type possible_keys key key_len ref rows Extra explain SELECT (SELECT SUM(c1 + c12 + 0.0) FROM t2 where (t1.c2 - 0e-3) = t2.c2 GROUP BY t1.c15 LIMIT 1) as scalar_s, exists (select 1.0e+0 from t2 where t2.c3 * 9.0000000000 = t1.c4) as exists_s, c5 * 4 in (select c6 + 0.3e+1 from t2) as in_s, (c7 - 4, c8 - 4) in (select c9 + 4.0, c10 + 40e-1 from t2) as in_row_s FROM t1, (select c25 x, c32 y from t2) tt WHERE x * 1 = c25; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -6 DERIVED NULL NULL NULL NULL NULL NULL NULL no matching row in const table 5 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 4 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 34dd8f3c8dc..37678a1b50d 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -5714,7 +5714,6 @@ ON t36.f36 = v60.f60 ; id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables -2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL no matching row in const table drop table t0, t1, t2, t3, t4, t5, t6, t7, t8, t9, t10, t11, t12, t13, t14, t15, t16, t17, t18, t19, t20, t21, t22, t23, t24, t25, t26, t27, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..b2c1d1e3db5 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -26322,6 +26322,7 @@ int JOIN::save_explain_data_intern(Explain_query *output, elimination. (2) they are not merged derived tables (3) they are not hanging CTEs (they are needed for execution) + (4) this is not a derived subquery in a degenerate select. */ if (!(tmp_unit->item && tmp_unit->item->eliminated) && // (1) (!tmp_unit->derived || @@ -26329,7 +26330,8 @@ int JOIN::save_explain_data_intern(Explain_query *output, (!tmp_unit->with_element || (tmp_unit->derived && tmp_unit->derived->derived_result && - !tmp_unit->with_element->is_hanging_recursive()))) // (3) + !tmp_unit->with_element->is_hanging_recursive())) && // (3) + !(tmp_unit->derived && zero_result_cause)) // (4) { explain->add_child(tmp_unit->first_select()->select_number); }

1 0

[Commits] 78318d34d: Add RangeLockManagerHandle::Counters::wait_count
by psergey 22 Nov '21

22 Nov '21

revision-id: 78318d34ddd05b9413090151bfc7bd5f7a7d74c2 (v5.8-3687-g78318d34d) parent(s): a6f155555ce451d855ab4617cf7bc00991ac2c62 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-23 00:06:23 +0300 message: Add RangeLockManagerHandle::Counters::wait_count --- include/rocksdb/utilities/transaction_db.h | 2 ++ .../transactions/lock/range/range_tree/range_tree_lock_manager.cc | 4 ++++ 2 files changed, 6 insertions(+) diff --git a/include/rocksdb/utilities/transaction_db.h b/include/rocksdb/utilities/transaction_db.h index 265d4b79a..a66c9b573 100644 --- a/include/rocksdb/utilities/transaction_db.h +++ b/include/rocksdb/utilities/transaction_db.h @@ -104,6 +104,8 @@ class RangeLockManagerHandle : public LockManagerHandle { // Number of times lock escalation was triggered (for all column families) uint64_t escalation_count; + uint64_t wait_count; + // How much memory is currently used for locks (total for all column // families) uint64_t current_lock_memory; diff --git a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc index 55a66bc59..2901d32fd 100644 --- a/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc +++ b/utilities/transactions/lock/range/range_tree/range_tree_lock_manager.cc @@ -331,6 +331,10 @@ RangeLockManagerHandle::Counters RangeTreeLockManager::GetStatus() { res.escalation_count = status->value.num; continue; } + if (strcmp(status->keyname, "LTM_WAIT_COUNT") == 0) { + res.wait_count = status->value.num; + continue; + } if (strcmp(status->keyname, "LTM_SIZE_CURRENT") == 0) { res.current_lock_memory = status->value.num; }

1 0

[Commits] ca81e591062: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values
by psergey 22 Nov '21

22 Nov '21

revision-id: ca81e591062bab602069b527297d93084d7bc58c (mariadb-10.3.31-70-gca81e591062) parent(s): 9962cda52722b77c2a7e0314bbaa2e4f963f55c1 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-22 10:55:38 +0300 message: MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values Make const_expression_in_where() ignore the equalities injected by LATERAL DERIVED optimization, unless these equalities are used for ref access. This follows what make_join_select()/make_cond_for_table() does: these functions will discard such equalities too. --- mysql-test/main/derived_split_innodb.result | 88 ++++++++++++++++++++++ mysql-test/main/derived_split_innodb.test | 112 ++++++++++++++++++++++++++++ sql/sql_select.cc | 34 +++++++++ 3 files changed, 234 insertions(+) diff --git a/mysql-test/main/derived_split_innodb.result b/mysql-test/main/derived_split_innodb.result index 7ea3b689f23..fcdba0d7d07 100644 --- a/mysql-test/main/derived_split_innodb.result +++ b/mysql-test/main/derived_split_innodb.result @@ -234,4 +234,92 @@ id itemid id id 4 2 4 2 drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +# +# MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values +# +CREATE TABLE t1 ( +the_date date NOT NULL +, PRIMARY KEY ( the_date ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13'); +CREATE TABLE t2 ( +the_date date NOT NULL, +ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '', +cco_stk_ttl int, +PRIMARY KEY ( the_date , ptn_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t2 VALUES +('2021-08-11','10002',NULL),('2021-08-11','10741',128), +('2021-08-11','11001',4),('2021-08-11','11003',2048), +('2021-08-12','10001',4096),('2021-08-12','10002',1), +('2021-08-12','10429',256),('2021-08-12','10499',16), +('2021-08-12','10580',8),('2021-08-12','10740',32), +('2021-08-12','10741',64),('2021-08-12','10771',512), +('2021-08-12','11001',2),('2021-08-12','11003',1024); +CREATE TABLE t3 ( +id int NOT NULL AUTO_INCREMENT, +nsc_id char(5) NOT NULL, +dept_id char(4) NOT NULL, +district_id char(3) NOT NULL, +region_id char(2) NOT NULL, +PRIMARY KEY ( id ), +UNIQUE KEY dept_district ( dept_id , district_id ), +KEY region_id ( dept_id , region_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t3 VALUES +(1,'MMD','ADVB','10','1'), (2,'MMD','ADVB','11','1'), +(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2'); +CREATE TABLE t4 ( +dept_id char(4) CHARACTER SET utf8mb3 NOT NULL, +ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL, +district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0', +nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL +, PRIMARY KEY (ptn_id , dept_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t4 VALUES +('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'), +('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'), +('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'), +('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'), +('ADVB','11002','11','MMD'); +CREATE TABLE `t10` ( +`the_date` date NOT NULL, +`dept_id` char(4) CHARACTER SET utf8mb4 , +`org_id` varchar(3) CHARACTER SET utf8mb4 , +`district_id` char(3) CHARACTER SET utf8mb4 , +`region_id` char(2) CHARACTER SET utf8mb4 +); +insert into t10 +SELECT cal.the_date , +org.dept_id , +coalesce(org.district_id, org.region_id, 'MMD') AS org_id , +org.district_id , +org.region_id +FROM t1 cal +CROSS JOIN t3 org +WHERE org.nsc_id = 'MMD' + AND org.dept_id IN ('ADVB') +AND cal.the_date = '2021-08-12' +GROUP BY cal.the_date, +org.dept_id, +org.region_id, +org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL +OR org.dept_id IS NULL); +explain $q; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY org2 ALL NULL NULL NULL NULL 7 +1 PRIMARY <derived2> ref key0 key0 43 test.org2.the_date,test.org2.dept_id,test.org2.region_id,test.org2.district_id 2 Using where +2 LATERAL DERIVED sub ref PRIMARY PRIMARY 3 test.org2.the_date 1 Using temporary; Using filesort +2 LATERAL DERIVED org ref PRIMARY PRIMARY 15 test.sub.ptn_id 1 Using where +2 LATERAL DERIVED dis eq_ref dept_district,region_id dept_district 28 const,func 1 Using index condition; Using where +$q; +the_date org_id dept_id cco_stk_ttl +2021-08-12 10 ADVB 4097 +2021-08-12 11 ADVB 2 +2021-08-12 1 ADVB 4099 +2021-08-12 21 ADVB 96 +2021-08-12 22 ADVB 256 +2021-08-12 2 ADVB 352 +2021-08-12 MMD ADVB 4451 +drop table t1,t2,t3,t4,t10; # End of 10.3 tests diff --git a/mysql-test/main/derived_split_innodb.test b/mysql-test/main/derived_split_innodb.test index 6f33c71eede..c127404f975 100644 --- a/mysql-test/main/derived_split_innodb.test +++ b/mysql-test/main/derived_split_innodb.test @@ -186,4 +186,116 @@ eval $q; drop table t1,t2,t3; set optimizer_switch='split_materialized=default'; +--echo # +--echo # MDEV-26337: subquery with groupby and ROLLUP returns incorrect results on LEFT JOIN on INDEXED values +--echo # + +CREATE TABLE t1 ( + the_date date NOT NULL + , PRIMARY KEY ( the_date ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES ('2021-08-10'),('2021-08-11'),('2021-08-12'),('2021-08-13'); + +CREATE TABLE t2 ( + the_date date NOT NULL, + ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL DEFAULT '', + cco_stk_ttl int, + PRIMARY KEY ( the_date , ptn_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +INSERT INTO t2 VALUES +('2021-08-11','10002',NULL),('2021-08-11','10741',128), +('2021-08-11','11001',4),('2021-08-11','11003',2048), +('2021-08-12','10001',4096),('2021-08-12','10002',1), +('2021-08-12','10429',256),('2021-08-12','10499',16), +('2021-08-12','10580',8),('2021-08-12','10740',32), +('2021-08-12','10741',64),('2021-08-12','10771',512), +('2021-08-12','11001',2),('2021-08-12','11003',1024); + +CREATE TABLE t3 ( + id int NOT NULL AUTO_INCREMENT, + nsc_id char(5) NOT NULL, + dept_id char(4) NOT NULL, + district_id char(3) NOT NULL, + region_id char(2) NOT NULL, + PRIMARY KEY ( id ), + UNIQUE KEY dept_district ( dept_id , district_id ), + KEY region_id ( dept_id , region_id ) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +INSERT INTO t3 VALUES +(1,'MMD','ADVB','10','1'), (2,'MMD','ADVB','11','1'), +(3,'MMD','ADVB','21','2'),(4,'MMD','ADVB','22','2'); + +CREATE TABLE t4 ( + dept_id char(4) CHARACTER SET utf8mb3 NOT NULL, + ptn_id char(5) CHARACTER SET utf8mb3 NOT NULL, + district_id char(3) CHARACTER SET utf8mb3 NOT NULL DEFAULT '0', + nsc_id char(5) CHARACTER SET utf8mb3 NOT NULL + , PRIMARY KEY (ptn_id , dept_id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; + +INSERT INTO t4 VALUES +('ADVB','10001','10','MMD'),('ADVB','10002','10','MMD'), +('ADVB','10003','10','MMD'),('ADVB','10429','22','MMD'), +('ADVB','10740','21','MMD'),('ADVB','10741','21','MMD'), +('ADVB','10771','23','MMD'),('ADVB','11001','11','MMD'), +('ADVB','11002','11','MMD'); + +CREATE TABLE `t10` ( + `the_date` date NOT NULL, + `dept_id` char(4) CHARACTER SET utf8mb4 , + `org_id` varchar(3) CHARACTER SET utf8mb4 , + `district_id` char(3) CHARACTER SET utf8mb4 , + `region_id` char(2) CHARACTER SET utf8mb4 +); + +insert into t10 +SELECT cal.the_date , + org.dept_id , + coalesce(org.district_id, org.region_id, 'MMD') AS org_id , + org.district_id , + org.region_id +FROM t1 cal +CROSS JOIN t3 org +WHERE org.nsc_id = 'MMD' + AND org.dept_id IN ('ADVB') + AND cal.the_date = '2021-08-12' +GROUP BY cal.the_date, + org.dept_id, + org.region_id, + org.district_id WITH ROLLUP HAVING NOT (cal.the_date IS NULL + OR org.dept_id IS NULL); + +let $q= +SELECT sql_no_cache org2.the_date , + org2.org_id , + org2.dept_id , + msr. cco_stk_ttl +FROM + t10 org2 +LEFT JOIN + ( SELECT sub.the_date , + dis.dept_id , + dis.region_id , + dis.district_id , + sum(sub.cco_stk_ttl) AS cco_stk_ttl + FROM t2 sub + JOIN t4 org ON org.ptn_id = sub.ptn_id + JOIN t3 dis ON dis.dept_id = org.dept_id + AND dis.district_id = org.district_id + WHERE dis.nsc_id = 'MMD' + AND dis.dept_id IN ('ADVB') + GROUP BY sub.the_date, + dis.dept_id, + dis.region_id, + dis.district_id WITH ROLLUP ) msr ON msr.the_date = org2.the_date +AND msr.dept_id <=> org2.dept_id +AND msr.region_id <=> org2.region_id +AND msr.district_id <=> org2.district_id; + +evalp explain $q; +evalp $q; + +drop table t1,t2,t3,t4,t10; --echo # End of 10.3 tests diff --git a/sql/sql_select.cc b/sql/sql_select.cc index fe02e7b44e4..0ea861987bd 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -17047,6 +17047,26 @@ const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field, Item *right_item= ((Item_func*) cond)->arguments()[1]; if (equal(left_item, comp_item, comp_field)) { + /* + If this condition + 1. Was injected by LATERAL DERIVED optimization, + 2. But is not used for ref access + then we ingore it. This is the same as what mmake_cond_for_table() does + when it is invoked from make_join_select(). + */ + if (func->functype() == Item_func::EQ_FUNC) + { + if (is_eq_cond_injected_for_split_opt((Item_func_eq*)func)) + { + bool used_for_ref= false; + if (left_item->type() == Item::FIELD_ITEM && + test_if_ref(func, (Item_field*)left_item, right_item)) + used_for_ref= true; + + if (!used_for_ref) + return 0; + } + } if (test_if_equality_guarantees_uniqueness (left_item, right_item)) { if (*const_item) @@ -17057,6 +17077,20 @@ const_expression_in_where(COND *cond, Item *comp_item, Field *comp_field, } else if (equal(right_item, comp_item, comp_field)) { + /* Do the same as above */ + if (func->functype() == Item_func::EQ_FUNC) + { + if (is_eq_cond_injected_for_split_opt((Item_func_eq*)func)) + { + bool used_for_ref= false; + if (right_item->type() == Item::FIELD_ITEM && + test_if_ref(func, (Item_field*)right_item, left_item)) + used_for_ref= true; + + if (!used_for_ref) + return 0; + } + } if (test_if_equality_guarantees_uniqueness (right_item, left_item)) { if (*const_item)

1 0

[Commits] 114e18b: MDEV-26470 "No database" selected when using CTE in a subquery of DELETE statement
by IgorBabaev 21 Nov '21

21 Nov '21

revision-id: 114e18b8b68a00b3829ac231cc8f84187f529287 (mariadb-10.2.31-1263-g114e18b) parent(s): 0dae41637abd24c8f08e925ef00490e949ce581d author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-20 21:35:54 -0800 message: MDEV-26470 "No database" selected when using CTE in a subquery of DELETE statement This bug led to reporting bogus messages "No database selected" for DELETE statements if they used subqueries in their WHERE conditions and these subqueries contained references to CTEs. The bug happened because the grammar rule for DELETE statement did not call the function LEX::check_cte_dependencies_and_resolve_references() and as a result of it references to CTEs were not identified as such. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 22 ++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 23 +++++++++++++++++++++++ sql/sql_yacc.yy | 4 ++++ 3 files changed, 49 insertions(+) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index d9b7f05..b2fee0d 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2162,4 +2162,26 @@ a 1 3 drop table t1,t2; +# +# MDEV-26470: CTE in WITH clause of subquery used in DELETE +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (5); +create table t2 (b int); +insert into t2 values (4), (1), (3), (2); +delete from t1 +where a in (with cte(a) as (select * from t2 where b <=2) select a from cte); +select * from t1; +a +3 +7 +5 +insert into t1 values (1), (3); +delete t1 from t1, t2 +where t1.a=t2.b or +t1.a in (with cte(a) as (select b+1 from t2) select * from cte); +select * from t1; +a +7 +drop table t1,t2; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index 4618e02..175be8b 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1601,4 +1601,27 @@ select * from t2; drop table t1,t2; +--echo # +--echo # MDEV-26470: CTE in WITH clause of subquery used in DELETE +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (5); + +create table t2 (b int); +insert into t2 values (4), (1), (3), (2); + +delete from t1 + where a in (with cte(a) as (select * from t2 where b <=2) select a from cte); +select * from t1; + +insert into t1 values (1), (3); + +delete t1 from t1, t2 + where t1.a=t2.b or + t1.a in (with cte(a) as (select b+1 from t2) select * from cte); +select * from t1; + +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 4dd2922..a4b1058 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -12668,6 +12668,10 @@ delete: lex->select_lex.init_order(); } opt_delete_options single_multi + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; single_multi:

1 0