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 = 'MyISAM'; 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 system 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 system c NULL NULL NULL 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 system 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 NULL NULL NULL NULL NULL NULL NULL Select tables optimized away 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 index condition 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# bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC 186CREATE TABLE t4 ( 187`pk` int(11) NOT NULL , 188`col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED, 189`col_int_key` int(11) DEFAULT NULL, 190`col_date_nokey` date DEFAULT NULL, 191`col_datetime_key` datetime DEFAULT NULL, 192PRIMARY KEY (`pk`), 193KEY `col_int_key` (`col_int_key`), 194KEY `col_datetime_key` (`col_datetime_key`) 195); 196INSERT INTO t4 VALUES 197(1,default,4,'2008-12-05','1900-01-01 00:00:00'); 198SELECT 199SQL_BIG_RESULT 200GRANDPARENT1 . `col_int_nokey` AS g1 201FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 . 202`col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` ) 203GROUP BY GRANDPARENT1 . `pk`; 204g1 2055 206DROP TABLE t4; 207# SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol> 208select * from t3 where a between 1 and 2 order by c; 209a b c 2102 -2 -2 2111 -1 -1 212explain select * from t3 where a between 1 and 2 order by c; 213id select_type table type possible_keys key key_len ref rows Extra 2141 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort 215# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol> 216select * from t3 where b between -2 and -1 order by a; 217a b c 2181 -1 -1 2192 -2 -2 220explain select * from t3 where b between -2 and -1 order by a; 221id select_type table type possible_keys key key_len ref rows Extra 2221 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 223# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol> 224select * from t3 where c between -2 and -1 order by a; 225a b c 2261 -1 -1 2272 -2 -2 228explain select * from t3 where c between -2 and -1 order by a; 229id select_type table type possible_keys key key_len ref rows Extra 2301 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort 231# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> 232select * from t3 where b between -2 and -1 order by b; 233a b c 2342 -2 -2 2351 -1 -1 236explain select * from t3 where b between -2 and -1 order by b; 237id select_type table type possible_keys key key_len ref rows Extra 2381 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 239# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> 240select * from t3 where c between -2 and -1 order by b; 241a b c 2422 -2 -2 2431 -1 -1 244explain select * from t3 where c between -2 and -1 order by b; 245id select_type table type possible_keys key key_len ref rows Extra 2461 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort 247# SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol> 248select * from t3 where b between -2 and -1 order by c; 249a b c 2502 -2 -2 2511 -1 -1 252explain select * from t3 where b between -2 and -1 order by c; 253id select_type table type possible_keys key key_len ref rows Extra 2541 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort 255# SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> 256select * from t3 where c between -2 and -1 order by c; 257a b c 2582 -2 -2 2591 -1 -1 260explain select * from t3 where c between -2 and -1 order by c; 261id select_type table type possible_keys key key_len ref rows Extra 2621 SIMPLE t3 range c c 5 NULL 2 Using index condition 263# SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 264select sum(b) from t1 group by b; 265sum(b) 266NULL 267-3 268-2 269-2 270explain select sum(b) from t1 group by b; 271id select_type table type possible_keys key key_len ref rows Extra 2721 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 273# SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 274select sum(c) from t1 group by c; 275sum(c) 276NULL 277-3 278-2 279-2 280explain select sum(c) from t1 group by c; 281id select_type table type possible_keys key key_len ref rows Extra 2821 SIMPLE t1 index NULL c 5 NULL 5 Using index 283# SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 284select sum(b) from t1 group by c; 285sum(b) 286NULL 287-3 288-2 289-2 290explain select sum(b) from t1 group by c; 291id select_type table type possible_keys key key_len ref rows Extra 2921 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 293# SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 294select sum(c) from t1 group by b; 295sum(c) 296NULL 297-3 298-2 299-2 300explain select sum(c) from t1 group by b; 301id select_type table type possible_keys key key_len ref rows Extra 3021 SIMPLE t1 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort 303drop table t1; 304# 305# Bug#20241655: WL411:FAILING ASSERTION ASSERTION 306# 307CREATE TABLE BB ( 308col_time_key time NOT NULL, 309col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 310col_datetime_key datetime NOT NULL); 311INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25'); 312Warnings: 313Note 1265 Data truncated for column 'col_time_nokey' at row 1 314CREATE TABLE CC ( 315col_time_key time NOT NULL, 316col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 317col_datetime_key datetime NOT NULL 318); 319INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00'); 320Warnings: 321Note 1265 Data truncated for column 'col_time_nokey' at row 1 322SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey); 323g1 3241 325DROP TABLE BB, CC; 326# 327# Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL 328# JUMP WHILE SELECTING FROM VIEW 329# 330CREATE TABLE A ( 331pk INTEGER AUTO_INCREMENT, 332col_int_nokey INTEGER, 333col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 334PRIMARY KEY (pk) 335); 336CREATE TABLE C ( 337pk INTEGER AUTO_INCREMENT, 338col_int_nokey INTEGER, 339col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 340col_varchar_nokey VARCHAR(1), 341col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 342(CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED, 343PRIMARY KEY (pk), 344KEY (col_int_key), 345KEY (col_varchar_key, col_int_key) 346); 347INSERT INTO C ( 348col_int_nokey, 349col_varchar_nokey 350) 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'); 351CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1. 352col_varchar_key AS field1 , alias1.pk AS field2, alias2. 353col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON 354alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1 355.pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key 356> 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3 357LIMIT 100 OFFSET 6; 358Warnings: 359Warning 1354 View merge algorithm can't be used here for now (assumed undefined algorithm) 360SELECT * FROM V1; 361field1 field2 field3 362qq 14 NULL 363rr 17 NULL 364ss 9 NULL 365xx 5 NULL 366DROP VIEW V1; 367DROP TABLE A,C; 368# 369# Bug#20406510: WL411:VALGRIND WARNINGS WITH 370# COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN 371# 372CREATE TABLE A ( 373pk INTEGER AUTO_INCREMENT, 374col_time_key TIME NOT NULL, 375col_datetime_key DATETIME NOT NULL, 376PRIMARY KEY (pk), 377KEY (col_time_key), 378KEY (col_datetime_key) 379); 380CREATE TABLE C ( 381pk INTEGER AUTO_INCREMENT, 382col_int_key INTEGER NOT NULL, 383col_varchar_key VARCHAR(1) NOT NULL, 384col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS 385(CONCAT(col_varchar_key, col_varchar_key)), 386PRIMARY KEY (pk), 387KEY (col_int_key), 388KEY (col_varchar_key, col_int_key) 389); 390INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd'); 391SELECT MIN( alias2 . col_int_key ) AS field1, 392COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2 393FROM ( A AS alias1 , C AS alias2 ) 394ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC; 395field1 field2 396NULL 0 397DROP TABLE A,C; 398# 399# Bug#20566325: WL8149: INNODB: FAILING ASSERTION: 400# COL_NR < TABLE->N_DEF 401# 402CREATE TABLE A ( 403pk INTEGER AUTO_INCREMENT, 404col_varchar_nokey VARCHAR(1) NOT NULL, 405col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 406(CONCAT(col_varchar_nokey, col_varchar_nokey)), 407PRIMARY KEY (pk) 408); 409INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k'); 410CREATE TABLE CC ( 411pk INTEGER AUTO_INCREMENT, 412col_datetime_nokey DATETIME /*! NULL */, 413col_time_nokey TIME /*! NULL */, 414col_time_key TIME GENERATED ALWAYS AS 415(ADDTIME(col_datetime_nokey, col_time_nokey)), 416col_varchar_nokey VARCHAR(1) /*! NULL */, 417col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 418(CONCAT(col_varchar_nokey, col_varchar_nokey)), 419PRIMARY KEY (pk)); 420INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES 421('13:06:13.033877','1900-01-01 00:00:00', 'p'), 422(NULL, '2007-05-25 11:58:54.015689', 'g'); 423SELECT 424table1.col_time_key AS field1, 425'z' AS field2 426FROM 427(CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON 428(table3.col_varchar_key = table2.col_varchar_nokey)) ON 429(table3.col_varchar_key = table2.col_varchar_nokey)) 430WHERE 431table2.pk != 6 432AND table1.col_varchar_key IN ('l', 's' , 'b' ) 433AND table3.col_varchar_key != table1.col_varchar_key 434ORDER BY table1.col_varchar_key , field1 , field2; 435field1 field2 436DROP TABLE A,CC; 437# 438# Bug#20573302: WL8149: SEGV IN HA_INNOBASE:: 439# BUILD_TEMPLATE AT INNOBASE/HANDLER/HA_INNODB.CC:665 440# 441CREATE TABLE c ( 442pk INTEGER AUTO_INCREMENT, 443col_int_nokey INTEGER NOT NULL, 444col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 445col_date_nokey DATE NOT NULL, 446col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, 447col_datetime_nokey DATETIME NOT NULL, 448col_time_nokey TIME NOT NULL, 449col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 450col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 451col_varchar_nokey VARCHAR(1) NOT NULL, 452col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)), 453PRIMARY KEY (pk), 454KEY (col_int_key), 455KEY (col_varchar_key), 456KEY (col_date_key), 457KEY (col_time_key), 458KEY (col_datetime_key), 459KEY (col_int_key, col_varchar_key), 460KEY (col_int_key, col_varchar_key, col_date_key, 461col_time_key, col_datetime_key)); 462INSERT /*! IGNORE */ INTO c ( 463col_int_nokey, 464col_date_nokey, 465col_time_nokey, 466col_datetime_nokey, 467col_varchar_nokey 468) VALUES 469(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), 470(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), 471(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), 472(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'), 473(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), 474(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'), 475(6, '2007-06-18', NULL, '2002-08-20 22:48:00.035785', 'd'), 476(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'), 477(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'), 478(8, NULL, '07:05:51.006712', '1900-01-01 00:00:00', 'm'), 479(4, '2006-03-09', '19:22:21.057406', '2008-05-16 08:09:06.002924', 'b'), 480(4, '2001-06-05', '03:53:16.001370', '2001-01-20 12:47:23.022022', 'x'), 481(7, '2006-05-28', '09:16:38.034570', '2008-07-02 00:00:00', 'g'), 482(4, '2001-04-19', '15:37:26.028315', '1900-01-01 00:00:00', 'p'), 483(1, '1900-01-01', '00:00:00', '2002-12-08 11:34:58.001571', 'q'), 484(9, '2004-08-20', '05:03:03.047452', '1900-01-01 00:00:00', 'w'), 485(4, '2004-10-10', '02:59:24.063764', '1900-01-01 00:00:00', 'd'), 486(8, '2000-04-02', '00:01:58.064243', '2002-08-25 20:35:06.064634', 'e'), 487(4, '2006-11-02', '00:00:00', '2001-10-22 11:13:24.048128', 'b'), 488(8, '2009-01-28', '02:20:16.024931', '2003-03-12 02:00:34.029335', 'y'); 489Warnings: 490Note 1265 Data truncated for column 'col_time_key' at row 1 491Note 1265 Data truncated for column 'col_time_key' at row 2 492Note 1265 Data truncated for column 'col_time_key' at row 3 493Note 1265 Data truncated for column 'col_time_key' at row 4 494Note 1265 Data truncated for column 'col_time_key' at row 5 495Note 1265 Data truncated for column 'col_time_key' at row 6 496Warning 1048 Column 'col_time_nokey' cannot be null 497Note 1265 Data truncated for column 'col_time_key' at row 7 498Note 1265 Data truncated for column 'col_time_key' at row 8 499Note 1265 Data truncated for column 'col_time_key' at row 9 500Warning 1048 Column 'col_date_nokey' cannot be null 501Warning 1292 Incorrect datetime value: '0000-00-00' 502Note 1265 Data truncated for column 'col_time_key' at row 10 503Note 1265 Data truncated for column 'col_time_key' at row 11 504Note 1265 Data truncated for column 'col_time_key' at row 12 505Note 1265 Data truncated for column 'col_time_key' at row 13 506Note 1265 Data truncated for column 'col_time_key' at row 14 507Note 1265 Data truncated for column 'col_time_key' at row 15 508Note 1265 Data truncated for column 'col_time_key' at row 16 509Note 1265 Data truncated for column 'col_time_key' at row 17 510Note 1265 Data truncated for column 'col_time_key' at row 18 511Note 1265 Data truncated for column 'col_time_key' at row 19 512Note 1265 Data truncated for column 'col_time_key' at row 20 513CREATE TABLE cc ( 514pk INTEGER AUTO_INCREMENT, 515col_int_nokey INTEGER NOT NULL, 516col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 517col_date_nokey DATE NOT NULL, 518col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, 519col_datetime_nokey DATETIME NOT NULL, 520col_time_nokey TIME NOT NULL, 521col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 522col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 523col_varchar_nokey VARCHAR(1) NOT NULL, 524col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)), 525PRIMARY KEY (pk), 526KEY (col_int_key), 527KEY (col_varchar_key), 528KEY (col_date_key), 529KEY (col_time_key), 530KEY (col_datetime_key), 531KEY (col_int_key, col_varchar_key), 532KEY (col_int_key, col_varchar_key, col_date_key, 533col_time_key, col_datetime_key)); 534INSERT /*! IGNORE */ INTO cc ( 535col_int_nokey, 536col_date_nokey, 537col_time_nokey, 538col_datetime_nokey, 539col_varchar_nokey 540) VALUES 541(0, '2003-02-06', '22:02:09.059926', '2003-08-07 14:43:09.011144', 'x'), 542(0, '2005-04-16', '19:33:15.014160', '2005-12-11 00:00:00', 'n'), 543(1, '2005-07-23', '22:03:16.058787', '2005-12-26 20:48:07.043628', 'w'), 544(7, '2001-11-15', '06:31:23.027263', '2008-06-12 06:41:21.012493', 's'), 545(0, '2006-03-24', '02:19:08.013275', '2007-10-11 18:46:28.030000', 'a'), 546(4, '2008-07-10', NULL, '2006-04-04 22:22:40.057947', 'd'), 547(1, '2009-12-07', NULL, '2002-08-10 20:52:58.035137', 'w'), 548(1, '2008-05-01', '10:28:01.038587', '2008-10-03 11:17:23.005299', 'j'), 549(1, '2008-06-22', '00:00:00', '2009-01-06 20:11:01.034339', 'm'), 550(4, '2001-11-11', '15:02:50.048785', '2009-09-19 00:00:00', 'k'), 551(7, '2000-12-21', '05:29:13.012729', '2007-09-02 12:14:27.029187', 't'), 552(4, '2007-09-03', '23:45:33.048507', '2003-09-26 00:00:00', 'k'), 553(2, '2003-02-18', '19:10:53.057455', '2001-11-18 18:10:16.063189', 'e'), 554(0, '2008-12-01', '01:45:27.037313', '2005-02-15 04:08:17.015554', 'i'), 555(1, '2008-10-18', '03:56:03.060218', '2009-06-13 23:04:40.013006', 't'), 556(91, '2004-08-28', '12:43:17.023797', '1900-01-01 00:00:00', 'm'), 557(6, '2006-10-05', '13:33:46.053634', '2005-03-20 02:48:24.045653', 'z'), 558(3, '2003-05-16', NULL, '2002-03-16 11:47:27.045297', 'c'), 559(6, '2008-10-10', NULL, '2000-05-22 00:00:00', 'i'), 560(8, '2002-01-19', '05:18:40.006865', '2009-02-12 00:00:00', 'v'); 561Warnings: 562Note 1265 Data truncated for column 'col_time_key' at row 1 563Note 1265 Data truncated for column 'col_time_key' at row 2 564Note 1265 Data truncated for column 'col_time_key' at row 3 565Note 1265 Data truncated for column 'col_time_key' at row 4 566Note 1265 Data truncated for column 'col_time_key' at row 5 567Warning 1048 Column 'col_time_nokey' cannot be null 568Note 1265 Data truncated for column 'col_time_key' at row 6 569Warning 1048 Column 'col_time_nokey' cannot be null 570Note 1265 Data truncated for column 'col_time_key' at row 7 571Note 1265 Data truncated for column 'col_time_key' at row 8 572Note 1265 Data truncated for column 'col_time_key' at row 9 573Note 1265 Data truncated for column 'col_time_key' at row 10 574Note 1265 Data truncated for column 'col_time_key' at row 11 575Note 1265 Data truncated for column 'col_time_key' at row 12 576Note 1265 Data truncated for column 'col_time_key' at row 13 577Note 1265 Data truncated for column 'col_time_key' at row 14 578Note 1265 Data truncated for column 'col_time_key' at row 15 579Note 1265 Data truncated for column 'col_time_key' at row 16 580Note 1265 Data truncated for column 'col_time_key' at row 17 581Warning 1048 Column 'col_time_nokey' cannot be null 582Note 1265 Data truncated for column 'col_time_key' at row 18 583Warning 1048 Column 'col_time_nokey' cannot be null 584Note 1265 Data truncated for column 'col_time_key' at row 19 585Note 1265 Data truncated for column 'col_time_key' at row 20 586EXPLAIN 587SELECT subquery2_t2.col_int_key AS subquery2_field1 588FROM (c AS subquery2_t1 RIGHT JOIN 589(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 590(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 591(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 592ORDER BY subquery2_field1; 593id select_type table type possible_keys key key_len ref rows Extra 5941 SIMPLE subquery2_t2 index NULL col_int_key_2 10 NULL 20 # 5951 SIMPLE subquery2_t3 ALL NULL NULL NULL NULL 20 # 5961 SIMPLE subquery2_t1 index NULL PRIMARY 4 NULL 20 # 597SELECT subquery2_t2.col_int_key AS subquery2_field1 598FROM (c AS subquery2_t1 RIGHT JOIN 599(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 600(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 601(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 602ORDER BY subquery2_field1; 603subquery2_field1 6041 6051 6061 6071 6081 6091 6101 6111 6121 6131 6141 6151 6161 6171 6181 6191 6201 6211 6221 6231 6241 6251 6261 6271 6281 6291 6301 6311 6321 6331 6341 6351 6361 6371 6382 6394 6404 6414 6424 6434 6444 6454 6464 6474 6484 6494 6504 6514 6524 6534 6544 6554 6564 6574 6584 6594 6604 6614 6624 6634 6644 6654 6664 6674 6684 6694 6704 6714 6724 6734 6744 6754 6766 6776 6786 6796 6807 6817 6828 6838 6848 6858 6869 6879 68824 689SELECT subquery2_t2.col_int_key AS subquery2_field1 690FROM (c AS subquery2_t1 RIGHT JOIN 691(c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 692(subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 693(subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 694ORDER BY subquery2_field1; 695subquery2_field1 6961 6971 6981 6991 7001 7011 7021 7031 7041 7051 7061 7071 7081 7091 7101 7111 7121 7131 7141 7151 7161 7171 7181 7191 7201 7211 7221 7231 7241 7251 7261 7271 7281 7291 7302 7314 7324 7334 7344 7354 7364 7374 7384 7394 7404 7414 7424 7434 7444 7454 7464 7474 7484 7494 7504 7514 7524 7534 7544 7554 7564 7574 7584 7594 7604 7614 7624 7634 7644 7654 7664 7674 7686 7696 7706 7716 7727 7737 7748 7758 7768 7778 7789 7799 78024 781DROP TABLE c,cc; 782# 783# Bug#2081065: WL8149:RESULT DIFF SEEN FOR SIMPLE 784# RANGE QUERIES WITH ORDER BY 785# 786CREATE TABLE cc ( 787pk INTEGER AUTO_INCREMENT, 788col_int_nokey INTEGER NOT NULL, 789col_int_key INTEGER GENERATED ALWAYS AS 790(col_int_nokey + col_int_nokey) VIRTUAL, 791PRIMARY KEY (pk), 792KEY (col_int_key) 793); 794INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5); 795EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3; 796id select_type table type possible_keys key key_len ref rows Extra 7971 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # 798SELECT pk FROM cc WHERE col_int_key > 3; 799pk 8005 8016 8023 803EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; 804id select_type table type possible_keys key key_len ref rows Extra 8051 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # 806SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; 807pk 8083 8095 8106 811DROP TABLE cc; 812# 813# Bug#20849676 :WL8149:ASSERTION `!TABLE || (!TABLE->READ_SET 814# || BITMAP_IS_SET(TABLE->READ_SET 815# 816CREATE TABLE c ( 817pk INTEGER AUTO_INCREMENT, 818col_int_nokey INTEGER NOT NULL, 819col_int_key INTEGER GENERATED ALWAYS AS 820(col_int_nokey + col_int_nokey) VIRTUAL, 821col_varchar_nokey VARCHAR(1) NOT NULL, 822col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 823(CONCAT(col_varchar_nokey, col_varchar_nokey)), 824PRIMARY KEY (pk), 825KEY (col_int_key), 826KEY (col_varchar_key), 827KEY (col_int_key, col_varchar_key) 828) ; 829INSERT INTO c (col_int_nokey, col_varchar_nokey) VALUES 830(1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'),(6, 'd'), 831(2, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'),(1, 'q'), 832(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y'); 833CREATE TABLE a ( 834pk INTEGER AUTO_INCREMENT, 835col_datetime_nokey DATETIME NOT NULL, 836col_time_nokey TIME NOT NULL, 837col_datetime_key DATETIME GENERATED ALWAYS AS 838(ADDTIME(col_datetime_nokey, col_time_nokey)), 839col_time_key TIME GENERATED ALWAYS AS 840(ADDTIME(col_datetime_nokey, col_time_nokey)), 841col_varchar_nokey VARCHAR(1) NOT NULL, 842col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 843(CONCAT(col_varchar_nokey, col_varchar_nokey)), 844PRIMARY KEY (pk), 845KEY (col_varchar_key), 846KEY (col_time_key), 847KEY (col_datetime_key), 848KEY (col_varchar_key, col_time_key, col_datetime_key) 849); 850INSERT INTO a ( 851col_time_nokey, 852col_datetime_nokey, 853col_varchar_nokey) VALUES 854('04:08:02.046897', '2001-11-04 19:07:55.051133', 'k'); 855Warnings: 856Note 1265 Data truncated for column 'col_time_key' at row 1 857ANALYZE TABLE a, c; 858Table Op Msg_type Msg_text 859test.a analyze status Engine-independent statistics collected 860test.a analyze status OK 861test.c analyze status Engine-independent statistics collected 862test.c analyze status OK 863EXPLAIN 864SELECT 865table1.pk AS field1 , 866table1.col_datetime_key AS field2 867FROM 868( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT 869SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2 870STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key = 871SUBQUERY1_t2.col_varchar_key ) ) 872ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key 873OR SUBQUERY1_t1.col_int_key <> 1 ) ) 874WHERE SUBQUERY1_t2.pk >= 9 ) AS table3 875ON (table3.col_int_key = table2.col_int_key ) ) ) 876ON (table3.col_int_nokey = table2.pk ) ) 877GROUP BY field1, field2; 878id select_type table type possible_keys key key_len ref rows Extra 8791 PRIMARY table1 system NULL NULL NULL NULL 1 # 8801 PRIMARY table2 ALL PRIMARY,col_int_key,col_int_key_2 NULL NULL NULL 19 # 8811 PRIMARY <derived2> ref key0 key0 9 test.table2.pk,test.table2.col_int_key 10 # 8822 DERIVED SUBQUERY1_t2 ALL PRIMARY,col_int_key,col_varchar_key,col_int_key_2 NULL NULL NULL 19 # 8832 DERIVED SUBQUERY1_t3 ref PRIMARY,col_varchar_key col_varchar_key 5 test.SUBQUERY1_t2.col_varchar_key 1 # 8842 DERIVED SUBQUERY1_t1 ALL col_int_key,col_int_key_2 NULL NULL NULL 19 # 885SELECT 886table1.pk AS field1 , 887table1.col_datetime_key AS field2 888FROM 889( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT 890SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2 891STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key = 892SUBQUERY1_t2.col_varchar_key ) ) 893ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key 894OR SUBQUERY1_t1.col_int_key <> 1 ) ) 895WHERE SUBQUERY1_t2.pk >= 9 ) AS table3 896ON (table3.col_int_key = table2.col_int_key ) ) ) 897ON (table3.col_int_nokey = table2.pk ) ) 898GROUP BY field1, field2; 899field1 field2 9001 2001-11-04 23:15:57 901DROP TABLE IF EXISTS c,a; 902CREATE TABLE c ( 903col_int_nokey INTEGER NOT NULL, 904col_int_key INTEGER GENERATED ALWAYS AS 905(col_int_nokey + col_int_nokey) VIRTUAL, 906col_varchar_nokey VARCHAR(1) NOT NULL, 907col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 908(CONCAT(col_varchar_nokey, col_varchar_nokey)), 909KEY (col_int_key), 910KEY (col_int_key, col_varchar_key) 911) ; 912INSERT INTO c ( 913col_int_nokey, 914col_varchar_nokey 915) VALUES (1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'), 916(6, 'd'),(2, 's'),(4, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'), 917(1, 'q'),(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y'); 918CREATE TABLE cc ( 919col_int_nokey INTEGER, 920col_int_key INTEGER GENERATED ALWAYS AS 921(col_int_nokey + col_int_nokey) VIRTUAL, 922col_varchar_nokey VARCHAR(1), 923col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 924(CONCAT(col_varchar_nokey, col_varchar_nokey)), 925KEY (col_int_key), 926KEY (col_varchar_key), 927KEY (col_int_key, col_varchar_key), 928KEY (col_int_key, col_int_nokey), 929KEY (col_varchar_key, col_varchar_nokey) 930); 931INSERT INTO cc ( 932col_int_nokey, 933col_varchar_nokey 934) VALUES (8, 'p'),(9, 'g'),(9, 'i'),(4, 'p'),(7, 'h'),(1, 'e'),(8, 'e'),(6, 'u'), 935(6, 'j'),(6, 'e'),(1, 'z'),(227, 'w'),(NULL, 't'),(9, 'i'),(1, 'i'),(8, 'i'), 936(5, 'b'),(8,'m'),(7, 'j'),(2, 'v'); 937ANALYZE TABLE c, cc; 938Table Op Msg_type Msg_text 939test.c analyze status Engine-independent statistics collected 940test.c analyze status OK 941test.cc analyze status Engine-independent statistics collected 942test.cc analyze status OK 943EXPLAIN SELECT 944alias2 . col_varchar_key AS field1 945FROM ( cc AS alias1 , cc AS alias2 ) 946WHERE 947( alias2 . col_int_key , alias1 . col_int_nokey ) 948NOT IN 949( 950SELECT 951DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 , 952SQ1_alias1 . col_int_key AS SQ1_field2 953FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 ) 954GROUP BY SQ1_field1 , SQ1_field2 955) 956GROUP BY field1; 957id select_type table type possible_keys key key_len ref rows Extra 9581 PRIMARY alias1 index NULL col_int_key_3 10 NULL 20 # 9591 PRIMARY alias2 index NULL col_int_key_2 10 NULL 20 # 9602 MATERIALIZED SQ1_alias1 index col_int_key,col_int_key_2,col_int_key_3 col_int_key 5 NULL 20 # 9612 MATERIALIZED SQ1_alias2 ALL NULL NULL NULL NULL 20 # 962SELECT 963alias2 . col_varchar_key AS field1 964FROM ( cc AS alias1 , cc AS alias2 ) 965WHERE 966( alias2 . col_int_key , alias1 . col_int_nokey ) 967NOT IN 968( 969SELECT 970DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 , 971SQ1_alias1 . col_int_key AS SQ1_field2 972FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 ) 973GROUP BY SQ1_field1 , SQ1_field2 974) 975GROUP BY field1; 976field1 977bb 978ee 979gg 980hh 981ii 982jj 983mm 984pp 985uu 986ww 987DROP TABLE IF EXISTS c,cc; 988SET @save_old_sql_mode= @@sql_mode; 989SET sql_mode=""; 990CREATE TABLE d ( 991col_int int(11) DEFAULT NULL, 992col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 993pk int(11) NOT NULL AUTO_INCREMENT, 994col_int_key int(11) GENERATED ALWAYS AS (col_int+col_int) VIRTUAL, 995col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT(SUBSTRING(col_varchar_10_utf8, -1), 5)) VIRTUAL, 996PRIMARY KEY (pk), 997KEY col_int_key (col_int_key), 998KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), 999KEY cover_key1 (col_int_key, col_varchar_10_utf8_key) 1000); 1001INSERT INTO d (col_int, col_varchar_10_utf8) VALUES ('qhlhtrovam',1),('how',2),('htrovamzqr',3),('rovamzqrdc',4),('well',5),('g',6),('rdcenchyhu',7),('want',8); 1002SELECT table1.pk AS field1 FROM d AS table1 LEFT JOIN d AS table2 ON table1.col_varchar_10_utf8_key = table2.col_varchar_10_utf8_key WHERE table1.col_int_key IS NULL GROUP BY table1.pk ; 1003field1 1004DROP TABLE d; 1005# 1006# Bug#21153237: WL8149: QUERIES USING FILESORT 1007# ON VIRTUAL GC HAVING INDEX GIVES WRONG RESULTS 1008# 1009CREATE TABLE j ( 1010col_int int(11), 1011pk int(11) NOT NULL, 1012col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 1013col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8 GENERATED ALWAYS AS 1014(col_varchar_10_utf8) VIRTUAL, 1015PRIMARY KEY (pk), 1016KEY cover_key1 (col_int, col_varchar_255_utf8_key)); 1017INSERT INTO j(col_int, pk, col_varchar_10_utf8) VALUES(9, 1, '951910400'), 1018(-1934295040, 2, '1235025920'),(-584581120, 3, '-1176633344'),(3, 4, '1074462720'); 1019EXPLAIN SELECT col_varchar_255_utf8_key FROM j ORDER BY 1; 1020id select_type table type possible_keys key key_len ref rows Extra 10211 SIMPLE j index NULL cover_key1 773 NULL 4 # 1022SELECT col_varchar_255_utf8_key FROM j ORDER BY col_varchar_255_utf8_key; 1023col_varchar_255_utf8_key 1024-117663334 10251074462720 10261235025920 1027951910400 1028DROP TABLE j; 1029set sql_mode= @save_old_sql_mode; 1030CREATE TABLE cc ( 1031pk int(11) NOT NULL AUTO_INCREMENT, 1032col_int_nokey int(11) NOT NULL, 1033col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED, 1034col_date_nokey date NOT NULL, 1035col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED, 1036col_datetime_nokey datetime NOT NULL, 1037col_time_nokey time NOT NULL, 1038col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED, 1039col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED, 1040col_varchar_nokey varchar(1) NOT NULL, 1041col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED, 1042PRIMARY KEY (pk), 1043KEY gc_idx1 (col_int_key), 1044KEY gc_idx2 (col_varchar_key), 1045KEY gc_idx3 (col_date_key), 1046KEY gc_idx4 (col_time_key), 1047KEY gc_idx5 (col_datetime_key), 1048KEY gc_idx6 (col_varchar_key,col_int_key), 1049KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key), 1050KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key, 1051col_datetime_key) 1052); 1053INSERT INTO cc ( 1054col_int_nokey, 1055col_date_nokey, 1056col_time_nokey, 1057col_datetime_nokey, 1058col_varchar_nokey 1059) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), 1060(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), 1061(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), 1062(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'), 1063(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), 1064(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'); 1065SET @save_old_sql_mode= @@sql_mode; 1066SET sql_mode=""; 1067SELECT DISTINCT alias1.col_varchar_key AS field1 1068FROM ( cc AS alias1 STRAIGHT_JOIN 1069(( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON 1070(alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON 1071(( alias3 .pk >= alias2.col_int_nokey ) AND 1072(alias3 .pk >= alias2.col_int_nokey ) )) 1073WHERE alias1.col_varchar_key <= 'v' 1074GROUP BY field1 HAVING field1 = 91 1075ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC, 1076alias1.col_time_key ASC, field1; 1077field1 1078Warnings: 1079Warning 1292 Truncated incorrect DOUBLE value: 'c' 1080Warning 1292 Truncated incorrect DOUBLE value: 't' 1081Warning 1292 Truncated incorrect DOUBLE value: 'm' 1082Warning 1292 Truncated incorrect DOUBLE value: 'd' 1083Warning 1292 Truncated incorrect DOUBLE value: 'd' 1084DROP TABLE cc; 1085SET sql_mode=@save_old_sql_mode; 1086# 1087# Bug#20797941: WL8149:ASSERTION !TABLE || 1088# (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET 1089# 1090CREATE TABLE t(a int, b int as(a+1)); 1091INSERT INTO t(a) values(1),(2); 1092SELECT * FROM t ORDER BY b; 1093a b 10941 2 10952 3 1096DROP TABLE t; 1097# 1098# Testing a few index-based accesses on the virtual column 1099# 1100CREATE TABLE t1 ( 1101id int(11) NOT NULL, 1102b int(11) GENERATED ALWAYS AS (id+1) VIRTUAL, 1103UNIQUE KEY (b) ); 1104INSERT INTO t1 (id) VALUES(NULL); 1105ERROR 23000: Column 'id' cannot be null 1106INSERT INTO t1 (id) VALUES(2),(3); 1107EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE b=3; 1108id select_type table type possible_keys key key_len ref rows Extra 11091 SIMPLE t1 const b b 5 const 1 1110EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b; 1111id select_type table type possible_keys key key_len ref rows Extra 11121 SIMPLE t2 ALL b NULL NULL NULL 2 Using where 11131 SIMPLE t1 eq_ref b b 5 test.t2.b 1 1114EXPLAIN SELECT b FROM t1 FORCE INDEX(b); 1115id select_type table type possible_keys key key_len ref rows Extra 11161 SIMPLE t1 index NULL b 5 NULL 2 Using index 1117INSERT INTO t1 (id) VALUES(4),(5),(6),(7),(8),(9),(10); 1118EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5; 1119id select_type table type possible_keys key key_len ref rows Extra 11201 SIMPLE t1 range b b 5 NULL 3 Using where; Using index 1121EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b)); 1122id select_type table type possible_keys key key_len ref rows Extra 11231 PRIMARY t1 system NULL NULL NULL NULL 1 11242 SUBQUERY t1 index_subquery b b 5 func 3 Using index; Full scan on NULL key 1125DROP TABLE t1; 1126DROP TABLE t2, t3; 1127# 1128# Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED 1129# 1130CREATE TABLE t1(a INT); 1131INSERT INTO t1 VALUES(2147483647); 1132ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL; 1133ERROR 22003: Out of range value for column 'b' at row 1 1134ALTER TABLE t1 DROP COLUMN b; 1135ERROR 42000: Can't DROP COLUMN `b`; check that it exists 1136ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL; 1137ERROR 22003: Out of range value for column 'c' at row 1 1138ALTER TABLE t1 DROP COLUMN c; 1139ERROR 42000: Can't DROP COLUMN `c`; check that it exists 1140ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL; 1141ERROR 22003: Out of range value for column 'd' at row 1 1142ALTER TABLE t1 DROP COLUMN d; 1143ERROR 42000: Can't DROP COLUMN `d`; check that it exists 1144ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL; 1145ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL; 1146ERROR 22003: Out of range value for column 'c' at row 1 1147ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL; 1148ERROR 22003: Out of range value for column 'c' at row 1 1149SELECT * FROM t1; 1150a c 11512147483647 2147483647 1152DROP TABLE t1; 1153CREATE TABLE t1(a INT); 1154INSERT INTO t1 VALUES(2147483647); 1155ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; 1156ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; 1157ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 1158ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED 1159ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 1160ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED 1161ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; 1162ERROR 22003: Out of range value for column 'f' at row 1 1163ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; 1164ERROR 22003: Out of range value for column 'g' at row 1 1165DROP TABLE t1; 1166# 1167# Bug#21980430 GCOLS: CRASHING 1168# 1169CREATE TABLE t ( 1170a INT, 1171b BLOB, 1172c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL, 1173UNIQUE KEY i0008 (a) 1174); 1175INSERT INTO t(a,b) VALUES(1,'cccc'); 1176EXPLAIN SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c 1177WHERE b.b>c.a; 1178id select_type table type possible_keys key key_len ref rows Extra 11791 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables 1180Warnings: 1181Warning 1292 Truncated incorrect DOUBLE value: 'cccc' 1182Warning 1292 Truncated incorrect DOUBLE value: 'cccc' 1183SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c 1184WHERE b.b>c.a; 1185c 1186Warnings: 1187Warning 1292 Truncated incorrect DOUBLE value: 'cccc' 1188Warning 1292 Truncated incorrect DOUBLE value: 'cccc' 1189DROP TABLE t; 1190set @optimizer_switch_save = @@optimizer_switch; 1191set optimizer_switch='mrr_cost_based=off'; 1192set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 1193set read_rnd_buffer_size=32; 1194CREATE TABLE t0 ( 1195i1 INTEGER NOT NULL 1196); 1197INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 1198CREATE TABLE t1 ( 1199pk INTEGER NOT NULL, 1200i1 INTEGER NOT NULL, 1201i2 INTEGER NOT NULL, 1202v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL, 1203v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL, 1204PRIMARY KEY (pk), 1205INDEX idx(i1) 1206); 1207INSERT INTO t1 (pk, i1, i2) 1208SELECT a0.i1 + a1.i1*10 + a2.i1*100, 1209a0.i1 + a1.i1*10, 1210a0.i1 + a1.i1*10 1211FROM t0 AS a0, t0 AS a1, t0 AS a2; 1212EXPLAIN SELECT * FROM t1 1213WHERE i1 > 41 AND i1 <= 43; 1214id select_type table type possible_keys key key_len ref rows Extra 12151 SIMPLE t1 range idx idx 4 NULL 20 Using index condition 1216SELECT * FROM t1 1217WHERE i1 > 41 AND i1 <= 43; 1218pk i1 i2 v1 v2 1219142 42 42 43 1 1220143 43 43 44 1 1221242 42 42 43 1 1222243 43 43 44 1 1223342 42 42 43 1 1224343 43 43 44 1 122542 42 42 43 1 122643 43 43 44 1 1227442 42 42 43 1 1228443 43 43 44 1 1229542 42 42 43 1 1230543 43 43 44 1 1231642 42 42 43 1 1232643 43 43 44 1 1233742 42 42 43 1 1234743 43 43 44 1 1235842 42 42 43 1 1236843 43 43 44 1 1237942 42 42 43 1 1238943 43 43 44 1 1239ALTER TABLE t1 ADD INDEX idx2(v1); 1240EXPLAIN SELECT * FROM t1 1241WHERE v1 > 41 AND v1 <= 43; 1242id select_type table type possible_keys key key_len ref rows Extra 12431 SIMPLE t1 range idx2 idx2 5 NULL # Using index condition 1244SELECT * FROM t1 1245WHERE v1 > 41 AND v1 <= 43; 1246pk i1 i2 v1 v2 1247141 41 41 42 1 1248142 42 42 43 1 1249241 41 41 42 1 1250242 42 42 43 1 1251341 41 41 42 1 1252342 42 42 43 1 125341 41 41 42 1 125442 42 42 43 1 1255441 41 41 42 1 1256442 42 42 43 1 1257541 41 41 42 1 1258542 42 42 43 1 1259641 41 41 42 1 1260642 42 42 43 1 1261741 41 41 42 1 1262742 42 42 43 1 1263841 41 41 42 1 1264842 42 42 43 1 1265941 41 41 42 1 1266942 42 42 43 1 1267DROP TABLE t0, t1; 1268set optimizer_switch= @optimizer_switch_save; 1269set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 1270# 1271# Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN 1272# SQL_JOIN_BUFFER.CC 1273# 1274# 1275# Test 1: Dynamic range scan with one covering index 1276# 1277CREATE TABLE t1 ( 1278i1 INTEGER NOT NULL, 1279c1 VARCHAR(1) NOT NULL 1280); 1281INSERT INTO t1 1282VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g'); 1283CREATE TABLE t2 ( 1284i1 INTEGER NOT NULL, 1285c1 VARCHAR(1) NOT NULL 1286); 1287INSERT INTO t2 1288VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e'); 1289CREATE TABLE t3 ( 1290pk INTEGER NOT NULL, 1291i1 INTEGER, 1292i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL, 1293PRIMARY KEY (pk) 1294); 1295# Add a covering index. The reason for this index being covering is that 1296# secondary indexes in InnoDB include the primary key. 1297ALTER TABLE t3 ADD INDEX v_idx (i2_key); 1298INSERT INTO t3 (pk, i1) 1299VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5), 1300(6, 39), (7, 6), (8, 8), (9, 3); 1301CREATE TABLE t4 ( 1302i1 INTEGER NOT NULL, 1303c1 VARCHAR(1) NOT NULL 1304); 1305INSERT INTO t4 1306VALUES (1, 'j'), (2, 'c'), (0, 'a'); 1307ANALYZE TABLE t1, t2, t3, t4; 1308Table Op Msg_type Msg_text 1309test.t1 analyze status Engine-independent statistics collected 1310test.t1 analyze status OK 1311test.t2 analyze status Engine-independent statistics collected 1312test.t2 analyze status OK 1313test.t3 analyze status Engine-independent statistics collected 1314test.t3 analyze status OK 1315test.t4 analyze status Engine-independent statistics collected 1316test.t4 analyze status OK 1317EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1318FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1319WHERE ( t3.pk IN 1320( 1321SELECT /*+ QB_NAME(subq1) */ t4.i1 1322FROM t4 1323WHERE t4.c1 < 'o' 1324 ) 1325) 1326AND t1.i1 <= t3.i2_key; 1327id select_type table type possible_keys key key_len ref rows Extra 13281 PRIMARY t1 ALL NULL NULL NULL NULL 4 13291 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 13301 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 13311 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 13322 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 1333SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1334FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1335WHERE ( t3.pk IN 1336( 1337SELECT /*+ QB_NAME(subq1) */ t4.i1 1338FROM t4 1339WHERE t4.c1 < 'o' 1340 ) 1341) 1342AND t1.i1 <= t3.i2_key; 1343c1 i1 1344c 2 1345c 5 1346c 7 1347c 7 1348c 9 1349g 2 1350g 5 1351g 7 1352g 7 1353g 9 1354i 2 1355i 5 1356i 7 1357i 7 1358i 9 1359t 2 1360t 2 1361t 5 1362t 5 1363t 7 1364t 7 1365t 7 1366t 7 1367t 9 1368t 9 1369# 1370# Test 2: Two alternative covering indexes for the range scan 1371# 1372ALTER TABLE t3 ADD INDEX v_idx2 (i2_key, i1); 1373EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1374FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1375WHERE ( t3.pk IN 1376( 1377SELECT /*+ QB_NAME(subq1) */ t4.i1 1378FROM t4 1379WHERE t4.c1 < 'o' 1380 ) 1381) 1382AND t1.i1 <= t3.i2_key; 1383id select_type table type possible_keys key key_len ref rows Extra 13841 PRIMARY t1 ALL NULL NULL NULL NULL 4 13851 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 13861 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 13871 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 13882 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 1389SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1390FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1391WHERE ( t3.pk IN 1392( 1393SELECT /*+ QB_NAME(subq1) */ t4.i1 1394FROM t4 1395WHERE t4.c1 < 'o' 1396 ) 1397) 1398AND t1.i1 <= t3.i2_key; 1399c1 i1 1400c 2 1401c 5 1402c 7 1403c 7 1404c 9 1405g 2 1406g 5 1407g 7 1408g 7 1409g 9 1410i 2 1411i 5 1412i 7 1413i 7 1414i 9 1415t 2 1416t 2 1417t 5 1418t 5 1419t 7 1420t 7 1421t 7 1422t 7 1423t 9 1424t 9 1425# 1426# Test 3: One covering index including the base column for the virtual 1427# column 1428# 1429# Drop the index with only the virtual column 1430ALTER TABLE t3 DROP INDEX v_idx; 1431EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1432FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1433WHERE ( t3.pk IN 1434( 1435SELECT /*+ QB_NAME(subq1) */ t4.i1 1436FROM t4 1437WHERE t4.c1 < 'o' 1438 ) 1439) 1440AND t1.i1 <= t3.i2_key; 1441id select_type table type possible_keys key key_len ref rows Extra 14421 PRIMARY t1 ALL NULL NULL NULL NULL 4 14431 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 14441 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where 14451 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 14462 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 1447SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1448FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1449WHERE ( t3.pk IN 1450( 1451SELECT /*+ QB_NAME(subq1) */ t4.i1 1452FROM t4 1453WHERE t4.c1 < 'o' 1454 ) 1455) 1456AND t1.i1 <= t3.i2_key; 1457c1 i1 1458c 2 1459c 5 1460c 7 1461c 7 1462c 9 1463g 2 1464g 5 1465g 7 1466g 7 1467g 9 1468i 2 1469i 5 1470i 7 1471i 7 1472i 9 1473t 2 1474t 2 1475t 5 1476t 5 1477t 7 1478t 7 1479t 7 1480t 7 1481t 9 1482t 9 1483# 1484# Test 4: One non-covering index 1485# 1486# Drop the index on two columns, add index on just one virtual column 1487ALTER TABLE t3 DROP INDEX v_idx2; 1488ALTER TABLE t3 ADD INDEX v_idx (i2_key); 1489# Add more data to the table so that it will run the dynamic range scan 1490# as both table scan and range scan (the purpose of this is to make the 1491# table scan more expensive). 1492INSERT INTO t3 (pk, i1) 1493VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1), 1494(19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1), 1495(28,1), (29,1); 1496# Change the query to read an extra column (t3.i1) making the index 1497# non-covering. 1498EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 1499FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1500WHERE ( t3.pk IN 1501( 1502SELECT /*+ QB_NAME(subq1) */ t4.i1 1503FROM t4 1504WHERE t4.c1 < 'o' 1505 ) 1506) 1507AND t1.i1 <= t3.i2_key; 1508id select_type table type possible_keys key key_len ref rows Extra 15091 PRIMARY t1 ALL NULL NULL NULL NULL 4 15101 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 15111 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 15121 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 15132 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where 1514SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 1515FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1516WHERE ( t3.pk IN 1517( 1518SELECT /*+ QB_NAME(subq1) */ t4.i1 1519FROM t4 1520WHERE t4.c1 < 'o' 1521 ) 1522) 1523AND t1.i1 <= t3.i2_key; 1524c1 i1 i1 1525c 2 48 1526c 5 48 1527c 7 48 1528c 7 48 1529c 9 48 1530g 2 48 1531g 5 48 1532g 7 48 1533g 7 48 1534g 9 48 1535i 2 48 1536i 5 48 1537i 7 48 1538i 7 48 1539i 9 48 1540t 2 1 1541t 2 48 1542t 5 1 1543t 5 48 1544t 7 1 1545t 7 1 1546t 7 48 1547t 7 48 1548t 9 1 1549t 9 48 1550# 1551# Test 5: Test where the added primary key to secondary indexes is 1552# used after it has been included in the join buffer 1553# 1554EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1555FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1556WHERE ( t3.pk IN 1557( 1558SELECT /*+ QB_NAME(subq1) */ t4.i1 1559FROM t4 1560WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1) 1561) 1562) 1563AND t1.i1 <= t3.i2_key; 1564id select_type table type possible_keys key key_len ref rows Extra 15651 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary 15661 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) 15671 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where 15681 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using where; End temporary; Using join buffer (flat, BNL join) 1569SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1570FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1571WHERE ( t3.pk IN 1572( 1573SELECT /*+ QB_NAME(subq1) */ t4.i1 1574FROM t4 1575WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1) 1576) 1577) 1578AND t1.i1 <= t3.i2_key; 1579c1 i1 1580c 2 1581c 5 1582c 7 1583c 7 1584c 9 1585g 2 1586g 5 1587g 7 1588g 7 1589g 9 1590i 2 1591i 5 1592i 7 1593i 7 1594i 9 1595t 2 1596t 2 1597t 5 1598t 5 1599t 7 1600t 7 1601t 7 1602t 7 1603t 9 1604t 9 1605DROP TABLE t1, t2, t3, t4; 1606SET optimizer_switch='derived_merge=default'; 1607DROP VIEW IF EXISTS v1,v2; 1608DROP TABLE IF EXISTS t1,t2,t3; 1609DROP PROCEDURE IF EXISTS p1; 1610DROP FUNCTION IF EXISTS f1; 1611DROP TRIGGER IF EXISTS trg1; 1612DROP TRIGGER IF EXISTS trg2; 1613set sql_warnings = 0; 1614