diff options
author | Sergei Golubchik <serg@mariadb.org> | 2021-02-23 13:01:27 +0100 |
---|---|---|
committer | Sergei Golubchik <serg@mariadb.org> | 2021-02-23 13:06:22 +0100 |
commit | f33e57a9e66f7e1790cb84b141381bb668e281a0 (patch) | |
tree | 31bb1c5bd8e0addc9e994a1bb42b4703c37f7ed8 /mysql-test/main | |
parent | 1a0526e2f294acdcac829672794ee1fe708eb2b3 (diff) | |
parent | 245d33db4e0586df4fe28362fb002cef0151a1c9 (diff) | |
download | mariadb-git-f33e57a9e66f7e1790cb84b141381bb668e281a0.tar.gz |
Merge branch '10.4' into 10.5
Diffstat (limited to 'mysql-test/main')
24 files changed, 678 insertions, 12 deletions
diff --git a/mysql-test/main/default.result b/mysql-test/main/default.result index ca65c66ea41..0d2c2e6acbc 100644 --- a/mysql-test/main/default.result +++ b/mysql-test/main/default.result @@ -3388,3 +3388,18 @@ ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa ERROR 42S22: Unknown column 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' in 'DEFAULT' DROP TABLE t1; # end of 10.2 test +# +# MDEV-22703 DEFAULT() on a BLOB column can overwrite the default +# record, which can cause crashes when accessing already released +# memory. +# +CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (),(); +ALTER TABLE t1 FORCE; +SELECT DEFAULT(h) FROM t1; +SELECT length(DEFAULT(h)) FROM t1; +length(DEFAULT(h)) +25 +25 +INSERT INTO t1 () VALUES (); +drop table t1; diff --git a/mysql-test/main/default.test b/mysql-test/main/default.test index 27e38eeeb49..c0561deac67 100644 --- a/mysql-test/main/default.test +++ b/mysql-test/main/default.test @@ -1,3 +1,5 @@ +--source include/have_innodb.inc + # # test of already fixed bugs # @@ -2107,5 +2109,20 @@ CREATE OR REPLACE TABLE t1(i int); ALTER TABLE t1 ADD b CHAR(255) DEFAULT `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa`; DROP TABLE t1; - --echo # end of 10.2 test + +--echo # +--echo # MDEV-22703 DEFAULT() on a BLOB column can overwrite the default +--echo # record, which can cause crashes when accessing already released +--echo # memory. +--echo # + +CREATE TEMPORARY TABLE t1 (h POINT DEFAULT ST_GEOMFROMTEXT('Point(1 1)')) ENGINE=InnoDB; +INSERT INTO t1 () VALUES (),(); +ALTER TABLE t1 FORCE; +--disable_result_log +SELECT DEFAULT(h) FROM t1; +--enable_result_log +SELECT length(DEFAULT(h)) FROM t1; +INSERT INTO t1 () VALUES (); +drop table t1; diff --git a/mysql-test/main/flush_and_binlog.result b/mysql-test/main/flush_and_binlog.result new file mode 100644 index 00000000000..b9560964046 --- /dev/null +++ b/mysql-test/main/flush_and_binlog.result @@ -0,0 +1,22 @@ +# +# MDEV-23843 Assertions in Diagnostics_area upon table operations under +# FTWRL +# +CREATE TABLE t1 (a INT); +FLUSH TABLES WITH READ LOCK; +connect con1,localhost,root,,; +SET lock_wait_timeout= 1; +OPTIMIZE TABLE t1; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +disconnect con1; +connection default; +UNLOCK TABLES; +DROP TABLE t1; +FLUSH TABLES WITH READ LOCK; +connect con1,localhost,root,,test; +SET lock_wait_timeout= 1; +FLUSH TABLES; +ERROR HY000: Lock wait timeout exceeded; try restarting transaction +connection default; +disconnect con1; +unlock tables; diff --git a/mysql-test/main/flush_and_binlog.test b/mysql-test/main/flush_and_binlog.test new file mode 100644 index 00000000000..373b900b451 --- /dev/null +++ b/mysql-test/main/flush_and_binlog.test @@ -0,0 +1,29 @@ +--source include/have_log_bin.inc + +--echo # +--echo # MDEV-23843 Assertions in Diagnostics_area upon table operations under +--echo # FTWRL +--echo # + +CREATE TABLE t1 (a INT); +FLUSH TABLES WITH READ LOCK; +--connect (con1,localhost,root,,) +SET lock_wait_timeout= 1; +--error ER_LOCK_WAIT_TIMEOUT +OPTIMIZE TABLE t1; +# Cleanup +--disconnect con1 +--connection default +UNLOCK TABLES; +DROP TABLE t1; +# +# Second test case from MDEV_23843 +# +FLUSH TABLES WITH READ LOCK; +--connect (con1,localhost,root,,test) +SET lock_wait_timeout= 1; +--error ER_LOCK_WAIT_TIMEOUT +FLUSH TABLES; +--connection default +--disconnect con1 +unlock tables; diff --git a/mysql-test/main/information_schema.result b/mysql-test/main/information_schema.result index 4b34fbed02c..fcaa0a921c9 100644 --- a/mysql-test/main/information_schema.result +++ b/mysql-test/main/information_schema.result @@ -2325,5 +2325,18 @@ group by f; f drop table t1; # +# MDEV-24929 Server crash in thr_multi_unlock or in +# get_schema_tables_result upon select from I_S with joins +# +CREATE TABLE t1 (a TIMESTAMP, KEY (a)); +INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11'); +SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL); +count(*) +2 +SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL); +count(*) +2 +DROP TABLE t1; +# # End of 10.3 tests # diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test index 59704803175..5879f910e4d 100644 --- a/mysql-test/main/information_schema.test +++ b/mysql-test/main/information_schema.test @@ -2033,5 +2033,15 @@ group by f; drop table t1; --echo # +--echo # MDEV-24929 Server crash in thr_multi_unlock or in +--echo # get_schema_tables_result upon select from I_S with joins +--echo # + +CREATE TABLE t1 (a TIMESTAMP, KEY (a)); +INSERT INTO t1 VALUES ('2012-12-12'),('2021-11-11'); +SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.ROUTINES) ON (t1b.a IS NULL); +SELECT count(*) FROM t1 AS t1a LEFT JOIN (t1 AS t1b JOIN INFORMATION_SCHEMA.PROFILING) ON (t1b.a IS NULL); +DROP TABLE t1; +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/innodb_ext_key.result b/mysql-test/main/innodb_ext_key.result index 5708cf88934..4c502a46981 100644 --- a/mysql-test/main/innodb_ext_key.result +++ b/mysql-test/main/innodb_ext_key.result @@ -799,6 +799,21 @@ EXPLAIN } drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; +# +# MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +# +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +CREATE TABLE t2 ( +pk VARCHAR(50), +a VARCHAR(20), +KEY k1(a), +PRIMARY KEY(pk) +)ENGINE=INNODB; +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 index NULL k1 23 NULL 10 Using index +DROP TABLE t1,t2; set global innodb_stats_persistent= @innodb_stats_persistent_save; -set global innodb_stats_persistent_sample_pages= -@innodb_stats_persistent_sample_pages_save; +set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/innodb_ext_key.test b/mysql-test/main/innodb_ext_key.test index 5fcf6a309b9..70d0009af27 100644 --- a/mysql-test/main/innodb_ext_key.test +++ b/mysql-test/main/innodb_ext_key.test @@ -624,6 +624,25 @@ select * from t1 force index(k1) where f2 <= 5 and pk2 <=5 and pk1 = 'abc' and drop table t1; SET optimizer_use_condition_selectivity=@save_optimizer_use_condition_selectivity; + +--echo # +--echo # MDEV-11172: EXPLAIN shows non-sensical value for key_len with type=index +--echo # + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +CREATE TABLE t2 ( + pk VARCHAR(50), + a VARCHAR(20), + KEY k1(a), + PRIMARY KEY(pk) +)ENGINE=INNODB; + +INSERT INTO t2 SELECT a,a FROM t1; +EXPLAIN SELECT pk FROM t2 FORCE INDEX(k1); + +DROP TABLE t1,t2; + set global innodb_stats_persistent= @innodb_stats_persistent_save; -set global innodb_stats_persistent_sample_pages= - @innodb_stats_persistent_sample_pages_save; +set global innodb_stats_persistent_sample_pages= @innodb_stats_persistent_sample_pages_save; diff --git a/mysql-test/main/lock_user.result b/mysql-test/main/lock_user.result index 3ee8bbf2296..24fff2105ab 100644 --- a/mysql-test/main/lock_user.result +++ b/mysql-test/main/lock_user.result @@ -156,6 +156,7 @@ alter user user1@localhost PASSWORD EXPIRE NEVER ACCOUNT UNLOCK ; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER alter user user1@localhost ACCOUNT LOCK PASSWORD EXPIRE DEFAULT; show create user user1@localhost; CREATE USER for user1@localhost @@ -167,5 +168,6 @@ localhost user1 {"access":0,"version_id":XXX,"plugin":"mysql_native_password","a show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 60 DAY drop user user1@localhost; drop user user2@localhost; diff --git a/mysql-test/main/password_expiration.result b/mysql-test/main/password_expiration.result index d05f6b3b5d0..897811bb4ad 100644 --- a/mysql-test/main/password_expiration.result +++ b/mysql-test/main/password_expiration.result @@ -125,6 +125,7 @@ alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY set password for user1@localhost= password(''); show create user user1@localhost; CREATE USER for user1@localhost @@ -151,10 +152,12 @@ alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER flush privileges; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER set password for user1@localhost= password(''); alter user user1@localhost password expire default; show create user user1@localhost; @@ -184,10 +187,12 @@ alter user user1@localhost password expire; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER flush privileges; show create user user1@localhost; CREATE USER for user1@localhost CREATE USER `user1`@`localhost` PASSWORD EXPIRE +ALTER USER `user1`@`localhost` PASSWORD EXPIRE NEVER set global disconnect_on_expired_password=ON; connect(localhost,user1,,test,MYSQL_PORT,MYSQL_SOCK); connect con1,localhost,user1; diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index d97720c2d41..b7e126e5061 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5485,6 +5485,18 @@ ERROR HY000: Default/ignore value is not supported for such parameter usage EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0; Database # +# MDEV-24779: main.subselect fails in buildbot with --ps-protocol +# +CREATE TABLE t1(a INT); +PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))"; +EXECUTE stmt; +EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) +0 +EXECUTE stmt; +EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1)) +0 +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index e702cb76bbb..6a109951d0e 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -4932,6 +4932,16 @@ EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING DEFAULT; EXECUTE IMMEDIATE 'SHOW DATABASES WHERE ?' USING 0; --echo # +--echo # MDEV-24779: main.subselect fails in buildbot with --ps-protocol +--echo # + +CREATE TABLE t1(a INT); +PREPARE stmt FROM "SELECT EXISTS(SELECT 1 FROM t1 GROUP BY a IN (select a from t1))"; +EXECUTE stmt; +EXECUTE stmt; +DROP TABLE t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/selectivity.result b/mysql-test/main/selectivity.result index 591294aff10..40ab309fffd 100644 --- a/mysql-test/main/selectivity.result +++ b/mysql-test/main/selectivity.result @@ -1882,4 +1882,62 @@ a b set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; # End of 10.1 tests +# +# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect +# +SET optimizer_use_condition_selectivity=4; +SET histogram_size=255; +CREATE TABLE t1 (a BIT(32), b INT); +INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.41 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81 +SELECT HEX(a), b from t1 where t1.a >= 81; +HEX(a) b +51 81 +52 82 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +DROP TABLE t1; +# +# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +# +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; +set use_stat_tables='preferably'; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +SET optimizer_use_condition_selectivity=3; +# filtered should show 25 % +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +DROP TABLE t1; +# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; diff --git a/mysql-test/main/selectivity.test b/mysql-test/main/selectivity.test index 2a0cc823eb4..1d96dc0bf80 100644 --- a/mysql-test/main/selectivity.test +++ b/mysql-test/main/selectivity.test @@ -1281,6 +1281,51 @@ drop table t1; --echo # End of 10.1 tests +--echo # +--echo # MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect +--echo # + +SET optimizer_use_condition_selectivity=4; +SET histogram_size=255; +CREATE TABLE t1 (a BIT(32), b INT); +INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81; +SELECT HEX(a), b from t1 where t1.a >= 81; + +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +DROP TABLE t1; + +--echo # +--echo # MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +--echo # + +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; + +set use_stat_tables='preferably'; + +ANALYZE TABLE t1 PERSISTENT FOR ALL; + +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +SET optimizer_use_condition_selectivity=3; + +--echo # filtered should show 25 % +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +FLUSH TABLES; + +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; + +DROP TABLE t1; + +--echo # End of 10.2 tests + # # Clean up # diff --git a/mysql-test/main/selectivity_innodb.result b/mysql-test/main/selectivity_innodb.result index 7a8f837d4e2..46723410b75 100644 --- a/mysql-test/main/selectivity_innodb.result +++ b/mysql-test/main/selectivity_innodb.result @@ -1892,6 +1892,64 @@ a b set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; drop table t1; # End of 10.1 tests +# +# MDEV-22583: Selectivity for BIT columns in filtered column for EXPLAIN is incorrect +# +SET optimizer_use_condition_selectivity=4; +SET histogram_size=255; +CREATE TABLE t1 (a BIT(32), b INT); +INSERT INTO t1 VALUES (80, 80), (81, 81), (82, 82); +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * from t1 where t1.a >= 81; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 66.41 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` >= 81 +SELECT HEX(a), b from t1 where t1.a >= 81; +HEX(a) b +51 81 +52 82 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +DROP TABLE t1; +# +# MDEV-19474: Histogram statistics are used even with optimizer_use_condition_selectivity=3 +# +CREATE TABLE t1(a int); +INSERT INTO t1 values (1),(2),(2),(3),(4); +SET optimizer_use_condition_selectivity=4; +SET histogram_size= 255; +set use_stat_tables='preferably'; +ANALYZE TABLE t1 PERSISTENT FOR ALL; +Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected +test.t1 analyze status OK +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 39.84 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +SET optimizer_use_condition_selectivity=3; +# filtered should show 25 % +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +FLUSH TABLES; +EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=2; +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 5 25.00 Using where +Warnings: +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where `test`.`t1`.`a` = 2 +set optimizer_use_condition_selectivity= @save_optimizer_use_condition_selectivity; +set histogram_size=@save_histogram_size; +set use_stat_tables= @save_use_stat_tables; +DROP TABLE t1; +# End of 10.2 tests set @@global.histogram_size=@save_histogram_size; set optimizer_switch=@save_optimizer_switch_for_selectivity_test; set @tmp_ust= @@use_stat_tables; diff --git a/mysql-test/main/subselect4.result b/mysql-test/main/subselect4.result index 44b879ae33b..90550058dd9 100644 --- a/mysql-test/main/subselect4.result +++ b/mysql-test/main/subselect4.result @@ -2687,6 +2687,15 @@ f bar DROP TABLE t1, t2; # +# MDEV-23449: alias do not exist and a query do not report an error +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); +SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); +ERROR 42S22: Unknown column 's.id' in 'group statement' +DROP TABLE t1; +# End of 10.2 tests +# # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init # CREATE TABLE t1 (i1 int,v1 varchar(1),KEY (v1,i1)); @@ -2717,7 +2726,6 @@ Warnings: Warning 1931 Query execution was interrupted. The query examined at least 3020 rows, which exceeds LIMIT ROWS EXAMINED (500). The query result may be incomplete SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4; -# End of 10.2 tests # # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison # diff --git a/mysql-test/main/subselect4.test b/mysql-test/main/subselect4.test index d46bb846a9e..423353fe677 100644 --- a/mysql-test/main/subselect4.test +++ b/mysql-test/main/subselect4.test @@ -2202,6 +2202,19 @@ SELECT * FROM t2; DROP TABLE t1, t2; --echo # +--echo # MDEV-23449: alias do not exist and a query do not report an error +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2), (3,3), (4,4), (5,5); + +--error ER_BAD_FIELD_ERROR +SELECT a, b FROM t1 WHERE a IN (SELECT A.a FROM t1 A GROUP BY s.id); +DROP TABLE t1; + +--echo # End of 10.2 tests + +--echo # --echo # MDEV-18335: Assertion `!error || error == 137' failed in subselect_rowid_merge_engine::init --echo # @@ -2236,9 +2249,6 @@ from t2 join t1 on SET join_cache_level= @save_join_cache_level; DROP TABLE t1,t2,t3,t4; - ---echo # End of 10.2 tests - --echo # --echo # MDEV-21265: IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison --echo # diff --git a/mysql-test/main/system_mysql_db_507.result b/mysql-test/main/system_mysql_db_507.result index 2d68dc82529..8069405aa3a 100644 --- a/mysql-test/main/system_mysql_db_507.result +++ b/mysql-test/main/system_mysql_db_507.result @@ -214,6 +214,7 @@ alter user user@localhost password expire; show create user user@localhost; CREATE USER for user@localhost CREATE USER `user`@`localhost` PASSWORD EXPIRE +ALTER USER `user`@`localhost` PASSWORD EXPIRE INTERVAL 123 DAY set password for user@localhost= password(''); show create user user@localhost; CREATE USER for user@localhost diff --git a/mysql-test/main/table_value_constr.result b/mysql-test/main/table_value_constr.result index c2269f0862b..7ab9666c373 100644 --- a/mysql-test/main/table_value_constr.result +++ b/mysql-test/main/table_value_constr.result @@ -2675,6 +2675,107 @@ values ((select min(a), max(b) from t1)); ERROR 21000: Operand should contain 1 column(s) drop table t1; # +# MDEV-24840: union of TVCs in IN subquery +# +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); +select a from t1 where a in (values (7) union values (8)); +a +7 +explain extended select a from t1 where a in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 DEPENDENT SUBQUERY <derived2> ref key0 key0 4 func 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 DEPENDENT UNION <derived3> ref key0 key0 4 func 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`7` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where <cache>(`test`.`t1`.`a`) = `tvc_0`.`8`))) +prepare stmt from "select a from t1 where a in (values (7) union values (8))"; +execute stmt; +a +7 +execute stmt; +a +7 +deallocate prepare stmt; +select a from t1 where a not in (values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a not in (values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 DEPENDENT SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 DEPENDENT UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 Using where +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where !<expr_cache><`test`.`t1`.`a`>(<in_optimizer>(`test`.`t1`.`a`,<exists>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`7`) union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0` where trigcond(<cache>(`test`.`t1`.`a`) = `tvc_0`.`8`)))) +select a from t1 where a < all(values (7) union values (8)); +a +3 +1 +explain extended select a from t1 where a < all(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <not>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +select a from t1 where a >= any(values (7) union values (8)); +a +7 +explain extended select a from t1 where a >= any(values (7) union values (8)); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 100.00 Using where +4 SUBQUERY <derived2> ALL NULL NULL NULL NULL 2 100.00 +2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +5 UNION <derived3> ALL NULL NULL NULL NULL 2 100.00 +3 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used +NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL NULL +Warnings: +Note 1003 /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` where <nop>(<in_optimizer>(`test`.`t1`.`a`,<min>(/* select#4 */ select `tvc_0`.`7` from (values (7)) `tvc_0` union /* select#5 */ select `tvc_0`.`8` from (values (8)) `tvc_0`) <= <cache>(`test`.`t1`.`a`))) +drop table t1; +# +# MDEV-24934:EXPLAIN for queries based on TVC using subqueries +# +create table t1 (a int); +insert into t1 values (3), (7), (1); +values (8), ((select * from t1 where a between 2 and 4)); +8 +8 +3 +explain values (8), ((select * from t1 where a between 2 and 4)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +(select * from t1 where a between 2 and 4) +3 +NULL +explain values ((select * from t1 where a between 2 and 4)), +((select * from t1 where a > 10)); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +3 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +values (10,11), ((select * from t1 where a = 7) + 1, 21); +10 11 +10 11 +8 21 +explain values (10,11), ((select * from t1 where a = 7) + 1, 21); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY NULL NULL NULL NULL NULL NULL NULL No tables used +2 SUBQUERY t1 ALL NULL NULL NULL NULL 3 Using where +drop table t1; +# # End of 10.3 tests # # diff --git a/mysql-test/main/table_value_constr.test b/mysql-test/main/table_value_constr.test index fc0288d3fa0..66463cb1f1a 100644 --- a/mysql-test/main/table_value_constr.test +++ b/mysql-test/main/table_value_constr.test @@ -1402,6 +1402,64 @@ values ((select min(a), max(b) from t1)); drop table t1; --echo # +--echo # MDEV-24840: union of TVCs in IN subquery +--echo # + +create table t1 (a int) engine=myisam; +insert into t1 values (3), (7), (1); + +let $q= +select a from t1 where a in (values (7) union values (8)); +eval $q; +eval explain extended $q; +eval prepare stmt from "$q"; +execute stmt; +execute stmt; +deallocate prepare stmt; + +let $q= +select a from t1 where a not in (values (7) union values (8)); +eval $q; +eval explain extended $q; + +let $q= +select a from t1 where a < all(values (7) union values (8)); +eval $q; +eval explain extended $q; + +let $q= +select a from t1 where a >= any(values (7) union values (8)); +eval $q; +eval explain extended $q; + +drop table t1; + +--echo # +--echo # MDEV-24934:EXPLAIN for queries based on TVC using subqueries +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1); + +let $q1= +values (8), ((select * from t1 where a between 2 and 4)); +eval $q1; +eval explain $q1; + +let $q2= +values ((select * from t1 where a between 2 and 4)), + ((select * from t1 where a > 10)); +eval $q2; +eval explain $q2; + +let $q3= +values (10,11), ((select * from t1 where a = 7) + 1, 21); +eval $q3; +eval explain $q3; + +drop table t1; + +--echo # --echo # End of 10.3 tests --echo # diff --git a/mysql-test/main/type_decimal.result b/mysql-test/main/type_decimal.result index 6e7d7c8b6fe..0228652f74c 100644 --- a/mysql-test/main/type_decimal.result +++ b/mysql-test/main/type_decimal.result @@ -176,9 +176,8 @@ Note 1265 Data truncated for column 'a' at row 2 insert ignore into t1 values ("1e+18446744073709551615"),("1e+18446744073709551616"),("1e-9223372036854775807"),("1e-9223372036854775809"); Warnings: Warning 1264 Out of range value for column 'a' at row 1 -Warning 1366 Incorrect decimal value: '1e+18446744073709551616' for column `test`.`t1`.`a` at row 2 +Warning 1264 Out of range value for column 'a' at row 2 Note 1265 Data truncated for column 'a' at row 3 -Warning 1366 Incorrect decimal value: '1e-9223372036854775809' for column `test`.`t1`.`a` at row 4 insert ignore into t1 values ("123.4e"),("123.4e+2"),("123.4e-2"),("123e1"),("123e+0"); Warnings: Warning 1265 Data truncated for column 'a' at row 1 @@ -209,7 +208,7 @@ a 99999999.99 0.00 99999999.99 -0.00 +99999999.99 0.00 0.00 123.40 @@ -1078,6 +1077,90 @@ t1 CREATE TABLE `t1` ( DROP TABLE t1; DROP TABLE t1dec102; # +# MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result +# +SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a; +a +0 +SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a; +a +0 +SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a; +a +99999999999999999999999999999999999999 +Warnings: +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '.00000000000000000000000000000000000001e111111111111111111111' +Warning 1264 Out of range value for column 'a' at row 1 +CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128)); +INSERT INTO t1 VALUES +('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'), +('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'), +('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'), +('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent'); +BEGIN NOT ATOMIC +DECLARE done INT DEFAULT FALSE; +DECLARE vstr, vcomment VARCHAR(128); +DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str; +DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; +OPEN cur1; +read_loop: +LOOP +FETCH cur1 INTO vstr, vcomment; +IF done THEN +LEAVE read_loop; +END IF; +SELECT vstr AS `--------`, vcomment AS `--------`; +SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; +SHOW WARNINGS; +SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; +SHOW WARNINGS; +END LOOP; +END; +$$ +-------- -------- +0e-111111111111111111111 Zero mantissa and a huge negative exponent +CAST(str AS DECIMAL(38,0)) +0 +Level Code Message +CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) +0 +Level Code Message +Warning 1292 Truncated incorrect DECIMAL value: '0e-111111111111111111111garbage' +-------- -------- +0e111111111111111111111 Zero mantissa and a huge positive exponent +CAST(str AS DECIMAL(38,0)) +0 +Level Code Message +CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) +0 +Level Code Message +Warning 1292 Truncated incorrect DECIMAL value: '0e111111111111111111111garbage' +-------- -------- +1e-111111111111111111111 Non-zero mantissa and a huge negative exponent +CAST(str AS DECIMAL(38,0)) +0 +Level Code Message +CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) +0 +Level Code Message +Warning 1292 Truncated incorrect DECIMAL value: '1e-111111111111111111111garbage' +-------- -------- +1e111111111111111111111 Non-zero mantissa, huge positive exponent +CAST(str AS DECIMAL(38,0)) +99999999999999999999999999999999999999 +Level Code Message +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e111111111111111111111' +Warning 1264 Out of range value for column 'CAST(str AS DECIMAL(38,0))' at row 1 +CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) +99999999999999999999999999999999999999 +Level Code Message +Warning 1916 Got overflow when converting '' to DECIMAL. Value truncated +Warning 1292 Truncated incorrect DECIMAL value: '1e111111111111111111111garbage' +Warning 1264 Out of range value for column 'CAST(CONCAT(str,'garbage') AS DECIMAL(38,0))' at row 1 +DROP TABLE t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/type_decimal.test b/mysql-test/main/type_decimal.test index 4ffbcbd3288..1076592fe62 100644 --- a/mysql-test/main/type_decimal.test +++ b/mysql-test/main/type_decimal.test @@ -670,6 +670,50 @@ DROP TABLE t1; DROP TABLE t1dec102; --echo # +--echo # MDEV-24790 CAST('0e1111111111' AS DECIMAL(38,0)) returns a wrong result +--echo # + +SELECT CAST('0e111111111' AS DECIMAL(38,0)) AS a; +SELECT CAST('0e1111111111' AS DECIMAL(38,0)) AS a; +SELECT CAST('.00000000000000000000000000000000000001e111111111111111111111' AS DECIMAL(38,0)) AS a; + +CREATE TABLE t1 (str VARCHAR(128), comment VARCHAR(128)); +INSERT INTO t1 VALUES +('0e111111111111111111111', 'Zero mantissa and a huge positive exponent'), +('1e111111111111111111111', 'Non-zero mantissa, huge positive exponent'), +('0e-111111111111111111111', 'Zero mantissa and a huge negative exponent'), +('1e-111111111111111111111', 'Non-zero mantissa and a huge negative exponent'); + +# The loop below issues SHOW WARNINGS manually, disable automatic warnings +--disable_warnings +DELIMITER $$; +BEGIN NOT ATOMIC + DECLARE done INT DEFAULT FALSE; + DECLARE vstr, vcomment VARCHAR(128); + DECLARE cur1 CURSOR FOR SELECT str, comment FROM t1 ORDER BY str; + DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; + OPEN cur1; +read_loop: + LOOP + FETCH cur1 INTO vstr, vcomment; + IF done THEN + LEAVE read_loop; + END IF; + SELECT vstr AS `--------`, vcomment AS `--------`; + SELECT CAST(str AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; + SHOW WARNINGS; + SELECT CAST(CONCAT(str,'garbage') AS DECIMAL(38,0)) FROM t1 WHERE str=vstr; + SHOW WARNINGS; + END LOOP; +END; +$$ +DELIMITER ;$$ +--enable_warnings + + +DROP TABLE t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index e9be123baa6..4585f370c1d 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -6777,6 +6777,21 @@ DROP PROCEDURE sp1; DROP VIEW v1; DROP TABLE t1, t2; # +# MDEV-23291: SUM column from a derived table returns invalid values +# +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +sum(z) +3 +SELECT sum(z) FROM v1; +sum(z) +3 +DROP TABLE t1; +DROP VIEW v1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index b1074264bef..78ab6a98ebb 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -6485,6 +6485,22 @@ DROP VIEW v1; DROP TABLE t1, t2; --echo # +--echo # MDEV-23291: SUM column from a derived table returns invalid values +--echo # + +CREATE TABLE t1(a INT, b INT); +INSERT INTO t1 VALUES (1,1), (2,2); + +CREATE view v1 AS +SELECT a as x, (select x) as y, (select y) as z FROM t1; + +SELECT sum(z) FROM (SELECT a as x, (select x) as y, (select y) as z FROM t1) q; +SELECT sum(z) FROM v1; + +DROP TABLE t1; +DROP VIEW v1; + +--echo # --echo # End of 10.2 tests --echo # |