Debian Codegraph @

mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result

raw
1 SET @@session.default_storage_engine = 'MyISAM';
2 create table t1 (a int,
3 b int generated always as (-a) virtual,
4 c int generated always as (-a) stored);
5 set sql_warnings = 1;
6 #
7 # *** INSERT ***
8 #
9 # INSERT INTO tbl_name VALUES... DEFAULT is specified against gcols
10 insert into t1 values (1,default,default);
11 select * from t1;
12 a b c
13 1 -1 -1
14 delete from t1;
15 select * from t1;
16 a b c
17 # INSERT INTO tbl_name VALUES... NULL is specified against gcols
18 insert into t1 values (1,null,null);
19 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
20 select * from t1;
21 a b c
22 delete from t1;
23 select * from t1;
24 a b c
25 # INSERT INTO tbl_name VALUES... a non-NULL value is specified against gcols
26 insert into t1 values (1,2,3);
27 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
28 select * from t1;
29 a b c
30 delete from t1;
31 select * from t1;
32 a b c
33 # INSERT INTO tbl_name (<non_gcol_list>) VALUES...
34 insert into t1 (a) values (1), (2);
35 select * from t1 order by a;
36 a b c
37 1 -1 -1
38 2 -2 -2
39 delete from t1;
40 select * from t1;
41 a b c
42 # INSERT INTO tbl_name (<normal+gcols>) VALUES... DEFAULT is specified
43 # against gcols
44 insert into t1 (a,b) values (1,default), (2,default);
45 select * from t1;
46 a b c
47 1 -1 -1
48 2 -2 -2
49 delete from t1;
50 select * from t1;
51 a b c
52 # INSERT INTO tbl_name (<normal+gcols>) VALUES... NULL is specified against gcols
53 insert into t1 (a,b) values (1,null), (2,null);
54 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
55 select * from t1;
56 a b c
57 delete from t1;
58 select * from t1;
59 a b c
60 # INSERT INTO tbl_name (<normal+gcols>) VALUES... a non-NULL value is specified
61 # against gcols
62 insert into t1 (a,b) values (1,3), (2,4);
63 ERROR HY000: The value specified for generated column 'b' in table 't1' is not allowed.
64 select * from t1;
65 a b c
66 delete from t1;
67 select * from t1;
68 a b c
69 drop table t1;
70 # Table with UNIQUE non-gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
71 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
72 create table t1 (a int unique,
73 b int generated always as (-a) virtual,
74 c int generated always as (-a) stored);
75 insert into t1 values (1,default,default);
76 insert into t1 values (1,default,default)
77 on duplicate key update a=2, b=default;
78 select a,b,c from t1;
79 a b c
80 2 -2 -2
81 delete from t1 where b in (1,2);
82 select * from t1;
83 a b c
84 2 -2 -2
85 drop table t1;
86 # Table with UNIQUE gcol field. INSERT INTO tbl_name VALUES... ON DUPLICATE
87 # KEY UPDATE <non_gcol>=expr, <gcol>=expr
88 create table t1 (a int,
89 b int generated always as (-a) virtual,
90 c int generated always as (-a) stored unique);
91 insert into t1 values (1,default,default);
92 insert into t1 values (1,default,default)
93 on duplicate key update a=2, b=default;
94 select a,b,c from t1;
95 a b c
96 2 -2 -2
97 # CREATE new_table ... LIKE old_table
98 # INSERT INTO new_table SELECT * from old_table
99 create table t2 like t1;
100 insert into t2 select * from t1;
101 ERROR HY000: The value specified for generated column 'b' in table 't2' is not allowed.
102 insert into t2(a) select a from t1;
103 select * from t2;
104 a b c
105 2 -2 -2
106 drop table t2;
107 # CREATE new_table ... LIKE old_table INSERT INTO new_table (<non-gcols>, <gcols>)
108 # SELECT <non-gcols>, <gcols> from old_table
109 insert into t1 values (1,default,default);
110 select * from t1;
111 a b c
112 1 -1 -1
113 2 -2 -2
114 create table t2 like t1;
115 insert into t2 (a,b) select a,b from t1;
116 ERROR HY000: The value specified for generated column 'b' in table 't2' is not allowed.
117 insert into t2 (a) select a from t1;
118 select * from t2 order by a;
119 a b c
120 1 -1 -1
121 2 -2 -2
122 drop table t2;
123 drop table t1;
124 #
125 # *** UPDATE ***
126 #
127 # UPDATE tbl_name SET non-gcol=expr WHERE non-gcol=expr
128 create table t1 (a int,
129 b int generated always as (-a) virtual,
130 c int generated always as (-a) stored);
131 insert into t1 (a) values (1), (2);
132 select * from t1 order by a;
133 a b c
134 1 -1 -1
135 2 -2 -2
136 update t1 set a=3 where a=2;
137 select * from t1 order by a;
138 a b c
139 1 -1 -1
140 3 -3 -3
141 delete from t1;
142 select * from t1;
143 a b c
144 # UPDATE tbl_name SET gcol=expr WHERE non-gcol=expr
145 insert into t1 (a) values (1), (2);
146 select * from t1;
147 a b c
148 1 -1 -1
149 2 -2 -2
150 update t1 set c=3 where a=2;
151 ERROR HY000: The value specified for generated column 'c' in table 't1' is not allowed.
152 select * from t1;
153 a b c
154 1 -1 -1
155 2 -2 -2
156 delete from t1;
157 select * from t1;
158 a b c
159 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=expr
160 insert into t1 (a) values (1), (2);
161 select * from t1;
162 a b c
163 1 -1 -1
164 2 -2 -2
165 update t1 set a=3 where b=-2;
166 select * from t1;
167 a b c
168 1 -1 -1
169 3 -3 -3
170 delete from t1;
171 select * from t1;
172 a b c
173 # UPDATE tbl_name SET gcol=expr WHERE gcol=expr
174 insert into t1 (a) values (1), (2);
175 select * from t1;
176 a b c
177 1 -1 -1
178 2 -2 -2
179 update t1 set c=3 where b=-2;
180 ERROR HY000: The value specified for generated column 'c' in table 't1' is not allowed.
181 select * from t1;
182 a b c
183 1 -1 -1
184 2 -2 -2
185 delete from t1;
186 select * from t1;
187 a b c
188 drop table t1;
189 # INDEX created on gcol
190 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=const
191 create table t1 (a int,
192 b int generated always as (-a) virtual,
193 c int generated always as (-a) stored unique);
194 insert into t1 (a) values (1), (2);
195 select * from t1 order by a;
196 a b c
197 1 -1 -1
198 2 -2 -2
199 update t1 set a=3 where c=-2;
200 select * from t1;
201 a b c
202 1 -1 -1
203 3 -3 -3
204 delete from t1;
205 select * from t1;
206 a b c
207 # INDEX created on gcol
208 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
209 insert into t1 (a) values (1), (2);
210 select * from t1;
211 a b c
212 1 -1 -1
213 2 -2 -2
214 update t1 set a=3 where c between -3 and -2;
215 select * from t1;
216 a b c
217 1 -1 -1
218 3 -3 -3
219 delete from t1;
220 select * from t1;
221 a b c
222 # No INDEX created on gcol
223 # UPDATE tbl_name SET non-gcol=expr WHERE gcol=between const1 and const2
224 insert into t1 (a) values (1), (2);
225 select * from t1;
226 a b c
227 1 -1 -1
228 2 -2 -2
229 update t1 set a=3 where b between -3 and -2;
230 select * from t1;
231 a b c
232 1 -1 -1
233 3 -3 -3
234 delete from t1;
235 select * from t1;
236 a b c
237 # INDEX created on gcol
238 # UPDATE tbl_name SET non-gcol=expr
239 # WHERE gcol=between const1 and const2 ORDER BY gcol
240 insert into t1 (a) values (1), (2), (3), (4), (5);
241 select * from t1;
242 a b c
243 1 -1 -1
244 2 -2 -2
245 3 -3 -3
246 4 -4 -4
247 5 -5 -5
248 update t1 set a=6 where c between -1 and 0
249 order by c;
250 select * from t1;
251 a b c
252 2 -2 -2
253 3 -3 -3
254 4 -4 -4
255 5 -5 -5
256 6 -6 -6
257 delete from t1 where c between -6 and 0;
258 select * from t1;
259 a b c
260 # INDEX created on gcol
261 # UPDATE tbl_name SET non-gcol=expr
262 # WHERE gcol=between const1 and const2 ORDER BY gcol LIMIT 2
263 insert into t1 (a) values (1), (2), (3), (4), (5);
264 select * from t1 order by a;
265 a b c
266 1 -1 -1
267 2 -2 -2
268 3 -3 -3
269 4 -4 -4
270 5 -5 -5
271 update t1 set a=6 where c between -1 and 0
272 order by c limit 2;
273 select * from t1;
274 a b c
275 2 -2 -2
276 3 -3 -3
277 4 -4 -4
278 5 -5 -5
279 6 -6 -6
280 delete from t1 where c between -2 and 0 order by c;
281 select * from t1 order by a;
282 a b c
283 3 -3 -3
284 4 -4 -4
285 5 -5 -5
286 6 -6 -6
287 delete from t1;
288 # INDEX created on gcol
289 # UPDATE tbl_name SET non-gcol=expr
290 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
291 insert into t1 (a) values (1), (2), (3), (4), (5);
292 select * from t1;
293 a b c
294 1 -1 -1
295 2 -2 -2
296 3 -3 -3
297 4 -4 -4
298 5 -5 -5
299 update t1 set a=6 where (c between -2 and 0) and (b=-1);
300 select * from t1;
301 a b c
302 2 -2 -2
303 3 -3 -3
304 4 -4 -4
305 5 -5 -5
306 6 -6 -6
307 delete from t1;
308 # INDEX created on gcol
309 # UPDATE tbl_name SET non-gcol=expr
310 # WHERE indexed gcol=between const1 and const2 and non-indexed gcol=const3
311 # ORDER BY indexed gcol
312 insert into t1 (a) values (1), (2), (3), (4), (5);
313 select * from t1;
314 a b c
315 1 -1 -1
316 2 -2 -2
317 3 -3 -3
318 4 -4 -4
319 5 -5 -5
320 update t1 set a=6 where (c between -2 and 0) and (b=-1) order by c;
321 select * from t1;
322 a b c
323 2 -2 -2
324 3 -3 -3
325 4 -4 -4
326 5 -5 -5
327 6 -6 -6
328 delete from t1;
329 drop table t1;
330 #
331 # *** REPLACE ***
332 #
333 # UNIQUE INDEX on gcol
334 # REPLACE tbl_name (non-gcols) VALUES (non-gcols);
335 create table t1 (a int,
336 b int generated always as (-a) virtual,
337 c int generated always as (-a) stored unique,
338 d varchar(16));
339 insert into t1 (a,d) values (1,'a'), (2,'b');
340 select * from t1 order by a;
341 a b c d
342 1 -1 -1 a
343 2 -2 -2 b
344 replace t1 (a,d) values (1,'c');
345 select * from t1 order by a;
346 a b c d
347 1 -1 -1 c
348 2 -2 -2 b
349 delete from t1;
350 select * from t1;
351 a b c d
352 set sql_warnings = 0;
353 drop table t1;
354 Bug#20170778: WL411:FAILING ASSERTION `!TABLE || (!TABLE->WRITE_SET ||
355 BITMAP_IS_SET(TABLE->WR
356 #
357 CREATE TABLE t1 (col1 INT, col2 INT, col3 INT, col4 INT, col5
358 INT GENERATED ALWAYS AS (col3 * col2) VIRTUAL, col6 INT GENERATED ALWAYS AS
359 (col4 * col1) STORED, col7 INT GENERATED ALWAYS AS (col6 + col6) VIRTUAL,
360 col8 INT GENERATED ALWAYS AS (col6 / col5) STORED, col9 TEXT);
361 SET @fill_amount = (@@innodb_page_size / 2 ) + 1;
362 INSERT INTO t1 (col1,col2,col3,col4,col5,col6,col7,col8,col9) VALUES /* 3 */
363 (3, 3 / 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT, DEFAULT ,REPEAT(CAST(3 AS
364 CHAR(1)),@fill_amount)) , (3, 3 * 3, 3 + 3, 3 / 3, DEFAULT, DEFAULT, DEFAULT,
365 DEFAULT ,REPEAT(CAST(3 AS CHAR(1)),@fill_amount));
366 UPDATE t1 SET col1 = 2;
367 UPDATE t1 SET col7 = DEFAULT;
368 UPDATE t1 SET col8 = DEFAULT;
369 DROP TABLE t1;
370 # Bug#21081742: ASSERTION !TABLE || (!TABLE->WRITE_SET ||
371 # BITMAP_IS_SET(TABLE->WRITE_SET
372 #
373 CREATE TABLE b (
374 pk INTEGER AUTO_INCREMENT,
375 col_varchar_nokey VARCHAR(1),
376 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
377 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
378 PRIMARY KEY (pk)
379 );
380 INSERT INTO b (col_varchar_nokey) VALUES ('v'),('v');
381 CREATE TABLE d (
382 pk INTEGER AUTO_INCREMENT,
383 col_varchar_nokey VARCHAR(1),
384 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
385 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
386 PRIMARY KEY (pk)
387 ) ;
388 INSERT INTO d (col_varchar_nokey) VALUES ('q'),('g'),('e'),('l'),(NULL),('v'),('c'),('u'),('x');
389 CREATE TABLE bb (
390 pk INTEGER AUTO_INCREMENT,
391 col_varchar_nokey VARCHAR(1) /*! NULL */,
392 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
393 (CONCAT(col_varchar_nokey, col_varchar_nokey)),
394 PRIMARY KEY (pk)
395 );
396 INSERT INTO bb (col_varchar_nokey) VALUES ('j'),('h');
397 EXPLAIN UPDATE
398 d AS outr1, b AS outr2
399 SET outr1.col_varchar_nokey = NULL
400 WHERE
401 ( 't', 'b' ) IN
402 (
403 SELECT
404 innr1.col_varchar_nokey AS x,
405 innr1.col_varchar_key AS y
406 FROM bb AS innr1
407 WHERE outr1.pk = 1
408 );
409 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
410 1 UPDATE outr1 NULL const PRIMARY PRIMARY 4 const 1 100.00 NULL
411 1 SIMPLE innr1 NULL ALL NULL NULL NULL NULL 2 50.00 Using where; FirstMatch(outr1)
412 1 SIMPLE outr2 NULL index NULL PRIMARY 4 NULL 2 100.00 Using index
413 Warnings:
414 Note 1276 Field or reference 'test.outr1.pk' of SELECT #2 was resolved in SELECT #1
415 Note 1003 update `test`.`d` `outr1` join `test`.`b` `outr2` semi join (`test`.`bb` `innr1`) set `test`.`outr1`.`col_varchar_nokey` = NULL where ((`test`.`innr1`.`col_varchar_key` = 'b') and (`test`.`innr1`.`col_varchar_nokey` = 't'))
416 DROP TABLE IF EXISTS b,bb,d;
417 #
418 # Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103)
419 #
420 CREATE TABLE t (
421 x INT, y INT, gc INT GENERATED ALWAYS AS (x+1) STORED
422 );
423 INSERT INTO t VALUES ();
424 UPDATE t t1, t t2 SET t2.y = 1, t1.x = 2;
425 SELECT * FROM t;
426 x y gc
427 2 1 3
428 DROP TABLE t;
429 # stored
430 CREATE TABLE c (
431 col_varchar_nokey VARCHAR(1),
432 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
433 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED
434 );
435 INSERT INTO c (col_varchar_nokey) VALUES ('c');
436 EXPLAIN UPDATE c AS outr1, c AS outr2
437 SET outr1.`col_varchar_nokey` = 'f',
438 outr2.`col_varchar_nokey` = "a";
439 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
440 1 UPDATE outr1 NULL system NULL NULL NULL NULL 1 100.00 NULL
441 1 UPDATE outr2 NULL system NULL NULL NULL NULL 1 100.00 NULL
442 Warnings:
443 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
444 UPDATE c AS outr1, c AS outr2
445 SET outr1.`col_varchar_nokey` = 'f',
446 outr2.`col_varchar_nokey` = "a";
447 SELECT * from c;
448 col_varchar_nokey col_varchar_key
449 a aa
450 DROP TABLE c;
451 # stored, indexed
452 CREATE TABLE c (
453 col_varchar_nokey VARCHAR(1),
454 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
455 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED,
456 KEY (col_varchar_key, col_varchar_nokey)
457 );
458 INSERT INTO c (col_varchar_nokey) VALUES ('c');
459 EXPLAIN UPDATE c AS outr1, c AS outr2
460 SET outr1.`col_varchar_nokey` = 'f',
461 outr2.`col_varchar_nokey` = "a";
462 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
463 1 UPDATE outr1 NULL system NULL NULL NULL NULL 1 100.00 NULL
464 1 UPDATE outr2 NULL system NULL NULL NULL NULL 1 100.00 NULL
465 Warnings:
466 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
467 UPDATE c AS outr1, c AS outr2
468 SET outr1.`col_varchar_nokey` = 'f',
469 outr2.`col_varchar_nokey` = "a";
470 SELECT * from c;
471 col_varchar_nokey col_varchar_key
472 a aa
473 DROP TABLE c;
474 # virtual
475 CREATE TABLE c (
476 col_varchar_nokey VARCHAR(1),
477 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS
478 (CONCAT(col_varchar_nokey, col_varchar_nokey)) VIRTUAL
479 );
480 INSERT INTO c (col_varchar_nokey) VALUES ('c');
481 EXPLAIN UPDATE c AS outr1, c AS outr2
482 SET outr1.`col_varchar_nokey` = 'f',
483 outr2.`col_varchar_nokey` = "a";
484 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
485 1 UPDATE outr1 NULL system NULL NULL NULL NULL 1 100.00 NULL
486 1 UPDATE outr2 NULL system NULL NULL NULL NULL 1 100.00 NULL
487 Warnings:
488 Note 1003 update `test`.`c` `outr1` join `test`.`c` `outr2` set `test`.`outr1`.`col_varchar_nokey` = 'f',`test`.`outr2`.`col_varchar_nokey` = 'a'
489 UPDATE c AS outr1, c AS outr2
490 SET outr1.`col_varchar_nokey` = 'f',
491 outr2.`col_varchar_nokey` = "a";
492 SELECT * from c;
493 col_varchar_nokey col_varchar_key
494 a aa
495 DROP TABLE c;
496 #Bug#21929967:GCOLS:GCOL VALUE CHANGES WHEN SESSION CHANGES SQL_MODE
497 CREATE TABLE t(c1 INT GENERATED ALWAYS AS (1) VIRTUAL NOT NULL,
498 c2 INT GENERATED ALWAYS AS(2) STORED NOT NULL);
499 INSERT INTO t VALUES(DEFAULT, DEFAULT);
500 SELECT * FROM t;
501 c1 c2
502 1 2
503 CREATE TABLE t1(c1 INT, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED);
504 INSERT INTO t1(c2) VALUES(DEFAULT);
505 SELECT * FROM t1;
506 c1 c2
507 NULL NULL
508 CREATE TABLE t2(c1 INT DEFAULT 1, c2 INT GENERATED ALWAYS AS(c1 + 1) STORED NOT NULL);
509 INSERT INTO t2(c2) VALUES(DEFAULT);
510 SELECT * FROM t2;
511 c1 c2
512 1 2
513 DROP TABLE t, t1, t2;
514 # Bug#22179637: INSERT INTO TABLE FROM SELECT ACCEPTS TO INSERT INTO
515 # GENERATED COLUMNS
516 CREATE TABLE t1 (
517 i1 INTEGER,
518 i2 INTEGER GENERATED ALWAYS AS (i1 + i1)
519 );
520 INSERT INTO t1 (i1, i2) SELECT 5, 6;
521 ERROR HY000: The value specified for generated column 'i2' in table 't1' is not allowed.
522 INSERT INTO t1 (i1) SELECT 5;
523 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
524 ERROR HY000: The value specified for generated column 'i2' in table 't1' is not allowed.
525 INSERT INTO t1 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
526 SELECT * FROM t1;
527 i1 i2
528 5 10
529 5 10
530 CREATE TABLE t2 (
531 i1 INTEGER,
532 i2 INTEGER GENERATED ALWAYS AS (i1 + i1) STORED
533 );
534 INSERT INTO t2 (i1, i2) SELECT 5, 6;
535 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
536 INSERT INTO t2 (i1, i2) SELECT * FROM t1;
537 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
538 INSERT INTO t2 (i1) SELECT 5;
539 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= 4;
540 ERROR HY000: The value specified for generated column 'i2' in table 't2' is not allowed.
541 INSERT INTO t2 (i1) SELECT 5 ON DUPLICATE KEY UPDATE i2= DEFAULT;
542 SELECT * FROM t2;
543 i1 i2
544 5 10
545 5 10
546 DROP TABLE t1,t2;
547 DROP VIEW IF EXISTS v1,v2;
548 DROP TABLE IF EXISTS t1,t2,t3;
549 DROP PROCEDURE IF EXISTS p1;
550 DROP FUNCTION IF EXISTS f1;
551 DROP TRIGGER IF EXISTS trg1;
552 DROP TRIGGER IF EXISTS trg2;
553 set sql_warnings = 0;