Debian Codegraph @

mysql-test/suite/vcol/r/vcol_sql_mode_datetime.result

raw
1 #
2 # Start of 10.4 tests
3 #
4 #
5 # 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
6 #
7 SET sql_mode=DEFAULT;
8 # OK: same FSP + virtual index
9 CREATE TABLE t1 (
10 t DATETIME(4),
11 d DATETIME,
12 v DATETIME(4) AS (t) VIRTUAL,
13 KEY(v,d)
14 );
15 DROP TABLE t1;
16 CREATE TABLE t1 (
17 t DATETIME(4),
18 d DATETIME,
19 v DATETIME(4) AS ('2001-01-01 10:20:30.1234') VIRTUAL,
20 KEY(v,d)
21 );
22 DROP TABLE t1;
23 # OK: lower FSP + no virtual index
24 CREATE TABLE t1 (
25 t DATETIME(4),
26 d DATETIME,
27 v DATETIME(3) AS (t) VIRTUAL
28 );
29 DROP TABLE t1;
30 CREATE TABLE t1 (
31 t DATETIME(4),
32 d DATETIME,
33 v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL
34 );
35 DROP TABLE t1;
36 # NOT OK: lower FSP + virtual index
37 CREATE TABLE t1 (
38 t DATETIME(4),
39 d DATETIME,
40 v DATETIME(3) AS (t) VIRTUAL,
41 KEY(v,d)
42 );
43 ERROR HY000: Function or expression '`t`' cannot be used in the GENERATED ALWAYS AS clause of `v`
44 CREATE TABLE t1 (
45 t DATETIME(4),
46 d DATETIME,
47 v DATETIME(3) AS (COALESCE(t)) VIRTUAL,
48 KEY(v,d)
49 );
50 ERROR HY000: Function or expression 'coalesce(`t`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
51 CREATE TABLE t1 (
52 t DATETIME(4),
53 d DATETIME,
54 v DATETIME(3) AS ('2001-01-01 10:20:30.1234') VIRTUAL,
55 KEY(v,d)
56 );
57 ERROR HY000: Function or expression ''2001-01-01 10:20:30.1234'' cannot be used in the GENERATED ALWAYS AS clause of `v`
58 # OK: lower FSP + ROUND + virtual index
59 SET sql_mode=DEFAULT;
60 CREATE TABLE t1 (
61 t DATETIME(4),
62 d DATETIME,
63 v DATETIME(3) AS (ROUND(t,3)) VIRTUAL,
64 KEY(v,d)
65 );
66 INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53');
67 SELECT * FROM t1;
68 t d v
69 2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.050
70 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
71 UPDATE IGNORE t1 SET d = NOW();
72 DROP TABLE t1;
73 SET sql_mode=DEFAULT;
74 # OK: lower FSP + TRUNCATE + virtual index
75 SET sql_mode=DEFAULT;
76 CREATE TABLE t1 (
77 t DATETIME(4),
78 d DATETIME,
79 v DATETIME(3) AS (TRUNCATE(t,3)) VIRTUAL,
80 KEY(v,d)
81 );
82 INSERT IGNORE INTO t1 (t,d) VALUES ('2006-03-01 12:44:34.0496','2029-10-10 21:27:53');
83 SELECT * FROM t1;
84 t d v
85 2006-03-01 12:44:34.0496 2029-10-10 21:27:53 2006-03-01 12:44:34.049
86 SET SQL_MODE= 'TIME_ROUND_FRACTIONAL';
87 UPDATE IGNORE t1 SET d = NOW();
88 DROP TABLE t1;
89 SET sql_mode=DEFAULT;
90 #
91 # 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
92 #
93 CREATE TABLE t1 (
94 a DATETIME(6),
95 v VARCHAR(30) GENERATED ALWAYS AS (CAST(a AS DATETIME(3))) VIRTUAL,
96 KEY (v)
97 );
98 ERROR HY000: Function or expression 'cast(`a` as datetime(3))' cannot be used in the GENERATED ALWAYS AS clause of `v`
99 CREATE TABLE t1 (
100 a DATETIME(6),
101 v VARCHAR(30) GENERATED ALWAYS AS (TRUNCATE(a,3)) VIRTUAL,
102 KEY (v)
103 );
104 DROP TABLE t1;
105 CREATE TABLE t1 (
106 a DATETIME(6),
107 v VARCHAR(30) GENERATED ALWAYS AS (ROUND(a,3)) VIRTUAL,
108 KEY (v)
109 );
110 DROP TABLE t1;
111 #
112 # End of 10.4 tests
113 #