Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sql_mode_timestamp.test

raw
1 --echo #
2 --echo # Start of 10.4 tests
3 --echo #
4
5 --echo #
6 --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
7 --echo #
8
9 SET sql_mode=DEFAULT;
10
11 --echo # OK: same FSP + virtual index
12
13 CREATE TABLE t1 (
14 t TIMESTAMP(4),
15 d DATETIME,
16 v TIMESTAMP(4) AS (t) VIRTUAL,
17 KEY(v,d)
18 );
19 DROP TABLE t1;
20
21 CREATE TABLE t1 (
22 t TIMESTAMP(4),
23 d DATETIME,
24 v TIMESTAMP(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL,
25 KEY(v,d)
26 );
27 DROP TABLE t1;
28
29
30 --echo # OK: lower FSP + no virtual index
31
32 CREATE TABLE t1 (
33 t TIMESTAMP(4),
34 d DATETIME,
35 v TIMESTAMP(3) AS (t) VIRTUAL
36 );
37 DROP TABLE t1;
38
39
40 CREATE TABLE t1 (
41 t TIMESTAMP(4),
42 d DATETIME,
43 v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL
44 );
45 DROP TABLE t1;
46
47
48 --echo # NOT OK: lower FSP + virtual index
49
50 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
51 CREATE TABLE t1 (
52 t TIMESTAMP(4),
53 d DATETIME,
54 v TIMESTAMP(3) AS (t) VIRTUAL,
55 KEY(v,d)
56 );
57
58 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
59 CREATE TABLE t1 (
60 t TIMESTAMP(4),
61 d DATETIME,
62 v TIMESTAMP(3) AS (COALESCE(t)) VIRTUAL,
63 KEY(v,d)
64 );
65
66 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
67 CREATE TABLE t1 (
68 t TIMESTAMP(4),
69 d DATETIME,
70 v TIMESTAMP(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL,
71 KEY(v,d)
72 );
73
74
75 --echo # OK: lower FSP + ROUND + virtual index
76 SET sql_mode=DEFAULT;
77 CREATE TABLE t1 (
78 t TIMESTAMP(4),
79 d DATETIME,
80 v TIMESTAMP(3) AS (ROUND(t,3)) VIRTUAL,
81 KEY(v,d)
82 );
83 INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53');
84 SELECT * FROM t1;
85 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
86 UPDATE IGNORE t1 SET d = NOW();
87 DROP TABLE t1;
88 SET sql_mode=DEFAULT;
89
90
91 --echo # OK: lower FSP + TRUNCATE + virtual index
92 SET sql_mode=DEFAULT;
93 CREATE TABLE t1 (
94 t TIMESTAMP(4),
95 d DATETIME,
96 v TIMESTAMP(3) AS (TRUNCATE(t,3)) VIRTUAL,
97 KEY(v,d)
98 );
99 INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53');
100 SELECT * FROM t1;
101 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
102 UPDATE IGNORE t1 SET d = NOW();
103 DROP TABLE t1;
104 SET sql_mode=DEFAULT;
105
106 --echo #
107 --echo # MDEV-20639 ASAN SEGV in get_prefix upon modifying base column type with existing indexed virtual column
108 --echo #
109
110 CREATE TABLE t1 (
111 a TIMESTAMP,
112 b TIMESTAMP AS (a) VIRTUAL,
113 KEY (b)
114 );
115 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
116 ALTER TABLE t1 MODIFY a BLOB FIRST;
117 SHOW WARNINGS;
118 DROP TABLE t1;
119
120 --echo #
121 --echo # End of 10.4 tests
122 --echo #