1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
|
--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)
);
--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)
);
--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)
);
--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)
);
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 #
|
