diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2021-04-14 11:35:39 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2021-04-14 11:35:39 +0300 |
commit | 6c3e860cbf36831c118f6ea183acbbeb3c889bed (patch) | |
tree | 8585545cc8a95e790c01eb164f74685674f9f23b /mysql-test/main | |
parent | 9ff737b25edbcb0c74d9d312f6da702e7d993e88 (diff) | |
parent | 5008171b05e0d3b8b5f4af312b94a312281e77c7 (diff) | |
download | mariadb-git-6c3e860cbf36831c118f6ea183acbbeb3c889bed.tar.gz |
Merge 10.4 into 10.5
Diffstat (limited to 'mysql-test/main')
24 files changed, 269 insertions, 43 deletions
diff --git a/mysql-test/main/derived_opt.result b/mysql-test/main/derived_opt.result index 9da9e6e4413..cf0c1cb617f 100644 --- a/mysql-test/main/derived_opt.result +++ b/mysql-test/main/derived_opt.result @@ -540,4 +540,31 @@ id select_type table type possible_keys key key_len ref rows Extra set join_cache_level=default; set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch="derived_merge=on"; +CREATE TABLE t1 (id int, d2 datetime, id1 int) ; +insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3); +CREATE TABLE t2 (id int, d1 datetime, id1 int) ; +insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2); +prepare stmt from " +SELECT * from + (SELECT min(d2) AS d2, min(d1) AS d1 FROM + (SELECT t1.d2 AS d2, (SELECT t2.d1 + FROM t2 WHERE t1.id1 = t2.id1 + ORDER BY t2.id DESC LIMIT 1) AS d1 + FROM t1 + ) dt2 + ) ca + ORDER BY ca.d2;"; +execute stmt; +d2 d1 +2020-01-01 10:10:10 2020-01-01 10:10:10 +execute stmt; +d2 d1 +2020-01-01 10:10:10 2020-01-01 10:10:10 +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1, t2; +# +# End of 10.3 tests +# set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/derived_opt.test b/mysql-test/main/derived_opt.test index eccf4c13020..dee424559ee 100644 --- a/mysql-test/main/derived_opt.test +++ b/mysql-test/main/derived_opt.test @@ -406,5 +406,38 @@ set optimizer_switch= @save_optimizer_switch; DROP TABLE t1,t2; +# +# MDEV-25182: Complex query in Store procedure corrupts results +# +set @save_optimizer_switch= @@optimizer_switch; +set optimizer_switch="derived_merge=on"; + +CREATE TABLE t1 (id int, d2 datetime, id1 int) ; +insert into t1 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',3); + +CREATE TABLE t2 (id int, d1 datetime, id1 int) ; +insert into t2 values (1,'2020-01-01 10:10:10',1),(2,'2020-01-01 10:10:10',2),(3,'2020-01-01 10:10:10',2); + +prepare stmt from " +SELECT * from + (SELECT min(d2) AS d2, min(d1) AS d1 FROM + (SELECT t1.d2 AS d2, (SELECT t2.d1 + FROM t2 WHERE t1.id1 = t2.id1 + ORDER BY t2.id DESC LIMIT 1) AS d1 + FROM t1 + ) dt2 + ) ca + ORDER BY ca.d2;"; + +execute stmt; +execute stmt; + +set optimizer_switch= @save_optimizer_switch; +DROP TABLE t1, t2; + +--echo # +--echo # End of 10.3 tests +--echo # + # The following command must be the last one the file set optimizer_switch=@exit_optimizer_switch; diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index 0f09aedc22a..177f0950a77 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -606,7 +606,7 @@ select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 0 NULL 15 Using where; Using index explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 3 NULL 14 Using where; Using index explain diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 10b92cbadca..a28b39c28f6 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -359,7 +359,7 @@ select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; explain select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); explain select max(a3) from t1 where a2 < 2 and a3 < 'SEA'; explain diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index 4f32db780fd..a17f3f09c3b 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2942,7 +2942,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 1a4906801a0..053239b7fb1 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1687,7 +1687,8 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,c1,i,c2 PRIMARY,i 0,5 NULL 69 Using sort_union(PRIMARY,i); Using where DROP TABLE t1; diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test index e35ce567573..10ddbee36b2 100644 --- a/mysql-test/main/index_merge_myisam.test +++ b/mysql-test/main/index_merge_myisam.test @@ -236,9 +236,11 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; +# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1 EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; DROP TABLE t1; diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index adf65780d21..c89d49cad0c 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -455,11 +455,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -689,23 +689,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index f059c3f95e5..d0614913480 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -448,11 +448,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -682,23 +682,23 @@ INSERT INTO t2 VALUES insert into t2 select seq from seq_1_to_100; SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL # Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a # Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/mysqldump-nl.test b/mysql-test/main/mysqldump-nl.test index 863c846b9a6..4451b0605c2 100644 --- a/mysql-test/main/mysqldump-nl.test +++ b/mysql-test/main/mysqldump-nl.test @@ -26,10 +26,10 @@ create procedure sp() select * from `v1 flush tables; use test; -exec $MYSQL_DUMP --compact --comment --routines --add-drop-database --databases 'mysqltest1 +exec $MYSQL_DUMP --compact --comments --routines --add-drop-database --databases 'mysqltest1 1tsetlqsym'; -exec $MYSQL_DUMP --compact --comment --routines --add-drop-database --databases 'mysqltest1 +exec $MYSQL_DUMP --compact --comments --routines --add-drop-database --databases 'mysqltest1 1tsetlqsym' | $MYSQL; show tables from `mysqltest1 @@ -45,11 +45,11 @@ create database `test\`` show databases like 'test%'; -exec $MYSQL_DUMP --compact --comment --add-drop-database --databases 'test`' 'test\` +exec $MYSQL_DUMP --compact --comments --add-drop-database --databases 'test`' 'test\` \! ls #'; -exec $MYSQL_DUMP --compact --comment --add-drop-database --databases 'test`' 'test\` +exec $MYSQL_DUMP --compact --comments --add-drop-database --databases 'test`' 'test\` \! ls #' | $MYSQL; diff --git a/mysql-test/main/parser_not_embedded.test b/mysql-test/main/parser_not_embedded.test index 3ebd23e888e..3af1260f4ad 100644 --- a/mysql-test/main/parser_not_embedded.test +++ b/mysql-test/main/parser_not_embedded.test @@ -21,7 +21,7 @@ select 7 as expected, /*!01000 1 + /*!01000 8 + /*!01000 error */ 16 + */ 2 + */ select 4 as expected, /* 1 + /*!01000 8 + */ 2 + */ 4; EOF ---exec $MYSQL --comment --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql +--exec $MYSQL --comments --force --table test <$MYSQLTEST_VARDIR/tmp/bug39559.sql --remove_file $MYSQLTEST_VARDIR/tmp/bug39559.sql --echo # Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense" diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 1c13b0a0b84..6b2586e9be6 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5514,6 +5514,43 @@ id select_type table type possible_keys key key_len ref rows Extra DEALLOCATE PREPARE stmt; DROP TABLE t1; # +# MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning +# in case it is executed in PS (prepared statement) mode +# +CREATE TABLE t1 (c int); +CREATE TABLE t2 (d int); +# EXPLAIN EXTENDED in regular way (not PS mode) +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +SHOW WARNINGS; +Level Code Message +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +# Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings +# and their content must be the same as in case running the statement +# in regular way +PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1"; +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +EXECUTE stmt; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 system NULL NULL NULL NULL 0 0.00 Const row not found +2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +Warnings: +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +SHOW WARNINGS; +Level Code Message +Note 1276 Field or reference 'test.t1.c' of SELECT #2 was resolved in SELECT #1 +Note 1003 /* select#1 */ select (/* select#2 */ select 1 from `test`.`t2` where 0) AS `(SELECT 1 FROM t2 WHERE d = c)` from `test`.`t1` +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2; +# # End of 10.2 tests # # @@ -5532,5 +5569,37 @@ DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1; # +# MDEV-25197: The statement set password=password('') executed in PS mode +# fails in case it is run by a user with expired password +# +CREATE USER user1@localhost PASSWORD EXPIRE; +SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; +SET GLOBAL disconnect_on_expired_password=OFF; +connect con1,localhost,user1; +connection con1; +# Check that no regular statement like SELECT can be prepared +# by a user with an expired password +PREPARE stmt FROM "SELECT 1"; +ERROR HY000: You must SET PASSWORD before executing this statement +# Check that the DEALLOCATE PREPARE statement can be run by a user +# with an expired password +PREPARE stmt FROM "SET password=password('')"; +DEALLOCATE PREPARE stmt; +# Check that the SET PASSWORD statement can be executed in PS mode by +# a user with an expired password +PREPARE stmt FROM "SET password=password('')"; +EXECUTE stmt; +PREPARE stmt FROM "SELECT 1"; +# Check that user's password is not expired anymore +EXECUTE stmt; +1 +1 +DEALLOCATE PREPARE stmt; +# Clean up +disconnect con1; +connection default; +SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; +DROP USER user1@localhost; +# # End of 10.4 tests # diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 2ce78b78e90..2a468d33ace 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -4956,6 +4956,26 @@ DEALLOCATE PREPARE stmt; DROP TABLE t1; --echo # +--echo # MDEV-25108: Running of the EXPLAIN EXTENDED statement produces extra warning +--echo # in case it is executed in PS (prepared statement) mode +--echo # +CREATE TABLE t1 (c int); +CREATE TABLE t2 (d int); + +--echo # EXPLAIN EXTENDED in regular way (not PS mode) +EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1; +SHOW WARNINGS; + +--echo # Now run the same EXPLAIN EXTENDED in PS mode. Number of warnings +--echo # and their content must be the same as in case running the statement +--echo # in regular way +PREPARE stmt FROM "EXPLAIN EXTENDED SELECT (SELECT 1 FROM t2 WHERE d = c) FROM t1"; +EXECUTE stmt; +SHOW WARNINGS; + +DEALLOCATE PREPARE stmt; +DROP TABLE t1, t2; +--echo # --echo # End of 10.2 tests --echo # @@ -4980,5 +5000,42 @@ DROP VIEW v1; DROP TABLE t1; --echo # +--echo # MDEV-25197: The statement set password=password('') executed in PS mode +--echo # fails in case it is run by a user with expired password +--echo # +CREATE USER user1@localhost PASSWORD EXPIRE; + +SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; +SET GLOBAL disconnect_on_expired_password=OFF; + +connect(con1,localhost,user1); +connection con1; +--echo # Check that no regular statement like SELECT can be prepared +--echo # by a user with an expired password +--error ER_MUST_CHANGE_PASSWORD +PREPARE stmt FROM "SELECT 1"; + +--echo # Check that the DEALLOCATE PREPARE statement can be run by a user +--echo # with an expired password +PREPARE stmt FROM "SET password=password('')"; +DEALLOCATE PREPARE stmt; + +--echo # Check that the SET PASSWORD statement can be executed in PS mode by +--echo # a user with an expired password +PREPARE stmt FROM "SET password=password('')"; +EXECUTE stmt; +PREPARE stmt FROM "SELECT 1"; +--echo # Check that user's password is not expired anymore +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # Clean up +disconnect con1; +connection default; + +SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; +DROP USER user1@localhost; + +--echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 8e9b9596e1e..d97cfb2b587 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3252,6 +3252,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index 8e8161cde25..6d43ad9090d 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2216,6 +2216,17 @@ DROP TABLE t1, t2; --echo # +--echo # MDEV-23634: Select query hanged the server and leads to OOM ... +--echo # (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +--echo # +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +--echo # must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +drop table t1; + +--echo # --echo # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index e24e3bc3903..9fa7bd29851 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3241,6 +3241,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index 6b2c9a2f0bc..207e012b825 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1810,11 +1810,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 2 Using index condition; Using where SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1846,12 +1846,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index 5ed5f621ab6..94210ce5dd3 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -1231,9 +1231,9 @@ INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); DROP TABLE t1; @@ -1266,10 +1266,10 @@ ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; DROP TABLE t1; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index 497aa3d94dc..4ac62e24940 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -1816,11 +1816,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY,idx idx 5 NULL 2 Using where; Using index SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1852,12 +1852,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 46723410b75..dc377aa5b6c 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1443,7 +1443,7 @@ EXPLAIN EXTENDED SELECT * FROM t1, t2 WHERE a <> 'USARussian' AND b IS NULL; id select_type table type possible_keys key key_len ref rows filtered Extra -1 SIMPLE t1 range PRIMARY,b b 23 NULL 2 100.00 Using where; Using index +1 SIMPLE t1 ref PRIMARY,b b 5 const 1 100.00 Using where; Using index 1 SIMPLE t2 ALL NULL NULL NULL NULL 14 100.00 Using join buffer (flat, BNL join) Warnings: Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b`,`test`.`t2`.`i` AS `i` from `test`.`t1` join `test`.`t2` where `test`.`t1`.`a` <> 'USARussian' and `test`.`t1`.`b` is null diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index 34c0ccab620..6a218ed5e04 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -189,8 +189,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; id select_type table type possible_keys key key_len ref rows Extra @@ -203,8 +203,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; field1 diff --git a/mysql-test/main/subselect_mat_cost_bugs.test b/mysql-test/main/subselect_mat_cost_bugs.test index 028cdced560..ba1aad06a15 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.test +++ b/mysql-test/main/subselect_mat_cost_bugs.test @@ -217,8 +217,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; @@ -226,8 +226,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; diff --git a/mysql-test/main/userstat.result b/mysql-test/main/userstat.result index 6d9cd491de6..9152f602304 100644 --- a/mysql-test/main/userstat.result +++ b/mysql-test/main/userstat.result @@ -243,6 +243,8 @@ create function f() returns int return (select 1 from performance_schema.threads set global userstat= 1; select f() from information_schema.table_statistics; ERROR 21000: Subquery returns more than 1 row +select f() from information_schema.index_statistics; +ERROR 21000: Subquery returns more than 1 row set global userstat= 0; drop function f; # diff --git a/mysql-test/main/userstat.test b/mysql-test/main/userstat.test index cc7ddd58e11..6d486810db1 100644 --- a/mysql-test/main/userstat.test +++ b/mysql-test/main/userstat.test @@ -121,6 +121,8 @@ create function f() returns int return (select 1 from performance_schema.threads set global userstat= 1; --error ER_SUBQUERY_NO_1_ROW select f() from information_schema.table_statistics; +--error ER_SUBQUERY_NO_1_ROW +select f() from information_schema.index_statistics; set global userstat= 0; drop function f; |