| 1 |
# Bug#21230709: Alter table statement fails with division by zero |
| 2 |
CREATE TABLE t1 ( |
| 3 |
col1 INTEGER NOT NULL, |
| 4 |
col2 INTEGER NOT NULL, |
| 5 |
col3 INTEGER NOT NULL, |
| 6 |
gcol1 INTEGER GENERATED ALWAYS AS (col3 + col3) VIRTUAL, |
| 7 |
col4 INTEGER DEFAULT NULL, |
| 8 |
col5 INTEGER DEFAULT NULL, |
| 9 |
col6 INTEGER DEFAULT NULL, |
| 10 |
col7 INTEGER DEFAULT NULL, |
| 11 |
col8 INTEGER DEFAULT NULL, |
| 12 |
col9 INTEGER DEFAULT NULL, |
| 13 |
col10 INTEGER DEFAULT NULL, |
| 14 |
col11 INTEGER DEFAULT NULL, |
| 15 |
col12 INTEGER DEFAULT NULL, |
| 16 |
col13 INTEGER DEFAULT NULL, |
| 17 |
col14 INTEGER DEFAULT NULL, |
| 18 |
col15 INTEGER DEFAULT NULL, |
| 19 |
col16 INTEGER DEFAULT NULL, |
| 20 |
col17 INTEGER DEFAULT NULL, |
| 21 |
col18 INTEGER DEFAULT NULL, |
| 22 |
col19 INTEGER DEFAULT NULL, |
| 23 |
col20 INTEGER DEFAULT NULL, |
| 24 |
col21 INTEGER DEFAULT NULL, |
| 25 |
col22 INTEGER DEFAULT NULL, |
| 26 |
col23 INTEGER DEFAULT NULL, |
| 27 |
col24 INTEGER DEFAULT NULL, |
| 28 |
col25 INTEGER DEFAULT NULL, |
| 29 |
col26 INTEGER DEFAULT NULL, |
| 30 |
col27 INTEGER DEFAULT NULL, |
| 31 |
col28 INTEGER DEFAULT NULL, |
| 32 |
col29 INTEGER DEFAULT NULL, |
| 33 |
col30 INTEGER DEFAULT NULL, |
| 34 |
col31 INTEGER DEFAULT NULL, |
| 35 |
col32 INTEGER DEFAULT NULL, |
| 36 |
col33 INTEGER DEFAULT NULL, |
| 37 |
gcol2 INTEGER GENERATED ALWAYS AS (col2 + col2) VIRTUAL, |
| 38 |
gcol3 INTEGER GENERATED ALWAYS AS (gcol2 / gcol2) VIRTUAL, |
| 39 |
PRIMARY KEY (col1), |
| 40 |
KEY idx1 (gcol1) |
| 41 |
) engine=innodb; |
| 42 |
INSERT INTO t1 (col1, col2, col3) |
| 43 |
VALUES (0,1,2), (1,2,3), (2,3,4), (3,4,5), (4,5,6); |
| 44 |
FLUSH TABLE t1; |
| 45 |
ALTER TABLE t1 ADD COLUMN extra INTEGER; |
| 46 |
DROP TABLE t1; |
| 47 |
# |
| 48 |
# Bug 21340801 WL8149:ASSERTION `IS_VIRTUAL_GCOL()' FAILED |
| 49 |
# |
| 50 |
CREATE TABLE t1 ( |
| 51 |
c_blob BLOB, |
| 52 |
c_blob_key BLOB GENERATED ALWAYS AS (REPEAT(c_blob,15)) STORED, |
| 53 |
KEY (c_blob_key(200)) |
| 54 |
); |
| 55 |
INSERT INTO t1 (c_blob) VALUES ('xceks'); |
| 56 |
DROP TABLE t1; |
| 57 |
# |
| 58 |
# Bug#21345972 WL8149:JOIN_CACHE::FILTER_VIRTUAL_GCOL_BASE_COLS(): ASSERTION `FALSE' FAILED. |
| 59 |
# |
| 60 |
CREATE TABLE c ( |
| 61 |
pk INTEGER AUTO_INCREMENT, |
| 62 |
col_int_nokey INTEGER /*! NULL */, |
| 63 |
col_int_key INTEGER GENERATED ALWAYS AS |
| 64 |
(col_int_nokey + col_int_nokey) VIRTUAL not null, |
| 65 |
col_date_nokey DATE /*! NULL */, |
| 66 |
col_date_key DATE GENERATED ALWAYS AS |
| 67 |
(DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null, |
| 68 |
col_datetime_nokey DATETIME /*! NULL */, |
| 69 |
col_time_nokey TIME /*! NULL */, |
| 70 |
col_datetime_key DATETIME GENERATED ALWAYS AS |
| 71 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 72 |
col_time_key TIME GENERATED ALWAYS AS |
| 73 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 74 |
col_varchar_nokey VARCHAR(1) /*! NULL */, |
| 75 |
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS |
| 76 |
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null, |
| 77 |
PRIMARY KEY (pk), |
| 78 |
UNIQUE KEY (col_int_key), |
| 79 |
UNIQUE KEY (col_varchar_key), |
| 80 |
UNIQUE KEY (col_date_key), |
| 81 |
KEY (col_time_key), |
| 82 |
KEY (col_datetime_key), |
| 83 |
UNIQUE KEY (col_int_key, col_varchar_key), |
| 84 |
KEY (col_int_key, col_int_nokey), |
| 85 |
KEY(col_int_key,col_date_key), |
| 86 |
KEY(col_int_key, col_time_key), |
| 87 |
KEY(col_int_key, col_datetime_key), |
| 88 |
UNIQUE |
| 89 |
KEY(col_date_key,col_time_key,col_datetime_key), |
| 90 |
UNIQUE KEY (col_varchar_key, col_varchar_nokey), |
| 91 |
UNIQUE KEY (col_int_key, col_varchar_key, |
| 92 |
col_date_key, col_time_key, col_datetime_key) |
| 93 |
) ENGINE=INNODB; |
| 94 |
INSERT /*! IGNORE */ INTO c ( |
| 95 |
col_int_nokey, |
| 96 |
col_date_nokey, |
| 97 |
col_time_nokey, |
| 98 |
col_datetime_nokey, |
| 99 |
col_varchar_nokey |
| 100 |
) VALUES (7, '2004-04-09', '14:03:03.042673', |
| 101 |
'2001-11-28 00:50:27.051028', 'c'),(1, '2006-05-13', '01:46:09.016386', |
| 102 |
'2007-10-09 19:53:04.008332', NULL); |
| 103 |
Warnings: |
| 104 |
Warning 1048 Column 'col_varchar_key' cannot be null |
| 105 |
CREATE TABLE bb ( |
| 106 |
pk INTEGER AUTO_INCREMENT, |
| 107 |
col_int_nokey INTEGER /*! NULL */, |
| 108 |
col_int_key INTEGER GENERATED ALWAYS AS |
| 109 |
(col_int_nokey + col_int_nokey) VIRTUAL not null, |
| 110 |
col_date_nokey DATE /*! NULL */, |
| 111 |
col_date_key DATE GENERATED ALWAYS AS |
| 112 |
(DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null, |
| 113 |
col_datetime_nokey DATETIME /*! NULL */, |
| 114 |
col_time_nokey TIME /*! NULL */, |
| 115 |
col_datetime_key DATETIME GENERATED ALWAYS AS |
| 116 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 117 |
col_time_key TIME GENERATED ALWAYS AS |
| 118 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 119 |
col_varchar_nokey VARCHAR(1) /*! NULL */, |
| 120 |
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS |
| 121 |
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null, |
| 122 |
PRIMARY KEY (pk), |
| 123 |
UNIQUE KEY (col_int_key), |
| 124 |
UNIQUE KEY (col_varchar_key), |
| 125 |
UNIQUE KEY (col_date_key), |
| 126 |
KEY (col_time_key), |
| 127 |
KEY (col_datetime_key), |
| 128 |
UNIQUE KEY (col_int_key, col_varchar_key), |
| 129 |
KEY (col_int_key, col_int_nokey), |
| 130 |
KEY(col_int_key,col_date_key), |
| 131 |
KEY(col_int_key, col_time_key), |
| 132 |
KEY(col_int_key, col_datetime_key), |
| 133 |
UNIQUE |
| 134 |
KEY(col_date_key,col_time_key,col_datetime_key), |
| 135 |
UNIQUE KEY (col_varchar_key, col_varchar_nokey), |
| 136 |
UNIQUE KEY (col_int_key, col_varchar_key, |
| 137 |
col_date_key, col_time_key, col_datetime_key) |
| 138 |
) AUTO_INCREMENT=10 ENGINE=INNODB; |
| 139 |
INSERT /*! IGNORE */ INTO bb ( |
| 140 |
col_int_nokey, |
| 141 |
col_date_nokey, |
| 142 |
col_time_nokey, |
| 143 |
col_datetime_nokey, |
| 144 |
col_varchar_nokey |
| 145 |
) VALUES (0, '2003-08-04', '01:48:05.048577', |
| 146 |
'2006-11-03 00:00:00', 'p'),(2, '2007-11-06', '00:00:00', '2009-11-26 19:28:11.005115', 'n'); |
| 147 |
CREATE TABLE cc ( |
| 148 |
pk INTEGER AUTO_INCREMENT, |
| 149 |
col_int_nokey INTEGER /*! NULL */, |
| 150 |
col_int_key INTEGER GENERATED ALWAYS AS |
| 151 |
(col_int_nokey + col_int_nokey) VIRTUAL not null, |
| 152 |
col_date_nokey DATE /*! NULL */, |
| 153 |
col_date_key DATE GENERATED ALWAYS AS |
| 154 |
(DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null, |
| 155 |
col_datetime_nokey DATETIME /*! NULL */, |
| 156 |
col_time_nokey TIME /*! NULL */, |
| 157 |
col_datetime_key DATETIME GENERATED ALWAYS AS |
| 158 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 159 |
col_time_key TIME GENERATED ALWAYS AS |
| 160 |
(ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 161 |
col_varchar_nokey VARCHAR(1) /*! NULL */, |
| 162 |
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS |
| 163 |
(CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null, |
| 164 |
PRIMARY KEY (pk), |
| 165 |
UNIQUE KEY (col_int_key), |
| 166 |
UNIQUE KEY (col_varchar_key), |
| 167 |
UNIQUE KEY (col_date_key), |
| 168 |
KEY (col_time_key), |
| 169 |
KEY (col_datetime_key), |
| 170 |
UNIQUE KEY (col_int_key, col_varchar_key), |
| 171 |
KEY (col_int_key, col_int_nokey), |
| 172 |
KEY(col_int_key,col_date_key), |
| 173 |
KEY(col_int_key, col_time_key), |
| 174 |
KEY(col_int_key, col_datetime_key), |
| 175 |
UNIQUE |
| 176 |
KEY(col_date_key,col_time_key,col_datetime_key), |
| 177 |
UNIQUE KEY (col_varchar_key, col_varchar_nokey), |
| 178 |
UNIQUE KEY (col_int_key, col_varchar_key, |
| 179 |
col_date_key, col_time_key, col_datetime_key) |
| 180 |
) AUTO_INCREMENT=10 ENGINE=INNODB; |
| 181 |
INSERT /*! IGNORE */ INTO cc ( |
| 182 |
col_int_nokey, |
| 183 |
col_date_nokey, |
| 184 |
col_time_nokey, |
| 185 |
col_datetime_nokey, |
| 186 |
col_varchar_nokey |
| 187 |
) VALUES (172, '2009-04-23', '00:00:00', '2000-12-07 10:17:40.013275', 'h'),(NULL, '2002-10-06', '00:50:49.017545', NULL, 'm'); |
| 188 |
Warnings: |
| 189 |
Warning 1048 Column 'col_int_key' cannot be null |
| 190 |
Warning 1048 Column 'col_datetime_key' cannot be null |
| 191 |
Warning 1048 Column 'col_time_key' cannot be null |
| 192 |
EXPLAIN SELECT |
| 193 |
gp1 . col_datetime_key AS g1 |
| 194 |
FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . col_datetime_key <> gp1 . |
| 195 |
col_time_nokey ) |
| 196 |
WHERE |
| 197 |
gp1 . col_varchar_nokey IN |
| 198 |
( |
| 199 |
SELECT |
| 200 |
DISTINCT p1 . col_varchar_key AS p1 |
| 201 |
FROM bb AS p1 LEFT JOIN bb AS p2 |
| 202 |
ON ( p1 . col_int_key = p2 . pk ) |
| 203 |
) |
| 204 |
AND gp1 . col_varchar_nokey = 'b' |
| 205 |
HAVING g1 > 6; |
| 206 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 207 |
1 SIMPLE gp1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where |
| 208 |
1 SIMPLE p1 NULL const col_varchar_key,col_varchar_key_2 col_varchar_key 10 const 1 100.00 NULL |
| 209 |
1 SIMPLE p2 NULL eq_ref PRIMARY PRIMARY 4 test.p1.col_int_key 1 100.00 Using index; FirstMatch(gp1) |
| 210 |
1 SIMPLE gp2 NULL index NULL col_datetime_key 5 NULL 2 100.00 Using where; Using index; Using join buffer (hash join) |
| 211 |
Warnings: |
| 212 |
Warning 1292 Incorrect datetime value: '6' for column 'col_datetime_key' at row 1 |
| 213 |
Note 1003 /* select#1 */ select `test`.`gp1`.`col_datetime_key` AS `g1` from `test`.`cc` `gp1` left join `test`.`c` `gp2` on((`test`.`gp2`.`col_datetime_key` <> cast(`test`.`gp1`.`col_time_nokey` as datetime))) semi join (`test`.`bb` `p1` left join `test`.`bb` `p2` on((`test`.`p1`.`col_int_key` = `test`.`p2`.`pk`))) where ((`test`.`gp1`.`col_varchar_nokey` = 'b') and ('b' = `test`.`p1`.`col_varchar_key`)) having (`g1` > 6) |
| 214 |
SELECT |
| 215 |
gp1 . col_datetime_key AS g1 |
| 216 |
FROM cc AS gp1 LEFT JOIN c AS gp2 ON ( gp2 . col_datetime_key <> gp1 . |
| 217 |
col_time_nokey ) |
| 218 |
WHERE |
| 219 |
gp1 . col_varchar_nokey IN |
| 220 |
( |
| 221 |
SELECT |
| 222 |
DISTINCT p1 . col_varchar_key AS p1 |
| 223 |
FROM bb AS p1 LEFT JOIN bb AS p2 |
| 224 |
ON ( p1 . col_int_key = p2 . pk ) |
| 225 |
) |
| 226 |
AND gp1 . col_varchar_nokey = 'b' |
| 227 |
HAVING g1 > 6; |
| 228 |
g1 |
| 229 |
Warnings: |
| 230 |
Warning 1292 Incorrect datetime value: '6' for column 'col_datetime_key' at row 1 |
| 231 |
DROP TABLE bb, c, cc; |
| 232 |
# Bug#21284646: Assertion !(table || table->read_set || bitmap_is_set()) |
| 233 |
CREATE TABLE c ( |
| 234 |
pk INTEGER AUTO_INCREMENT, |
| 235 |
col_int_nokey INTEGER NOT NULL, |
| 236 |
col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL not null, |
| 237 |
col_date_nokey DATE NOT NULL, |
| 238 |
col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL not null, |
| 239 |
col_datetime_nokey DATETIME NOT NULL, |
| 240 |
col_time_nokey TIME NOT NULL, |
| 241 |
col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 242 |
col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)) VIRTUAL not null, |
| 243 |
col_varchar_nokey VARCHAR(1) NOT NULL, |
| 244 |
col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL not null, |
| 245 |
PRIMARY KEY (pk,col_int_nokey), |
| 246 |
UNIQUE KEY (col_int_key), |
| 247 |
UNIQUE KEY (col_varchar_key), |
| 248 |
UNIQUE KEY (col_date_key), |
| 249 |
KEY (col_time_key), |
| 250 |
KEY (col_datetime_key), |
| 251 |
UNIQUE KEY (col_int_key, col_varchar_key), |
| 252 |
KEY (col_int_key, col_int_nokey), |
| 253 |
KEY(col_int_key,col_date_key), |
| 254 |
KEY(col_int_key, col_time_key), |
| 255 |
KEY(col_int_key, col_datetime_key), |
| 256 |
UNIQUE KEY (col_date_key,col_time_key,col_datetime_key), |
| 257 |
UNIQUE KEY (col_varchar_key, col_varchar_nokey), |
| 258 |
UNIQUE KEY (col_int_key, col_varchar_key, col_date_key, col_time_key, col_datetime_key) |
| 259 |
) ENGINE=INNODB; |
| 260 |
INSERT INTO c (col_int_nokey, col_date_nokey, col_time_nokey, col_datetime_nokey, col_varchar_nokey) VALUES |
| 261 |
(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), |
| 262 |
(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), |
| 263 |
(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), |
| 264 |
(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), |
| 265 |
(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'), |
| 266 |
(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'); |
| 267 |
ANALYZE TABLE c; |
| 268 |
Table Op Msg_type Msg_text |
| 269 |
test.c analyze status OK |
| 270 |
explain SELECT COUNT(DISTINCT col_varchar_key) AS x |
| 271 |
FROM c |
| 272 |
WHERE col_varchar_key IN ('rr', 'rr') OR |
| 273 |
col_int_nokey <> 9 AND |
| 274 |
pk >= 8 |
| 275 |
HAVING x > '2000-02-06' |
| 276 |
ORDER BY col_time_nokey, pk; |
| 277 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 278 |
1 SIMPLE c NULL index_merge PRIMARY,col_varchar_key,col_int_key_2,col_varchar_key_2,col_int_key_7 col_varchar_key,PRIMARY 10,4 NULL 2 100.00 Using sort_union(col_varchar_key,PRIMARY); Using where |
| 279 |
Warnings: |
| 280 |
Note 1003 /* select#1 */ select count(distinct `test`.`c`.`col_varchar_key`) AS `x` from `test`.`c` where ((`test`.`c`.`col_varchar_key` in ('rr','rr')) or ((`test`.`c`.`col_int_nokey` <> 9) and (`test`.`c`.`pk` >= 8))) having (`x` > '2000-02-06') |
| 281 |
SELECT COUNT(DISTINCT col_varchar_key) AS x |
| 282 |
FROM c |
| 283 |
WHERE col_varchar_key IN ('rr', 'rr') OR |
| 284 |
col_int_nokey <> 9 AND |
| 285 |
pk >= 8 |
| 286 |
HAVING x > '2000-02-06' |
| 287 |
ORDER BY col_time_nokey, pk; |
| 288 |
x |
| 289 |
Warnings: |
| 290 |
Warning 1292 Truncated incorrect DOUBLE value: '2000-02-06' |
| 291 |
DROP TABLE c; |
| 292 |
# Bug#21341044: Conditional jump at sort_param::make_sort_key |
| 293 |
CREATE TABLE t1 ( |
| 294 |
pk INTEGER AUTO_INCREMENT, |
| 295 |
col_int_nokey INTEGER /*! NULL */, |
| 296 |
col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey + col_int_nokey) VIRTUAL, |
| 297 |
col_blob_nokey BLOB /*! NULL */, |
| 298 |
col_blob_key BLOB GENERATED ALWAYS AS (REPEAT(col_blob_nokey,15)) VIRTUAL, |
| 299 |
col_longblob_nokey LONGBLOB /*! NULL */, |
| 300 |
col_longtext_nokey LONGTEXT /*! NULL */, |
| 301 |
col_longblob_key LONGBLOB GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 20)) VIRTUAL, |
| 302 |
col_longtext_key LONGTEXT GENERATED ALWAYS AS (REPEAT(col_longblob_nokey, 18)) VIRTUAL, |
| 303 |
col_text_nokey TEXT /*! NULL */, |
| 304 |
col_text_key TEXT GENERATED ALWAYS AS (REPEAT(col_text_nokey, 30)) VIRTUAL, |
| 305 |
PRIMARY KEY (pk), |
| 306 |
KEY (col_int_key), |
| 307 |
KEY (col_text_key(50)), |
| 308 |
KEY (col_blob_key(200)), |
| 309 |
KEY (col_longtext_key(200)), |
| 310 |
KEY (col_longblob_key(200)), |
| 311 |
KEY (col_int_key, col_text_key(100)), |
| 312 |
KEY (col_int_key, col_longtext_key(100)), |
| 313 |
KEY (col_int_key, col_blob_key(100)), |
| 314 |
KEY (col_int_key, col_longblob_key(100)), |
| 315 |
KEY (col_longtext_key(10), col_longblob_key(100)), |
| 316 |
KEY (col_int_key, col_text_key(10), col_blob_key(100), col_longtext_key(50), col_longblob_key(50)) |
| 317 |
) engine=innodb; |
| 318 |
INSERT INTO t1 (col_int_nokey,col_blob_nokey,col_longtext_nokey,col_longblob_nokey,col_text_nokey) |
| 319 |
VALUES |
| 320 |
(0, 'ijcszxw', 'ijcszxw', 'ijcszxw', 'ijcszxw'), |
| 321 |
(5, 'jcszxwb', 'jcszxwb', 'jcszxwb', 'jcszxwb'), |
| 322 |
(4, 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv', 'cszxwbjjvv'), |
| 323 |
(3, 'szxw', 'szxw', 'szxw', 'szxw'), |
| 324 |
(7, 'zxwb', 'zxwb', 'zxwb', 'zxwb'), |
| 325 |
(42, 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky', 'xwbjjvvky'), |
| 326 |
(142, 'wbjj', 'wbjj', 'wbjj', 'wbjj'), |
| 327 |
(5, 'bjjv', 'bjjv', 'bjjv', 'bjjv'), |
| 328 |
(0, 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu', 'jjvvkymalu'), |
| 329 |
(3, 'j', 'j', 'j', 'j'); |
| 330 |
SELECT alias1.pk AS field1 |
| 331 |
FROM t1 AS alias1 LEFT OUTER JOIN t1 AS alias2 |
| 332 |
ON alias1.col_int_key = alias2.col_int_key |
| 333 |
WHERE alias2.col_int_key BETWEEN 8 AND (8 + 1 ) OR |
| 334 |
alias2.col_int_key BETWEEN 8 AND (8 + 5 ) AND |
| 335 |
alias2.col_int_key != 20 OR |
| 336 |
alias2.col_int_key IN (8, 5, 8) AND |
| 337 |
alias2.col_int_key >= 0 AND |
| 338 |
alias2.col_int_key <= ( 8 + 75 ) AND |
| 339 |
alias1.pk IS NOT NULL |
| 340 |
ORDER BY field1; |
| 341 |
field1 |
| 342 |
2 |
| 343 |
2 |
| 344 |
3 |
| 345 |
8 |
| 346 |
8 |
| 347 |
DROP TABLE t1; |
| 348 |
# bug#21487651: gcols: memory leak after failed alter table |
| 349 |
CREATE TABLE t(a int); |
| 350 |
ALTER TABLE t ADD COLUMN b int GENERATED ALWAYS AS ( |
| 351 |
date_sub(a,interval a month)) VIRTUAL; |
| 352 |
ALTER TABLE t ADD COLUMN c int GENERATED ALWAYS AS (sum(a)); |
| 353 |
ERROR HY000: Invalid use of group function |
| 354 |
DROP TABLE t; |
| 355 |
# |
| 356 |
# Bug#21628840: CRASH/MEMORY CORRUPTION ADDING INDEXES TO VIRTUAL COLUMN |
| 357 |
# (II) |
| 358 |
# |
| 359 |
CREATE TABLE t1( a INT ) ENGINE = INNODB; |
| 360 |
INSERT INTO t1( a ) VALUES ( 1 ), ( 2 ), ( 3 ), ( 4 ), ( 5 ); |
| 361 |
ALTER TABLE t1 ADD COLUMN b INT GENERATED ALWAYS AS (a - 1) STORED; |
| 362 |
ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL; |
| 363 |
# Used to cause valgrind warning. |
| 364 |
ALTER TABLE t1 ADD INDEX( c ); |
| 365 |
ANALYZE TABLE t1; |
| 366 |
Table Op Msg_type Msg_text |
| 367 |
test.t1 analyze status OK |
| 368 |
# Make sure the index is correct. That's kinda important. |
| 369 |
EXPLAIN |
| 370 |
SELECT c FROM t1; |
| 371 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 372 |
1 SIMPLE t1 NULL index NULL c 5 NULL 5 100.00 Using index |
| 373 |
Warnings: |
| 374 |
Note 1003 /* select#1 */ select `test`.`t1`.`c` AS `c` from `test`.`t1` |
| 375 |
SELECT c FROM t1; |
| 376 |
c |
| 377 |
1 |
| 378 |
2 |
| 379 |
3 |
| 380 |
4 |
| 381 |
5 |
| 382 |
DROP TABLE t1; |
| 383 |
# |
| 384 |
# Bug#21797776 ASSERTION `BIT < MAP->N_BITS' FAILED. |
| 385 |
# |
| 386 |
CREATE TABLE C ( |
| 387 |
col_int_1 INT, |
| 388 |
col_int_2 INT GENERATED ALWAYS AS (col_int_1 + col_int_1) STORED, |
| 389 |
col_int_3 INT GENERATED ALWAYS AS (col_int_2 + col_int_1) VIRTUAL |
| 390 |
); |
| 391 |
CREATE ALGORITHM=TEMPTABLE VIEW v1 AS |
| 392 |
SELECT |
| 393 |
col_int_2 AS field1, col_int_2 AS field2, |
| 394 |
col_int_3 AS field3, col_int_3 AS field4 |
| 395 |
FROM C; |
| 396 |
SELECT * FROM v1; |
| 397 |
field1 field2 field3 field4 |
| 398 |
DROP TABLE C; |
| 399 |
DROP VIEW v1; |
| 400 |
# |
| 401 |
# Bug#21613615 GCOLS: ASSERTION FAILED: !TABLE || (!TABLE->READ_SET || BITMAP_IS_SET |
| 402 |
# |
| 403 |
CREATE TABLE t (a INT); |
| 404 |
CREATE TABLE v ( |
| 405 |
a INT, |
| 406 |
c INT, |
| 407 |
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, |
| 408 |
KEY(c,b(1))); |
| 409 |
INSERT INTO v (a,c) VALUES (1,1); |
| 410 |
EXPLAIN SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v ); |
| 411 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 412 |
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL Impossible WHERE |
| 413 |
3 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Not optimized, outer query is empty |
| 414 |
2 SUBQUERY t NULL ALL NULL NULL NULL NULL 1 100.00 NULL |
| 415 |
Warnings: |
| 416 |
Note 1003 /* select#1 */ select 1 AS `1` from `test`.`t` where false |
| 417 |
SELECT 1 FROM t WHERE ( SELECT 1 FROM t ) >=ANY( SELECT c FROM v ); |
| 418 |
1 |
| 419 |
EXPLAIN SELECT (SELECT MAX(c) FROM v); |
| 420 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 421 |
1 PRIMARY NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used |
| 422 |
2 SUBQUERY NULL NULL NULL NULL NULL NULL NULL NULL NULL Select tables optimized away |
| 423 |
Warnings: |
| 424 |
Note 1003 /* select#1 */ select (/* select#2 */ select max(`test`.`v`.`c`) from `test`.`v`) AS `(SELECT MAX(c) FROM v)` |
| 425 |
SELECT (SELECT MAX(c) FROM v); |
| 426 |
(SELECT MAX(c) FROM v) |
| 427 |
1 |
| 428 |
DROP TABLE t, v; |
| 429 |
CREATE TABLE v ( |
| 430 |
a INT, |
| 431 |
c INT, |
| 432 |
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, KEY(c,b(1))); |
| 433 |
INSERT INTO v (a,c) VALUES (1,1); |
| 434 |
SELECT MAX(c), COUNT(b) FROM v; |
| 435 |
MAX(c) COUNT(b) |
| 436 |
1 1 |
| 437 |
DROP TABLE v; |
| 438 |
CREATE TABLE v ( |
| 439 |
a INT PRIMARY KEY, |
| 440 |
b INT, KEY(b)); |
| 441 |
INSERT INTO v (a,b) VALUES (1,1); |
| 442 |
SELECT MAX(a) FROM v WHERE b=1; |
| 443 |
MAX(a) |
| 444 |
1 |
| 445 |
DROP TABLE v; |
| 446 |
# |
| 447 |
# Bug#21824519: ASSERTION IN DROP TRIGGER WHEN TABLE HAS |
| 448 |
# VIRTUAL GENERATED COLUMN |
| 449 |
# |
| 450 |
CREATE TABLE t (a INT, b INT GENERATED ALWAYS AS (a) VIRTUAL); |
| 451 |
CREATE TRIGGER tr BEFORE INSERT ON t FOR EACH ROW BEGIN END; |
| 452 |
INSERT INTO t (a) VALUES (1); |
| 453 |
SELECT * FROM t; |
| 454 |
a b |
| 455 |
1 1 |
| 456 |
DROP TRIGGER tr; |
| 457 |
SELECT * FROM t; |
| 458 |
a b |
| 459 |
1 1 |
| 460 |
CREATE FUNCTION f() RETURNS INT RETURN (SELECT COUNT(*) FROM t); |
| 461 |
SELECT f(); |
| 462 |
f() |
| 463 |
1 |
| 464 |
DROP FUNCTION f; |
| 465 |
SELECT * FROM t; |
| 466 |
a b |
| 467 |
1 1 |
| 468 |
DROP TABLE t; |
| 469 |
# |
| 470 |
# Bug#21833760 CALC_DAYNR: ASSERTION `DELSUM+(INT) Y/4-TEMP >= 0' FAILED. |
| 471 |
# |
| 472 |
CREATE TABLE C( |
| 473 |
c1 INT AUTO_INCREMENT, |
| 474 |
c8 DATETIME, |
| 475 |
c9 TIME, |
| 476 |
c11 TIME GENERATED ALWAYS AS(ADDTIME(c8,c9)) VIRTUAL, |
| 477 |
c13 TIME GENERATED ALWAYS AS(ADDTIME(c8,c11)) VIRTUAL, |
| 478 |
PRIMARY KEY(c1), |
| 479 |
UNIQUE KEY(c13) |
| 480 |
); |
| 481 |
INSERT INTO C (c8,c9) VALUES('1970-01-01',0),('1970-01-01',1); |
| 482 |
CREATE VIEW view_C AS SELECT * FROM C; |
| 483 |
SELECT /*+ NO_BNL(t1) */ t1.c13 FROM C AS t2 STRAIGHT_JOIN C AS t1 FORCE INDEX(c13); |
| 484 |
c13 |
| 485 |
00:00:00 |
| 486 |
00:00:00 |
| 487 |
00:00:01 |
| 488 |
00:00:01 |
| 489 |
SELECT DISTINCT t1.c13 FROM C AS t1, view_C AS t2; |
| 490 |
c13 |
| 491 |
00:00:00 |
| 492 |
00:00:01 |
| 493 |
DROP TABLE C; |
| 494 |
DROP VIEW view_C; |
| 495 |
# |
| 496 |
# Bug #21808680: JSON + GENERATED COLUMN CORRUPTS TABLE CACHE |
| 497 |
# MEMORY, CRASHES |
| 498 |
# |
| 499 |
CREATE TABLE t (a INT, b JSON, c TEXT GENERATED ALWAYS AS (REPEAT(a=b, 2))); |
| 500 |
INSERT INTO t (a, b) VALUES (1, '2'), (3, '3'); |
| 501 |
SELECT * FROM t; |
| 502 |
a b c |
| 503 |
1 2 00 |
| 504 |
3 3 11 |
| 505 |
DROP TABLE t; |
| 506 |
# |
| 507 |
# Bug#21810529: CRASH IN ITEM_FUNC::WALK WHEN CODE JUMPS TO GARBAGE |
| 508 |
# LOCATION |
| 509 |
# |
| 510 |
CREATE TABLE t (a TIME,b INT GENERATED ALWAYS AS (a=1) VIRTUAL); |
| 511 |
ALTER TABLE t CHANGE COLUMN q w INT; |
| 512 |
ERROR 42S22: Unknown column 'q' in 't' |
| 513 |
ALTER TABLE t CHANGE COLUMN q w INT; |
| 514 |
ERROR 42S22: Unknown column 'q' in 't' |
| 515 |
ALTER TABLE t CHANGE COLUMN q w INT; |
| 516 |
ERROR 42S22: Unknown column 'q' in 't' |
| 517 |
ALTER TABLE t CHANGE COLUMN q w INT; |
| 518 |
ERROR 42S22: Unknown column 'q' in 't' |
| 519 |
DROP TABLE t; |
| 520 |
# |
| 521 |
# Bug#21940542 TOO MUCH SPAM: INNODB: COMPUTE VIRTUAL COLUMN VALUES FAILED |
| 522 |
# |
| 523 |
CREATE TABLE t(b BLOB); |
| 524 |
ALTER TABLE t ADD COLUMN c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL; |
| 525 |
ERROR HY000: Expression of generated column 'c' cannot refer to a row value. |
| 526 |
DROP TABLE t; |
| 527 |
CREATE TABLE t(b BLOB, c INT GENERATED ALWAYS AS ((1,1)) VIRTUAL); |
| 528 |
ERROR HY000: Expression of generated column 'c' cannot refer to a row value. |
| 529 |
# |
| 530 |
# Bug#21929967 GCOLS: GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE |
| 531 |
# |
| 532 |
CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); |
| 533 |
Warnings: |
| 534 |
Warning 1287 '|| as a synonym for OR' is deprecated and will be removed in a future release. Please use OR instead |
| 535 |
SHOW CREATE TABLE t1; |
| 536 |
Table Create Table |
| 537 |
t1 CREATE TABLE `t1` ( |
| 538 |
`a` char(1) DEFAULT NULL, |
| 539 |
`b` char(1) DEFAULT NULL, |
| 540 |
`c` char(2) GENERATED ALWAYS AS (((0 <> `a`) or (0 <> `b`))) VIRTUAL |
| 541 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 542 |
INSERT INTO t1 (a,b) VALUES('1','1'); |
| 543 |
SELECT * FROM t1; |
| 544 |
a b c |
| 545 |
1 1 1 |
| 546 |
SET SQL_MODE=PIPES_AS_CONCAT; |
| 547 |
SELECT * FROM t1; |
| 548 |
a b c |
| 549 |
1 1 1 |
| 550 |
FLUSH TABLES; |
| 551 |
SELECT * FROM t1; |
| 552 |
a b c |
| 553 |
1 1 1 |
| 554 |
DROP TABLE t1; |
| 555 |
CREATE TABLE t1(a CHAR(1), b CHAR(1), c CHAR(2) AS (a || b)); |
| 556 |
SHOW CREATE TABLE t1; |
| 557 |
Table Create Table |
| 558 |
t1 CREATE TABLE `t1` ( |
| 559 |
`a` char(1) DEFAULT NULL, |
| 560 |
`b` char(1) DEFAULT NULL, |
| 561 |
`c` char(2) GENERATED ALWAYS AS (concat(`a`,`b`)) VIRTUAL |
| 562 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 563 |
INSERT INTO t1 (a,b) VALUES('1','1'); |
| 564 |
SELECT * FROM t1; |
| 565 |
a b c |
| 566 |
1 1 11 |
| 567 |
SET SQL_MODE=DEFAULT; |
| 568 |
SELECT * FROM t1; |
| 569 |
a b c |
| 570 |
1 1 11 |
| 571 |
FLUSH TABLES; |
| 572 |
SELECT * FROM t1; |
| 573 |
a b c |
| 574 |
1 1 11 |
| 575 |
DROP TABLE t1; |
| 576 |
# Bug#22018999: gcols: assertion failed: !error |
| 577 |
SET @save_old_sql_mode= @@sql_mode; |
| 578 |
SET sql_mode=""; |
| 579 |
CREATE TABLE t (a INTEGER AS (SUBSTR('','a',1))) engine=innodb; |
| 580 |
ERROR 22007: Truncated incorrect INTEGER value: 'a' |
| 581 |
CREATE TABLE t (a INTEGER) engine=innodb; |
| 582 |
ALTER TABLE t ADD b INTEGER AS (SUBSTR('','a',1)); |
| 583 |
ERROR 22007: Truncated incorrect INTEGER value: 'a' |
| 584 |
DROP TABLE t; |
| 585 |
set sql_mode= @save_old_sql_mode; |
| 586 |
# |
| 587 |
# Bug#22157531 WITH NO MORE .FRM 64K LIMIT WE CAN CREATE MASSIVE TABLES, |
| 588 |
# BUT TOO SLOWLY.. |
| 589 |
# |
| 590 |
set @s:=concat("CREATE TABLE t(a LONGBLOB GENERATED ALWAYS AS |
| 591 |
('",REPEAT("a",1024*1024),"') STORED) ENGINE=INNODB;"); |
| 592 |
PREPARE r FROM @s; |
| 593 |
EXECUTE r; |
| 594 |
DEALLOCATE PREPARE r; |
| 595 |
DROP TABLE t; |
| 596 |
# |
| 597 |
# Bug#22239803 WITH NO MORE .FRM 64K LIMIT WE CAN CREATE MASSIVE TABLES, |
| 598 |
# BUT TOO SLOW (II) |
| 599 |
# |
| 600 |
set @s:=concat("CREATE TABLE t(w INT, A INT GENERATED ALWAYS |
| 601 |
AS('",REPEAT("\\0",1024*1024),"') STORED) ENGINE=INNODB;"); |
| 602 |
PREPARE r FROM @s; |
| 603 |
EXECUTE r; |
| 604 |
DEALLOCATE PREPARE r; |
| 605 |
DROP TABLE t; |
| 606 |
# Bug#21875520 Problems with virtual column indexes |
| 607 |
CREATE TABLE t( |
| 608 |
a TIMESTAMP, |
| 609 |
b BLOB, |
| 610 |
c TIMESTAMP GENERATED ALWAYS AS (GREATEST(a, '2000-01-01 00:00:00')) VIRTUAL, |
| 611 |
UNIQUE KEY(c) |
| 612 |
); |
| 613 |
INSERT INTO t(b) VALUES (''); |
| 614 |
UPDATE t SET a='2001-01-01 00:00:00'; |
| 615 |
SELECT c FROM t; |
| 616 |
c |
| 617 |
2001-01-01 00:00:00 |
| 618 |
SELECT c, a FROM t; |
| 619 |
c a |
| 620 |
2001-01-01 00:00:00 2001-01-01 00:00:00 |
| 621 |
UPDATE t SET b='xyz'; |
| 622 |
DO (SELECT @c1:= c FROM t); |
| 623 |
Warnings: |
| 624 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 625 |
DO (SELECT (@c2:= c) - a FROM t); |
| 626 |
Warnings: |
| 627 |
Warning 1287 Setting user variables within expressions is deprecated and will be removed in a future release. Consider alternatives: 'SET variable=expression, ...', or 'SELECT expression(s) INTO variables(s)'. |
| 628 |
Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00' |
| 629 |
SELECT @c2 - @c1; |
| 630 |
@c2 - @c1 |
| 631 |
0 |
| 632 |
Warnings: |
| 633 |
Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00' |
| 634 |
Warning 1292 Truncated incorrect DOUBLE value: '2001-01-01 00:00:00' |
| 635 |
DROP TABLE t; |
| 636 |
# |
| 637 |
# Bug#22133710 GCOLS: READ UNCOMMITTED: ASSERT !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET(TA |
| 638 |
# |
| 639 |
CREATE TABLE t ( |
| 640 |
a INT, |
| 641 |
b INT GENERATED ALWAYS AS (1) VIRTUAL, |
| 642 |
c INT GENERATED ALWAYS AS (1) VIRTUAL, |
| 643 |
d INT GENERATED ALWAYS AS (1) VIRTUAL, |
| 644 |
KEY (b,d) |
| 645 |
) ENGINE=INNODB; |
| 646 |
INSERT INTO t VALUES(); |
| 647 |
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ; |
| 648 |
SELECT 1 FROM t WHERE c GROUP BY b; |
| 649 |
1 |
| 650 |
1 |
| 651 |
COMMIT; |
| 652 |
DROP TABLE t; |
| 653 |
# |
| 654 |
# bug#21982792: TOO MUCH SPAM: 'GOT ERROR 1 WHEN READING TABLE' |
| 655 |
# |
| 656 |
CREATE TABLE t ( |
| 657 |
a INT GENERATED ALWAYS AS (1) VIRTUAL NOT NULL, |
| 658 |
b DATE GENERATED ALWAYS AS (1) VIRTUAL NOT NULL, |
| 659 |
KEY(b) |
| 660 |
); |
| 661 |
SET @old_sql_mode=@@sql_mode; |
| 662 |
SET sql_mode=''; |
| 663 |
INSERT INTO t VALUES(); |
| 664 |
Warnings: |
| 665 |
Warning 1265 Data truncated for column 'b' at row 1 |
| 666 |
SET sql_mode= STRICT_ALL_TABLES; |
| 667 |
Warnings: |
| 668 |
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release. |
| 669 |
DELETE d2 FROM t AS d1, t AS d2 WHERE d1.a = d2.b; |
| 670 |
ERROR 22007: Incorrect date value: '1' for column 'b' at row 1 |
| 671 |
DROP TABLE t; |
| 672 |
SET sql_mode=@old_sql_mode; |
| 673 |
CREATE TABLE t ( |
| 674 |
a INT, |
| 675 |
b INT GENERATED ALWAYS AS (1), |
| 676 |
KEY i(a,b) |
| 677 |
); |
| 678 |
INSERT INTO t (a) VALUES(1), (2); |
| 679 |
SELECT 1 FROM t a RIGHT JOIN t USING(b) |
| 680 |
WHERE 1 NOT LIKE(SELECT a FROM t WHERE a.b); |
| 681 |
ERROR 21000: Subquery returns more than 1 row |
| 682 |
DROP TABLE t; |
| 683 |
# Bug#22748217 gcols: assert in Item_func_in::resolve_type |
| 684 |
SET @old_sql_mode= @@sql_mode; |
| 685 |
SET sql_mode=""; |
| 686 |
CREATE TABLE t (a TIMESTAMP AS (0) VIRTUAL) engine=innodb; |
| 687 |
ALTER TABLE t ADD COLUMN b JSON AS (a NOT IN (1,1)) STORED; |
| 688 |
ERROR 22007: Incorrect datetime value: '1' for column 'a' at row 1 |
| 689 |
DROP TABLE t; |
| 690 |
SET sql_mode= @old_sql_mode; |
| 691 |
# |
| 692 |
# Bug#22987899: ALTER TABLE MODIFY COLUMN DOES NOT SET |
| 693 |
# ALTER_CHANGE_COLUMN_DEFAULT FLAG |
| 694 |
# |
| 695 |
CREATE TABLE t1( |
| 696 |
a INTEGER DEFAULT 5, |
| 697 |
b INTEGER NOT NULL, |
| 698 |
gc1 INTEGER AS (a + DEFAULT(a)) VIRTUAL, |
| 699 |
gc2 INTEGER AS (a + DEFAULT(a)) STORED, |
| 700 |
KEY k1(gc1) |
| 701 |
); |
| 702 |
INSERT INTO t1(a, b) VALUES (1 ,1); |
| 703 |
SELECT * FROM t1; |
| 704 |
a b gc1 gc2 |
| 705 |
1 1 6 6 |
| 706 |
SELECT gc1 FROM t1; |
| 707 |
gc1 |
| 708 |
6 |
| 709 |
ALTER TABLE t1 MODIFY COLUMN a INT DEFAULT 6; |
| 710 |
affected rows: 1 |
| 711 |
info: Records: 1 Duplicates: 0 Warnings: 0 |
| 712 |
SELECT * FROM t1; |
| 713 |
a b gc1 gc2 |
| 714 |
1 1 7 7 |
| 715 |
SELECT gc1 FROM t1; |
| 716 |
gc1 |
| 717 |
7 |
| 718 |
ALTER TABLE t1 MODIFY COLUMN b INTEGER NULL; |
| 719 |
affected rows: 0 |
| 720 |
info: Records: 0 Duplicates: 0 Warnings: 0 |
| 721 |
SELECT * FROM t1; |
| 722 |
a b gc1 gc2 |
| 723 |
1 1 7 7 |
| 724 |
SELECT gc1 FROM t1; |
| 725 |
gc1 |
| 726 |
7 |
| 727 |
DROP TABLE t1; |
| 728 |
# |
| 729 |
# Bug#22991924 GCOLS: UTF16 STRING OPERATIONS GIVE DOUBLED \0\0 STRINGS |
| 730 |
# ON EACH TABLE REBUILD |
| 731 |
# |
| 732 |
CREATE TABLE t1 ( |
| 733 |
a TEXT CHARSET utf16, |
| 734 |
b BLOB GENERATED ALWAYS AS ( concat( 'ABC', a ) ) VIRTUAL |
| 735 |
); |
| 736 |
# Results change due to bug fix. |
| 737 |
INSERT INTO t1 ( a ) VALUES ( 'abc' ); |
| 738 |
SHOW CREATE TABLE t1; |
| 739 |
Table Create Table |
| 740 |
t1 CREATE TABLE `t1` ( |
| 741 |
`a` text CHARACTER SET utf16 COLLATE utf16_general_ci, |
| 742 |
`b` blob GENERATED ALWAYS AS (concat(_utf16'\0A\0B\0C',`a`)) VIRTUAL |
| 743 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 744 |
SELECT HEX(b) FROM t1; |
| 745 |
HEX(b) |
| 746 |
004100420043006100620063 |
| 747 |
OPTIMIZE TABLE t1; |
| 748 |
Table Op Msg_type Msg_text |
| 749 |
test.t1 optimize note Table does not support optimize, doing recreate + analyze instead |
| 750 |
test.t1 optimize status OK |
| 751 |
SELECT HEX(b) FROM t1; |
| 752 |
HEX(b) |
| 753 |
004100420043006100620063 |
| 754 |
SHOW CREATE TABLE t1; |
| 755 |
Table Create Table |
| 756 |
t1 CREATE TABLE `t1` ( |
| 757 |
`a` text CHARACTER SET utf16 COLLATE utf16_general_ci, |
| 758 |
`b` blob GENERATED ALWAYS AS (concat(_utf16'\0A\0B\0C',`a`)) VIRTUAL |
| 759 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 760 |
SELECT |
| 761 |
length( convert( b USING utf8 ) ), |
| 762 |
char_length ( convert( b USING utf8 ) ) |
| 763 |
FROM t1; |
| 764 |
length( convert( b USING utf8 ) ) char_length ( convert( b USING utf8 ) ) |
| 765 |
12 12 |
| 766 |
Warnings: |
| 767 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 768 |
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. |
| 769 |
SELECT |
| 770 |
length( convert( b USING utf16 ) ), |
| 771 |
char_length ( convert( b USING utf16 ) ) |
| 772 |
FROM t1; |
| 773 |
length( convert( b USING utf16 ) ) char_length ( convert( b USING utf16 ) ) |
| 774 |
12 6 |
| 775 |
SELECT |
| 776 |
length( convert( b USING latin1 ) ), |
| 777 |
char_length ( convert( b USING latin1 ) ) |
| 778 |
FROM t1; |
| 779 |
length( convert( b USING latin1 ) ) char_length ( convert( b USING latin1 ) ) |
| 780 |
12 12 |
| 781 |
DROP TABLE t1; |
| 782 |
# |
| 783 |
# Bug#25615803: ASSERT IN SQL/FIELD.CC:... |
| 784 |
# VIRTUAL STRING* FIELD_VARSTRING::VAL_STR |
| 785 |
# |
| 786 |
CREATE TABLE t1 ( |
| 787 |
a INT, |
| 788 |
b INT, |
| 789 |
c INT, |
| 790 |
d INT GENERATED ALWAYS AS ( b + 1 ) STORED |
| 791 |
) |
| 792 |
PARTITION BY RANGE( a ) ( PARTITION p0 VALUES LESS THAN (1) ); |
| 793 |
INSERT INTO t1 ( b ) VALUES ( 1 ); |
| 794 |
UPDATE t1 SET c = d; |
| 795 |
DROP TABLE t1; |
| 796 |
# |
| 797 |
# Bug #25793677 INNODB: FAILING ASSERTION: CLUST_TEMPL_FOR_SEC || LEN .... |
| 798 |
# |
| 799 |
CREATE TABLE v ( |
| 800 |
a INT, |
| 801 |
c INT, |
| 802 |
b CHAR(2) GENERATED ALWAYS AS (a IN (1)) VIRTUAL, |
| 803 |
KEY(c,b(1))) charset utf8mb4; |
| 804 |
INSERT INTO v (a,c) VALUES (1,1); |
| 805 |
SELECT (SELECT MAX(c) FROM v); |
| 806 |
(SELECT MAX(c) FROM v) |
| 807 |
1 |
| 808 |
DROP TABLE v; |
| 809 |
# Bug#23021693 gcol:assertion '!table->in_use->is_error()' failed |
| 810 |
CREATE TABLE t1 ( |
| 811 |
pk INTEGER AUTO_INCREMENT, |
| 812 |
c1 INTEGER, |
| 813 |
gc1 INTEGER GENERATED ALWAYS AS (c1+c1) VIRTUAL, |
| 814 |
gc2 INTEGER GENERATED ALWAYS AS (gc1 + c1) VIRTUAL, |
| 815 |
c2 VARCHAR(5), |
| 816 |
gc3 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(c2, c2)) VIRTUAL, |
| 817 |
gc4 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(gc3, 'x')) VIRTUAL, |
| 818 |
PRIMARY KEY (pk DESC), |
| 819 |
KEY(gc1), |
| 820 |
KEY(gc3), |
| 821 |
UNIQUE KEY (gc1, gc3), |
| 822 |
UNIQUE KEY (gc2, gc4) |
| 823 |
) ENGINE=InnoDB; |
| 824 |
INSERT INTO t1 (c1, c2) VALUES (3, 'cek'),(3,'eks'); |
| 825 |
CREATE TABLE t2 ( |
| 826 |
pk INTEGER AUTO_INCREMENT, |
| 827 |
c1 INTEGER, |
| 828 |
gc1 INTEGER GENERATED ALWAYS AS (c1 + c1) VIRTUAL, |
| 829 |
gc2 INTEGER GENERATED ALWAYS AS (gc1 + c1) VIRTUAL, |
| 830 |
c2 VARCHAR(5), |
| 831 |
gc3 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(c2, c2)) VIRTUAL, |
| 832 |
gc4 VARCHAR(10) GENERATED ALWAYS AS (CONCAT(gc3, 'x')) VIRTUAL, |
| 833 |
PRIMARY KEY (pk DESC), |
| 834 |
KEY(gc1), |
| 835 |
KEY(gc3), |
| 836 |
UNIQUE KEY (gc1, gc3), |
| 837 |
UNIQUE KEY (gc2, gc4) |
| 838 |
) ENGINE=InnoDB; |
| 839 |
INSERT INTO t2 (c1, c2) VALUES |
| 840 |
(3, 'ksa'), (6,'sate'), (3, 'a'), (NULL, 't'), (9, 'efq'), |
| 841 |
(NULL, 'f'), (6, 'qsdk'), (63, NULL), (3, 's'), (1,NULL); |
| 842 |
DELETE outr1.* |
| 843 |
FROM t2 AS outr1 INNER JOIN t1 AS outr2 |
| 844 |
ON outr1.gc4 = outr2.gc4 |
| 845 |
WHERE outr1.pk > (SELECT innr1.gc2 AS y |
| 846 |
FROM t1 AS innr1 |
| 847 |
WHERE innr1.pk = 5 OR outr2.pk <> 7 |
| 848 |
); |
| 849 |
ERROR 21000: Subquery returns more than 1 row |
| 850 |
DROP TABLE t1, t2; |
| 851 |
# |
| 852 |
# Bug#23321196: GCOLS: ASSERTION FAILURE: |
| 853 |
# DFIELD_IS_NULL(DFIELD2) || DFIELD2->DATA |
| 854 |
# |
| 855 |
CREATE TABLE t(gc TEXT GENERATED ALWAYS AS (NULL) NOT NULL, KEY (gc(1))); |
| 856 |
INSERT IGNORE INTO t VALUES (); |
| 857 |
Warnings: |
| 858 |
Warning 1048 Column 'gc' cannot be null |
| 859 |
SELECT * FROM t; |
| 860 |
gc |
| 861 |
|
| 862 |
DELETE FROM t; |
| 863 |
SELECT * FROM t; |
| 864 |
gc |
| 865 |
DROP TABLE t; |
| 866 |
# |
| 867 |
# Bug #27041382: ASSERTION `!TABLE->IN_USE->IS_ERROR()' FAILED. |
| 868 |
# |
| 869 |
CREATE TABLE t1 (c1 VARCHAR(100), |
| 870 |
c2 VARCHAR(400) GENERATED ALWAYS AS (CONCAT(c1,c1))); |
| 871 |
CREATE TRIGGER trg1 BEFORE INSERT ON t1 |
| 872 |
FOR EACH ROW INSERT/*!INTO*/t1 VALUES (1); |
| 873 |
INSERT INTO t1 VALUES (),(); |
| 874 |
ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1' |
| 875 |
INSERT INTO t1(c1) SELECT 1 FROM DUAL; |
| 876 |
ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1' |
| 877 |
LOAD DATA INFILE '../../std_data/loaddata5.dat' |
| 878 |
INTO TABLE t1 FIELDS TERMINATED BY '' ENCLOSED BY '' (c1) ; |
| 879 |
ERROR 42000: Trigger 'trg1' has an error in its body: 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 't1 VALUES (1)' at line 1' |
| 880 |
DROP TABLE t1; |
| 881 |
# |
| 882 |
# Bug#27452082 ASSERTION FAILED: !TABLE->IN_USE->IS_ERROR() IN UPDATE_GENERATED_READ_FIELDS |
| 883 |
# |
| 884 |
CREATE TABLE t ( |
| 885 |
a INT, |
| 886 |
b INT GENERATED ALWAYS AS (MAKETIME(1,1,1)) STORED, |
| 887 |
KEY (a) |
| 888 |
); |
| 889 |
INSERT INTO t (a) VALUES (32767),(-1); |
| 890 |
SELECT * FROM t WHERE a>-19106 AND a-0x4d; |
| 891 |
ERROR 22003: BIGINT UNSIGNED value is out of range in '(`test`.`t`.`a` - 0x4d)' |
| 892 |
DROP TABLE t; |
| 893 |
# |
| 894 |
# Bug#27954073 ASSERTION FAILED: !TABLE || (!TABLE->WRITE_SET || BITMAP_IS_SET |
| 895 |
# |
| 896 |
SET BINLOG_FORMAT=STATEMENT; |
| 897 |
Warnings: |
| 898 |
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. |
| 899 |
CREATE TABLE t( |
| 900 |
a INT, |
| 901 |
b INT GENERATED ALWAYS AS (0) VIRTUAL NOT NULL, |
| 902 |
KEY (a) |
| 903 |
) ENGINE=INNODB PARTITION BY KEY(b) PARTITIONS 2; |
| 904 |
INSERT INTO t(a) VALUES(1),(1),(1),(1); |
| 905 |
DELETE FROM t WHERE a=1; |
| 906 |
DROP TABLE t; |
| 907 |
SET BINLOG_FORMAT=DEFAULT; |
| 908 |
Warnings: |
| 909 |
Warning 1287 '@@binlog_format' is deprecated and will be removed in a future release. |
| 910 |
# |
| 911 |
# Bug #28652826 THE CONTINUATION OF BUG 27968952 .. CRASH & CORRUPTION |
| 912 |
# WITH GCOLS |
| 913 |
# |
| 914 |
CREATE TABLE t( |
| 915 |
a INT NOT NULL, |
| 916 |
b INT NOT NULL, |
| 917 |
c INT NOT NULL, |
| 918 |
g MEDIUMTEXT GENERATED ALWAYS AS((a <> b)) VIRTUAL, |
| 919 |
UNIQUE KEY i0000 (a), |
| 920 |
UNIQUE KEY i0001 (g(78)) |
| 921 |
) ENGINE=INNODB; |
| 922 |
REPLACE INTO t(a, b, c) VALUES (1,8,9); |
| 923 |
REPLACE INTO t(a, b, c) VALUES (0,0,10); |
| 924 |
REPLACE INTO t(a, b, c) VALUES (4,4,11); |
| 925 |
REPLACE INTO t(a, b, c) VALUES (0,7,12); |
| 926 |
REPLACE INTO t(a, b, c) VALUES (0,0,13); |
| 927 |
SELECT * FROM t; |
| 928 |
a b c g |
| 929 |
0 0 13 0 |
| 930 |
DROP TABLE t; |
| 931 |
# |
| 932 |
# Bug #28772251 "SWAPPING COLUMN BY ALTER TABLE RENAME COLUMN BREAKS |
| 933 |
# STORED GENERATED COLUMN". |
| 934 |
# |
| 935 |
# |
| 936 |
# 0) Original test case. |
| 937 |
# |
| 938 |
CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 1) STORED); |
| 939 |
SHOW CREATE TABLE t1; |
| 940 |
Table Create Table |
| 941 |
t1 CREATE TABLE `t1` ( |
| 942 |
`a` int DEFAULT NULL, |
| 943 |
`b` int DEFAULT NULL, |
| 944 |
`gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED |
| 945 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 946 |
INSERT INTO t1 (a, b) VALUES (1, 2); |
| 947 |
SELECT * FROM t1; |
| 948 |
a b gcol |
| 949 |
1 2 2 |
| 950 |
# We block renaming of columns on which generated columns depend. |
| 951 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a; |
| 952 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 953 |
# However, it is OK to rename such column in dependent expression |
| 954 |
# is redefined in the same ALTER TABLE. |
| 955 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 1) STORED; |
| 956 |
SHOW CREATE TABLE t1; |
| 957 |
Table Create Table |
| 958 |
t1 CREATE TABLE `t1` ( |
| 959 |
`b` int DEFAULT NULL, |
| 960 |
`a` int DEFAULT NULL, |
| 961 |
`gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED |
| 962 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 963 |
# Check that generated column value reflect the fact that columns |
| 964 |
# were swapped. |
| 965 |
SELECT * FROM t1; |
| 966 |
b a gcol |
| 967 |
1 2 3 |
| 968 |
# Similarly, we disallow replacement (via DROP + ADD) of columns on |
| 969 |
# which generated columns depend. Replacement is allowed if |
| 970 |
# dependent expression is updated within the same ALTER. |
| 971 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 3; |
| 972 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 973 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 3, MODIFY gcol INT AS (a + 1) STORED; |
| 974 |
SHOW CREATE TABLE t1; |
| 975 |
Table Create Table |
| 976 |
t1 CREATE TABLE `t1` ( |
| 977 |
`b` int DEFAULT NULL, |
| 978 |
`gcol` int GENERATED ALWAYS AS ((`a` + 1)) STORED, |
| 979 |
`a` int DEFAULT '3' |
| 980 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 981 |
# Check that generated column value reflect the fact that column |
| 982 |
# was replaced. |
| 983 |
SELECT * FROM t1; |
| 984 |
b gcol a |
| 985 |
1 4 3 |
| 986 |
DROP TABLE t1; |
| 987 |
# |
| 988 |
# More systematic test coverage. |
| 989 |
# |
| 990 |
# 1) Renaming columns on which generated columns depend. |
| 991 |
# |
| 992 |
# 1.1) Renaming column on which generated column depends is |
| 993 |
# prohibited (for both stored and virtual columns). |
| 994 |
CREATE TABLE t1 (a INT, gcol INT AS (a + 1) STORED); |
| 995 |
CREATE TABLE t2 (a INT, gcol INT AS (a + 1) VIRTUAL); |
| 996 |
ALTER TABLE t1 RENAME COLUMN a TO b; |
| 997 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 998 |
ALTER TABLE t2 RENAME COLUMN a TO b; |
| 999 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1000 |
# Cases when rename happens through CHANGE COLUMN are also detected. |
| 1001 |
ALTER TABLE t1 CHANGE COLUMN a b INT; |
| 1002 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1003 |
ALTER TABLE t2 CHANGE COLUMN a b INT; |
| 1004 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1005 |
# |
| 1006 |
# 1.2) It is OK to rename such columns if dependent generated column |
| 1007 |
# is dropped in the same ALTER. |
| 1008 |
ALTER TABLE t1 RENAME COLUMN a TO b, DROP COLUMN gcol; |
| 1009 |
ALTER TABLE t2 RENAME COLUMN a TO b, DROP COLUMN gcol; |
| 1010 |
# This works even if dropped column comes earlier than renamed column. |
| 1011 |
ALTER TABLE t1 ADD gcol INT AS (b + 1) STORED FIRST; |
| 1012 |
ALTER TABLE t2 ADD gcol INT AS (b + 1) VIRTUAL FIRST; |
| 1013 |
ALTER TABLE t1 DROP COLUMN gcol, RENAME COLUMN b TO c; |
| 1014 |
ALTER TABLE t2 DROP COLUMN gcol, RENAME COLUMN b TO c; |
| 1015 |
# |
| 1016 |
# 1.3) It is also OK to rename such columns if dependent generated |
| 1017 |
# column expression is updated/redefined. |
| 1018 |
ALTER TABLE t1 ADD COLUMN gcol INT AS (c + 1) STORED; |
| 1019 |
ALTER TABLE t2 ADD COLUMN gcol INT AS (c + 1) VIRTUAL; |
| 1020 |
ALTER TABLE t1 RENAME COLUMN c TO d, MODIFY gcol INT AS (d + 1) STORED; |
| 1021 |
ALTER TABLE t2 RENAME COLUMN c TO d, MODIFY gcol INT AS (d + 1) VIRTUAL; |
| 1022 |
# And again this works even if generated column comes earlier than |
| 1023 |
# renamed column. |
| 1024 |
ALTER TABLE t1 DROP COLUMN gcol, ADD COLUMN gcol INT AS (d + 1) STORED FIRST; |
| 1025 |
ALTER TABLE t2 DROP COLUMN gcol, ADD COLUMN gcol INT AS (d + 1) VIRTUAL FIRST; |
| 1026 |
ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, RENAME COLUMN d TO e; |
| 1027 |
ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, RENAME COLUMN d TO e; |
| 1028 |
# If updated expression uses wrong column name, error is reported. |
| 1029 |
# Note that error code and message are different because technically |
| 1030 |
# it is issue with new generation expression. |
| 1031 |
ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, RENAME COLUMN e TO f; |
| 1032 |
ERROR 42S22: Unknown column 'e' in 'generated column function' |
| 1033 |
ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, RENAME COLUMN e TO f; |
| 1034 |
ERROR 42S22: Unknown column 'e' in 'generated column function' |
| 1035 |
DROP TABLES t1, t2; |
| 1036 |
# |
| 1037 |
# 1.4) Complex case. Renaming of columns which causes generated |
| 1038 |
# column re-evaluation. |
| 1039 |
CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 10) STORED); |
| 1040 |
INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1041 |
CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 10) VIRTUAL, KEY(gcol)); |
| 1042 |
INSERT INTO t2 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1043 |
# We block simple renaming of columns even if dependent generation |
| 1044 |
# expression stays valid. This is intentional -- we want user to |
| 1045 |
# realize and explicitly confirm what is done (in most cases such |
| 1046 |
# rename is likely to be an error). |
| 1047 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a; |
| 1048 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1049 |
ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a; |
| 1050 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1051 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) STORED; |
| 1052 |
ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) VIRTUAL; |
| 1053 |
# Contents of generated column should be updated accordingly. |
| 1054 |
SELECT * FROM t1; |
| 1055 |
b a gcol |
| 1056 |
1 2 12 |
| 1057 |
3 4 14 |
| 1058 |
5 6 16 |
| 1059 |
SELECT * FROM t2; |
| 1060 |
b a gcol |
| 1061 |
1 2 12 |
| 1062 |
3 4 14 |
| 1063 |
5 6 16 |
| 1064 |
SELECT * FROM t2 WHERE gcol = 14; |
| 1065 |
b a gcol |
| 1066 |
3 4 14 |
| 1067 |
# Such ALTER TABLEs are not inplace/instant due to generated |
| 1068 |
# column re-evaluation. |
| 1069 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) STORED, ALGORITHM=INPLACE; |
| 1070 |
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. |
| 1071 |
ALTER TABLE t2 RENAME COLUMN a TO b, RENAME COLUMN b TO a, MODIFY gcol INT AS (a + 10) VIRTUAL, ALGORITHM=INPLACE; |
| 1072 |
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. |
| 1073 |
DROP TABLES t1, t2; |
| 1074 |
# |
| 1075 |
# 2) Renaming columns on which generated default depends. |
| 1076 |
# |
| 1077 |
# 2.1) Renaming column on which generated default depends is |
| 1078 |
# prohibited. |
| 1079 |
CREATE TABLE t1 (a INT, def INT DEFAULT (a + 1)); |
| 1080 |
ALTER TABLE t1 RENAME COLUMN a TO b; |
| 1081 |
ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed. |
| 1082 |
# Cases when rename happens through CHANGE COLUMN are also detected. |
| 1083 |
ALTER TABLE t1 CHANGE COLUMN a b INT; |
| 1084 |
ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed. |
| 1085 |
# |
| 1086 |
# 2.2) It is OK to rename such columns if dependent generated default |
| 1087 |
# is dropped: |
| 1088 |
# |
| 1089 |
# a) By dropping column |
| 1090 |
ALTER TABLE t1 RENAME COLUMN a TO b, DROP COLUMN def; |
| 1091 |
# b) By dropping default using DROP DEFAULT |
| 1092 |
ALTER TABLE t1 ADD COLUMN def INT DEFAULT (b + 1); |
| 1093 |
ALTER TABLE t1 RENAME COLUMN b TO c, ALTER COLUMN def DROP DEFAULT; |
| 1094 |
# c) By dropping default using CHANGE/MODIFY COLUMN |
| 1095 |
ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (c + 1); |
| 1096 |
ALTER TABLE t1 RENAME COLUMN c TO d, MODIFY COLUMN def INT; |
| 1097 |
# This works even if column with generated default comes earlier than |
| 1098 |
# renamed column. |
| 1099 |
ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (d + 1) FIRST; |
| 1100 |
ALTER TABLE t1 DROP COLUMN def, RENAME COLUMN d TO e; |
| 1101 |
ALTER TABLE t1 ADD COLUMN def INT DEFAULT (e + 1) FIRST; |
| 1102 |
ALTER TABLE t1 ALTER COLUMN def DROP DEFAULT, RENAME COLUMN e TO f; |
| 1103 |
ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (f + 1); |
| 1104 |
ALTER TABLE t1 MODIFY COLUMN def INT, RENAME COLUMN f TO g; |
| 1105 |
DROP TABLE t1; |
| 1106 |
# |
| 1107 |
# 2.3) It is also OK to rename such columns if dependent generated |
| 1108 |
# default value is updated/redefined: |
| 1109 |
# |
| 1110 |
# a) Using SET DEFAULT |
| 1111 |
CREATE TABLE t1 (a INT, def INT DEFAULT (a + 1)); |
| 1112 |
ALTER TABLE t1 RENAME COLUMN a TO b, ALTER COLUMN def SET DEFAULT (b + 1); |
| 1113 |
# b) Using MODIFY/CHANGE column |
| 1114 |
ALTER TABLE t1 RENAME COLUMN b TO c, MODIFY COLUMN def INT DEFAULT (c + 1); |
| 1115 |
# And again this works even if generated default comes earlier than |
| 1116 |
# renamed column. |
| 1117 |
ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (c + 1) FIRST; |
| 1118 |
ALTER TABLE t1 ALTER COLUMN def SET DEFAULT (d + 1), RENAME COLUMN c TO d; |
| 1119 |
ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (e + 1), RENAME COLUMN d TO e; |
| 1120 |
# If updated expression uses wrong column name, error is reported. |
| 1121 |
# Again, error code and message are different because technically |
| 1122 |
# it is issue with new generated default. |
| 1123 |
ALTER TABLE t1 MODIFY def INT DEFAULT (e + 1), RENAME COLUMN e TO f; |
| 1124 |
ERROR 42S22: Unknown column 'e' in 'default value expression' |
| 1125 |
DROP TABLES t1; |
| 1126 |
# |
| 1127 |
# 2.4) Complex case. Swapping of columns on which generated |
| 1128 |
# default depends. |
| 1129 |
CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a)); |
| 1130 |
INSERT INTO t1 (a, b) VALUES (1, 2); |
| 1131 |
# Similarly to the above we block simple renaming of columns even |
| 1132 |
# if dependent default expression stays valid. This is intentional |
| 1133 |
# -- we want user to realize and explicitly confirm what is done |
| 1134 |
# (in most cases such rename is likely to be an error). |
| 1135 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a; |
| 1136 |
ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed. |
| 1137 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, ALTER COLUMN def SET DEFAULT (a); |
| 1138 |
# Old rows should stay the same, new default will only affect new rows. |
| 1139 |
INSERT INTO t1 (a, b) VALUES (3, 4); |
| 1140 |
SELECT * FROM t1; |
| 1141 |
b a def |
| 1142 |
1 2 1 |
| 1143 |
4 3 3 |
| 1144 |
# Unlike for generated columns case, such ALTER TABLE can be done |
| 1145 |
# inplace, since it doesn't affect table data. |
| 1146 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, ALTER COLUMN def SET DEFAULT (a), ALGORITHM=INPLACE; |
| 1147 |
DROP TABLES t1; |
| 1148 |
# |
| 1149 |
# 3) Renaming columns on which functional index depends. |
| 1150 |
# |
| 1151 |
# 3.1) Renaming column on which functional index depends is |
| 1152 |
# prohibited. |
| 1153 |
CREATE TABLE t1 (a INT, KEY fi((a + 1))); |
| 1154 |
ALTER TABLE t1 RENAME COLUMN a TO b; |
| 1155 |
ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed. |
| 1156 |
# Cases when rename happens through CHANGE COLUMN are also detected. |
| 1157 |
ALTER TABLE t1 CHANGE COLUMN a b INT; |
| 1158 |
ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed. |
| 1159 |
# |
| 1160 |
# 3.2) It is OK to rename such columns if dependent index is dropped. |
| 1161 |
ALTER TABLE t1 RENAME COLUMN a TO b, DROP KEY fi; |
| 1162 |
# This works even if hidden column for functional index processed |
| 1163 |
# earlier than renamed column. |
| 1164 |
ALTER TABLE t1 ADD KEY fi ((b + 1)); |
| 1165 |
# Move column 'b' to the table's end first. |
| 1166 |
ALTER TABLE t1 ADD c INT; |
| 1167 |
ALTER TABLE t1 MODIFY b INT AFTER c; |
| 1168 |
ALTER TABLE t1 DROP KEY fi, RENAME COLUMN b TO d; |
| 1169 |
# |
| 1170 |
# Note that the only way to update/redefine functional index is to |
| 1171 |
# drop and re-create it. So the this section also covers the case |
| 1172 |
# when functional index is updated/redefined along with rename. |
| 1173 |
# Let us check such scenario for completeness. |
| 1174 |
ALTER TABLE t1 ADD KEY fi ((d + 1)); |
| 1175 |
ALTER TABLE t1 RENAME COLUMN d TO e, DROP KEY fi, ADD KEY fi ((e + 1)); |
| 1176 |
# If updated expression uses wrong column name, error is reported. |
| 1177 |
# Again, error code and message are different because technically |
| 1178 |
# it is issue with new functional index. |
| 1179 |
ALTER TABLE t1 RENAME COLUMN e TO f, DROP KEY fi, ADD KEY fi ((e + 1)); |
| 1180 |
ERROR 42S22: Unknown column 'e' in 'functional index' |
| 1181 |
DROP TABLE t1; |
| 1182 |
# |
| 1183 |
# 3.3) Complex case. Swapping of columns on which functional index |
| 1184 |
# depends. |
| 1185 |
CREATE TABLE t1 (a INT, b INT, KEY fi ((a + 10))); |
| 1186 |
INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1187 |
SELECT * FROM t1 WHERE a + 10 = 13; |
| 1188 |
a b |
| 1189 |
3 4 |
| 1190 |
# We block simple renaming of columns even if dependent functional |
| 1191 |
# index stays valid. This is intentional -- we want user to realize |
| 1192 |
# and explicitly confirm what is done (in most cases such rename is |
| 1193 |
# likely to be an error). |
| 1194 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a; |
| 1195 |
ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed. |
| 1196 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, DROP KEY fi, ADD KEY fi((a + 10)); |
| 1197 |
# Contents of functional index/hidden generated column should be |
| 1198 |
# updated accordingly. |
| 1199 |
SELECT * FROM t1 WHERE a + 10 = 14; |
| 1200 |
b a |
| 1201 |
3 4 |
| 1202 |
# Such ALTER TABLEs are not inplace/instant due to re-evaluation |
| 1203 |
# (and other reasons). |
| 1204 |
ALTER TABLE t1 RENAME COLUMN a TO b, RENAME COLUMN b TO a, DROP KEY fi, ADD KEY fi((a + 10)), ALGORITHM=INPLACE; |
| 1205 |
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY. |
| 1206 |
DROP TABLES t1; |
| 1207 |
# |
| 1208 |
# 4) Dropping columns on which generated columns depend. |
| 1209 |
# |
| 1210 |
# 4.1) Dropping column on which generated column depends is |
| 1211 |
# prohibited (for both stored and virtual columns). |
| 1212 |
CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 1) STORED); |
| 1213 |
CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 1) VIRTUAL); |
| 1214 |
ALTER TABLE t1 DROP COLUMN a; |
| 1215 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1216 |
ALTER TABLE t2 DROP COLUMN a; |
| 1217 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1218 |
# |
| 1219 |
# 4.2) It is OK to drop such columns if dependent generated column |
| 1220 |
# is dropped in the same ALTER. |
| 1221 |
ALTER TABLE t1 DROP COLUMN a, DROP COLUMN gcol; |
| 1222 |
ALTER TABLE t2 DROP COLUMN a, DROP COLUMN gcol; |
| 1223 |
# This works even if generated column is processed earlier than |
| 1224 |
# base column. |
| 1225 |
ALTER TABLE t1 ADD gcol INT AS (a + 1) STORED FIRST, ADD COLUMN a INT; |
| 1226 |
ALTER TABLE t1 DROP COLUMN gcol, DROP COLUMN a; |
| 1227 |
# |
| 1228 |
# 4.3) It is also OK to drop such columns if dependent generated |
| 1229 |
# column expression is updated/redefined. |
| 1230 |
ALTER TABLE t1 ADD COLUMN gcol INT AS (a + 1) STORED, ADD COLUMN a INT; |
| 1231 |
ALTER TABLE t2 ADD COLUMN gcol INT AS (a + 1) VIRTUAL, ADD COLUMN a INT; |
| 1232 |
ALTER TABLE t1 DROP COLUMN a, MODIFY gcol INT AS (b + 1) STORED; |
| 1233 |
ALTER TABLE t2 DROP COLUMN a, MODIFY gcol INT AS (b + 1) VIRTUAL; |
| 1234 |
# And again this works even if generated column is processed earlier |
| 1235 |
# than base column. |
| 1236 |
ALTER TABLE t1 DROP COLUMN gcol, ADD COLUMN gcol INT AS (a + 1) STORED FIRST, ADD COLUMN a INT; |
| 1237 |
ALTER TABLE t1 MODIFY gcol INT AS (b + 1) STORED, DROP COLUMN a; |
| 1238 |
ALTER TABLE t2 DROP COLUMN gcol, ADD COLUMN gcol INT AS (a + 1) VIRTUAL FIRST, ADD COLUMN a INT; |
| 1239 |
ALTER TABLE t2 MODIFY gcol INT AS (b + 1) VIRTUAL, DROP COLUMN a; |
| 1240 |
# If updated expression uses wrong column name, error is reported. |
| 1241 |
# Note that error code and message are different because technically |
| 1242 |
# it is issue with new generation expression. |
| 1243 |
ALTER TABLE t1 ADD COLUMN c INT; |
| 1244 |
ALTER TABLE t2 ADD COLUMN c INT; |
| 1245 |
ALTER TABLE t1 MODIFY gcol INT AS (e + 1) STORED, DROP COLUMN b; |
| 1246 |
ERROR 42S22: Unknown column 'e' in 'generated column function' |
| 1247 |
ALTER TABLE t2 MODIFY gcol INT AS (e + 1) VIRTUAL, DROP COLUMN b; |
| 1248 |
ERROR 42S22: Unknown column 'e' in 'generated column function' |
| 1249 |
DROP TABLES t1, t2; |
| 1250 |
# |
| 1251 |
# 4.4) Complex case. Replacement (dropping and addition) of columns |
| 1252 |
# which causes generated column re-evaluation. |
| 1253 |
CREATE TABLE t1 (a INT, b INT, gcol INT AS (a + 10) STORED); |
| 1254 |
INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1255 |
CREATE TABLE t2 (a INT, b INT, gcol INT AS (a + 10) VIRTUAL, KEY(gcol)); |
| 1256 |
INSERT INTO t2 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1257 |
# We block simple replacement of columns even if dependent generation |
| 1258 |
# expression stays valid. This is intentional -- we want user to |
| 1259 |
# realize and explicitly confirm what is done (in most cases this |
| 1260 |
# is likely to be an error). |
| 1261 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0; |
| 1262 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1263 |
ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0; |
| 1264 |
ERROR HY000: Column 'a' has a generated column dependency. |
| 1265 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) STORED; |
| 1266 |
ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) VIRTUAL; |
| 1267 |
# Contents of generated column should be updated accordingly. |
| 1268 |
SELECT * FROM t1; |
| 1269 |
b gcol a |
| 1270 |
2 10 0 |
| 1271 |
4 10 0 |
| 1272 |
6 10 0 |
| 1273 |
SELECT * FROM t2; |
| 1274 |
b gcol a |
| 1275 |
2 10 0 |
| 1276 |
4 10 0 |
| 1277 |
6 10 0 |
| 1278 |
SELECT * FROM t2 WHERE gcol = 10; |
| 1279 |
b gcol a |
| 1280 |
2 10 0 |
| 1281 |
4 10 0 |
| 1282 |
6 10 0 |
| 1283 |
# Such ALTER TABLEs are not inplace/instant due to generated |
| 1284 |
# column re-evaluation. |
| 1285 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) STORED, ALGORITHM=INPLACE; |
| 1286 |
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. |
| 1287 |
ALTER TABLE t2 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, MODIFY gcol INT AS (a + 10) VIRTUAL, ALGORITHM=INPLACE; |
| 1288 |
ERROR 0A000: ALGORITHM=INPLACE is not supported for this operation. Try ALGORITHM=COPY. |
| 1289 |
DROP TABLES t1, t2; |
| 1290 |
# |
| 1291 |
# 5) Dropping column on which generated default depends. |
| 1292 |
# |
| 1293 |
# 5.1) Dropping column on which generated default depends is |
| 1294 |
# prohibited. |
| 1295 |
CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a + 1)); |
| 1296 |
ALTER TABLE t1 DROP COLUMN a; |
| 1297 |
ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed. |
| 1298 |
# |
| 1299 |
# 5.2) It is OK to drop such columns if dependent generated default |
| 1300 |
# is dropped: |
| 1301 |
# |
| 1302 |
# a) By dropping column |
| 1303 |
ALTER TABLE t1 DROP COLUMN a, DROP COLUMN def; |
| 1304 |
# b) By dropping default using DROP DEFAULT |
| 1305 |
ALTER TABLE t1 ADD COLUMN a INT, ADD COLUMN def INT DEFAULT (a + 1); |
| 1306 |
ALTER TABLE t1 DROP COLUMN a, ALTER COLUMN def DROP DEFAULT; |
| 1307 |
# c) By dropping default using CHANGE/MODIFY COLUMN |
| 1308 |
ALTER TABLE t1 ADD COLUMN a INT, MODIFY COLUMN def INT DEFAULT (a + 1); |
| 1309 |
ALTER TABLE t1 DROP COLUMN a, MODIFY COLUMN def INT; |
| 1310 |
# This works even if column with generated default comes earlier than |
| 1311 |
# dropped column. |
| 1312 |
ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT; |
| 1313 |
ALTER TABLE t1 DROP COLUMN def, DROP COLUMN a; |
| 1314 |
ALTER TABLE t1 ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT; |
| 1315 |
ALTER TABLE t1 ALTER COLUMN def DROP DEFAULT, DROP COLUMN a; |
| 1316 |
ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (a + 1), ADD COLUMN a INT; |
| 1317 |
ALTER TABLE t1 MODIFY COLUMN def INT, DROP COLUMN a; |
| 1318 |
DROP TABLE t1; |
| 1319 |
# |
| 1320 |
# 5.3) It is also OK to drop such columns if dependent generated |
| 1321 |
# default value is updated/redefined: |
| 1322 |
# |
| 1323 |
# a) Using SET DEFAULT |
| 1324 |
CREATE TABLE t1 (a INT, b INT, c INT, d INT, def INT DEFAULT (a + 1)); |
| 1325 |
ALTER TABLE t1 DROP COLUMN a, ALTER COLUMN def SET DEFAULT (b + 1); |
| 1326 |
# b) Using MODIFY/CHANGE column |
| 1327 |
ALTER TABLE t1 DROP COLUMN b, MODIFY COLUMN def INT DEFAULT (c + 1); |
| 1328 |
# And again this works even if generated default comes earlier than |
| 1329 |
# dropped column. |
| 1330 |
ALTER TABLE t1 DROP COLUMN def, ADD COLUMN def INT DEFAULT (a + 1) FIRST, ADD COLUMN a INT, ADD COLUMN b INT; |
| 1331 |
ALTER TABLE t1 ALTER COLUMN def SET DEFAULT (b + 1), DROP COLUMN a; |
| 1332 |
ALTER TABLE t1 MODIFY COLUMN def INT DEFAULT (c + 1), DROP COLUMN b; |
| 1333 |
# If updated expression uses wrong column name, error is reported. |
| 1334 |
# Again, error code and message are different because technically |
| 1335 |
# it is issue with new generated default. |
| 1336 |
ALTER TABLE t1 MODIFY def INT DEFAULT (e + 1), DROP COLUMN c; |
| 1337 |
ERROR 42S22: Unknown column 'e' in 'default value expression' |
| 1338 |
DROP TABLES t1; |
| 1339 |
# |
| 1340 |
# 5.4) Complex case. Replacement of column on which generated |
| 1341 |
# default depends. |
| 1342 |
CREATE TABLE t1 (a INT, b INT, def INT DEFAULT (a)); |
| 1343 |
INSERT INTO t1 (a, b) VALUES (1, 2); |
| 1344 |
# Similarly to the above we block simple replacement of columns even |
| 1345 |
# if dependent default expression stays valid. This is intentional |
| 1346 |
# -- we want user to realize and explicitly confirm what is done |
| 1347 |
# (in most cases such replacement is likely to be an error). |
| 1348 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT; |
| 1349 |
ERROR HY000: Column 'a' of table 't1' has a default value expression dependency and cannot be dropped or renamed. |
| 1350 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 10, ALTER COLUMN def SET DEFAULT (a); |
| 1351 |
# Old rows should stay the same, new generated default will only affect new rows. |
| 1352 |
INSERT INTO t1 (a, b) VALUES (3, 4); |
| 1353 |
SELECT * FROM t1; |
| 1354 |
b def a |
| 1355 |
2 1 10 |
| 1356 |
4 3 3 |
| 1357 |
# Unlike for generated columns case, such ALTER TABLE can be done |
| 1358 |
# inplace, since it doesn't affect table data. |
| 1359 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 42, ALTER COLUMN def SET DEFAULT (a), ALGORITHM=INPLACE; |
| 1360 |
DROP TABLES t1; |
| 1361 |
# |
| 1362 |
# 6) Dropping column on which functional index depends. |
| 1363 |
# |
| 1364 |
# 6.1) Dropping column on which functional index depends is |
| 1365 |
# prohibited. |
| 1366 |
CREATE TABLE t1 (a INT, b INT, KEY fi((a + 1))); |
| 1367 |
ALTER TABLE t1 DROP COLUMN a; |
| 1368 |
ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed. |
| 1369 |
# |
| 1370 |
# 6.2) It is OK to drop such column if dependent index is dropped. |
| 1371 |
ALTER TABLE t1 DROP COLUMN a, DROP KEY fi; |
| 1372 |
# This works even if hidden column for functional index processed |
| 1373 |
# earlier than dropped column. |
| 1374 |
ALTER TABLE t1 ADD KEY fi ((b + 1)); |
| 1375 |
# Move column 'b' to the table's end first. |
| 1376 |
ALTER TABLE t1 ADD c INT; |
| 1377 |
ALTER TABLE t1 MODIFY b INT AFTER c; |
| 1378 |
ALTER TABLE t1 DROP KEY fi, DROP COLUMN b; |
| 1379 |
# |
| 1380 |
# Note that the only way to update/redefine functional index is to |
| 1381 |
# drop and re-create it. So the this section also covers the case |
| 1382 |
# when functional index is updated/redefined along with drop of |
| 1383 |
# base column. Let us check such scenario for completeness. |
| 1384 |
ALTER TABLE t1 ADD KEY fi ((c + 1)), ADD COLUMN d INT, ADD COLUMN e INT; |
| 1385 |
ALTER TABLE t1 DROP COLUMN c, DROP KEY fi, ADD KEY fi ((d + 1)); |
| 1386 |
# If updated expression uses wrong column name, error is reported. |
| 1387 |
# Again, error code and message are different because technically |
| 1388 |
# it is issue with new functional index. |
| 1389 |
ALTER TABLE t1 DROP COLUMN d, DROP KEY fi, ADD KEY fi ((f + 1)); |
| 1390 |
ERROR 42S22: Unknown column 'f' in 'functional index' |
| 1391 |
DROP TABLE t1; |
| 1392 |
# |
| 1393 |
# 6.3) Complex case. Replacing column on which functional index |
| 1394 |
# depends. |
| 1395 |
CREATE TABLE t1 (a INT, b INT, KEY fi ((a + 10))); |
| 1396 |
INSERT INTO t1 (a, b) VALUES (1, 2), (3, 4), (5, 6); |
| 1397 |
SELECT * FROM t1 WHERE a + 10 = 13; |
| 1398 |
a b |
| 1399 |
3 4 |
| 1400 |
# We block simple replacement of column even if dependent functional |
| 1401 |
# index stays valid. This is intentional -- we want user to realize |
| 1402 |
# and explicitly confirm what is done (in most cases such replacement |
| 1403 |
# is likely to be an error). |
| 1404 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0; |
| 1405 |
ERROR HY000: Column 'a' has a functional index dependency and cannot be dropped or renamed. |
| 1406 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, DROP KEY fi, ADD KEY fi((a + 10)); |
| 1407 |
# Contents of functional index/hidden generated column should be |
| 1408 |
# updated accordingly. |
| 1409 |
SELECT * FROM t1 WHERE a + 10 = 10; |
| 1410 |
b a |
| 1411 |
2 0 |
| 1412 |
4 0 |
| 1413 |
6 0 |
| 1414 |
# Such ALTER TABLEs are not inplace/instant due to re-evaluation |
| 1415 |
# (and other reasons). |
| 1416 |
ALTER TABLE t1 DROP COLUMN a, ADD COLUMN a INT DEFAULT 0, DROP KEY fi, ADD KEY fi((a + 10)), ALGORITHM=INPLACE; |
| 1417 |
ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY. |
| 1418 |
DROP TABLES t1; |
| 1419 |
# |
| 1420 |
# Bug #30417361: MAKE THE SORTING CODE INDEPENDENT OF QEP_TABS |
| 1421 |
# |
| 1422 |
CREATE TABLE t1 (i INTEGER, a INTEGER, vgc INTEGER GENERATED ALWAYS AS (-a) VIRTUAL); |
| 1423 |
INSERT INTO t1 (i, a) VALUES (1, 1), (2, 2); |
| 1424 |
SELECT i FROM t1 ORDER BY vgc; |
| 1425 |
i |
| 1426 |
2 |
| 1427 |
1 |
| 1428 |
DROP TABLE t1; |
| 1429 |
# |
| 1430 |
# Bug #30664660 GCOLS, CRASH AND CORRUPTIONS WITH DELETE IGNORE + JSON |
| 1431 |
# |
| 1432 |
CREATE TABLE t ( |
| 1433 |
a JSON NOT NULL, |
| 1434 |
b TIMESTAMP GENERATED ALWAYS AS (a) VIRTUAL NOT NULL, |
| 1435 |
KEY (b) |
| 1436 |
) ENGINE=INNODB; |
| 1437 |
INSERT IGNORE INTO t(a) VALUES ('{}'); |
| 1438 |
Warnings: |
| 1439 |
Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1 |
| 1440 |
Warning 1264 Out of range value for column 'b' at row 1 |
| 1441 |
DELETE IGNORE FROM t; |
| 1442 |
Warnings: |
| 1443 |
Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1 |
| 1444 |
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'b' at row 1 |
| 1445 |
Warning 3156 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column a at row 1 |
| 1446 |
Warning 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'b' at row 1 |
| 1447 |
CHECK TABLE t; |
| 1448 |
Table Op Msg_type Msg_text |
| 1449 |
test.t check status OK |
| 1450 |
DROP TABLE t; |
| 1451 |
# |
| 1452 |
# Bug #31463511: NATURAL JOIN RETURNS WRONG RESULT WHEN EXPRESSION INDEX IS USED |
| 1453 |
# |
| 1454 |
CREATE TABLE t1 (a INTEGER); |
| 1455 |
CREATE TABLE t2 (a INTEGER); |
| 1456 |
CREATE INDEX idx ON t1 ((a+1)); |
| 1457 |
CREATE INDEX idx ON t2 ((a+2)); |
| 1458 |
INSERT INTO t1 VALUES (1); |
| 1459 |
INSERT INTO t2 VALUES (1); |
| 1460 |
SELECT * FROM t1 NATURAL JOIN t2; |
| 1461 |
a |
| 1462 |
1 |
| 1463 |
DROP TABLE t1, t2; |
| 1464 |
# |
| 1465 |
# Bug#31982292: ITEM_JSON_FUNC.CC:1406: BOOL SQL_SCALAR_TO_JSON(ITEM*, |
| 1466 |
# CONST CHAR*, STRING*, STR |
| 1467 |
# |
| 1468 |
CREATE TABLE t(x VARCHAR(10), |
| 1469 |
y INT GENERATED ALWAYS AS (x), |
| 1470 |
z JSON GENERATED ALWAYS AS (JSON_ARRAY(x, y))); |
| 1471 |
INSERT INTO t (x) VALUES ('abc'); |
| 1472 |
ERROR HY000: Incorrect integer value: 'abc' for column 'y' at row 1 |
| 1473 |
DROP TABLE t; |
| 1474 |
CREATE TABLE t(x VARCHAR(10)); |
| 1475 |
INSERT INTO t VALUES (''); |
| 1476 |
ALTER TABLE t |
| 1477 |
ADD COLUMN g1 JSON GENERATED ALWAYS AS (x), |
| 1478 |
ADD COLUMN g2 JSON GENERATED ALWAYS AS (JSON_ARRAY(x)); |
| 1479 |
SELECT * FROM t; |
| 1480 |
ERROR 22032: Invalid JSON text: "The document is empty." at position 0 in value for column 't.g1'. |
| 1481 |
DROP TABLE t; |
| 1482 |
# |
| 1483 |
# Bug#32234773: UPDATE_GENERATED_READ_FIELDS: |
| 1484 |
# ASSERTION `!TABLE->IN_USE->IS_ERROR()' FAILED |
| 1485 |
# |
| 1486 |
CREATE TABLE t ( |
| 1487 |
id INT PRIMARY KEY AUTO_INCREMENT, |
| 1488 |
i INT, |
| 1489 |
gc INT GENERATED ALWAYS AS (1) STORED, |
| 1490 |
KEY (i) |
| 1491 |
); |
| 1492 |
INSERT INTO t(i) VALUES (5853267), (32766); |
| 1493 |
SELECT * FROM t WHERE i > 123 AND i * FROM_UNIXTIME(1536999224); |
| 1494 |
ERROR 22003: BIGINT value is out of range in '(`test`.`t`.`i` * <cache>(from_unixtime(1536999224)))' |
| 1495 |
DROP TABLE t; |
| 1496 |
# Bug#32824978: Generated columns inconsistency |
| 1497 |
CREATE TABLE t1 ( |
| 1498 |
id INTEGER NOT NULL, |
| 1499 |
mac BIGINT UNSIGNED DEFAULT NULL, |
| 1500 |
str CHAR(12) AS (LPAD(CONVERT(HEX(mac) USING utf8mb4), 12,_utf8mb4'0')) VIRTUAL, |
| 1501 |
separated CHAR(17) AS (INSERT(INSERT(INSERT(INSERT(INSERT(str, 11, 0, _utf8mb4':'), 9, 0, _utf8mb4':'), 7, 0, _utf8mb4':'), 5, 0, _utf8mb4':'), 3, 0, _utf8mb4':')) VIRTUAL, |
| 1502 |
PRIMARY KEY (id) |
| 1503 |
); |
| 1504 |
INSERT INTO t1 (id, mac) VALUES |
| 1505 |
(1, 10), (2, 564685488), (3, NULL), (4, 684756486); |
| 1506 |
SELECT * FROM t1; |
| 1507 |
id mac str separated |
| 1508 |
1 10 00000000000A 00:00:00:00:00:0A |
| 1509 |
2 564685488 000021A86AB0 00:00:21:A8:6A:B0 |
| 1510 |
3 NULL NULL NULL |
| 1511 |
4 684756486 000028D08E06 00:00:28:D0:8E:06 |
| 1512 |
DROP TABLE t1; |
| 1513 |
# Bug#32427727: Functional index ignored by SELECT query from |
| 1514 |
# within stored function |
| 1515 |
CREATE TABLE t1 |
| 1516 |
(pk BIGINT AUTO_INCREMENT PRIMARY KEY, |
| 1517 |
n1 varchar(30), |
| 1518 |
n2 varchar(30), |
| 1519 |
n3 varchar(30), |
| 1520 |
index n12((concat(n1, n2))) |
| 1521 |
); |
| 1522 |
ANALYZE TABLE t1; |
| 1523 |
Table Op Msg_type Msg_text |
| 1524 |
test.t1 analyze status OK |
| 1525 |
CREATE PROCEDURE p1 (str VARCHAR(60)) |
| 1526 |
BEGIN |
| 1527 |
EXPLAIN |
| 1528 |
SELECT n3, COUNT(*) as 'count' |
| 1529 |
FROM t1 |
| 1530 |
WHERE CONCAT(n1,n2) = str |
| 1531 |
GROUP BY n3; |
| 1532 |
SELECT n3, COUNT(*) as 'count' |
| 1533 |
FROM t1 |
| 1534 |
WHERE CONCAT(n1,n2) = str |
| 1535 |
GROUP BY n3; |
| 1536 |
END | |
| 1537 |
CALL p1('str'); |
| 1538 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1539 |
1 SIMPLE t1 NULL ref n12 n12 243 const 1 100.00 Using where; Using temporary |
| 1540 |
n3 count |
| 1541 |
DROP PROCEDURE p1; |
| 1542 |
DROP TABLE t1; |
| 1543 |
|
| 1544 |
# VARBINARY as primary key |
| 1545 |
|
| 1546 |
CREATE TABLE t1 ( |
| 1547 |
doc JSON, |
| 1548 |
id VARBINARY(32) GENERATED ALWAYS AS |
| 1549 |
(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY); |
| 1550 |
INSERT INTO t1 (doc) VALUES |
| 1551 |
(JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar')); |
| 1552 |
ANALYZE TABLE t1; |
| 1553 |
Table Op Msg_type Msg_text |
| 1554 |
test.t1 analyze status OK |
| 1555 |
SET @a = '2'; |
| 1556 |
SET @num = 2; |
| 1557 |
SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1558 |
doc id |
| 1559 |
{"id": "2", "name": "bar"} 2 |
| 1560 |
explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1561 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1562 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL |
| 1563 |
Warnings: |
| 1564 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1565 |
SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1566 |
doc id |
| 1567 |
{"id": "2", "name": "bar"} 2 |
| 1568 |
explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1569 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1570 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL |
| 1571 |
Warnings: |
| 1572 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1573 |
SELECT * FROM t1 WHERE id = '2'; |
| 1574 |
doc id |
| 1575 |
{"id": "2", "name": "bar"} 2 |
| 1576 |
explain SELECT * FROM t1 WHERE id = '2'; |
| 1577 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1578 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL |
| 1579 |
Warnings: |
| 1580 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1581 |
PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1582 |
# Not using index, resulting JSON comparison delivers empty result. |
| 1583 |
# This deviates from the result when the GC is not indexed. |
| 1584 |
EXECUTE s11 USING @a; |
| 1585 |
doc id |
| 1586 |
PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1587 |
EXECUTE s12 USING @a; |
| 1588 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1589 |
1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 2 50.00 Using where |
| 1590 |
Warnings: |
| 1591 |
Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'id' |
| 1592 |
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id' |
| 1593 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = '2') |
| 1594 |
PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1595 |
EXECUTE s21 USING @a; |
| 1596 |
doc id |
| 1597 |
{"id": "2", "name": "bar"} 2 |
| 1598 |
PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1599 |
EXECUTE s22 USING @a; |
| 1600 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1601 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL |
| 1602 |
Warnings: |
| 1603 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1604 |
PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?"; |
| 1605 |
EXECUTE s31 USING @a; |
| 1606 |
doc id |
| 1607 |
{"id": "2", "name": "bar"} 2 |
| 1608 |
PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?"; |
| 1609 |
EXECUTE s32 USING @a; |
| 1610 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1611 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 34 const 1 100.00 NULL |
| 1612 |
Warnings: |
| 1613 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1614 |
DROP TABLE t1; |
| 1615 |
|
| 1616 |
# VARCHAR as primary key |
| 1617 |
|
| 1618 |
CREATE TABLE t1 ( |
| 1619 |
doc JSON, |
| 1620 |
id VARCHAR(32) COLLATE utf8mb4_bin GENERATED ALWAYS AS |
| 1621 |
(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY); |
| 1622 |
INSERT INTO t1 (doc) VALUES |
| 1623 |
(JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar')); |
| 1624 |
ANALYZE TABLE t1; |
| 1625 |
Table Op Msg_type Msg_text |
| 1626 |
test.t1 analyze status OK |
| 1627 |
SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1628 |
doc id |
| 1629 |
{"id": "2", "name": "bar"} 2 |
| 1630 |
explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1631 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1632 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL |
| 1633 |
Warnings: |
| 1634 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1635 |
SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1636 |
doc id |
| 1637 |
{"id": "2", "name": "bar"} 2 |
| 1638 |
explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1639 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1640 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL |
| 1641 |
Warnings: |
| 1642 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1643 |
SELECT * FROM t1 WHERE id = '2'; |
| 1644 |
doc id |
| 1645 |
{"id": "2", "name": "bar"} 2 |
| 1646 |
explain SELECT * FROM t1 WHERE id = '2'; |
| 1647 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1648 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL |
| 1649 |
Warnings: |
| 1650 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1651 |
PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1652 |
# Not using index, resulting JSON comparison delivers expected row |
| 1653 |
EXECUTE s11 USING @a; |
| 1654 |
doc id |
| 1655 |
{"id": "2", "name": "bar"} 2 |
| 1656 |
PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1657 |
EXECUTE s12 USING @a; |
| 1658 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1659 |
1 SIMPLE t1 NULL ALL PRIMARY NULL NULL NULL 2 50.00 Using where |
| 1660 |
Warnings: |
| 1661 |
Warning 1739 Cannot use ref access on index 'PRIMARY' due to type or collation conversion on field 'id' |
| 1662 |
Warning 1739 Cannot use range access on index 'PRIMARY' due to type or collation conversion on field 'id' |
| 1663 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (`test`.`t1`.`id` = '2') |
| 1664 |
PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1665 |
EXECUTE s21 USING @a; |
| 1666 |
doc id |
| 1667 |
{"id": "2", "name": "bar"} 2 |
| 1668 |
PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1669 |
EXECUTE s22 USING @a; |
| 1670 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1671 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL |
| 1672 |
Warnings: |
| 1673 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1674 |
PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?"; |
| 1675 |
EXECUTE s31 USING @a; |
| 1676 |
doc id |
| 1677 |
{"id": "2", "name": "bar"} 2 |
| 1678 |
PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?"; |
| 1679 |
EXECUTE s32 USING @a; |
| 1680 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1681 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 130 const 1 100.00 NULL |
| 1682 |
Warnings: |
| 1683 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1684 |
DROP TABLE t1; |
| 1685 |
|
| 1686 |
# BIGINT as primary key |
| 1687 |
|
| 1688 |
CREATE TABLE t1 ( |
| 1689 |
doc JSON, |
| 1690 |
id BIGINT GENERATED ALWAYS AS |
| 1691 |
(JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id'))) STORED PRIMARY KEY); |
| 1692 |
INSERT INTO t1 (doc) VALUES |
| 1693 |
(JSON_OBJECT('id','1','name','foo')),(JSON_OBJECT('id','2','name','bar')); |
| 1694 |
ANALYZE TABLE t1; |
| 1695 |
Table Op Msg_type Msg_text |
| 1696 |
test.t1 analyze status OK |
| 1697 |
SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1698 |
doc id |
| 1699 |
{"id": "2", "name": "bar"} 2 |
| 1700 |
explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = '2'; |
| 1701 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1702 |
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where |
| 1703 |
Warnings: |
| 1704 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_extract(`test`.`t1`.`doc`,'$.id') = '2') |
| 1705 |
SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1706 |
doc id |
| 1707 |
{"id": "2", "name": "bar"} 2 |
| 1708 |
explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = '2'; |
| 1709 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1710 |
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where |
| 1711 |
Warnings: |
| 1712 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_unquote(json_extract(`test`.`t1`.`doc`,'$.id')) = '2') |
| 1713 |
SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = 2; |
| 1714 |
doc id |
| 1715 |
{"id": "2", "name": "bar"} 2 |
| 1716 |
explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = 2; |
| 1717 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1718 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1719 |
Warnings: |
| 1720 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1721 |
SELECT * FROM t1 WHERE id = '2'; |
| 1722 |
doc id |
| 1723 |
{"id": "2", "name": "bar"} 2 |
| 1724 |
explain SELECT * FROM t1 WHERE id = '2'; |
| 1725 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1726 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1727 |
Warnings: |
| 1728 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1729 |
PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1730 |
EXECUTE s11 USING @a; |
| 1731 |
doc id |
| 1732 |
{"id": "2", "name": "bar"} 2 |
| 1733 |
PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1734 |
EXECUTE s12 USING @a; |
| 1735 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1736 |
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where |
| 1737 |
Warnings: |
| 1738 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_extract(`test`.`t1`.`doc`,'$.id') = '2') |
| 1739 |
PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1740 |
EXECUTE s21 USING @a; |
| 1741 |
doc id |
| 1742 |
{"id": "2", "name": "bar"} 2 |
| 1743 |
PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1744 |
EXECUTE s22 USING @a; |
| 1745 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1746 |
1 SIMPLE t1 NULL ALL NULL NULL NULL NULL 2 100.00 Using where |
| 1747 |
Warnings: |
| 1748 |
Note 1003 /* select#1 */ select `test`.`t1`.`doc` AS `doc`,`test`.`t1`.`id` AS `id` from `test`.`t1` where (json_unquote(json_extract(`test`.`t1`.`doc`,'$.id')) = '2') |
| 1749 |
PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?"; |
| 1750 |
EXECUTE s31 USING @a; |
| 1751 |
doc id |
| 1752 |
{"id": "2", "name": "bar"} 2 |
| 1753 |
PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?"; |
| 1754 |
EXECUTE s32 USING @a; |
| 1755 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1756 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1757 |
Warnings: |
| 1758 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1759 |
PREPARE s11 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1760 |
EXECUTE s11 USING @num; |
| 1761 |
doc id |
| 1762 |
{"id": "2", "name": "bar"} 2 |
| 1763 |
PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1764 |
EXECUTE s11 USING @num; |
| 1765 |
doc id |
| 1766 |
{"id": "2", "name": "bar"} 2 |
| 1767 |
PREPARE s12 FROM "explain SELECT * FROM t1 WHERE JSON_EXTRACT(doc, '$.id') = ?"; |
| 1768 |
EXECUTE s12 USING @num; |
| 1769 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1770 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1771 |
Warnings: |
| 1772 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1773 |
PREPARE s21 FROM "SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1774 |
EXECUTE s21 USING @num; |
| 1775 |
doc id |
| 1776 |
{"id": "2", "name": "bar"} 2 |
| 1777 |
PREPARE s22 FROM "explain SELECT * FROM t1 WHERE JSON_UNQUOTE(JSON_EXTRACT(doc, '$.id')) = ?"; |
| 1778 |
EXECUTE s22 USING @num; |
| 1779 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1780 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1781 |
Warnings: |
| 1782 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1783 |
PREPARE s31 FROM "SELECT * FROM t1 WHERE id = ?"; |
| 1784 |
EXECUTE s31 USING @num; |
| 1785 |
doc id |
| 1786 |
{"id": "2", "name": "bar"} 2 |
| 1787 |
PREPARE s32 FROM "explain SELECT * FROM t1 WHERE id = ?"; |
| 1788 |
EXECUTE s32 USING @num; |
| 1789 |
id select_type table partitions type possible_keys key key_len ref rows filtered Extra |
| 1790 |
1 SIMPLE t1 NULL const PRIMARY PRIMARY 8 const 1 100.00 NULL |
| 1791 |
Warnings: |
| 1792 |
Note 1003 /* select#1 */ select '{"id": "2", "name": "bar"}' AS `doc`,'2' AS `id` from `test`.`t1` where true |
| 1793 |
DROP TABLE t1; |
| 1794 |
# Bug#33142135: Assert for fields not marked as READ |
| 1795 |
CREATE TABLE v1(v2 INT SERIAL DEFAULT VALUE, v3 INT AS (DEFAULT(v2)/1 IS NULL)); |
| 1796 |
ERROR HY000: Generated column 'v3' cannot refer to auto-increment column. |
| 1797 |
CREATE TABLE v1(v2 INT DEFAULT 10, v3 INT AS (DEFAULT(v2)/1 IS NULL)); |
| 1798 |
SHOW CREATE TABLE v1; |
| 1799 |
Table Create Table |
| 1800 |
v1 CREATE TABLE `v1` ( |
| 1801 |
`v2` int DEFAULT '10', |
| 1802 |
`v3` int GENERATED ALWAYS AS (((default(`v2`) / 1) is null)) VIRTUAL |
| 1803 |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
| 1804 |
DROP TABLE v1; |