- commits - lists.mariadb.org

[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

[Commits] 8388a82: MDEV-27086 "No database selected" when using UNION of CTEs to define table
by IgorBabaev 19 Nov '21

19 Nov '21

revision-id: 8388a825ccd6c5ce093c79108e1111e1139cef4a (mariadb-10.2.31-1258-g8388a82) parent(s): 8f24f5fee267706c0a5f475140a19a9304e224b4 author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-19 14:51:12 -0800 message: MDEV-27086 "No database selected" when using UNION of CTEs to define table This bug concerned only CREATE TABLE statements of the form CREATE TABLE <table name> AS <with clause> <union>. For such a statement not all references to CTE used in <union> were resolved. As a result a bogus message was reported for the first unresolved reference. This happened because for such statements the function resolving references to CTEs LEX::check_cte_dependencies_and_resolve_references() was called prematurely in the parser. Approved by Oleksandr Byelkin <sanja(a)mariadb.com> --- mysql-test/r/cte_nonrecursive.result | 26 ++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 23 +++++++++++++++++++++++ sql/sql_yacc.yy | 8 ++++---- 3 files changed, 53 insertions(+), 4 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index db747a7..d9b7f05 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2136,4 +2136,30 @@ drop procedure sp1; drop function g; drop function f; drop table t1; +# +# MDEV-27086: union using CTEs in CREATE TABLE +# +create or replace temporary table tmp as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from tmp; +a +1 +2 +create table t1 as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from t1; +a +1 +2 +insert into t1 values (3); +create table t2 as +with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2) +select * from cte1 union select * from cte2; +select * from t2; +a +1 +3 +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 2b90bab..4618e02 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1578,4 +1578,27 @@ drop function g; drop function f; drop table t1; +--echo # +--echo # MDEV-27086: union using CTEs in CREATE TABLE +--echo # + +create or replace temporary table tmp as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from tmp; + +create table t1 as +with cte1 as (select 1 as a), cte2 as (select 2 as a) +select * from cte1 union select * from cte2; +select * from t1; + +insert into t1 values (3); + +create table t2 as +with cte1 as (select * from t1 where a <2), cte2 as (select * from t1 where a > 2) +select * from cte1 union select * from cte2; +select * from t2; + +drop table t1,t2; + --echo # End of 10.2 tests diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy index 312ea68..4dd2922 100644 --- a/sql/sql_yacc.yy +++ b/sql/sql_yacc.yy @@ -4869,6 +4869,10 @@ create_like: opt_create_select: /* empty */ {} | opt_duplicate opt_as create_select_query_expression + { + if (Lex->check_cte_dependencies_and_resolve_references()) + MYSQL_YYABORT; + } ; create_select_query_expression: @@ -4877,16 +4881,12 @@ create_select_query_expression: { Select->set_braces(0); Select->set_with_clause($1); - if (Lex->check_cte_dependencies_and_resolve_references()) - MYSQL_YYABORT; } union_clause | opt_with_clause SELECT_SYM create_select_part2 create_select_part3_union_not_ready create_select_part4 { Select->set_with_clause($1); - if (Lex->check_cte_dependencies_and_resolve_references()) - MYSQL_YYABORT; } | '(' create_select_query_specification ')' | '(' create_select_query_specification ')'

1 0

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

19 Nov '21

revision-id: c3e83036f689f90aa9494ca73db593957a838e3a (mariadb-10.6.5-11-gc3e83036f68) parent(s): 0a168398a0a1f80ae9becab10c777cfe4631ea70 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-19 12:17:14 +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 } --- mysql-test/main/opt_trace,ps.rdiff | 92 -------------------------------------- mysql-test/main/opt_trace.test | 1 - sql/opt_subselect.cc | 9 ++++ 3 files changed, 9 insertions(+), 93 deletions(-) diff --git a/mysql-test/main/opt_trace,ps.rdiff b/mysql-test/main/opt_trace,ps.rdiff deleted file mode 100644 index 3e2218de673..00000000000 --- a/mysql-test/main/opt_trace,ps.rdiff +++ /dev/null @@ -1,92 +0,0 @@ ---- /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.result 2021-07-21 19:17:11.000000000 +0700 -+++ /Users/shulga/projects/mariadb/server-10.6/mysql-test/main/opt_trace.reject 2021-07-21 19:17:48.000000000 +0700 -@@ -2829,14 +2829,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t10.pk from t10" - } - ] -@@ -4402,14 +4394,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t1 t_inner_1 join t1 t_inner_2" - } - ] -@@ -4852,14 +4836,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] -@@ -4879,14 +4855,6 @@ - } - }, - { -- "transformation": { -- "select_id": 3, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] -@@ -6432,14 +6400,6 @@ - } - }, - { -- "transformation": { -- "select_id": 2, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#2 */ select t_inner_1.a from t2 t_inner_2 join t1 t_inner_1" - } - ] -@@ -6459,14 +6419,6 @@ - } - }, - { -- "transformation": { -- "select_id": 3, -- "from": "IN (SELECT)", -- "to": "semijoin", -- "chosen": true -- } -- }, -- { - "expanded_query": "/* select#3 */ select t_inner_3.a from t2 t_inner_3 join t1 t_inner_4" - } - ] diff --git a/mysql-test/main/opt_trace.test b/mysql-test/main/opt_trace.test index 70223560187..402caf2a165 100644 --- a/mysql-test/main/opt_trace.test +++ b/mysql-test/main/opt_trace.test @@ -1,6 +1,5 @@ --source include/not_embedded.inc --source include/have_sequence.inc ---source include/protocol.inc SELECT table_name, column_name FROM information_schema.columns where table_name="OPTIMIZER_TRACE"; set optimizer_trace="enabled=on"; show variables like 'optimizer_trace'; diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index dc93535bd87..99f75d9ad2f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -717,6 +717,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] b09d941: MDEV-26825 Bogus error for query with two usage of CTE referring another CTE
by IgorBabaev 16 Nov '21

16 Nov '21

revision-id: b09d941daed35136c95c590f8ac37257179e5e99 (mariadb-10.2.31-1237-gb09d941) parent(s): 42ae765960869a7ce381341d7b98c1e8aa157b29 author: Igor Babaev committer: Igor Babaev timestamp: 2021-11-15 22:21:05 -0800 message: MDEV-26825 Bogus error for query with two usage of CTE referring another CTE This bug affected queries with two or more references to a CTE referring another CTE if the definition of the latter contained an invocation of a stored function that used a base table. The bug could lead to a bogus error message or to an assertion failure. For any non-first reference to CTE cte1 With_element::clone_parsed_spec() is called that parses the specification of cte1 to construct the unit structure for this usage of cte1. If cte1 refers to another CTE cte2 outside of the specification of cte1 then With_element::clone_parsed_spec() has to be called for cte2 as well. This call is made by the function LEX::resolve_references_to_cte() within the invocation of the function With_element::clone_parsed_spec() for cte1. When the specification of a CTE is parsed all table references encountered in it must be added to the global list of table references for the query. As the specification for the non-first usage of a CTE is parsed at a recursive call of the parser the function With_element::clone_parsed_spec() invoked at this recursive call should takes care of appending the list of table references encountered in the specification of this CTE cte1 to the list of table references created for the query. And it should do it after the call of LEX::resolve_references_to_cte() that resolves references to CTEs defined outside of the specification of cte1 because this call may invoke the parser again for specifications of other CTEs and the table references from their specifications must ultimately appear in the global list of table references of the query. The code of With_element::clone_parsed_spec() misplaced the call of LEX::resolve_references_to_cte(). As a result LEX::query_tables_last used for the query that was supposed to point to the field 'next_global' of the last element in the global list of table references actually pointed to 'next_global' of the previous element. The above inconsistency certainly caused serious problems when table references used in the stored functions invoked in cloned specifications of CTEs were added to the global list of table references. --- mysql-test/r/cte_nonrecursive.result | 50 +++++++++++++++++++++++++++++++ mysql-test/t/cte_nonrecursive.test | 36 +++++++++++++++++++++++ sql/sql_cte.cc | 57 +++++++++++++++++++++--------------- 3 files changed, 120 insertions(+), 23 deletions(-) diff --git a/mysql-test/r/cte_nonrecursive.result b/mysql-test/r/cte_nonrecursive.result index 2504e55..db747a7 100644 --- a/mysql-test/r/cte_nonrecursive.result +++ b/mysql-test/r/cte_nonrecursive.result @@ -2086,4 +2086,54 @@ a b a b 1 3 1 3 drop procedure sp; drop table t1; +# +# MDEV-26825: query with two usage of CTE that refers to another CTE +# with stored function using a base table. +# +create table t1 (id int primary key); +insert into t1 values +(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); +create function f(in_id int) returns integer +return (select id from t1 where t1.id = in_id); +with c1 as (select id from t1 where f(id)=id group by id), +c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +with c1 as (select id from t1 as r where f(id)=id group by id), +c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; +id +1 +2 +3 +4 +5 +6 +7 +8 +9 +10 +create function g() returns int return (select count(*) from t1); +create procedure sp1() +with c1 as (select id from t1 a where g() > 10), +c2 as (select id from c1) +select id from c2 as s1 union select id from c2 as s2; +call sp1(); +id +call sp1(); +id +drop procedure sp1; +drop function g; +drop function f; +drop table t1; # End of 10.2 tests diff --git a/mysql-test/t/cte_nonrecursive.test b/mysql-test/t/cte_nonrecursive.test index c20a0dc..2b90bab 100644 --- a/mysql-test/t/cte_nonrecursive.test +++ b/mysql-test/t/cte_nonrecursive.test @@ -1542,4 +1542,40 @@ call sp(); drop procedure sp; drop table t1; +--echo # +--echo # MDEV-26825: query with two usage of CTE that refers to another CTE +--echo # with stored function using a base table. +--echo # + +create table t1 (id int primary key); +insert into t1 values +(1), (2), (3), (4), (5), (6), (7), (8), (9), (10); + +create function f(in_id int) returns integer +return (select id from t1 where t1.id = in_id); + +with c1 as (select id from t1 where f(id)=id group by id), + c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; + +with c1 as (select id from t1 as r where f(id)=id group by id), + c2 as (select id from c1 as pt group by id) +select id from c2 as s1 union select id from c2 as s2; + +create function g() returns int return (select count(*) from t1); +create procedure sp1() + +with c1 as (select id from t1 a where g() > 10), + c2 as (select id from c1) +select id from c2 as s1 union select id from c2 as s2; + +call sp1(); +call sp1(); + +drop procedure sp1; +drop function g; + +drop function f; +drop table t1; + --echo # End of 10.2 tests diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc index 22a9984..e6b868d 100644 --- a/sql/sql_cte.cc +++ b/sql/sql_cte.cc @@ -1021,6 +1021,15 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, lex_start(thd); lex->clone_spec_offset= unparsed_spec_offset; lex->with_cte_resolution= true; + /* + There's no need to add SPs/SFs referenced in the clone to the global + list of the SPs/SFs used in the query as they were added when the first + reference to the cloned CTE was parsed. Yet the recursive call of the + parser must to know that they were already included into the list. + */ + lex->sroutines= old_lex->sroutines; + lex->sroutines_list_own_last= old_lex->sroutines_list_own_last; + lex->sroutines_list_own_elements= old_lex->sroutines_list_own_elements; /* The specification of a CTE is to be parsed as a regular query. @@ -1067,6 +1076,29 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, goto err; /* + The unit of the specification that just has been parsed is included + as a slave of the select that contained in its from list the table + reference for which the unit has been created. + */ + lex->unit.include_down(with_table->select_lex); + lex->unit.set_slave(with_select); + lex->unit.cloned_from= spec; + + /* + Now all references to the CTE defined outside of the cloned specification + has to be resolved. Additionally if old_lex->only_cte_resolution == false + for the table references that has not been resolved requests for mdl_locks + has to be set. + */ + lex->only_cte_resolution= old_lex->only_cte_resolution; + if (lex->resolve_references_to_cte(lex->query_tables, + lex->query_tables_last)) + { + res= NULL; + goto err; + } + + /* The global chain of TABLE_LIST objects created for the specification that just has been parsed is added to such chain that contains the reference to the CTE whose specification is parsed right after the TABLE_LIST object @@ -1090,32 +1122,11 @@ st_select_lex_unit *With_element::clone_parsed_spec(LEX *old_lex, old_lex->query_tables_last= lex->query_tables_last; } } + old_lex->sroutines_list_own_last= lex->sroutines_list_own_last; + old_lex->sroutines_list_own_elements= lex->sroutines_list_own_elements; res= &lex->unit; res->with_element= this; - /* - The unit of the specification that just has been parsed is included - as a slave of the select that contained in its from list the table - reference for which the unit has been created. - */ - lex->unit.include_down(with_table->select_lex); - lex->unit.set_slave(with_select); - lex->unit.cloned_from= spec; - - /* - Now all references to the CTE defined outside of the cloned specification - has to be resolved. Additionally if old_lex->only_cte_resolution == false - for the table references that has not been resolved requests for mdl_locks - has to be set. - */ - lex->only_cte_resolution= old_lex->only_cte_resolution; - if (lex->resolve_references_to_cte(lex->query_tables, - lex->query_tables_last)) - { - res= NULL; - goto err; - } - last_clone_select= lex->all_selects_list; while (last_clone_select->next_select_in_list()) last_clone_select= last_clone_select->next_select_in_list();

1 0

[Commits] a6f155555: Basic return-early optimization in RangeTreeLockManager::TryLock
by psergey 08 Nov '21

08 Nov '21

revision-id: a6f155555ce451d855ab4617cf7bc00991ac2c62 (v5.8-3686-ga6f155555) parent(s): c04a86a0e9a28ef3e02d5fe74928dfd5e8fdd910 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-08 22:01:23 +0300 message: Basic return-early optimization in RangeTreeLockManager::TryLock Do not call lock_request::wait when lock acquisition has succeeded. --- .../lock/range/range_tree/range_tree_lock_manager.cc | 10 ++++++---- 1 file changed, 6 insertions(+), 4 deletions(-) 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 6dfb78d3f..55a66bc59 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 @@ -115,11 +115,13 @@ Status RangeTreeLockManager::TryLock(PessimisticTransaction* txn, std::move(end)}); }; - request.start(); + int r = request.start(); - const int r = request.wait(wait_time_msec, killed_time_msec, - nullptr, // killed_callback - wait_callback_for_locktree, nullptr); + if (r) { + r = request.wait(wait_time_msec, killed_time_msec, + nullptr, // killed_callback + wait_callback_for_locktree, nullptr); + } // Inform the txn that we are no longer waiting: txn->ClearWaitingTxn();

1 0

[Commits] 2ddeb16841f: MDEV-23766: Make Json_writer assert when one tries to author invalid JSON
by psergey 05 Nov '21

05 Nov '21

revision-id: 2ddeb16841ff10ee5c4178b6aeee52ee285a2838 (mariadb-10.5.12-113-g2ddeb16841f) parent(s): a51ad4ee623648feab1eac5de4b7a3bbd06a3049 author: Sergei Petrunia committer: Sergei Petrunia timestamp: 2021-11-05 20:01:43 +0300 message: MDEV-23766: Make Json_writer assert when one tries to author invalid JSON - Add unit test. --- sql/my_json_writer.cc | 14 ++--- sql/my_json_writer.h | 22 ++++++- unittest/sql/CMakeLists.txt | 5 ++ unittest/sql/my_json_writer-t.cc | 130 +++++++++++++++++++++++++++++++++++++++ 4 files changed, 163 insertions(+), 8 deletions(-) diff --git a/sql/my_json_writer.cc b/sql/my_json_writer.cc index 687da202164..96bdf20ee5e 100644 --- a/sql/my_json_writer.cc +++ b/sql/my_json_writer.cc @@ -39,7 +39,7 @@ inline void Json_writer::on_start_object() #ifndef NDEBUG if(!fmt_helper.is_making_writer_calls()) { - DBUG_ASSERT(got_name == named_item_expected()); + VALIDITY_ASSERT(got_name == named_item_expected()); named_items_expectation.push_back(true); } #endif @@ -74,7 +74,7 @@ void Json_writer::start_array() #ifndef NDEBUG if(!fmt_helper.is_making_writer_calls()) { - DBUG_ASSERT(got_name == named_item_expected()); + VALIDITY_ASSERT(got_name == named_item_expected()); named_items_expectation.push_back(false); got_name= false; } @@ -98,7 +98,7 @@ void Json_writer::end_object() { #ifndef NDEBUG named_items_expectation.pop_back(); - DBUG_ASSERT(!got_name); + VALIDITY_ASSERT(!got_name); got_name= false; #endif indent_level-=INDENT_SIZE; @@ -246,8 +246,8 @@ void Json_writer::add_unquoted_str(const char* str) void Json_writer::add_unquoted_str(const char* str, size_t len) { - DBUG_ASSERT(fmt_helper.is_making_writer_calls() || - got_name == named_item_expected()); + VALIDITY_ASSERT(fmt_helper.is_making_writer_calls() || + got_name == named_item_expected()); if (on_add_str(str, len)) return; @@ -279,8 +279,8 @@ void Json_writer::add_str(const char *str) void Json_writer::add_str(const char* str, size_t num_bytes) { - DBUG_ASSERT(fmt_helper.is_making_writer_calls() || - got_name == named_item_expected()); + VALIDITY_ASSERT(fmt_helper.is_making_writer_calls() || + got_name == named_item_expected()); if (on_add_str(str, num_bytes)) return; diff --git a/sql/my_json_writer.h b/sql/my_json_writer.h index 50b277a5052..6f9ae73c5ee 100644 --- a/sql/my_json_writer.h +++ b/sql/my_json_writer.h @@ -16,7 +16,17 @@ #ifndef JSON_WRITER_INCLUDED #define JSON_WRITER_INCLUDED #include "my_base.h" + +#ifdef JSON_WRITER_UNIT_TEST +#include "sql_string.h" +#include <vector> +// Also, mock objects are defined in my_json_writer-t.cc +#define VALIDITY_ASSERT(x) if ((!x)) this->invalid_json= true; +#else #include "sql_select.h" +#define VALIDITY_ASSERT(x) DBUG_ASSERT(x) +#endif + class Opt_trace_stmt; class Opt_trace_context; class Json_writer; @@ -191,12 +201,22 @@ class String_with_limit class Json_writer { #ifndef NDEBUG - + /* + In debug mode, Json_writer will fail and assertion if one attempts to + produce an invalid JSON document (e.g. JSON array having named elements). + */ std::vector<bool> named_items_expectation; bool named_item_expected() const; bool got_name; + +#ifdef JSON_WRITER_UNIT_TEST +public: + // When compiled for unit test, creating invalid JSON will set this to true + // instead of an assertion. + bool invalid_json= false; +#endif #endif public: diff --git a/unittest/sql/CMakeLists.txt b/unittest/sql/CMakeLists.txt index 987e78433a4..ab174680fab 100644 --- a/unittest/sql/CMakeLists.txt +++ b/unittest/sql/CMakeLists.txt @@ -36,3 +36,8 @@ ADD_EXECUTABLE(mf_iocache-t mf_iocache-t.cc ../../sql/mf_iocache_encr.cc) TARGET_LINK_LIBRARIES(mf_iocache-t mysys mytap mysys_ssl) ADD_DEPENDENCIES(mf_iocache-t GenError) MY_ADD_TEST(mf_iocache) + +# Json writer needs String which needs sql library +ADD_EXECUTABLE(my_json_writer-t my_json_writer-t.cc dummy_builtins.cc) +TARGET_LINK_LIBRARIES(my_json_writer-t sql mytap) +MY_ADD_TEST(my_json_writer) diff --git a/unittest/sql/my_json_writer-t.cc b/unittest/sql/my_json_writer-t.cc new file mode 100644 index 00000000000..46c3f4dc967 --- /dev/null +++ b/unittest/sql/my_json_writer-t.cc @@ -0,0 +1,130 @@ +/* + Copyright (c) 2021, MariaDB Corporation. + + This program is free software; you can redistribute it and/or modify + it under the terms of the GNU General Public License as published by + the Free Software Foundation; version 2 of the License. + + This program is distributed in the hope that it will be useful, + but WITHOUT ANY WARRANTY; without even the implied warranty of + MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the + GNU General Public License for more details. + + You should have received a copy of the GNU General Public License + along with this program; if not, write to the Free Software + Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1335 USA */ + +#include <my_global.h> +#include <my_pthread.h> +#include <my_sys.h> +#include <stdio.h> +#include <tap.h> + +/* + Unit tests for class Json_writer. At the moment there are only tests for the + "Fail an assertion if one attempts to produce invalid JSON" feature. +*/ + +struct TABLE; +struct JOIN_TAB; +class Json_writer; + + +/* Several fake objects */ +class Opt_trace +{ +public: + void enable_tracing_if_required() {} + void disable_tracing_if_required() {} + Json_writer *get_current_json() { return nullptr; } +}; + +class THD +{ +public: + Opt_trace opt_trace; +}; + +#define JSON_WRITER_UNIT_TEST +#include "../sql/my_json_writer.h" +#include "../sql/my_json_writer.cc" + +int main(int args, char **argv) +{ + plan(NO_PLAN); + diag("Testing Json_writer checks"); + + { + Json_writer w; + w.start_object(); + w.add_member("foo"); + w.end_object(); + ok(w.invalid_json, "Started a name but didn't add a value"); + } + + { + Json_writer w; + w.start_object(); + w.add_ull(123); + ok(w.invalid_json, "Unnamed value in an object"); + } + + { + Json_writer w; + w.start_array(); + w.add_member("bebebe").add_ull(345); + ok(w.invalid_json, "Named member in array"); + } + + { + Json_writer w; + w.start_object(); + w.start_array(); + ok(w.invalid_json, "Unnamed array in an object"); + } + + { + Json_writer w; + w.start_object(); + w.start_object(); + ok(w.invalid_json, "Unnamed object in an object"); + } + + { + Json_writer w; + w.start_array(); + w.add_member("zzz"); + w.start_object(); + ok(w.invalid_json, "Named object in an array"); + } + { + Json_writer w; + w.start_array(); + w.add_member("zzz"); + w.start_array(); + ok(w.invalid_json, "Named array in an array"); + } + + // BAD: + { + Json_writer w; + w.start_array(); + w.end_object(); + ok(!w.invalid_json, "BAD: not checked!"); + } + + // BAD: + { + Json_writer w; + w.start_object(); + w.end_array(); + ok(!w.invalid_json, "BAD: not checked!"); + } + + + + diag("Done"); + + return exit_status(); +} +

1 0