diff options
author | Marko Mäkelä <marko.makela@mariadb.com> | 2021-04-21 07:58:42 +0300 |
---|---|---|
committer | Marko Mäkelä <marko.makela@mariadb.com> | 2021-04-21 07:58:42 +0300 |
commit | a0588d54a20488b17a178244989e1abc5151a88a (patch) | |
tree | 7de7ef6bd7153479ee397006dd71eab7599b43f8 /mysql-test | |
parent | 031f11717d9f351dfb12cd27c225c533e289261a (diff) | |
parent | 75c01f39b1b4a6d27d36d075f8baab9bdda7cc7e (diff) | |
download | mariadb-git-a0588d54a20488b17a178244989e1abc5151a88a.tar.gz |
Merge 10.3 into 10.4
Diffstat (limited to 'mysql-test')
-rw-r--r-- | mysql-test/main/alter_table.result | 17 | ||||
-rw-r--r-- | mysql-test/main/alter_table.test | 12 | ||||
-rw-r--r-- | mysql-test/main/join_outer.result | 103 | ||||
-rw-r--r-- | mysql-test/main/join_outer.test | 122 | ||||
-rw-r--r-- | mysql-test/main/join_outer_jcl6.result | 103 | ||||
-rw-r--r-- | mysql-test/main/subselect_exists2in.result | 18 | ||||
-rw-r--r-- | mysql-test/main/subselect_exists2in.test | 23 | ||||
-rw-r--r-- | mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result | 1 | ||||
-rw-r--r-- | mysql-test/suite/binlog/t/binlog_statement_insert_delayed.test | 1 | ||||
-rw-r--r-- | mysql-test/suite/innodb/r/temporary_table.result | 96 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/temporary_table.test | 107 | ||||
-rw-r--r-- | mysql-test/suite/versioning/r/alter.result | 17 | ||||
-rw-r--r-- | mysql-test/suite/versioning/t/alter.test | 22 |
13 files changed, 545 insertions, 97 deletions
diff --git a/mysql-test/main/alter_table.result b/mysql-test/main/alter_table.result index 908435710a3..3c59b83f2ce 100644 --- a/mysql-test/main/alter_table.result +++ b/mysql-test/main/alter_table.result @@ -2529,6 +2529,23 @@ ALTER TABLE t1 ALTER COLUMN k1 SET DEFAULT (SELECT 1 FROM t2 limit 1); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `k1` DROP TABLE t1,t2; # +# MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used +# +create table t1(t int, d date not null); +insert into t1 values (1,'2001-1-1'); +set sql_mode = "no_zero_date"; +alter table t1 change d d date not null after t, add i int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` int(11) DEFAULT NULL, + `d` date NOT NULL, + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add x date not null; +ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`x` at row 1 +drop table t1; +# # End of 10.2 tests # # diff --git a/mysql-test/main/alter_table.test b/mysql-test/main/alter_table.test index ce461b2fe46..fa931fcadbf 100644 --- a/mysql-test/main/alter_table.test +++ b/mysql-test/main/alter_table.test @@ -2051,6 +2051,18 @@ ALTER TABLE t1 ALTER COLUMN k1 SET DEFAULT (SELECT 1 FROM t2 limit 1); DROP TABLE t1,t2; --echo # +--echo # MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used +--echo # +create table t1(t int, d date not null); +insert into t1 values (1,'2001-1-1'); +set sql_mode = "no_zero_date"; +alter table t1 change d d date not null after t, add i int; +show create table t1; +--error ER_TRUNCATED_WRONG_VALUE +alter table t1 add x date not null; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/mysql-test/main/join_outer.result b/mysql-test/main/join_outer.result index c92b8e6115b..b7fcb55e4fe 100644 --- a/mysql-test/main/join_outer.result +++ b/mysql-test/main/join_outer.result @@ -2687,6 +2687,77 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# +# MDEV-22866: Crash in join optimizer with constant outer join nest +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); +SELECT * +FROM +t1 +LEFT JOIN ( +t2 LEFT JOIN ( +t3 JOIN +t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 +) ON t2.b >= t4.d +) ON t1.a <= t2.b +LEFT JOIN t5 ON t2.b = t5.e +LEFT JOIN t6 ON t3.c = t6.f; +a b c d e f +1 3 NULL NULL NULL NULL +1 4 NULL NULL NULL NULL +2 3 NULL NULL NULL NULL +2 4 NULL NULL NULL NULL +drop table t1,t2,t3,t4,t5,t6; +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); @@ -2754,35 +2825,5 @@ WHERE t3.pk IN (2); 1 drop view v4; drop table t1,t2,t3,t4; +# end of 10.3 tests SET optimizer_switch=@org_optimizer_switch; -# -# MDEV-22866: Crash in join optimizer with constant outer join nest -# -CREATE TABLE t1 (a INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (b INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (3),(4); -CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; -CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; -INSERT INTO t4 VALUES (5),(6); -CREATE TABLE t5 (e INT) ENGINE=MyISAM; -INSERT INTO t5 VALUES (7),(8); -CREATE TABLE t6 (f INT) ENGINE=MyISAM; -INSERT INTO t6 VALUES (9),(10); -SELECT * -FROM -t1 -LEFT JOIN ( -t2 LEFT JOIN ( -t3 JOIN -t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 -) ON t2.b >= t4.d -) ON t1.a <= t2.b -LEFT JOIN t5 ON t2.b = t5.e -LEFT JOIN t6 ON t3.c = t6.f; -a b c d e f -1 3 NULL NULL NULL NULL -2 3 NULL NULL NULL NULL -1 4 NULL NULL NULL NULL -2 4 NULL NULL NULL NULL -drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/main/join_outer.test b/mysql-test/main/join_outer.test index f835d8af5a8..82c7b265b56 100644 --- a/mysql-test/main/join_outer.test +++ b/mysql-test/main/join_outer.test @@ -2192,6 +2192,91 @@ DROP TABLE t1,t2,t3,t4; --echo # end of 10.1 tests --echo # +--echo # MDEV-25362: name resolution for subqueries in ON expressions +--echo # + +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); + +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=t1.a) + ) on (t2.c=t1.a ); + +# This must produce an error: +--error ER_BAD_FIELD_ERROR +explain +select * +from + t1 left join + (t2 + join + t3 on + (t3.f=(select max(g) from t4 where t4.h=t1.a)) + ) on (t2.c=t1.a ); + +drop table t1,t2,t3,t4; + +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); + +--error ER_BAD_FIELD_ERROR +select * from ( select * from t1 left join t2 + on b in (select x from t3 as sq1) + ) as sq2; + +drop table t1,t2,t3; + +--echo # end of 10.2 tests + +--echo # +--echo # MDEV-22866: Crash in join optimizer with constant outer join nest +--echo # + +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); + +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; + +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); + +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); + +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); + +SELECT * +FROM + t1 + LEFT JOIN ( + t2 LEFT JOIN ( + t3 JOIN + t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 + ) ON t2.b >= t4.d + ) ON t1.a <= t2.b + LEFT JOIN t5 ON t2.b = t5.e + LEFT JOIN t6 ON t3.c = t6.f; + +drop table t1,t2,t3,t4,t5,t6; + +--echo # --echo # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins --echo # create table t1(a int); @@ -2251,39 +2336,6 @@ WHERE t3.pk IN (2); drop view v4; drop table t1,t2,t3,t4; -SET optimizer_switch=@org_optimizer_switch; - ---echo # ---echo # MDEV-22866: Crash in join optimizer with constant outer join nest ---echo # - -CREATE TABLE t1 (a INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); +--echo # end of 10.3 tests -CREATE TABLE t2 (b INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (3),(4); - -CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; - -CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; -INSERT INTO t4 VALUES (5),(6); - -CREATE TABLE t5 (e INT) ENGINE=MyISAM; -INSERT INTO t5 VALUES (7),(8); - -CREATE TABLE t6 (f INT) ENGINE=MyISAM; -INSERT INTO t6 VALUES (9),(10); - -SELECT * -FROM - t1 - LEFT JOIN ( - t2 LEFT JOIN ( - t3 JOIN - t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 - ) ON t2.b >= t4.d - ) ON t1.a <= t2.b - LEFT JOIN t5 ON t2.b = t5.e - LEFT JOIN t6 ON t3.c = t6.f; - -drop table t1,t2,t3,t4,t5,t6; +SET optimizer_switch=@org_optimizer_switch; diff --git a/mysql-test/main/join_outer_jcl6.result b/mysql-test/main/join_outer_jcl6.result index 2c7dc7b85bb..3cb846426fe 100644 --- a/mysql-test/main/join_outer_jcl6.result +++ b/mysql-test/main/join_outer_jcl6.result @@ -2694,6 +2694,77 @@ id timestamp modifiedBy id REV REVTYPE profile_id id REV person_id id REV DROP TABLE t1,t2,t3,t4; # end of 10.1 tests # +# MDEV-25362: name resolution for subqueries in ON expressions +# +create table t1 (a int, b int); +create table t2 (c int, d int); +create table t3 (e int, f int); +create table t4 (g int, h int); +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=t1.a) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'on clause' +explain +select * +from +t1 left join +(t2 +join +t3 on +(t3.f=(select max(g) from t4 where t4.h=t1.a)) +) on (t2.c=t1.a ); +ERROR 42S22: Unknown column 't1.a' in 'where clause' +drop table t1,t2,t3,t4; +create table t1 (a int); +insert into t1 values (1),(2); +create table t2 (b int); +insert into t2 values (1),(2); +create table t3 (c int); +insert into t3 values (1),(2); +select * from ( select * from t1 left join t2 +on b in (select x from t3 as sq1) +) as sq2; +ERROR 42S22: Unknown column 'x' in 'field list' +drop table t1,t2,t3; +# end of 10.2 tests +# +# MDEV-22866: Crash in join optimizer with constant outer join nest +# +CREATE TABLE t1 (a INT) ENGINE=MyISAM; +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3),(4); +CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; +CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; +INSERT INTO t4 VALUES (5),(6); +CREATE TABLE t5 (e INT) ENGINE=MyISAM; +INSERT INTO t5 VALUES (7),(8); +CREATE TABLE t6 (f INT) ENGINE=MyISAM; +INSERT INTO t6 VALUES (9),(10); +SELECT * +FROM +t1 +LEFT JOIN ( +t2 LEFT JOIN ( +t3 JOIN +t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 +) ON t2.b >= t4.d +) ON t1.a <= t2.b +LEFT JOIN t5 ON t2.b = t5.e +LEFT JOIN t6 ON t3.c = t6.f; +a b c d e f +1 3 NULL NULL NULL NULL +2 3 NULL NULL NULL NULL +1 4 NULL NULL NULL NULL +2 4 NULL NULL NULL NULL +drop table t1,t2,t3,t4,t5,t6; +# # MDEV-17518: Range optimization doesn't use ON expressions from nested outer joins # create table t1(a int); @@ -2761,35 +2832,5 @@ WHERE t3.pk IN (2); 1 drop view v4; drop table t1,t2,t3,t4; +# end of 10.3 tests SET optimizer_switch=@org_optimizer_switch; -# -# MDEV-22866: Crash in join optimizer with constant outer join nest -# -CREATE TABLE t1 (a INT) ENGINE=MyISAM; -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (b INT) ENGINE=MyISAM; -INSERT INTO t2 VALUES (3),(4); -CREATE TABLE t3 (c INT, KEY(c)) ENGINE=MyISAM; -CREATE TABLE t4 (d INT, KEY(d)) ENGINE=MyISAM; -INSERT INTO t4 VALUES (5),(6); -CREATE TABLE t5 (e INT) ENGINE=MyISAM; -INSERT INTO t5 VALUES (7),(8); -CREATE TABLE t6 (f INT) ENGINE=MyISAM; -INSERT INTO t6 VALUES (9),(10); -SELECT * -FROM -t1 -LEFT JOIN ( -t2 LEFT JOIN ( -t3 JOIN -t4 ON t3.c = t4.d and t3.c >2 and t3.c<0 -) ON t2.b >= t4.d -) ON t1.a <= t2.b -LEFT JOIN t5 ON t2.b = t5.e -LEFT JOIN t6 ON t3.c = t6.f; -a b c d e f -1 3 NULL NULL NULL NULL -2 3 NULL NULL NULL NULL -1 4 NULL NULL NULL NULL -2 4 NULL NULL NULL NULL -drop table t1,t2,t3,t4,t5,t6; diff --git a/mysql-test/main/subselect_exists2in.result b/mysql-test/main/subselect_exists2in.result index e8ef7081b09..6ff518b5a29 100644 --- a/mysql-test/main/subselect_exists2in.result +++ b/mysql-test/main/subselect_exists2in.result @@ -1102,4 +1102,22 @@ U5.`storage_target_id` = V0.`id` ); id drop table t1,t2,t3; +# +# MDEV-25407: EXISTS subquery with correlation in ON expression crashes +# +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; +create table t1 (a int, b int); +insert into t1 select a,a from t10; +create table t2 (a int, b int); +insert into t2 select a,a from t11; +create table t3 as select * from t2; +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 10 +1 PRIMARY t2 ALL NULL NULL NULL NULL 1000 Using where; Start temporary; Using join buffer (flat, BNL join) +1 PRIMARY t3 ALL NULL NULL NULL NULL 1000 Using where; End temporary; Using join buffer (incremental, BNL join) +drop table t1, t2, t3, t10, t11; set optimizer_switch=default; diff --git a/mysql-test/main/subselect_exists2in.test b/mysql-test/main/subselect_exists2in.test index e27ce57038b..e70d643138b 100644 --- a/mysql-test/main/subselect_exists2in.test +++ b/mysql-test/main/subselect_exists2in.test @@ -941,5 +941,28 @@ WHERE ( drop table t1,t2,t3; +--echo # +--echo # MDEV-25407: EXISTS subquery with correlation in ON expression crashes +--echo # +create table t10(a int primary key); +insert into t10 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +create table t11(a int primary key); +insert into t11 select a.a + b.a* 10 + c.a * 100 from t10 a, t10 b, t10 c; + +create table t1 (a int, b int); +insert into t1 select a,a from t10; + +create table t2 (a int, b int); +insert into t2 select a,a from t11; + +create table t3 as select * from t2; + + +explain select * from t1 where exists (select t2.a from t2 left join t3 on (t3.b=t1.b) where t2.a=t1.a); + +drop table t1, t2, t3, t10, t11; + + #restore defaults set optimizer_switch=default; diff --git a/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result b/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result index d6875ab60e0..a4cd5b4080d 100644 --- a/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result +++ b/mysql-test/suite/binlog/r/binlog_statement_insert_delayed.result @@ -50,3 +50,4 @@ a 400 401 drop table t1; +reset master; diff --git a/mysql-test/suite/binlog/t/binlog_statement_insert_delayed.test b/mysql-test/suite/binlog/t/binlog_statement_insert_delayed.test index b2af560fa50..9145afc047f 100644 --- a/mysql-test/suite/binlog/t/binlog_statement_insert_delayed.test +++ b/mysql-test/suite/binlog/t/binlog_statement_insert_delayed.test @@ -10,3 +10,4 @@ disable_query_log; call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT"); enable_query_log; -- source include/binlog_insert_delayed.test +reset master; diff --git a/mysql-test/suite/innodb/r/temporary_table.result b/mysql-test/suite/innodb/r/temporary_table.result index d7ea25fa117..0df99aecc46 100644 --- a/mysql-test/suite/innodb/r/temporary_table.result +++ b/mysql-test/suite/innodb/r/temporary_table.result @@ -678,3 +678,99 @@ SET FOREIGN_KEY_CHECKS = 0; CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB; ERROR HY000: Can't create table `test`.`t1` (errno: 150 "Foreign key constraint is incorrectly formed") +CREATE TABLE t (c INT) ENGINE=InnoDB; +INSERT INTO t VALUES(0); +CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t2 SELECT * FROM t; +COMMIT; +DROP TABLE t, t2; +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t VALUES(0); +ERROR 21S01: Column count doesn't match value count at row 1 +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +COMMIT; +DROP TABLE t; +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t VALUES(0); +ERROR 21S01: Column count doesn't match value count at row 1 +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +ROLLBACK; +DROP TABLE t; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; +CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB; +SET SESSION tx_read_only=TRUE; +LOCK TABLE test.t READ; +SELECT * FROM t; +c +INSERT INTO t VALUES(0xADC3); +SET SESSION tx_read_only=FALSE; +DROP TABLE t; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2; +COMMIT; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int, c varchar(255)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1, repeat('a', 200)); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2, c=repeat('a', 250); +COMMIT; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +ROLLBACK; +DROP TABLE t1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +DELETE FROM t1 WHERE a= 2; +COMMIT; +DROP TABLE t1; +CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB; +INSERT INTO tmp () VALUES (),(); +SET TX_READ_ONLY= 1; +INSERT INTO tmp SELECT * FROM tmp; +SET TX_READ_ONLY= 0; +DROP TABLE tmp; +SET sql_mode=''; +SET GLOBAL tx_read_only=TRUE; +CREATE TEMPORARY TABLE t (c INT); +SET SESSION tx_read_only=DEFAULT; +INSERT INTO t VALUES(1); +INSERT INTO t SELECT * FROM t; +SET SESSION tx_read_only=FALSE; +SET GLOBAL tx_read_only=OFF; +DROP TABLE t; +CREATE TEMPORARY TABLE t(a INT); +SET SESSION tx_read_only=ON; +LOCK TABLE t READ; +SELECT COUNT(*)FROM t; +COUNT(*) +0 +INSERT INTO t VALUES (0); +SET SESSION tx_read_only=OFF; +DROP TABLE t; +CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB; +INSERT INTO t VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t SET a = NULL; +ROLLBACK; diff --git a/mysql-test/suite/innodb/t/temporary_table.test b/mysql-test/suite/innodb/t/temporary_table.test index 8e3ddf95634..13c203b587b 100644 --- a/mysql-test/suite/innodb/t/temporary_table.test +++ b/mysql-test/suite/innodb/t/temporary_table.test @@ -502,3 +502,110 @@ SET FOREIGN_KEY_CHECKS = 0; --error ER_CANT_CREATE_TABLE CREATE TEMPORARY TABLE t1(f1 INT NOT NULL, FOREIGN KEY(f1) REFERENCES t0(f1))ENGINE=InnoDB; + +CREATE TABLE t (c INT) ENGINE=InnoDB; +INSERT INTO t VALUES(0); +CREATE TEMPORARY TABLE t2 (c INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t2 SELECT * FROM t; +COMMIT; +DROP TABLE t, t2; + +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES(0); +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +COMMIT; +DROP TABLE t; + +CREATE TEMPORARY TABLE t (c INT,c2 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +--error ER_WRONG_VALUE_COUNT_ON_ROW +INSERT INTO t VALUES(0); +SAVEPOINT s; +INSERT INTO t VALUES(0,0); +ROLLBACK; +DROP TABLE t; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t(c INT) ENGINE=InnoDB; +SET SESSION tx_read_only=TRUE; +LOCK TABLE test.t READ; +SELECT * FROM t; +INSERT INTO t VALUES(0xADC3); +SET SESSION tx_read_only=FALSE; +DROP TABLE t; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY, b int, c varchar(255)) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1, 1, repeat('a', 200)); +START TRANSACTION READ ONLY; +UPDATE t1 SET b= 2, c=repeat('a', 250); +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t1 SET a= 2; +ROLLBACK; +DROP TABLE t1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES (1); +START TRANSACTION READ ONLY; +DELETE FROM t1 WHERE a= 2; +COMMIT; +DROP TABLE t1; + +CREATE TEMPORARY TABLE tmp (a INT) ENGINE=InnoDB; +INSERT INTO tmp () VALUES (),(); +SET TX_READ_ONLY= 1; +INSERT INTO tmp SELECT * FROM tmp; +SET TX_READ_ONLY= 0; +DROP TABLE tmp; + +SET sql_mode=''; +SET GLOBAL tx_read_only=TRUE; +CREATE TEMPORARY TABLE t (c INT); +SET SESSION tx_read_only=DEFAULT; +INSERT INTO t VALUES(1); +INSERT INTO t SELECT * FROM t; +SET SESSION tx_read_only=FALSE; +SET GLOBAL tx_read_only=OFF; +DROP TABLE t; + +CREATE TEMPORARY TABLE t(a INT); +SET SESSION tx_read_only=ON; +LOCK TABLE t READ; +SELECT COUNT(*)FROM t; +INSERT INTO t VALUES (0); +SET SESSION tx_read_only=OFF; +DROP TABLE t; + +CREATE TEMPORARY TABLE t (a INT) ENGINE=InnoDB; +INSERT INTO t VALUES (1); +START TRANSACTION READ ONLY; +UPDATE t SET a = NULL; +ROLLBACK; diff --git a/mysql-test/suite/versioning/r/alter.result b/mysql-test/suite/versioning/r/alter.result index e0cd63c4d56..b2dbbba7027 100644 --- a/mysql-test/suite/versioning/r/alter.result +++ b/mysql-test/suite/versioning/r/alter.result @@ -745,3 +745,20 @@ create or replace table t1 (x int); alter table t1 add column y timestamp(6) as row start; ERROR HY000: Table `t1` is not system-versioned drop table t1; +# +# MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table +# +create table t1 (pk int, a int, primary key (pk), key (a)) +with system versioning; +insert into t1 values (1, 1), (2, 2); +delete from t1; +set system_versioning_alter_history= keep; +alter table t1 drop pk; +drop table t1; +create table t1 (pk int, a int, primary key (pk), key (a)) +with system versioning; +insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0); +delete from t1; +set system_versioning_alter_history= keep; +alter ignore table t1 drop pk; +drop table t1; diff --git a/mysql-test/suite/versioning/t/alter.test b/mysql-test/suite/versioning/t/alter.test index efb7609bbbe..16f391b1454 100644 --- a/mysql-test/suite/versioning/t/alter.test +++ b/mysql-test/suite/versioning/t/alter.test @@ -631,3 +631,25 @@ create or replace table t1 (x int); alter table t1 add column y timestamp(6) as row start; # cleanup drop table t1; + + +--echo # +--echo # MDEV-25327 Unexpected ER_DUP_ENTRY upon dropping PK column from system-versioned table +--echo # +create table t1 (pk int, a int, primary key (pk), key (a)) +with system versioning; +insert into t1 values (1, 1), (2, 2); +delete from t1; +set system_versioning_alter_history= keep; +alter table t1 drop pk; +# cleanup +drop table t1; + +create table t1 (pk int, a int, primary key (pk), key (a)) +with system versioning; +insert into t1 values (1, 2), (2, 8), (3, 4), (4, 4), (5, 0); +delete from t1; +set system_versioning_alter_history= keep; +alter ignore table t1 drop pk; +# cleanup +drop table t1; |