Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sql_mode_datetime.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 DATETIME(4),
15 d DATETIME,
16 v DATETIME(4) AS (t) VIRTUAL,
17 KEY(v,d)
18 );
19 DROP TABLE t1;
20
21 CREATE TABLE t1 (
22 t DATETIME(4),
23 d DATETIME,
24 v DATETIME(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 DATETIME(4),
34 d DATETIME,
35 v DATETIME(3) AS (t) VIRTUAL
36 );
37 DROP TABLE t1;
38
39
40 CREATE TABLE t1 (
41 t DATETIME(4),
42 d DATETIME,
43 v DATETIME(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 DATETIME(4),
53 d DATETIME,
54 v DATETIME(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 DATETIME(4),
61 d DATETIME,
62 v DATETIME(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 DATETIME(4),
69 d DATETIME,
70 v DATETIME(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 DATETIME(4),
79 d DATETIME,
80 v DATETIME(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 DATETIME(4),
95 d DATETIME,
96 v DATETIME(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-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
108 --echo #
109
110 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
111 CREATE TABLE t1 (
112 a DATETIME(6),
113 v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL,
114 KEY (v)
115 );
116
117 CREATE TABLE t1 (
118 a DATETIME(6),
119 v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL,
120 KEY (v)
121 );
122 DROP TABLE t1;
123
124 CREATE TABLE t1 (
125 a DATETIME(6),
126 v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL,
127 KEY (v)
128 );
129 DROP TABLE t1;
130
131
132 --echo #
133 --echo # End of 10.4 tests
134 --echo #