diff options
Diffstat (limited to 'mysql-test/suite/vcol')
20 files changed, 877 insertions, 62 deletions
diff --git a/mysql-test/suite/vcol/inc/vcol_select.inc b/mysql-test/suite/vcol/inc/vcol_select.inc index 0641e14564a..cbd1f2cdd26 100644 --- a/mysql-test/suite/vcol/inc/vcol_select.inc +++ b/mysql-test/suite/vcol/inc/vcol_select.inc @@ -35,7 +35,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); --echo # select_type=SIMPLE, type=system diff --git a/mysql-test/suite/vcol/r/update.result b/mysql-test/suite/vcol/r/update.result index 3e8bbc43246..fe235228698 100644 --- a/mysql-test/suite/vcol/r/update.result +++ b/mysql-test/suite/vcol/r/update.result @@ -122,7 +122,7 @@ select * from t; a b c d e 10 5 5 5 5 replace delayed t (a,b,d) values (10,6,6); -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK @@ -130,7 +130,7 @@ select * from t; a b c d e 10 6 6 6 6 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7; -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK diff --git a/mysql-test/suite/vcol/r/update_binlog.result b/mysql-test/suite/vcol/r/update_binlog.result index 977037535b4..822381a1944 100644 --- a/mysql-test/suite/vcol/r/update_binlog.result +++ b/mysql-test/suite/vcol/r/update_binlog.result @@ -124,7 +124,7 @@ select * from t; a b c d e 10 5 5 5 5 replace delayed t (a,b,d) values (10,6,6); -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK @@ -132,7 +132,7 @@ select * from t; a b c d e 10 6 6 6 6 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7; -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK @@ -304,7 +304,7 @@ select * from t; a b c d e 10 5 5 5 5 replace delayed t (a,b,d) values (10,6,6); -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK @@ -312,7 +312,7 @@ select * from t; a b c d e 10 6 6 6 6 insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7; -flush tables; +flush tables t; check table t; Table Op Msg_type Msg_text test.t check status OK diff --git a/mysql-test/suite/vcol/r/vcol_keys_innodb.result b/mysql-test/suite/vcol/r/vcol_keys_innodb.result index c327b369ea3..9419c35bb38 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_keys_innodb.result @@ -257,6 +257,11 @@ insert into t1 (col_varchar,col_int,col_datetime,col_time,col_blob,col_bit,col_y ('foo',1,'2010-05-08 13:08:12.034783','18:32:14','foo',b'0111110101001001',1992,'f',0.2,'','1994-12-26','2019-01-11 00:00:00'), ('bar',6,'1900-01-01 00:00:00','00:00:00','bar',b'10011000001101011000101',1985,'b',0.7,'','2028-04-06','1971-01-01 00:00:00'); alter table t1 add index(vcol_datetime); +Warnings: +Warning 1901 Function or expression '`col_datetime`' cannot be used in the GENERATED ALWAYS AS clause of `vcol_datetime` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +Warning 1901 Function or expression '`col_datetime`' cannot be used in the GENERATED ALWAYS AS clause of `vcol_datetime` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL drop table t1; create table t1 ( pk int, diff --git a/mysql-test/suite/vcol/r/vcol_keys_myisam.result b/mysql-test/suite/vcol/r/vcol_keys_myisam.result index dd6e4c414f0..803f7446810 100644 --- a/mysql-test/suite/vcol/r/vcol_keys_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_keys_myisam.result @@ -277,6 +277,7 @@ a b c d delete from t1 where b=12; analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment @@ -389,7 +390,7 @@ drop table t1; CREATE TABLE t1 (i INT, d1 DATE, d2 DATE NOT NULL, t TIMESTAMP, KEY(t)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'2023-03-16','2023-03-15','2012-12-12 12:12:12'); ALTER TABLE t1 MODIFY t FLOAT AS (i) PERSISTENT; -SELECT i, d1, d2 FROM t1 INTO OUTFILE 'load_t1'; +SELECT i, d1, d2 INTO OUTFILE 'load_t1' FROM t1; DELETE FROM t1; LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2); SELECT * FROM t1 WHERE d2 < d1; diff --git a/mysql-test/suite/vcol/r/vcol_misc.result b/mysql-test/suite/vcol/r/vcol_misc.result index b9812bf2c4a..6b2457ff323 100644 --- a/mysql-test/suite/vcol/r/vcol_misc.result +++ b/mysql-test/suite/vcol/r/vcol_misc.result @@ -182,16 +182,36 @@ a b c 2 3 y 0 1 y,n drop table t1,t2; -CREATE TABLE t1 ( +SET @old_debug= @@global.debug; +SET @old_debug= @@global.debug; +SET GLOBAL debug_dbug= "+d,write_delay_wakeup"; +CREATE TABLE t1 (a int, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL ) ENGINE=MyISAM; -INSERT INTO t1 (tsv) VALUES (DEFAULT); -INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT); +# First test FLUSH TABLES +INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT); FLUSH TABLES; +SELECT COUNT(*) > 0 FROM t1; +COUNT(*) > 0 +1 +# Then test FLUSH TABLES t1; +INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT); +FLUSH TABLES t1; +SELECT COUNT(*) FROM t1; +COUNT(*) +4 +# Then test FLUSH TABLES WITH READ LOCK; +INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT); +FLUSH TABLES WITH READ LOCK; SELECT COUNT(*) FROM t1; COUNT(*) -2 +6 +set GLOBAL debug_dbug= @old_debug; +unlock tables; DROP TABLE t1; # # MDEV-4823 Server crashes in Item_func_not::fix_fields on diff --git a/mysql-test/suite/vcol/r/vcol_select_innodb.result b/mysql-test/suite/vcol/r/vcol_select_innodb.result index 63c35bade07..0f6cdf918f9 100644 --- a/mysql-test/suite/vcol/r/vcol_select_innodb.result +++ b/mysql-test/suite/vcol/r/vcol_select_innodb.result @@ -9,7 +9,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); # select_type=SIMPLE, type=system select * from t2; a b c @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,7 +73,7 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -160,7 +160,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed vcol expr> select * from t3 where c between -2 and -1; a b c @@ -192,7 +192,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL PRIMARY 4 NULL 3 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -200,7 +200,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -216,7 +216,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where c between -2 and -1 order by c; a b c diff --git a/mysql-test/suite/vcol/r/vcol_select_myisam.result b/mysql-test/suite/vcol/r/vcol_select_myisam.result index d6a8babc045..017531f31eb 100644 --- a/mysql-test/suite/vcol/r/vcol_select_myisam.result +++ b/mysql-test/suite/vcol/r/vcol_select_myisam.result @@ -9,7 +9,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int as (-a), c int as (-a) persistent unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); # select_type=SIMPLE, type=system select * from t2; a b c @@ -44,7 +44,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using index condition +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -63,8 +63,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -73,8 +73,8 @@ a b c 1 -1 -1 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index -1 PRIMARY t1 ref c c 5 test.t3.c 2 +1 PRIMARY t1 range c c 5 NULL 3 Using index condition +1 PRIMARY t3 eq_ref c c 5 test.t1.c 1 Using index # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> select * from t1 union select * from t2; @@ -152,7 +152,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> select * from t3 where b between -2 and -1; a b c @@ -160,7 +160,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed vcol expr> select * from t3 where c between -2 and -1; a b c @@ -168,7 +168,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-vcol expr> ORDER BY <indexed vcol> select * from t3 where a between 1 and 2 order by c; a b c @@ -176,7 +176,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-vcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -184,7 +184,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-vcol> select * from t3 where c between -2 and -1 order by a; a b c @@ -192,7 +192,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -200,7 +200,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <non-indexed vcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -208,7 +208,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -216,7 +216,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed vcol expr> ORDER BY <indexed vcol> select * from t3 where c between -2 and -1 order by c; a b c @@ -224,7 +224,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT sum(<non-indexed vcol>) FROM tbl_name GROUP BY <non-indexed vcol> select sum(b) from t1 group by b; sum(b) diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode.result b/mysql-test/suite/vcol/r/vcol_sql_mode.result index 385939fd037..ecbafc87b84 100644 --- a/mysql-test/suite/vcol/r/vcol_sql_mode.result +++ b/mysql-test/suite/vcol/r/vcol_sql_mode.result @@ -26,6 +26,9 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); +Warnings: +Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL DROP TABLE t1; CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); Warnings: diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result b/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result new file mode 100644 index 00000000000..151b95095a8 --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result @@ -0,0 +1,125 @@ +# +# Start of 10.4 tests +# +# +# MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +# +SET sql_mode=DEFAULT; +# OK: same FSP + virtual index +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(4) AS (t) VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +# OK: lower FSP + no virtual index +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS (t) VIRTUAL +); +DROP TABLE t1; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; +# NOT OK: lower FSP + virtual index +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS (t) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS (COALESCE(t)) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +# OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS (ROUND(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +t d v +2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.050 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; +# OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t DATETIME(4), +d DATETIME, +v DATETIME(3) AS (TRUNCATE(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +t d v +2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.049 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL +# +CREATE TABLE t1 ( +a DATETIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL, +KEY (v) +); +Warnings: +Warning 1901 Function or expression 'cast(`a` as datetime(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +a DATETIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, +KEY (v) +); +DROP TABLE t1; +CREATE TABLE t1 ( +a DATETIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL, +KEY (v) +); +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_time.result b/mysql-test/suite/vcol/r/vcol_sql_mode_time.result new file mode 100644 index 00000000000..adf650fff9f --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_time.result @@ -0,0 +1,125 @@ +# +# Start of 10.4 tests +# +# +# MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +# +SET sql_mode=DEFAULT; +# OK: same FSP + virtual index +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(4) AS (t) VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(4) AS ('10:20:30.1234') VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +# OK: lower FSP + no virtual index +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS (t) VIRTUAL +); +DROP TABLE t1; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; +# NOT OK: lower FSP + virtual index +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS (t) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS (COALESCE(t)) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +# OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS (ROUND(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53'); +SELECT * FROM t1; +t d v +12:44:34.0496 21:27:53 12:44:34.050 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = CURRENT_TIME; +DROP TABLE t1; +SET sql_mode=DEFAULT; +# OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t TIME(4), +d TIME, +v TIME(3) AS (TRUNCATE(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53'); +SELECT * FROM t1; +t d v +12:44:34.0496 21:27:53 12:44:34.049 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = CURRENT_TIME; +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL +# +CREATE TABLE t1 ( +a TIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS TIME(3))) VIRTUAL, +KEY (v) +); +Warnings: +Warning 1901 Function or expression 'cast(`a` as time(3))' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +a TIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, +KEY (v) +); +DROP TABLE t1; +CREATE TABLE t1 ( +a TIME(6), +v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL, +KEY (v) +); +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result b/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result new file mode 100644 index 00000000000..7c82856717d --- /dev/null +++ b/mysql-test/suite/vcol/r/vcol_sql_mode_timestamp.result @@ -0,0 +1,118 @@ +# +# Start of 10.4 tests +# +# +# MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +# +SET sql_mode=DEFAULT; +# OK: same FSP + virtual index +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(4) AS (t) VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL, +KEY(v,d) +); +DROP TABLE t1; +# OK: lower FSP + no virtual index +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS (t) VIRTUAL +); +DROP TABLE t1; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; +# NOT OK: lower FSP + virtual index +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS (t) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS (COALESCE(t)) VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, +KEY(v,d) +); +Warnings: +Warning 1901 Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +# OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS (ROUND(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +t d v +2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.050 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; +# OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( +t TIMESTAMP(4), +d DATETIME, +v TIMESTAMP(3) AS (TRUNCATE(t,3)) VIRTUAL, +KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +t d v +2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.049 +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; +# +# MDEV-20639 ASAN SEGV in get_prefix upon modifying base column type with existing indexed virtual column +# +CREATE TABLE t1 ( +a TIMESTAMP, +b TIMESTAMP AS (a) VIRTUAL, +KEY (b) +); +ALTER TABLE t1 MODIFY a BLOB FIRST; +Warnings: +Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +Warning 1901 Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `b` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL +DROP TABLE t1; +# +# End of 10.4 tests +# diff --git a/mysql-test/suite/vcol/r/vcol_syntax.result b/mysql-test/suite/vcol/r/vcol_syntax.result index e2131be2cce..d1339af2986 100644 --- a/mysql-test/suite/vcol/r/vcol_syntax.result +++ b/mysql-test/suite/vcol/r/vcol_syntax.result @@ -170,10 +170,10 @@ select 1 from t1 x natural join t1; 1 1 Warnings: -Warning 1292 Incorrect datetime value: 'x' -Warning 1292 Incorrect datetime value: 'root@localhost' -Warning 1292 Incorrect datetime value: 'x' -Warning 1292 Incorrect datetime value: 'root@localhost' +Warning 1292 Truncated incorrect datetime value: 'x' +Warning 1292 Truncated incorrect datetime value: 'root@localhost' +Warning 1292 Truncated incorrect datetime value: 'x' +Warning 1292 Truncated incorrect datetime value: 'root@localhost' drop table t1; # MDEV-28089 (duplicate) create table t1 (a int , b date as (1 in ('x' ,(database () = 'x' is null) ))) ; diff --git a/mysql-test/suite/vcol/r/wrong_arena.result b/mysql-test/suite/vcol/r/wrong_arena.result index 8edcd5c881f..d5ba19b3db3 100644 --- a/mysql-test/suite/vcol/r/wrong_arena.result +++ b/mysql-test/suite/vcol/r/wrong_arena.result @@ -10,9 +10,9 @@ d int as ((a,a) in ((1,1),(2,1),(NULL,1))), # cmp_item_datetime e int as ((a,1) in ((1,1),(2,1),(NULL,1))) # cmp_item_row::alloc_comparators() ); Warnings: -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '2' -Warning 1292 Incorrect datetime value: '3' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '2' +Warning 1292 Truncated incorrect datetime value: '3' show create table t1; Table Create Table t1 CREATE TABLE `t1` ( @@ -23,21 +23,21 @@ t1 CREATE TABLE `t1` ( `e` int(11) GENERATED ALWAYS AS ((`a`,1) in ((1,1),(2,1),(NULL,1))) VIRTUAL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 Warnings: -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '2' -Warning 1292 Incorrect datetime value: '3' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '2' +Warning 1292 Truncated incorrect datetime value: '3' connect con1, localhost, root; insert t1 (a) values ('2010-10-10 10:10:10'); select * from t1; a b c d e 2010-10-10 10:10:10 1 0 0 NULL Warnings: -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '2' -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '1' -Warning 1292 Incorrect datetime value: '2' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '2' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '2' disconnect con1; connection default; select * from t1; @@ -53,14 +53,14 @@ select * from t1; a b 2010-10-10 10:10:10 0000-00-00 00:00:00 Warnings: -Warning 1292 Incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '1' disconnect con1; connection default; select * from t1; a b 2010-10-10 10:10:10 0000-00-00 00:00:00 Warnings: -Warning 1292 Incorrect datetime value: '1' +Warning 1292 Truncated incorrect datetime value: '1' drop table t1; # # MDEV-13435 Crash when selecting virtual columns generated using JSON functions diff --git a/mysql-test/suite/vcol/t/update.test b/mysql-test/suite/vcol/t/update.test index 4218f0e3efb..6065f926bff 100644 --- a/mysql-test/suite/vcol/t/update.test +++ b/mysql-test/suite/vcol/t/update.test @@ -93,10 +93,10 @@ check table t; select * from t; insert t(a,b,d) select 10,4,4 on duplicate key update b=5, d=5; check table t; select * from t; replace delayed t (a,b,d) values (10,6,6); -flush tables; +flush tables t; check table t; select * from t; insert delayed t(a,b,d) values (10,6,6) on duplicate key update b=7, d=7; -flush tables; +flush tables t; check table t; select * from t; --write_file $MYSQLTEST_VARDIR/tmp/vblobs.txt 10 8 foo 8 foo diff --git a/mysql-test/suite/vcol/t/vcol_keys_myisam.test b/mysql-test/suite/vcol/t/vcol_keys_myisam.test index 24612f4d55f..6c3a94d2086 100644 --- a/mysql-test/suite/vcol/t/vcol_keys_myisam.test +++ b/mysql-test/suite/vcol/t/vcol_keys_myisam.test @@ -281,7 +281,7 @@ drop table t1; CREATE TABLE t1 (i INT, d1 DATE, d2 DATE NOT NULL, t TIMESTAMP, KEY(t)) ENGINE=MyISAM; INSERT INTO t1 VALUES (1,'2023-03-16','2023-03-15','2012-12-12 12:12:12'); ALTER TABLE t1 MODIFY t FLOAT AS (i) PERSISTENT; -SELECT i, d1, d2 FROM t1 INTO OUTFILE 'load_t1'; +SELECT i, d1, d2 INTO OUTFILE 'load_t1' FROM t1; DELETE FROM t1; LOAD DATA INFILE 'load_t1' INTO TABLE t1 (i,d1,d2); SELECT * FROM t1 WHERE d2 < d1; diff --git a/mysql-test/suite/vcol/t/vcol_misc.test b/mysql-test/suite/vcol/t/vcol_misc.test index e5609003806..93fc84ba74c 100644 --- a/mysql-test/suite/vcol/t/vcol_misc.test +++ b/mysql-test/suite/vcol/t/vcol_misc.test @@ -1,4 +1,5 @@ --source include/have_ucs2.inc +--source include/have_debug.inc let $MYSQLD_DATADIR= `select @@datadir`; @@ -184,19 +185,35 @@ drop table t1,t2; # Bug mdev-3938: INSERT DELAYED for a table with virtual columns # -CREATE TABLE t1 ( +SET @old_debug= @@global.debug; +SET @old_debug= @@global.debug; +SET GLOBAL debug_dbug= "+d,write_delay_wakeup"; +CREATE TABLE t1 (a int, ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, tsv TIMESTAMP AS (ADDDATE(ts, INTERVAL 1 DAY)) VIRTUAL ) ENGINE=MyISAM; -INSERT INTO t1 (tsv) VALUES (DEFAULT); - -INSERT DELAYED INTO t1 (tsv) VALUES (DEFAULT); - +--echo # First test FLUSH TABLES +INSERT INTO t1 (a,tsv) VALUES (1,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (2,DEFAULT); FLUSH TABLES; +# Count may be 1 or 2, depending on FLUSH happened before or after delayed +SELECT COUNT(*) > 0 FROM t1; +--echo # Then test FLUSH TABLES t1; +INSERT INTO t1 (a,tsv) VALUES (3,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (4,DEFAULT); +FLUSH TABLES t1; SELECT COUNT(*) FROM t1; +--echo # Then test FLUSH TABLES WITH READ LOCK; + +INSERT INTO t1 (a,tsv) VALUES (5,DEFAULT); +INSERT DELAYED INTO t1 (a,tsv) VALUES (6,DEFAULT); +FLUSH TABLES WITH READ LOCK; +SELECT COUNT(*) FROM t1; +set GLOBAL debug_dbug= @old_debug; +unlock tables; DROP TABLE t1; --echo # diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test b/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test new file mode 100644 index 00000000000..bef1e09b719 --- /dev/null +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_datetime.test @@ -0,0 +1,138 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +--echo # + +SET sql_mode=DEFAULT; + +--echo # OK: same FSP + virtual index + +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(4) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + no virtual index + +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS (t) VIRTUAL +); +DROP TABLE t1; + + +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; + + +--echo # NOT OK: lower FSP + virtual index + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS (COALESCE(t)) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS (ROUND(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t DATETIME(4), + d DATETIME, + v DATETIME(3) AS (TRUNCATE(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL +--echo # + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a DATETIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + a DATETIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + a DATETIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_time.test b/mysql-test/suite/vcol/t/vcol_sql_mode_time.test new file mode 100644 index 00000000000..05160a43ebb --- /dev/null +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_time.test @@ -0,0 +1,139 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +--echo # + +SET sql_mode=DEFAULT; + +--echo # OK: same FSP + virtual index + +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(4) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(4) AS ('10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + no virtual index + +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS (t) VIRTUAL +); +DROP TABLE t1; + + +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; + + +--echo # NOT OK: lower FSP + virtual index + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS (COALESCE(t)) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS (ROUND(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = CURRENT_TIME; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t TIME(4), + d TIME, + v TIME(3) AS (TRUNCATE(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = CURRENT_TIME; +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # +--echo # MDEV-20423 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with TIME_ROUND_FRACTIONAL +--echo # + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + a TIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS TIME(3))) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + a TIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + a TIME(6), + v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL, + KEY (v) +); +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test b/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test new file mode 100644 index 00000000000..4f38da75948 --- /dev/null +++ b/mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test @@ -0,0 +1,124 @@ +--echo # +--echo # Start of 10.4 tests +--echo # + +--echo # +--echo # MDEV-18153 Assertion `0' or Assertion `btr_validate_index(index, 0)' failed in row_upd_sec_index_entry or error code 126: Index is corrupted upon UPDATE with TIME_ROUND_FRACTIONAL +--echo # + +SET sql_mode=DEFAULT; + +--echo # OK: same FSP + virtual index + +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(4) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + no virtual index + +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS (t) VIRTUAL +); +DROP TABLE t1; + + +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL +); +DROP TABLE t1; + + +--echo # NOT OK: lower FSP + virtual index + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS (t) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS (COALESCE(t)) VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL, + KEY(v,d) +); +DROP TABLE t1; + + +--echo # OK: lower FSP + ROUND + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS (ROUND(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; + + +--echo # OK: lower FSP + TRUNCATE + virtual index +SET sql_mode=DEFAULT; +CREATE TABLE t1 ( + t TIMESTAMP(4), + d DATETIME, + v TIMESTAMP(3) AS (TRUNCATE(t,3)) VIRTUAL, + KEY(v,d) +); +INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53'); +SELECT * FROM t1; +SET SQL_MODE= 'TIME_ROUND_FRACTIONAL'; +UPDATE IGNORE t1 SET d = NOW(); +DROP TABLE t1; +SET sql_mode=DEFAULT; + +--echo # +--echo # MDEV-20639 ASAN SEGV in get_prefix upon modifying base column type with existing indexed virtual column +--echo # + +CREATE TABLE t1 ( + a TIMESTAMP, + b TIMESTAMP AS (a) VIRTUAL, + KEY (b) +); +ALTER TABLE t1 MODIFY a BLOB FIRST; +DROP TABLE t1; + + +--echo # +--echo # End of 10.4 tests +--echo # |