From 7f96dd50e25abc2a9a75d96cc1c711124b6be765 Mon Sep 17 00:00:00 2001 From: Monty Date: Tue, 2 May 2023 22:30:57 +0300 Subject: MDEV-6768 Wrong result with aggregate with join with no result set When a query does implicit grouping and join operation produces an empty result set, a NULL-complemented row combination is generated. However, constant table fields still show non-NULL values. What happens in the is that end_send_group() is called with a const row but without any rows matching the WHERE clause. This last part is shown by 'join->first_record' not being set. This causes item->no_rows_in_result() to be called for all items to reset all sum functions to their initial state. However fields are not set to NULL. The used fix is to produce NULL-complemented records for constant tables as well. Also, reset the constant table's records back in case we're in a subquery which may get re-executed. An alternative fix would have item->no_rows_in_result() also work with Item_field objects. There is some other issues with the code: - join->no_rows_in_result_called is used but never set. - Tables that are used with group functions are not properly marked as maybe_null, which is required if the table rows should be regarded as null-complemented (not existing). - The code that tries to detect if mixed_implicit_grouping should be set didn't take into account all usage of fields and sum functions. - Item_func::restore_to_before_no_rows_in_result() called the wrong function. - join->clear() does not use a table_map argument to clear_tables(), which caused it to ignore constant tables. - unclear_tables() does not correctly restore status to what is was before clear_tables(). Main bug fix was to always use a table_map argument to clear_tables() and always use join->clear() and clear_tables() together with unclear_tables(). Other fixes: - Fixed Item_func::restore_to_before_no_rows_in_result() - Set 'join->no_rows_in_result_called' when no_rows_in_result_set() is called. - Removed not used argument from setup_end_select_func(). - More code comments - Ensure that end_send_group() modifies the same fields as are in the result set. - Changed return_zero_rows() to use pointers instead of references, similar to the rest of the code. --- mysql-test/main/group_min_max.result | 113 +++++++++++++++++++++++++++++++++ mysql-test/main/group_min_max.test | 115 ++++++++++++++++++++++++++++++++++ mysql-test/main/type_timestamp.result | 2 + mysql-test/main/type_timestamp.test | 1 + 4 files changed, 231 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index fd9b5be4260..4c2693e5e4c 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4025,3 +4025,116 @@ drop table t1; # # End of 10.1 tests # +# +# MDEV-6768 Wrong result with agregate with join with no resultset +# +create table t1 +( +PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, +PARENT_FIELD VARCHAR(10), +PRIMARY KEY (PARENT_ID) +) engine=innodb; +create table t2 +( +CHILD_ID INT NOT NULL AUTO_INCREMENT, +PARENT_ID INT NOT NULL, +CHILD_FIELD varchar(10), +PRIMARY KEY (CHILD_ID) +)engine=innodb; +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +select +1, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +1 min(CHILD_FIELD) +1 NULL +select +IFNULL(t1.PARENT_ID,1), +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +IFNULL(t1.PARENT_ID,1) min(CHILD_FIELD) +1 NULL +# Check that things works with MyISAM (which has different explain) +alter table t1 engine=myisam; +alter table t2 engine=myisam; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +drop table t1,t2; +# Check that things works if sub queries are re-executed +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); +explain +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +a b s1 +1 1 t2:1;min_t3_b:1 +2 2 t2:t2a-null;min_t3_b:t3b-null +3 3 t2:1;min_t3_b:3 +drop table t1,t2,t3; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 506323599cb..3c5c1b5bb9b 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -5,6 +5,7 @@ --source include/no_valgrind_without_big.inc --source include/default_optimizer_switch.inc +--source include/have_innodb.inc # # TODO: @@ -1688,3 +1689,117 @@ drop table t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # MDEV-6768 Wrong result with agregate with join with no resultset +--echo # + +create table t1 +( + PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, + PARENT_FIELD VARCHAR(10), + PRIMARY KEY (PARENT_ID) +) engine=innodb; + +create table t2 +( + CHILD_ID INT NOT NULL AUTO_INCREMENT, + PARENT_ID INT NOT NULL, + CHILD_FIELD varchar(10), + PRIMARY KEY (CHILD_ID) +)engine=innodb; + +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; + +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + 1, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + IFNULL(t1.PARENT_ID,1), + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + + +--echo # Check that things works with MyISAM (which has different explain) + +alter table t1 engine=myisam; +alter table t2 engine=myisam; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +drop table t1,t2; + +--echo # Check that things works if sub queries are re-executed + +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); + +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); + +explain +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +drop table t1,t2,t3; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index 58cb12ae267..dbccee08ada 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1230,6 +1230,8 @@ SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); c1 Warnings: Warning 1292 Truncated incorrect datetime value: 'r' +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; +c1 DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); INSERT INTO t1 VALUES ('2010-01-01 00:00:00'); diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index f12cc2a4bc3..c8517656071 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -810,6 +810,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r')); SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); -- cgit v1.2.1 From 08a4732860348bcdc16b4ad8ecfc2b4b2e644ae5 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 3 May 2023 21:27:30 +0300 Subject: MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size The problem was that join_buffer_size conflicted with join_buffer_space_limit, which caused the query to be run without join buffer. However this caused wrong results as the optimizer assumed that hash+join buffer would ensure that the equi-join condition would be satisfied, and didn't check it itself. Fixed by not using join_buffer_space_limit when optimize_join_buffer_size=off. This matches the documentation at https://mariadb.com/kb/en/block-based-join-algorithms Other things: - Removed not used variable JOIN_TAB::join_buffer_size_limit - Give an error if we cannot allocate a join buffer. This can only happen if the join_buffer variables are wrongly configured or we are running out of memory. In the future, instead of returning an error, we could properly convert the query plan that uses BNL-H join into one that doesn't use join buffering: make sure the equi-join condition is checked where appropriate. Reviewer: Sergei Petrunia --- mysql-test/main/join_cache.result | 33 ++++++++++++++++++++++++++++++--- mysql-test/main/join_cache.test | 28 ++++++++++++++++++++++++++++ mysql-test/main/join_optimizer.test | 2 ++ 3 files changed, 60 insertions(+), 3 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 1837576e719..3b02740c67c 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -5655,13 +5655,13 @@ EXPLAIN SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; a a b b c +3 3 32 32 302 3 3 30 30 300 3 3 31 NULL NULL -3 3 32 32 302 set join_buffer_space_limit=@save_join_buffer_space_limit; set join_buffer_size=@save_join_buffer_size; set join_cache_level=@save_join_cache_level; @@ -6229,6 +6229,33 @@ EXPLAIN } drop table t1,t2,t3; # End of 10.3 tests +# +# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +# +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +SET SESSION join_buffer_size= X; +Warnings: +Warning X Truncated incorrect join_buffer_size value: 'X' +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i +SET optimizer_switch='optimize_join_buffer_size=off'; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i i +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; +# +# End of 10.4 tests +# set @@optimizer_switch=@save_optimizer_switch; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 07ac0b760cf..500fd9e1049 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4201,6 +4201,34 @@ drop table t1,t2,t3; --echo # End of 10.3 tests +--echo # +--echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +--echo # + +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +--replace_regex /[0-9][0-9]+/X/ +SET SESSION join_buffer_size= 5250229460064350213; +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET optimizer_switch='optimize_join_buffer_size=off'; +--replace_regex /[0-9][0-9]+/X/ +--error ER_OUTOFMEMORY +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # + # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_optimizer.test b/mysql-test/main/join_optimizer.test index 3afe82113b9..e5f6181944d 100644 --- a/mysql-test/main/join_optimizer.test +++ b/mysql-test/main/join_optimizer.test @@ -2,6 +2,8 @@ drop table if exists t0,t1,t2,t3; --enable_warnings +--source include/have_innodb.inc + --echo # --echo # BUG#38049 incorrect rows estimations with references from preceding table --echo # -- cgit v1.2.1 From 5fd46be5a7da83e935c59796733d52906b59f14c Mon Sep 17 00:00:00 2001 From: Monty Date: Thu, 4 May 2023 12:43:18 +0300 Subject: Fixed calculation of JOIN_CACHE::max_records The old code did set max_records to either number_of_rows (partial_join_cardinality) or memory size (join_buffer_space_limit) which did not make sense. Fixed by setting max_records to number of rows that fits into join_buffer_size. Other things: - Initialize buffer cache values in JOIN_CACHE constructors (safety) Reviewer: Sergei Petrunia --- mysql-test/main/join_cache.result | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 3b02740c67c..f5ddbfea733 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3781,9 +3781,9 @@ id1 num3 text1 id4 id3 dummy 228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 18 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 -228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 50 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 +228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 89 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 19 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 -- cgit v1.2.1 From 84b9fc25a29b94a37eb9d5ac2e2c0f75c0efafda Mon Sep 17 00:00:00 2001 From: Monty Date: Fri, 5 May 2023 11:31:35 +0300 Subject: Fixed wrong test cases (embedded and ASAN) - main.selectivity failed because one test produced different result with embedded (missing feature). Fixed by moving the failing part to selectivity_notembedded. - Disabled maria.encrypt-no-key for embedded as embedded does not support encryption - Moved test from join_cache to join_cache_notasan that tried to alloc() a buffer bigger than available memory. --- mysql-test/main/join_cache.result | 27 ------- mysql-test/main/join_cache.test | 28 ------- mysql-test/main/join_cache_notasan.result | 27 +++++++ mysql-test/main/join_cache_notasan.test | 35 +++++++++ mysql-test/main/selectivity.result | 69 ----------------- mysql-test/main/selectivity.test | 79 ------------------- mysql-test/main/selectivity_innodb.result | 67 ---------------- mysql-test/main/selectivity_notembedded.result | 83 ++++++++++++++++++++ mysql-test/main/selectivity_notembedded.test | 102 +++++++++++++++++++++++++ 9 files changed, 247 insertions(+), 270 deletions(-) create mode 100644 mysql-test/main/join_cache_notasan.result create mode 100644 mysql-test/main/join_cache_notasan.test create mode 100644 mysql-test/main/selectivity_notembedded.result create mode 100644 mysql-test/main/selectivity_notembedded.test (limited to 'mysql-test/main') diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index f5ddbfea733..6b39f936628 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -6229,33 +6229,6 @@ EXPLAIN } drop table t1,t2,t3; # End of 10.3 tests -# -# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size -# -CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t1 VALUES (1332945389); -CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t2 VALUES (1180244875), (1951338178); -SET SESSION join_buffer_size= X; -Warnings: -Warning X Truncated incorrect join_buffer_size value: 'X' -SET SESSION join_cache_level = 4; -SET optimizer_switch='optimize_join_buffer_size=on'; -SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -i -SET optimizer_switch='optimize_join_buffer_size=off'; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' -SET SESSION join_buffer_size= 10000000; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -i i -SET SESSION optimizer_switch= default; -SET SESSION join_buffer_size= default; -SET SESSION join_cache_level= default; -drop table t1,t2; -# -# End of 10.4 tests -# set @@optimizer_switch=@save_optimizer_switch; set global innodb_stats_persistent= @innodb_stats_persistent_save; set global innodb_stats_persistent_sample_pages= diff --git a/mysql-test/main/join_cache.test b/mysql-test/main/join_cache.test index 500fd9e1049..07ac0b760cf 100644 --- a/mysql-test/main/join_cache.test +++ b/mysql-test/main/join_cache.test @@ -4201,34 +4201,6 @@ drop table t1,t2,t3; --echo # End of 10.3 tests ---echo # ---echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size ---echo # - -CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t1 VALUES (1332945389); -CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; -INSERT INTO t2 VALUES (1180244875), (1951338178); ---replace_regex /[0-9][0-9]+/X/ -SET SESSION join_buffer_size= 5250229460064350213; -SET SESSION join_cache_level = 4; -SET optimizer_switch='optimize_join_buffer_size=on'; -SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET optimizer_switch='optimize_join_buffer_size=off'; ---replace_regex /[0-9][0-9]+/X/ ---error ER_OUTOFMEMORY -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET SESSION join_buffer_size= 10000000; -SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; -SET SESSION optimizer_switch= default; -SET SESSION join_buffer_size= default; -SET SESSION join_cache_level= default; -drop table t1,t2; - ---echo # ---echo # End of 10.4 tests ---echo # - # The following command must be the last one in the file set @@optimizer_switch=@save_optimizer_switch; diff --git a/mysql-test/main/join_cache_notasan.result b/mysql-test/main/join_cache_notasan.result new file mode 100644 index 00000000000..3cec949f5c6 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.result @@ -0,0 +1,27 @@ +# +# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +# +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +SET SESSION join_buffer_size= X; +Warnings: +Warning X Truncated incorrect join_buffer_size value: 'X' +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i +SET optimizer_switch='optimize_join_buffer_size=off'; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i i +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/join_cache_notasan.test b/mysql-test/main/join_cache_notasan.test new file mode 100644 index 00000000000..7fd5e4e80b1 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.test @@ -0,0 +1,35 @@ +# +# Tests that should be in join_cache but cannot be run with ASAN + +--source include/not_asan.inc +--source include/have_innodb.inc + +--echo # +--echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +--echo # + +# This test tries to allocate a too big bufffer, for which ASAN gives an error + +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +--replace_regex /[0-9][0-9]+/X/ +SET SESSION join_buffer_size= 5250229460064350213; +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET optimizer_switch='optimize_join_buffer_size=off'; +--replace_regex /[0-9][0-9]+/X/ +--error ER_OUTOFMEMORY +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 7d7343847cd..a6866e55b13 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1937,75 +1937,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.0357 - } - ] -] -set optimizer_trace=@tmp; -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 06a3e32da95..def74394ec5 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1324,85 +1324,6 @@ DROP TABLE t1; --echo # End of 10.2 tests ---echo # ---echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram ---echo # -create table t0(a int); # This holds how many rows we hold in a bucket. -insert into t0 select 1 from seq_1_to_78; - -create table t1(a int); # one-third of a bucket -insert into t1 select 1 from seq_1_to_26; - -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; - -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; - - -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; - - -insert into t10 select 34785 from t0, seq_1_to_8; - -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; - -insert into t10 select 65216 from t0, seq_1_to_52; - -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; - -insert into t10 select 65219 from t0; - - -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; - -insert into t10 select 73915 from t0, seq_1_to_40; - - -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; - -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; - -insert into t10 select 91302 from t0, seq_1_to_6; - -insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; - -insert into t10 select 91305 from t0, seq_1_to_8; - -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; - -set use_stat_tables=preferably; -analyze table t10 persistent for all; -flush tables; - -set @tmp=@@optimizer_trace; -set optimizer_trace=1; -explain select * from t10 where a in (91303); - ---echo # Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; - -set optimizer_trace=@tmp; -drop table t0,t1,t10; - set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index dfba12f2b05..13816f8185c 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1947,73 +1947,6 @@ Warnings: Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 DROP TABLE t1; # End of 10.2 tests -# -# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram -# -create table t0(a int); -insert into t0 select 1 from seq_1_to_78; -create table t1(a int); -insert into t1 select 1 from seq_1_to_26; -create table t10 (a int); -insert into t10 select 0 from t0, seq_1_to_4; -insert into t10 select 8693 from t1; -insert into t10 select 8694 from t1; -insert into t10 select 8695 from t1; -insert into t10 select 34783 from t1; -insert into t10 select 34784 from t1; -insert into t10 select 34785 from t1; -insert into t10 select 34785 from t0, seq_1_to_8; -insert into t10 select 65214 from t1; -insert into t10 select 65215 from t1; -insert into t10 select 65216 from t1; -insert into t10 select 65216 from t0, seq_1_to_52; -insert into t10 select 65217 from t1; -insert into t10 select 65218 from t1; -insert into t10 select 65219 from t1; -insert into t10 select 65219 from t0; -insert into t10 select 73913 from t1; -insert into t10 select 73914 from t1; -insert into t10 select 73915 from t1; -insert into t10 select 73915 from t0, seq_1_to_40; -insert into t10 select 78257 from t1; -insert into t10 select 78258 from t1; -insert into t10 select 78259 from t1; -insert into t10 select 91300 from t1; -insert into t10 select 91301 from t1; -insert into t10 select 91302 from t1; -insert into t10 select 91302 from t0, seq_1_to_6; -insert into t10 select 91303 from t1; -insert into t10 select 91304 from t1; -insert into t10 select 91305 from t1; -insert into t10 select 91305 from t0, seq_1_to_8; -insert into t10 select 99998 from t1; -insert into t10 select 99999 from t1; -insert into t10 select 100000 from t1; -set use_stat_tables=preferably; -analyze table t10 persistent for all; -Table Op Msg_type Msg_text -test.t10 analyze status Engine-independent statistics collected -test.t10 analyze status OK -flush tables; -set statement optimizer_trace=1 for -explain select * from t10 where a in (91303); -id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where -# Must have selectivity_from_histogram <= 1.0: -select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -from information_schema.optimizer_trace; -json_detailed(json_extract(trace, '$**.selectivity_for_columns')) -[ - [ - { - "column_name": "a", - "ranges": - ["91303 <= a <= 91303"], - "selectivity_from_histogram": 0.035714283 - } - ] -] -drop table t0,t1,t10; set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; set histogram_size=@save_histogram_size; set use_stat_tables= @save_use_stat_tables; diff --git a/mysql-test/main/selectivity_notembedded.result b/mysql-test/main/selectivity_notembedded.result new file mode 100644 index 00000000000..8b298a95801 --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.result @@ -0,0 +1,83 @@ +# +# MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +# +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set @save_use_stat_tables=@@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; +SET histogram_size= 255; +set histogram_type='DOUBLE_PREC_HB'; +set use_stat_tables=preferably; +SET optimizer_use_condition_selectivity=3; +create table t0(a int); +insert into t0 select 1 from seq_1_to_78; +create table t1(a int); +insert into t1 select 1 from seq_1_to_26; +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; +insert into t10 select 34785 from t0, seq_1_to_8; +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; +insert into t10 select 65216 from t0, seq_1_to_52; +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; +insert into t10 select 65219 from t0; +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; +insert into t10 select 73915 from t0, seq_1_to_40; +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; +insert into t10 select 91302 from t0, seq_1_to_6; +insert into t10 select 91303 from t1; +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; +insert into t10 select 91305 from t0, seq_1_to_8; +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; +analyze table t10 persistent for all; +Table Op Msg_type Msg_text +test.t10 analyze status Engine-independent statistics collected +test.t10 analyze status OK +flush tables; +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t10 ALL NULL NULL NULL NULL 9984 Using where +# Must have selectivity_from_histogram <= 1.0: +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +from information_schema.optimizer_trace; +json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +[ + [ + { + "column_name": "a", + "ranges": + ["91303 <= a <= 91303"], + "selectivity_from_histogram": 0.0357 + } + ] +] +drop table t0,t1,t10; +set optimizer_trace=@tmp; +set @@histogram_size=@save_histogram_size; +set @histogram_type=@save_histogram_type; +set @use_stat_tables=@save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/selectivity_notembedded.test b/mysql-test/main/selectivity_notembedded.test new file mode 100644 index 00000000000..f79c370186f --- /dev/null +++ b/mysql-test/main/selectivity_notembedded.test @@ -0,0 +1,102 @@ +--source include/have_stat_tables.inc +--source include/have_sequence.inc +--source include/not_embedded.inc + +--echo # +--echo # MDEV-31067: selectivity_from_histogram >1.0 for a DOUBLE_PREC_HB histogram +--echo # + +set @save_histogram_size=@@histogram_size; +set @save_histogram_type=@@histogram_type; +set @save_use_stat_tables=@@use_stat_tables; +set @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity; + +SET histogram_size= 255; +set histogram_type='DOUBLE_PREC_HB'; +set use_stat_tables=preferably; +SET optimizer_use_condition_selectivity=3; + +create table t0(a int); # This holds how many rows we hold in a bucket. +insert into t0 select 1 from seq_1_to_78; + +create table t1(a int); # one-third of a bucket +insert into t1 select 1 from seq_1_to_26; + +create table t10 (a int); +insert into t10 select 0 from t0, seq_1_to_4; + +insert into t10 select 8693 from t1; +insert into t10 select 8694 from t1; +insert into t10 select 8695 from t1; + + +insert into t10 select 34783 from t1; +insert into t10 select 34784 from t1; +insert into t10 select 34785 from t1; + + +insert into t10 select 34785 from t0, seq_1_to_8; + +insert into t10 select 65214 from t1; +insert into t10 select 65215 from t1; +insert into t10 select 65216 from t1; + +insert into t10 select 65216 from t0, seq_1_to_52; + +insert into t10 select 65217 from t1; +insert into t10 select 65218 from t1; +insert into t10 select 65219 from t1; + +insert into t10 select 65219 from t0; + + +insert into t10 select 73913 from t1; +insert into t10 select 73914 from t1; +insert into t10 select 73915 from t1; + +insert into t10 select 73915 from t0, seq_1_to_40; + + +insert into t10 select 78257 from t1; +insert into t10 select 78258 from t1; +insert into t10 select 78259 from t1; + +insert into t10 select 91300 from t1; +insert into t10 select 91301 from t1; +insert into t10 select 91302 from t1; + +insert into t10 select 91302 from t0, seq_1_to_6; + +insert into t10 select 91303 from t1; # Only 1/3rd of bucket matches the search tuple +insert into t10 select 91304 from t1; +insert into t10 select 91305 from t1; + +insert into t10 select 91305 from t0, seq_1_to_8; + +insert into t10 select 99998 from t1; +insert into t10 select 99999 from t1; +insert into t10 select 100000 from t1; + +analyze table t10 persistent for all; +flush tables; + +set @tmp=@@optimizer_trace; +set optimizer_trace=1; +explain select * from t10 where a in (91303); + +--echo # Must have selectivity_from_histogram <= 1.0: +--replace_result 0.035714283 0.0357 +select json_detailed(json_extract(trace, '$**.selectivity_for_columns')) +from information_schema.optimizer_trace; + +drop table t0,t1,t10; + +set optimizer_trace=@tmp; +set @@histogram_size=@save_histogram_size; +set @histogram_type=@save_histogram_type; +set @use_stat_tables=@save_use_stat_tables; +set @@optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # End of 10.4 tests +--echo # -- cgit v1.2.1 From a09f661f4392fc9574a5239972243eebc65fe7f2 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Mon, 8 May 2023 11:42:24 -0700 Subject: MDEV-31181 Crash with EXPLAIN EXTENDED for single-table DELETE using IN predicand This bug affected EXPLAIN EXTENDED command for single-table DELETE that used an IN subquery in its WHERE clause. A crash happened if the optimizer chose to employ index_subquery or unique_subquery access when processing such command. The crash happened when the command tried to print the transformed query. In the current code of 10.4 for single-table DELETE statements the output of any explain command is produced after the join structures of all used subqueries have been destroyed. JOIN::destroy() sets the field tab of the JOIN_TAB structures created for subquery tables to NULL. As a result subselect_indexsubquery_engine::print(), subselect_indexsubquery_engine() cannot use this field to get the alias name of the joined table. This patch suggests to use the field TABLE_LIST::TAB that can be accessed from JOIN_TAB::tab_list to get the alias name of the joined table. Approved by Oleksandr Byelkin --- mysql-test/main/explain_non_select.result | 32 +++++++++++++++++++++++++++++++ mysql-test/main/explain_non_select.test | 28 +++++++++++++++++++++++++++ 2 files changed, 60 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d60f10f85c8..d7ed3992572 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -277,3 +277,35 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 drop table t1,t2; +# +# MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); +explain extended delete from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t2 on PRIMARY))) +delete from t1 where a in (select pk from t2); +select * from t1; +a +7 +4 +explain extended delete from t1 where a in (select a from t3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t3 index_subquery a a 5 func 2 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where (`test`.`t1`.`a`,(((`test`.`t1`.`a`) in t3 on a))) +delete from t1 where a in (select a from t3); +select * from t1; +a +4 +drop table t1,t2,t3; +# End of 10.4 tests diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test index d9ff0fb7245..c0c543ad273 100644 --- a/mysql-test/main/explain_non_select.test +++ b/mysql-test/main/explain_non_select.test @@ -250,3 +250,31 @@ PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 SELECT * FROM t2'; EXECUTE stmt; drop table t1,t2; +--echo # +--echo # MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); + +let $q1= +delete from t1 where a in (select pk from t2); + +eval explain extended $q1; +eval $q1; +select * from t1; + +let $q2= +delete from t1 where a in (select a from t3); + +eval explain extended $q2; +eval $q2; +select * from t1; + +drop table t1,t2,t3; + +--echo # End of 10.4 tests -- cgit v1.2.1 From 6544d88ff5a7b2ec66153933a3a0531b9091c357 Mon Sep 17 00:00:00 2001 From: Igor Babaev Date: Tue, 9 May 2023 21:20:10 -0700 Subject: MDEV-31224 Crash with EXPLAIN EXTENDED for multi-table update of system table EXPLAIN EXTENDED should always print the field item used in the left part of an equality expression from the SET clause of an update statement as a reference to table column. Approved by Oleksandr Byelkin --- mysql-test/main/explain_non_select.result | 18 ++++++++++++++++++ mysql-test/main/explain_non_select.test | 19 +++++++++++++++++++ mysql-test/main/myisam_explain_non_select_all.result | 4 ++-- 3 files changed, 39 insertions(+), 2 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d7ed3992572..009a568e2c2 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -308,4 +308,22 @@ select * from t1; a 4 drop table t1,t2,t3; +# +# MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); +EXPLAIN EXTENDED UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 update `test`.`t1` set `test`.`t2`.`b` = 4 where `test`.`t1`.`a` in (6,2) +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +SELECT * from t2; +b +4 +DROP TABLE t1, t2; # End of 10.4 tests diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test index c0c543ad273..e861955b3f1 100644 --- a/mysql-test/main/explain_non_select.test +++ b/mysql-test/main/explain_non_select.test @@ -277,4 +277,23 @@ select * from t1; drop table t1,t2,t3; +--echo # +--echo # MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); + +let $q= +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); + +eval EXPLAIN EXTENDED $q; +eval $q; +SELECT * from t2; + +DROP TABLE t1, t2; + --echo # End of 10.4 tests diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 3ca9629d027..b515cb4fd83 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -2689,7 +2689,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 update `test`.`t1` set NULL = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -2734,7 +2734,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 update `test`.`t1` set NULL = 10 where `test`.`t1`.`c3` = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 -- cgit v1.2.1 From 7e7e12e747a8284efea697518940f6a647ff915c Mon Sep 17 00:00:00 2001 From: Oleg Smirnov Date: Mon, 24 Apr 2023 18:38:42 +0700 Subject: MDEV-30765 SHOW TABLES not working properly with lower_case_table_names=2 lower_case_table_names=2 means "table names and database names are stored as declared, but they are compared in lowercase". But names of objects in grants are stored in lowercase for any value of lower_case_table_names. This caused an error when checking grants for objects containing uppercase letters since table_hash_search() didn't take into account lower_case_table_names value --- mysql-test/main/lowercase_table2.opt | 1 + mysql-test/main/lowercase_table2.result | 36 +++++++++++++++++++++++++++------ mysql-test/main/lowercase_table2.test | 26 ++++++++++++++++++++++++ 3 files changed, 57 insertions(+), 6 deletions(-) create mode 100644 mysql-test/main/lowercase_table2.opt (limited to 'mysql-test/main') diff --git a/mysql-test/main/lowercase_table2.opt b/mysql-test/main/lowercase_table2.opt new file mode 100644 index 00000000000..ac4d3211e89 --- /dev/null +++ b/mysql-test/main/lowercase_table2.opt @@ -0,0 +1 @@ +--lower-case-table-names=2 diff --git a/mysql-test/main/lowercase_table2.result b/mysql-test/main/lowercase_table2.result index 9194638a4d2..fe06fb671a2 100644 --- a/mysql-test/main/lowercase_table2.result +++ b/mysql-test/main/lowercase_table2.result @@ -14,7 +14,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -70,7 +70,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -319,18 +319,42 @@ Database (mysql_t%) mysql_TEST show create database mysql_test; Database Create Database -mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create database mysql_TEST; Database Create Database -mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create table mysql_TEST.T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table mysql_test.t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop database mysql_TEST; +# MDEV-30765 SHOW TABLES not working properly with +# lower_case_table_names=2 +# +create database db1; +use db1; +# lowercase table name +create table `a` (a int); +# uppercase table name +create table `B` (a int); +create user 'mysqltest_1'@'localhost' identified by 'password'; +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; +connect conn1, localhost, mysqltest_1, password, test; +connection conn1; +use db1; +show tables; +Tables_in_db1 +B +a +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; diff --git a/mysql-test/main/lowercase_table2.test b/mysql-test/main/lowercase_table2.test index 601089ca760..82c07bf9345 100644 --- a/mysql-test/main/lowercase_table2.test +++ b/mysql-test/main/lowercase_table2.test @@ -288,3 +288,29 @@ show create database mysql_TEST; show create table mysql_TEST.T1; show create table mysql_test.t1; drop database mysql_TEST; + +--echo # MDEV-30765 SHOW TABLES not working properly with +--echo # lower_case_table_names=2 +--echo # +create database db1; +use db1; +--echo # lowercase table name +create table `a` (a int); +--echo # uppercase table name +create table `B` (a int); + +create user 'mysqltest_1'@'localhost' identified by 'password'; + +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; + +connect (conn1, localhost, mysqltest_1, password, test); +connection conn1; +use db1; +show tables; + +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; \ No newline at end of file -- cgit v1.2.1