1DROP VIEW IF EXISTS v1,v2; 2DROP TABLE IF EXISTS t1,t2,t3; 3DROP PROCEDURE IF EXISTS p1; 4DROP FUNCTION IF EXISTS f1; 5DROP TRIGGER IF EXISTS trg1; 6DROP TRIGGER IF EXISTS trg2; 7set sql_warnings = 0; 8SET @@session.default_storage_engine = 'InnoDB'; 9SET optimizer_switch='derived_merge=off'; 10create table t1 (a int, 11b int generated always as (-a) virtual, 12c int generated always as (-a) stored, 13index (c)); 14insert into t1 (a) values (2), (1), (1), (3), (NULL); 15create table t2 like t1; 16insert into t2 (a) values (1); 17create table t3 (a int primary key, 18b int generated always as (-a) virtual, 19c int generated always as (-a) stored unique); 20insert into t3 (a) values (2),(1),(3),(5),(4),(7); 21analyze table t1,t2,t3; 22Table Op Msg_type Msg_text 23test.t1 analyze status Engine-independent statistics collected 24test.t1 analyze status OK 25test.t2 analyze status Engine-independent statistics collected 26test.t2 analyze status OK 27test.t3 analyze status Engine-independent statistics collected 28test.t3 analyze status OK 29# select_type=SIMPLE, type=system 30select * from t2; 31a b c 321 -1 -1 33explain select * from t2; 34id select_type table type possible_keys key key_len ref rows Extra 351 SIMPLE t2 ALL NULL NULL NULL NULL 1 36select * from t2 where c=-1; 37a b c 381 -1 -1 39explain select * from t2 where c=-1; 40id select_type table type possible_keys key key_len ref rows Extra 411 SIMPLE t2 ref c c 5 const 1 42# select_type=SIMPLE, type=ALL 43select * from t1 where b=-1; 44a b c 451 -1 -1 461 -1 -1 47explain select * from t1 where b=-1; 48id select_type table type possible_keys key key_len ref rows Extra 491 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using where 50# select_type=SIMPLE, type=const 51select * from t3 where a=1; 52a b c 531 -1 -1 54explain select * from t3 where a=1; 55id select_type table type possible_keys key key_len ref rows Extra 561 SIMPLE t3 const PRIMARY PRIMARY 4 const 1 57# select_type=SIMPLE, type=range 58select * from t3 where c>=-1; 59a b c 601 -1 -1 61explain select * from t3 where c>=-1; 62id select_type table type possible_keys key key_len ref rows Extra 631 SIMPLE t3 range c c 5 NULL 1 Using index condition 64# select_type=SIMPLE, type=ref 65select * from t1,t3 where t1.c=t3.c and t3.c=-1; 66a b c a b c 671 -1 -1 1 -1 -1 681 -1 -1 1 -1 -1 69explain select * from t1,t3 where t1.c=t3.c and t3.c=-1; 70id select_type table type possible_keys key key_len ref rows Extra 711 SIMPLE t3 const c c 5 const 1 721 SIMPLE t1 ref c c 5 const 2 73# select_type=PRIMARY, type=index,ALL 74select * from t1 where b in (select c from t3); 75a b c 761 -1 -1 771 -1 -1 782 -2 -2 793 -3 -3 80explain select * from t1 where b in (select c from t3); 81id select_type table type possible_keys key key_len ref rows Extra 821 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where 831 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index 84# select_type=PRIMARY, type=range,ref 85select * from t1 where c in (select c from t3 where c between -2 and -1); 86a b c 871 -1 -1 881 -1 -1 892 -2 -2 90explain select * from t1 where c in (select c from t3 where c between -2 and -1); 91id select_type table type possible_keys key key_len ref rows Extra 921 PRIMARY t3 range c c 5 NULL 2 Using where; Using index 931 PRIMARY t1 ref c c 5 test.t3.c 1 94# select_type=UNION, type=system 95# select_type=UNION RESULT, type=<union1,2> 96select * from t1 union select * from t2; 97a b c 981 -1 -1 992 -2 -2 1003 -3 -3 101NULL NULL NULL 102explain select * from t1 union select * from t2; 103id select_type table type possible_keys key key_len ref rows Extra 1041 PRIMARY t1 ALL NULL NULL NULL NULL 5 1052 UNION t2 ALL NULL NULL NULL NULL 1 106NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL 107# select_type=DERIVED, type=system 108select * from (select a,b,c from t1) as t11; 109a b c 1101 -1 -1 1111 -1 -1 1122 -2 -2 1133 -3 -3 114NULL NULL NULL 115explain select * from (select a,b,c from t1) as t11; 116id select_type table type possible_keys key key_len ref rows Extra 1171 PRIMARY <derived2> ALL NULL NULL NULL NULL 5 1182 DERIVED t1 ALL NULL NULL NULL NULL 5 119### 120### Using aggregate functions with/without DISTINCT 121### 122# SELECT COUNT(*) FROM tbl_name 123select count(*) from t1; 124count(*) 1255 126explain select count(*) from t1; 127id select_type table type possible_keys key key_len ref rows Extra 1281 SIMPLE t1 index NULL c 5 NULL 5 Using index 129# SELECT COUNT(DISTINCT <non-gcol>) FROM tbl_name 130select count(distinct a) from t1; 131count(distinct a) 1323 133explain select count(distinct a) from t1; 134id select_type table type possible_keys key key_len ref rows Extra 1351 SIMPLE t1 ALL NULL NULL NULL NULL 5 136# SELECT COUNT(DISTINCT <non-stored gcol>) FROM tbl_name 137select count(distinct b) from t1; 138count(distinct b) 1393 140explain select count(distinct b) from t1; 141id select_type table type possible_keys key key_len ref rows Extra 1421 SIMPLE t1 ALL NULL NULL NULL NULL 5 143# SELECT COUNT(DISTINCT <stored gcol>) FROM tbl_name 144select count(distinct c) from t1; 145count(distinct c) 1463 147explain select count(distinct c) from t1; 148id select_type table type possible_keys key key_len ref rows Extra 1491 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by 150### 151### filesort & range-based utils 152### 153# SELECT * FROM tbl_name WHERE <gcol expr> 154select * from t3 where c >= -2; 155a b c 1561 -1 -1 1572 -2 -2 158explain select * from t3 where c >= -2; 159id select_type table type possible_keys key key_len ref rows Extra 1601 SIMPLE t3 range c c 5 NULL 2 Using index condition 161# SELECT * FROM tbl_name WHERE <non-gcol expr> 162select * from t3 where a between 1 and 2; 163a b c 1641 -1 -1 1652 -2 -2 166explain select * from t3 where a between 1 and 2; 167id select_type table type possible_keys key key_len ref rows Extra 1681 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where 169# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> 170select * from t3 where b between -2 and -1; 171a b c 1721 -1 -1 1732 -2 -2 174explain select * from t3 where b between -2 and -1; 175id select_type table type possible_keys key key_len ref rows Extra 1761 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where 177# SELECT * FROM tbl_name WHERE <indexed gcol expr> 178select * from t3 where c between -2 and -1; 179a b c 1801 -1 -1 1812 -2 -2 182explain select * from t3 where c between -2 and -1; 183id select_type table type possible_keys key key_len ref rows Extra 1841 SIMPLE t3 range c c 5 NULL 2 Using index condition 185# SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed gcol> 186select * from t3 where a between 1 and 2 order by b; 187a b c 1882 -2 -2 1891 -1 -1 190explain select * from t3 where a between 1 and 2 order by b; 191id select_type table type possible_keys key key_len ref rows Extra 1921 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 193# bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC 194# SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed stored gcol> 195select * from t3 where a between 1 and 2 order by c; 196a b c 1972 -2 -2 1981 -1 -1 199explain select * from t3 where a between 1 and 2 order by c; 200id select_type table type possible_keys key key_len ref rows Extra 2011 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 202# bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC 203CREATE TABLE t4 ( 204`pk` int(11) NOT NULL , 205`col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED, 206`col_int_key` int(11) DEFAULT NULL, 207`col_date_nokey` date DEFAULT NULL, 208`col_datetime_key` datetime DEFAULT NULL, 209PRIMARY KEY (`pk`), 210KEY `col_int_key` (`col_int_key`), 211KEY `col_datetime_key` (`col_datetime_key`) 212); 213INSERT INTO t4 VALUES 214(1,default,4,'2008-12-05','1900-01-01 00:00:00'); 215SELECT 216SQL_BIG_RESULT 217GRANDPARENT1 . `col_int_nokey` AS g1 218FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 . 219`col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` ) 220GROUP BY GRANDPARENT1 . `pk`; 221g1 2225 223DROP TABLE t4; 224# SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol> 225select * from t3 where a between 1 and 2 order by c; 226a b c 2272 -2 -2 2281 -1 -1 229explain select * from t3 where a between 1 and 2 order by c; 230id select_type table type possible_keys key key_len ref rows Extra 2311 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using where; Using filesort 232# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol> 233select * from t3 where b between -2 and -1 order by a; 234a b c 2351 -1 -1 2362 -2 -2 237explain select * from t3 where b between -2 and -1 order by a; 238id select_type table type possible_keys key key_len ref rows Extra 2391 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where 240# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> 241select * from t3 where b between -2 and -1 order by b; 242a b c 2432 -2 -2 2441 -1 -1 245explain select * from t3 where b between -2 and -1 order by b; 246id select_type table type possible_keys key key_len ref rows Extra 2471 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 248# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> 249select * from t3 where c between -2 and -1 order by b; 250a b c 2512 -2 -2 2521 -1 -1 253explain select * from t3 where c between -2 and -1 order by b; 254id select_type table type possible_keys key key_len ref rows Extra 2551 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort 256# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol> 257select * from t3 where b between -2 and -1 order by c; 258a b c 2592 -2 -2 2601 -1 -1 261explain select * from t3 where b between -2 and -1 order by c; 262id select_type table type possible_keys key key_len ref rows Extra 2631 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 264# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> 265select * from t3 where c between -2 and -1 order by c; 266a b c 2672 -2 -2 2681 -1 -1 269explain select * from t3 where c between -2 and -1 order by c; 270id select_type table type possible_keys key key_len ref rows Extra 2711 SIMPLE t3 range c c 5 NULL 2 Using index condition 272# SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 273select sum(b) from t1 group by b; 274sum(b) 275NULL 276-3 277-2 278-2 279explain select sum(b) from t1 group by b; 280id select_type table type possible_keys key key_len ref rows Extra 2811 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 282# SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 283select sum(c) from t1 group by c; 284sum(c) 285NULL 286-3 287-2 288-2 289explain select sum(c) from t1 group by c; 290id select_type table type possible_keys key key_len ref rows Extra 2911 SIMPLE t1 index NULL c 5 NULL 5 Using index 292# SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 293select sum(b) from t1 group by c; 294sum(b) 295NULL 296-3 297-2 298-2 299explain select sum(b) from t1 group by c; 300id select_type table type possible_keys key key_len ref rows Extra 3011 SIMPLE t1 index NULL c 5 NULL 5 302# SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 303select sum(c) from t1 group by b; 304sum(c) 305NULL 306-3 307-2 308-2 309explain select sum(c) from t1 group by b; 310id select_type table type possible_keys key key_len ref rows Extra 3111 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 312drop table t1; 313# 314# Bug#20241655: WL411:FAILING ASSERTION ASSERTION 315# 316CREATE TABLE BB ( 317col_time_key time NOT NULL, 318col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 319col_datetime_key datetime NOT NULL); 320INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25'); 321Warnings: 322Note 1265 Data truncated for column 'col_time_nokey' at row 1 323CREATE TABLE CC ( 324col_time_key time NOT NULL, 325col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 326col_datetime_key datetime NOT NULL 327); 328INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00'); 329Warnings: 330Note 1265 Data truncated for column 'col_time_nokey' at row 1 331SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey); 332g1 3331 334DROP TABLE BB, CC; 335# 336# Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL 337# JUMP WHILE SELECTING FROM VIEW 338# 339CREATE TABLE A ( 340pk INTEGER AUTO_INCREMENT, 341col_int_nokey INTEGER, 342col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 343PRIMARY KEY (pk) 344); 345CREATE TABLE C ( 346pk INTEGER AUTO_INCREMENT, 347col_int_nokey INTEGER, 348col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 349col_varchar_nokey VARCHAR(1), 350col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 351(CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED, 352PRIMARY KEY (pk), 353KEY (col_int_key), 354KEY (col_varchar_key, col_int_key) 355); 356INSERT INTO C ( 357col_int_nokey, 358col_varchar_nokey 359) VALUES (4, 'v'),(62, 'v'),(7, 'c'),(1, NULL),(0, 'x'),(7, 'i'),(7, 'e'),(1, 'p'),(7, 's'),(1, 'j'),(5, 'z'),(2, 'c'),(0, 'a'),(1, 'q'),(8, 'y'),(1, NULL),(1, 'r'),(9, 'v'),(1, NULL),(5, 'r'); 360CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1. 361col_varchar_key AS field1 , alias1.pk AS field2, alias2. 362col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON 363alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1 364.pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key 365> 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3 366LIMIT 100 OFFSET 6; 367Warnings: 368Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 369SELECT * FROM V1; 370field1 field2 field3 371qq 14 NULL 372rr 17 NULL 373ss 9 NULL 374xx 5 NULL 375DROP VIEW V1; 376DROP TABLE A,C; 377# 378# Bug#20406510: WL411:VALGRIND WARNINGS WITH 379# COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN 380# 381CREATE TABLE A ( 382pk INTEGER AUTO_INCREMENT, 383col_time_key TIME NOT NULL, 384col_datetime_key DATETIME NOT NULL, 385PRIMARY KEY (pk), 386KEY (col_time_key), 387KEY (col_datetime_key) 388); 389CREATE TABLE C ( 390pk INTEGER AUTO_INCREMENT, 391col_int_key INTEGER NOT NULL, 392col_varchar_key VARCHAR(1) NOT NULL, 393col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS 394(CONCAT(col_varchar_key, col_varchar_key)), 395PRIMARY KEY (pk), 396KEY (col_int_key), 397KEY (col_varchar_key, col_int_key) 398); 399INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd'); 400SELECT MIN( alias2 . col_int_key ) AS field1, 401COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2 402FROM ( A AS alias1 , C AS alias2 ) 403ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC; 404field1 field2 405NULL 0 406DROP TABLE A,C; 407# 408# Bug#20566325: WL8149: INNODB: FAILING ASSERTION: 409# COL_NR < TABLE->N_DEF 410# 411CREATE TABLE A ( 412pk INTEGER AUTO_INCREMENT, 413col_varchar_nokey VARCHAR(1) NOT NULL, 414col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 415(CONCAT(col_varchar_nokey, col_varchar_nokey)), 416PRIMARY KEY (pk) 417); 418INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k'); 419CREATE TABLE CC ( 420pk INTEGER AUTO_INCREMENT, 421col_datetime_nokey DATETIME /*! NULL */, 422col_time_nokey TIME /*! NULL */, 423col_time_key TIME GENERATED ALWAYS AS 424(ADDTIME(col_datetime_nokey, col_time_nokey)), 425col_varchar_nokey VARCHAR(1) /*! NULL */, 426col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 427(CONCAT(col_varchar_nokey, col_varchar_nokey)), 428PRIMARY KEY (pk)); 429INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES 430('13:06:13.033877','1900-01-01 00:00:00', 'p'), 431(NULL, '2007-05-25 11:58:54.015689', 'g'); 432SELECT 433table1.col_time_key AS field1, 434'z' AS field2 435FROM 436(CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON 437(table3.col_varchar_key = table2.col_varchar_nokey)) ON 438(table3.col_varchar_key = table2.col_varchar_nokey)) 439WHERE 440table2.pk != 6 441AND table1.col_varchar_key IN ('l', 's' , 'b' ) 442AND table3.col_varchar_key != table1.col_varchar_key 443ORDER BY table1.col_varchar_key , field1 , field2; 444field1 field2 445DROP TABLE A,CC; 446CREATE TABLE cc ( 447pk int(11) NOT NULL AUTO_INCREMENT, 448col_int_nokey int(11) NOT NULL, 449col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED, 450col_date_nokey date NOT NULL, 451col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED, 452col_datetime_nokey datetime NOT NULL, 453col_time_nokey time NOT NULL, 454col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED, 455col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED, 456col_varchar_nokey varchar(1) NOT NULL, 457col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED, 458PRIMARY KEY (pk), 459KEY gc_idx1 (col_int_key), 460KEY gc_idx2 (col_varchar_key), 461KEY gc_idx3 (col_date_key), 462KEY gc_idx4 (col_time_key), 463KEY gc_idx5 (col_datetime_key), 464KEY gc_idx6 (col_varchar_key,col_int_key), 465KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key), 466KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key, 467col_datetime_key) 468); 469INSERT INTO cc ( 470col_int_nokey, 471col_date_nokey, 472col_time_nokey, 473col_datetime_nokey, 474col_varchar_nokey 475) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), 476(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), 477(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), 478(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'), 479(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), 480(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'); 481SET @save_old_sql_mode= @@sql_mode; 482SET sql_mode=""; 483SELECT DISTINCT alias1.col_varchar_key AS field1 484FROM ( cc AS alias1 STRAIGHT_JOIN 485(( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON 486(alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON 487(( alias3 .pk >= alias2.col_int_nokey ) AND 488(alias3 .pk >= alias2.col_int_nokey ) )) 489WHERE alias1.col_varchar_key <= 'v' 490GROUP BY field1 HAVING field1 = 91 491ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC, 492alias1.col_time_key ASC, field1; 493field1 494Warnings: 495Warning 1292 Truncated incorrect DOUBLE value: 'c' 496Warning 1292 Truncated incorrect DOUBLE value: 't' 497Warning 1292 Truncated incorrect DOUBLE value: 'm' 498Warning 1292 Truncated incorrect DOUBLE value: 'd' 499Warning 1292 Truncated incorrect DOUBLE value: 'd' 500DROP TABLE cc; 501SET sql_mode=@save_old_sql_mode; 502# 503# Bug#20797941: WL8149:ASSERTION !TABLE || 504# (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET 505# 506CREATE TABLE t(a int, b int as(a+1)); 507INSERT INTO t(a) values(1),(2); 508SELECT * FROM t ORDER BY b; 509a b 5101 2 5112 3 512DROP TABLE t; 513DROP TABLE t2, t3; 514# 515# Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED 516# 517CREATE TABLE t1(a INT); 518INSERT INTO t1 VALUES(2147483647); 519ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL; 520ALTER TABLE t1 DROP COLUMN b; 521ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL; 522ALTER TABLE t1 DROP COLUMN c; 523ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL; 524ALTER TABLE t1 DROP COLUMN d; 525ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL; 526ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL; 527ERROR 22003: Out of range value for column 'c' at row 1 528ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL; 529ERROR 22003: Out of range value for column 'c' at row 1 530SELECT * FROM t1; 531a c 5322147483647 2147483647 533DROP TABLE t1; 534CREATE TABLE t1(a INT); 535INSERT INTO t1 VALUES(2147483647); 536ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; 537ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; 538ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 539ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED 540ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 541ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED 542ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; 543ERROR 22003: Out of range value for column 'f' at row 1 544ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; 545ERROR 22003: Out of range value for column 'g' at row 1 546DROP TABLE t1; 547# 548# Bug#21980430 GCOLS: CRASHING 549# 550CREATE TABLE t ( 551a INT, 552b BLOB, 553c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL, 554UNIQUE KEY i0008 (a) 555); 556INSERT INTO t(a,b) VALUES(1,'cccc'); 557EXPLAIN SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c 558WHERE b.b>c.a; 559id select_type table type possible_keys key key_len ref rows Extra 5601 SIMPLE b ALL i0008 NULL NULL NULL 1 5611 SIMPLE c ALL i0008 NULL NULL NULL 1 Range checked for each record (index map: 0x1) 562SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c 563WHERE b.b>c.a; 564c 565Warnings: 566Warning 1292 Truncated incorrect DOUBLE value: 'cccc' 567DROP TABLE t; 568set @optimizer_switch_save = @@optimizer_switch; 569set optimizer_switch='mrr_cost_based=off'; 570set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 571set read_rnd_buffer_size=32; 572CREATE TABLE t0 ( 573i1 INTEGER NOT NULL 574); 575INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 576CREATE TABLE t1 ( 577pk INTEGER NOT NULL, 578i1 INTEGER NOT NULL, 579i2 INTEGER NOT NULL, 580v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL, 581v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL, 582PRIMARY KEY (pk), 583INDEX idx(i1) 584); 585INSERT INTO t1 (pk, i1, i2) 586SELECT a0.i1 + a1.i1*10 + a2.i1*100, 587a0.i1 + a1.i1*10, 588a0.i1 + a1.i1*10 589FROM t0 AS a0, t0 AS a1, t0 AS a2; 590EXPLAIN SELECT * FROM t1 591WHERE i1 > 41 AND i1 <= 43; 592id select_type table type possible_keys key key_len ref rows Extra 5931 SIMPLE t1 range idx idx 4 NULL 20 Using index condition 594SELECT * FROM t1 595WHERE i1 > 41 AND i1 <= 43; 596pk i1 i2 v1 v2 597142 42 42 43 1 598143 43 43 44 1 599242 42 42 43 1 600243 43 43 44 1 601342 42 42 43 1 602343 43 43 44 1 60342 42 42 43 1 60443 43 43 44 1 605442 42 42 43 1 606443 43 43 44 1 607542 42 42 43 1 608543 43 43 44 1 609642 42 42 43 1 610643 43 43 44 1 611742 42 42 43 1 612743 43 43 44 1 613842 42 42 43 1 614843 43 43 44 1 615942 42 42 43 1 616943 43 43 44 1 617EXPLAIN SELECT * FROM t1 618WHERE v1 > 41 AND v1 <= 43; 619id select_type table type possible_keys key key_len ref rows Extra 6201 SIMPLE t1 ALL NULL NULL NULL NULL # Using where 621SELECT * FROM t1 622WHERE v1 > 41 AND v1 <= 43; 623pk i1 i2 v1 v2 624141 41 41 42 1 625142 42 42 43 1 626241 41 41 42 1 627242 42 42 43 1 628341 41 41 42 1 629342 42 42 43 1 63041 41 41 42 1 63142 42 42 43 1 632441 41 41 42 1 633442 42 42 43 1 634541 41 41 42 1 635542 42 42 43 1 636641 41 41 42 1 637642 42 42 43 1 638741 41 41 42 1 639742 42 42 43 1 640841 41 41 42 1 641842 42 42 43 1 642941 41 41 42 1 643942 42 42 43 1 644DROP TABLE t0, t1; 645set optimizer_switch= @optimizer_switch_save; 646set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 647# 648# Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN 649# SQL_JOIN_BUFFER.CC 650# 651# 652# Test 1: Dynamic range scan with one covering index 653# 654CREATE TABLE t1 ( 655i1 INTEGER NOT NULL, 656c1 VARCHAR(1) NOT NULL 657); 658INSERT INTO t1 659VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g'); 660CREATE TABLE t2 ( 661i1 INTEGER NOT NULL, 662c1 VARCHAR(1) NOT NULL 663); 664INSERT INTO t2 665VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e'); 666CREATE TABLE t3 ( 667pk INTEGER NOT NULL, 668i1 INTEGER, 669i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL, 670PRIMARY KEY (pk) 671); 672INSERT INTO t3 (pk, i1) 673VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5), 674(6, 39), (7, 6), (8, 8), (9, 3); 675CREATE TABLE t4 ( 676i1 INTEGER NOT NULL, 677c1 VARCHAR(1) NOT NULL 678); 679INSERT INTO t4 680VALUES (1, 'j'), (2, 'c'), (0, 'a'); 681ANALYZE TABLE t1, t2, t3, t4; 682Table Op Msg_type Msg_text 683test.t1 analyze status Engine-independent statistics collected 684test.t1 analyze status OK 685test.t2 analyze status Engine-independent statistics collected 686test.t2 analyze status OK 687test.t3 analyze status Engine-independent statistics collected 688test.t3 analyze status OK 689test.t4 analyze status Engine-independent statistics collected 690test.t4 analyze status OK 691EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 692FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 693WHERE ( t3.pk IN 694( 695SELECT /*+ QB_NAME(subq1) */ t4.i1 696FROM t4 697WHERE t4.c1 < 'o' 698 ) 699) 700AND t1.i1 <= t3.i2_key; 701id select_type table type possible_keys key key_len ref rows Extra 7021 PRIMARY t1 ALL NULL NULL NULL NULL 4 7031 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 7041 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 7051 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 7062 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 707SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 708FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 709WHERE ( t3.pk IN 710( 711SELECT /*+ QB_NAME(subq1) */ t4.i1 712FROM t4 713WHERE t4.c1 < 'o' 714 ) 715) 716AND t1.i1 <= t3.i2_key; 717c1 i1 718c 2 719c 5 720c 7 721c 7 722c 9 723g 2 724g 5 725g 7 726g 7 727g 9 728i 2 729i 5 730i 7 731i 7 732i 9 733t 2 734t 2 735t 5 736t 5 737t 7 738t 7 739t 7 740t 7 741t 9 742t 9 743# 744# Test 2: Two alternative covering indexes for the range scan 745# 746EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 747FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 748WHERE ( t3.pk IN 749( 750SELECT /*+ QB_NAME(subq1) */ t4.i1 751FROM t4 752WHERE t4.c1 < 'o' 753 ) 754) 755AND t1.i1 <= t3.i2_key; 756id select_type table type possible_keys key key_len ref rows Extra 7571 PRIMARY t1 ALL NULL NULL NULL NULL 4 7581 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 7591 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 7601 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 7612 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 762SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 763FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 764WHERE ( t3.pk IN 765( 766SELECT /*+ QB_NAME(subq1) */ t4.i1 767FROM t4 768WHERE t4.c1 < 'o' 769 ) 770) 771AND t1.i1 <= t3.i2_key; 772c1 i1 773c 2 774c 5 775c 7 776c 7 777c 9 778g 2 779g 5 780g 7 781g 7 782g 9 783i 2 784i 5 785i 7 786i 7 787i 9 788t 2 789t 2 790t 5 791t 5 792t 7 793t 7 794t 7 795t 7 796t 9 797t 9 798# 799# Test 3: One covering index including the base column for the virtual 800# column 801# 802EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 803FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 804WHERE ( t3.pk IN 805( 806SELECT /*+ QB_NAME(subq1) */ t4.i1 807FROM t4 808WHERE t4.c1 < 'o' 809 ) 810) 811AND t1.i1 <= t3.i2_key; 812id select_type table type possible_keys key key_len ref rows Extra 8131 PRIMARY t1 ALL NULL NULL NULL NULL 4 8141 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 8151 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 8161 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 8172 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 818SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 819FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 820WHERE ( t3.pk IN 821( 822SELECT /*+ QB_NAME(subq1) */ t4.i1 823FROM t4 824WHERE t4.c1 < 'o' 825 ) 826) 827AND t1.i1 <= t3.i2_key; 828c1 i1 829c 2 830c 5 831c 7 832c 7 833c 9 834g 2 835g 5 836g 7 837g 7 838g 9 839i 2 840i 5 841i 7 842i 7 843i 9 844t 2 845t 2 846t 5 847t 5 848t 7 849t 7 850t 7 851t 7 852t 9 853t 9 854# 855# Test 4: One non-covering index 856# 857# Add more data to the table so that it will run the dynamic range scan 858# as both table scan and range scan (the purpose of this is to make the 859# table scan more expensive). 860INSERT INTO t3 (pk, i1) 861VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1), 862(19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1), 863(28,1), (29,1); 864# Change the query to read an extra column (t3.i1) making the index 865# non-covering. 866EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 867FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 868WHERE ( t3.pk IN 869( 870SELECT /*+ QB_NAME(subq1) */ t4.i1 871FROM t4 872WHERE t4.c1 < 'o' 873 ) 874) 875AND t1.i1 <= t3.i2_key; 876id select_type table type possible_keys key key_len ref rows Extra 8771 PRIMARY t1 ALL NULL NULL NULL NULL 4 8781 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 8791 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 8801 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 8812 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 882SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 883FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 884WHERE ( t3.pk IN 885( 886SELECT /*+ QB_NAME(subq1) */ t4.i1 887FROM t4 888WHERE t4.c1 < 'o' 889 ) 890) 891AND t1.i1 <= t3.i2_key; 892c1 i1 i1 893c 2 48 894c 5 48 895c 7 48 896c 7 48 897c 9 48 898g 2 48 899g 5 48 900g 7 48 901g 7 48 902g 9 48 903i 2 48 904i 5 48 905i 7 48 906i 7 48 907i 9 48 908t 2 1 909t 2 48 910t 5 1 911t 5 48 912t 7 1 913t 7 1 914t 7 48 915t 7 48 916t 9 1 917t 9 48 918# 919# Test 5: Test where the added primary key to secondary indexes is 920# used after it has been included in the join buffer 921# 922EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 923FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 924WHERE ( t3.pk IN 925( 926SELECT /*+ QB_NAME(subq1) */ t4.i1 927FROM t4 928WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1) 929) 930) 931AND t1.i1 <= t3.i2_key; 932id select_type table type possible_keys key key_len ref rows Extra 9331 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary 9341 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 9351 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where 9361 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) 937SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 938FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 939WHERE ( t3.pk IN 940( 941SELECT /*+ QB_NAME(subq1) */ t4.i1 942FROM t4 943WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1) 944) 945) 946AND t1.i1 <= t3.i2_key; 947c1 i1 948c 2 949c 5 950c 7 951c 7 952c 9 953g 2 954g 5 955g 7 956g 7 957g 9 958i 2 959i 5 960i 7 961i 7 962i 9 963t 2 964t 2 965t 5 966t 5 967t 7 968t 7 969t 7 970t 7 971t 9 972t 9 973DROP TABLE t1, t2, t3, t4; 974SET optimizer_switch='derived_merge=default'; 975DROP VIEW IF EXISTS v1,v2; 976DROP TABLE IF EXISTS t1,t2,t3; 977DROP PROCEDURE IF EXISTS p1; 978DROP FUNCTION IF EXISTS f1; 979DROP TRIGGER IF EXISTS trg1; 980DROP TRIGGER IF EXISTS trg2; 981set sql_warnings = 0; 982