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
|
--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)
);
--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)
);
--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)
);
--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)
);
--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
ALTER TABLE t1 MODIFY a BLOB FIRST;
SHOW WARNINGS;
DROP TABLE t1;
--echo #
--echo # End of 10.4 tests
--echo #
|
