Debian Codegraph @

mysql-test/suite/vcol/t/vcol_sql_mode_time.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 TIME(4),
15 d TIME,
16 v TIME(4) AS (t) VIRTUAL,
17 KEY(v,d)
18 );
19 DROP TABLE t1;
20
21 CREATE TABLE t1 (
22 t TIME(4),
23 d TIME,
24 v TIME(4) AS ('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 TIME(4),
34 d TIME,
35 v TIME(3) AS (t) VIRTUAL
36 );
37 DROP TABLE t1;
38
39
40 CREATE TABLE t1 (
41 t TIME(4),
42 d TIME,
43 v TIME(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 TIME(4),
53 d TIME,
54 v TIME(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 TIME(4),
61 d TIME,
62 v TIME(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 TIME(4),
69 d TIME,
70 v TIME(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 TIME(4),
79 d TIME,
80 v TIME(3) AS (ROUND(t,3)) VIRTUAL,
81 KEY(v,d)
82 );
83 INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53');
84 SELECT * FROM t1;
85 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
86 UPDATE IGNORE t1 SET d = CURRENT_TIME;
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 TIME(4),
95 d TIME,
96 v TIME(3) AS (TRUNCATE(t,3)) VIRTUAL,
97 KEY(v,d)
98 );
99 INSERT IGNORE INTO t1 (t,d) VALUES ('12:44:34.0496','21:27:53');
100 SELECT * FROM t1;
101 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
102 UPDATE IGNORE t1 SET d = CURRENT_TIME;
103 DROP TABLE t1;
104 SET sql_mode=DEFAULT;
105
106
107 --echo #
108 --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
109 --echo #
110
111 --error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
112 CREATE TABLE t1 (
113 a TIME(6),
114 v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS TIME(3))) VIRTUAL,
115 KEY (v)
116 );
117
118 CREATE TABLE t1 (
119 a TIME(6),
120 v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL,
121 KEY (v)
122 );
123 DROP TABLE t1;
124
125 CREATE TABLE t1 (
126 a TIME(6),
127 v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL,
128 KEY (v)
129 );
130 DROP TABLE t1;
131
132
133 --echo #
134 --echo # End of 10.4 tests
135 --echo #