From d0603fc5ba4dc17a155a575edd79ae0fb9de3679 Mon Sep 17 00:00:00 2001 From: Monty Date: Mon, 2 Jan 2023 18:34:19 +0200 Subject: MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT The problem was that when storing rows into a temporary table, MIN/MAX items that where marked as constants (as theire value had been computed at start of query) would be reset. Fixed by not reseting MIN/MAX items that are marked as const in Item_sum_min_max::clear(). --- mysql-test/main/group_min_max.result | 20 ++++++++++++++++++++ mysql-test/main/group_min_max.test | 17 +++++++++++++++++ 2 files changed, 37 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 214f64df874..d1bd4d8cedb 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4065,3 +4065,23 @@ SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUN owner_id 1 DROP TABLE t1; +# +# MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT +# +drop table if exists t1,t2; +Warnings: +Note 1051 Unknown table 'test.t1,test.t2' +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1),(2); +SELECT SQL_BUFFER_RESULT MIN(pk) FROM t1, t2; +MIN(pk) +1 +SELECT MIN(pk) FROM t1, t2; +MIN(pk) +1 +DROP TABLE t1, t2; +# +# End of 10.5 tests +# diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 3b043fc0842..7de57d75d36 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1723,3 +1723,20 @@ EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); DROP TABLE t1; + +--echo # +--echo # MDEV-30240 Wrong result upon aggregate function with SQL_BUFFER_RESULT +--echo # + +drop table if exists t1,t2; +CREATE TABLE t1 (pk INT PRIMARY KEY); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (a INT); +INSERT INTO t2 VALUES (1),(2); +SELECT SQL_BUFFER_RESULT MIN(pk) FROM t1, t2; +SELECT MIN(pk) FROM t1, t2; +DROP TABLE t1, t2; + +--echo # +--echo # End of 10.5 tests +--echo # -- cgit v1.2.1 From 12a85c6caf595c685336455e416099b6a8020534 Mon Sep 17 00:00:00 2001 From: Thirunarayanan Balathandayuthapani Date: Thu, 5 Jan 2023 11:06:35 +0530 Subject: MDEV-30346 Avoid block device required error in innodb_fts.misc_debug - Returns DB_LOCK_WAIT_TIMEOUT for the stats_lock_fail debug sync point --- mysql-test/suite/innodb_fts/r/misc_debug.result | 2 +- mysql-test/suite/innodb_fts/t/misc_debug.test | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb_fts/r/misc_debug.result b/mysql-test/suite/innodb_fts/r/misc_debug.result index 11df7d89f0b..cdfc4ff489a 100644 --- a/mysql-test/suite/innodb_fts/r/misc_debug.result +++ b/mysql-test/suite/innodb_fts/r/misc_debug.result @@ -68,7 +68,7 @@ DROP TABLE t1; CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(100))ENGINE=InnoDB; SET DEBUG_DBUG="+d,stats_lock_fail"; ALTER TABLE t1 ADD FULLTEXT(f2); -ERROR HY000: Got error 15 "Block device required" from storage engine InnoDB +ERROR HY000: Lock wait timeout exceeded; try restarting transaction SET debug_dbug=@saved_debug_dbug; ALTER TABLE t1 DISCARD TABLESPACE; ALTER TABLE t1 ADD FULLTEXT(f2); diff --git a/mysql-test/suite/innodb_fts/t/misc_debug.test b/mysql-test/suite/innodb_fts/t/misc_debug.test index 229d468201e..08581768eec 100644 --- a/mysql-test/suite/innodb_fts/t/misc_debug.test +++ b/mysql-test/suite/innodb_fts/t/misc_debug.test @@ -108,7 +108,7 @@ DROP TABLE t1; CREATE TABLE t1(f1 INT NOT NULL, f2 CHAR(100))ENGINE=InnoDB; SET DEBUG_DBUG="+d,stats_lock_fail"; ---error ER_GET_ERRNO +--error ER_LOCK_WAIT_TIMEOUT ALTER TABLE t1 ADD FULLTEXT(f2); SET debug_dbug=@saved_debug_dbug; ALTER TABLE t1 DISCARD TABLESPACE; -- cgit v1.2.1 From 494acc19388c7d7f744ebc036cae1329e46101bb Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 4 Jan 2023 19:16:45 +0200 Subject: MDEV-30325 Wrong result upon range query using index condition wrong result upon range query using index condition This was caused by a bug in key_or() when SEL_ARG* key1 has been cloned and is overlapping with SEL_ARG *key2 Cloning of SEL_ARG's happens only in very special cases, which is why this bug has remained undetected for years. It happend in the following query: SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); Because there are two different indexes that can be used and the code for IN causes a 'tree_or', which causes all SEL_ARG's to be cloned. Other things: - While checking the code, I found a bug in SEL_ARG::SEL_ARG(SEL_ARG &arg) - This was incrementing next_key_part->use_count as part of creating a copy of an existing SEL_ARG. This is however not enough as the 'reverse operation' when the copy is not needed is 'key2_cpy.increment_use_count(-1)', which does something completely different. Fixed by calling increment_use_count(1) in SEL_ARG::SEL_ARG. --- mysql-test/main/range_aria_dbt3.result | 13 +++++++++++++ mysql-test/main/range_aria_dbt3.test | 24 ++++++++++++++++++++++++ 2 files changed, 37 insertions(+) create mode 100644 mysql-test/main/range_aria_dbt3.result create mode 100644 mysql-test/main/range_aria_dbt3.test (limited to 'mysql-test') diff --git a/mysql-test/main/range_aria_dbt3.result b/mysql-test/main/range_aria_dbt3.result new file mode 100644 index 00000000000..ae5a2e1329f --- /dev/null +++ b/mysql-test/main/range_aria_dbt3.result @@ -0,0 +1,13 @@ +set default_storage_engine=Aria; +CREATE DATABASE dbt3_s001; +use dbt3_s001; +# +# MDEV-30325 Wrong result upon range query using index condition +# +SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); +COUNT(*) +5056 +# +# End of 10.5 tests +# +DROP DATABASE dbt3_s001; diff --git a/mysql-test/main/range_aria_dbt3.test b/mysql-test/main/range_aria_dbt3.test new file mode 100644 index 00000000000..89328280987 --- /dev/null +++ b/mysql-test/main/range_aria_dbt3.test @@ -0,0 +1,24 @@ +# +# This is generic tests using dbt3_s001 tables +# This file uses the Aria storage engine +# + +set default_storage_engine=Aria; + +CREATE DATABASE dbt3_s001; +use dbt3_s001; +--disable_query_log +--source include/dbt3_s001.inc +--enable_query_log + +--echo # +--echo # MDEV-30325 Wrong result upon range query using index condition +--echo # + +SELECT COUNT(*) FROM lineitem force index (i_l_orderkey_quantity,i_l_shipdate) WHERE l_shipdate < '1994-01-01' AND l_orderkey < 800 OR l_quantity > 3 AND l_orderkey NOT IN ( 157, 1444 ); + +--echo # +--echo # End of 10.5 tests +--echo # + +DROP DATABASE dbt3_s001; -- cgit v1.2.1