1############################################################################## 2# inc/gcol_select.inc # 3# # 4# Purpose: # 5# Testing different SELECTs. # 6# # 7# # 8#----------------------------------------------------------------------------# 9# Original Author: Andrey Zhakov # 10# Original Date: 2008-09-18 # 11# Change Author: # 12# Change Date: # 13# Change: # 14############################################################################## 15 16--disable_query_log 17set @local_optimizer_switch=@@optimizer_switch; 18set optimizer_switch='derived_merge=off,join_cache_hashed=off'; 19--enable_query_log 20 21# Table t1 is used below to test: 22# - Join type of ALL (sequential scan of the entire table) 23# - Join type of Index 24# - Join type of Range 25# - Join type of Ref_or_null 26create table t1 (a int, 27 b int generated always as (-a) virtual, 28 c int generated always as (-a) stored, 29 index (c)); 30insert into t1 (a) values (2), (1), (1), (3), (NULL); 31 32# Table t2 is used below to test: 33# - Join type of system and const 34create table t2 like t1; 35insert into t2 (a) values (1); 36 37# Table t3 is used below to test 38# - Join type of Eq_ref with a unique generated column 39# - Join type of Const 40create table t3 (a int primary key, 41 b int generated always as (-a) virtual, 42 c int generated always as (-a) stored unique); 43insert into t3 (a) values (2),(1),(3),(5),(4),(7); 44analyze table t1,t2,t3; 45 46--echo # select_type=SIMPLE, type=system 47let $s = select * from t2; 48eval $s; 49eval explain $s; 50 51let $s = select * from t2 where c=-1; 52eval $s; 53eval explain $s; 54 55--echo # select_type=SIMPLE, type=ALL 56let $s = select * from t1 where b=-1; 57eval $s; 58eval explain $s; 59 60--echo # select_type=SIMPLE, type=const 61let $s = select * from t3 where a=1; 62eval $s; 63eval explain $s; 64 65--echo # select_type=SIMPLE, type=range 66let $s = select * from t3 where c>=-1; 67eval $s; 68eval explain $s; 69 70--echo # select_type=SIMPLE, type=ref 71let $s = select * from t1,t3 where t1.c=t3.c and t3.c=-1; 72eval $s; 73eval explain $s; 74 75--echo # select_type=PRIMARY, type=index,ALL 76let $s = select * from t1 where b in (select c from t3); 77--sorted_result 78eval $s; 79eval explain $s; 80 81--echo # select_type=PRIMARY, type=range,ref 82--sorted_result 83let $s = select * from t1 where c in (select c from t3 where c between -2 and -1); 84eval $s; 85eval explain $s; 86 87--echo # select_type=UNION, type=system 88--echo # select_type=UNION RESULT, type=<union1,2> 89let $s = select * from t1 union select * from t2; 90--sorted_result 91eval $s; 92eval explain $s; 93 94--echo # select_type=DERIVED, type=system 95let $s = select * from (select a,b,c from t1) as t11; 96--sorted_result 97eval $s; 98eval explain $s; 99 100--echo ### 101--echo ### Using aggregate functions with/without DISTINCT 102--echo ### 103--echo # SELECT COUNT(*) FROM tbl_name 104let $s = select count(*) from t1; 105eval $s; 106eval explain $s; 107 108--echo # SELECT COUNT(DISTINCT <non-gcol>) FROM tbl_name 109let $s = select count(distinct a) from t1; 110eval $s; 111eval explain $s; 112 113--echo # SELECT COUNT(DISTINCT <non-stored gcol>) FROM tbl_name 114let $s = select count(distinct b) from t1; 115eval $s; 116eval explain $s; 117 118--echo # SELECT COUNT(DISTINCT <stored gcol>) FROM tbl_name 119let $s = select count(distinct c) from t1; 120eval $s; 121eval explain $s; 122 123--echo ### 124--echo ### filesort & range-based utils 125--echo ### 126--echo # SELECT * FROM tbl_name WHERE <gcol expr> 127let $s = select * from t3 where c >= -2; 128--sorted_result 129eval $s; 130eval explain $s; 131 132--echo # SELECT * FROM tbl_name WHERE <non-gcol expr> 133let $s = select * from t3 where a between 1 and 2; 134--sorted_result 135eval $s; 136eval explain $s; 137 138--echo # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> 139let $s = select * from t3 where b between -2 and -1; 140--sorted_result 141eval $s; 142eval explain $s; 143 144--echo # SELECT * FROM tbl_name WHERE <indexed gcol expr> 145let $s = select * from t3 where c between -2 and -1; 146--sorted_result 147eval $s; 148eval explain $s; 149 150#### Remove for MyISAM due to a bug 151#### when all the three records are returned (a=1,2,3) 152#### instead of just two (a=1,2). 153#### This bug is presumably in base SQL routines as the same happens 154#### with this table: 155#### create table t4 (a int primary key, b int, c int unique); 156let $myisam_engine = `SELECT @@session.default_storage_engine='myisam'`; 157if (!$myisam_engine) 158{ 159 --echo # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed gcol> 160 let $s = select * from t3 where a between 1 and 2 order by b; 161 eval $s; 162 eval explain $s; 163 --echo # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC 164 --echo # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <non-indexed stored gcol> 165 let $s = select * from t3 where a between 1 and 2 order by c; 166 eval $s; 167 eval explain $s; 168} 169--echo # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC 170CREATE TABLE t4 ( 171 `pk` int(11) NOT NULL , 172 `col_int_nokey` int(11) GENERATED ALWAYS AS (pk + col_int_key) STORED, 173 `col_int_key` int(11) DEFAULT NULL, 174 `col_date_nokey` date DEFAULT NULL, 175 `col_datetime_key` datetime DEFAULT NULL, 176 PRIMARY KEY (`pk`), 177 KEY `col_int_key` (`col_int_key`), 178 KEY `col_datetime_key` (`col_datetime_key`) 179); 180 181INSERT INTO t4 VALUES 182(1,default,4,'2008-12-05','1900-01-01 00:00:00'); 183 184SELECT 185SQL_BIG_RESULT 186GRANDPARENT1 . `col_int_nokey` AS g1 187FROM t4 AS GRANDPARENT1 LEFT JOIN t4 AS GRANDPARENT2 ON ( GRANDPARENT2 . 188`col_datetime_key` <= GRANDPARENT1 . `col_date_nokey` ) 189GROUP BY GRANDPARENT1 . `pk`; 190DROP TABLE t4; 191 192--echo # SELECT * FROM tbl_name WHERE <non-gcol expr> ORDER BY <indexed gcol> 193let $s = select * from t3 where a between 1 and 2 order by c; 194eval $s; 195eval explain $s; 196 197--echo # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol> 198let $s = select * from t3 where b between -2 and -1 order by a; 199eval $s; 200eval explain $s; 201 202#### Remove for MyISAM due to a bug 203#### when all the three records are returned (a=1,2,3) 204#### instead of just two (a=1,2). 205#### This bug is presumably in base SQL routines as the same happens 206#### with this table: 207#### create table t4 (a int primary key, b int, c int unique); 208let $innodb_engine = `SELECT @@session.default_storage_engine='innodb'`; 209if (!$innodb_engine) 210{ 211 --echo # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol> 212 let $s = select * from t3 where c between -2 and -1 order by a; 213 eval $s; 214 eval explain $s; 215} 216 217--echo # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> 218let $s = select * from t3 where b between -2 and -1 order by b; 219eval $s; 220eval explain $s; 221 222--echo # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> 223let $s = select * from t3 where c between -2 and -1 order by b; 224eval $s; 225eval explain $s; 226 227--echo # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol> 228let $s = select * from t3 where b between -2 and -1 order by c; 229eval $s; 230eval explain $s; 231 232--echo # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> 233let $s = select * from t3 where c between -2 and -1 order by c; 234eval $s; 235eval explain $s; 236 237--echo # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 238let $s = select sum(b) from t1 group by b; 239eval $s; 240eval explain $s; 241 242--echo # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 243let $s = select sum(c) from t1 group by c; 244eval $s; 245eval explain $s; 246 247--echo # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <indexed gcol> 248let $s = select sum(b) from t1 group by c; 249eval $s; 250eval explain $s; 251 252--echo # SELECT sum(<indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> 253let $s = select sum(c) from t1 group by b; 254eval $s; 255eval explain $s; 256 257drop table t1; 258 259--echo # 260--echo # Bug#20241655: WL411:FAILING ASSERTION ASSERTION 261--echo # 262CREATE TABLE BB ( 263 col_time_key time NOT NULL, 264 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 265 col_datetime_key datetime NOT NULL); 266INSERT INTO BB VALUES('23:28:02', default, '2005-03-15 22:48:25'); 267 268CREATE TABLE CC ( 269 col_time_key time NOT NULL, 270 col_time_nokey time GENERATED ALWAYS AS (ADDTIME(col_datetime_key, col_time_key)) VIRTUAL, 271 col_datetime_key datetime NOT NULL 272); 273INSERT INTO CC VALUES('16:22:51', default, '1900-01-01 00:00:00'); 274 275SELECT 1 AS g1 FROM BB AS gp1 LEFT JOIN BB AS gp2 USING ( col_time_nokey); 276DROP TABLE BB, CC; 277 278--echo # 279--echo # Bug#20328786: WL411:VALGRIND WARNINGS OF CONDITIONAL 280--echo # JUMP WHILE SELECTING FROM VIEW 281--echo # 282CREATE TABLE A ( 283 pk INTEGER AUTO_INCREMENT, 284 col_int_nokey INTEGER, 285 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 286 PRIMARY KEY (pk) 287); 288 289CREATE TABLE C ( 290 pk INTEGER AUTO_INCREMENT, 291 col_int_nokey INTEGER, 292 col_int_key INTEGER GENERATED ALWAYS AS (2 + 2 + col_int_nokey) STORED, 293 col_varchar_nokey VARCHAR(1), 294 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 295 (CONCAT(col_varchar_nokey, col_varchar_nokey)) STORED, 296 PRIMARY KEY (pk), 297 KEY (col_int_key), 298 KEY (col_varchar_key, col_int_key) 299); 300 301INSERT INTO C ( 302 col_int_nokey, 303 col_varchar_nokey 304) 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'); 305 306CREATE OR REPLACE ALGORITHM=MERGE VIEW V1 AS SELECT alias1. 307col_varchar_key AS field1 , alias1.pk AS field2, alias2. 308col_int_nokey AS field3 FROM C AS alias1 LEFT JOIN A AS alias2 ON 309alias1.pk = alias2.col_int_key WHERE alias1.pk > 8 AND alias1 310.pk < ( 9 + 2 ) AND alias1.col_int_key <> 1 OR alias1.col_int_key 311> 0 AND alias1.col_int_key <= ( 3 + 2 ) ORDER BY field1, field2, field3 312LIMIT 100 OFFSET 6; 313 314SELECT * FROM V1; 315 316DROP VIEW V1; 317DROP TABLE A,C; 318 319--echo # 320--echo # Bug#20406510: WL411:VALGRIND WARNINGS WITH 321--echo # COUNT DISTINCT QUERY ON VIRTUAL GC VARCHAR COLUMN 322--echo # 323CREATE TABLE A ( 324 pk INTEGER AUTO_INCREMENT, 325 col_time_key TIME NOT NULL, 326 col_datetime_key DATETIME NOT NULL, 327 PRIMARY KEY (pk), 328 KEY (col_time_key), 329 KEY (col_datetime_key) 330); 331 332CREATE TABLE C ( 333 pk INTEGER AUTO_INCREMENT, 334 col_int_key INTEGER NOT NULL, 335 col_varchar_key VARCHAR(1) NOT NULL, 336 col_varchar_nokey VARCHAR(2) GENERATED ALWAYS AS 337 (CONCAT(col_varchar_key, col_varchar_key)), 338 PRIMARY KEY (pk), 339 KEY (col_int_key), 340 KEY (col_varchar_key, col_int_key) 341); 342 343INSERT INTO C (col_int_key,col_varchar_key) VALUES (0, 'j'),(8, 'v'),(1, 'c'),(8, 'm'),(9, 'd'); 344SELECT MIN( alias2 . col_int_key ) AS field1, 345COUNT( DISTINCT alias2 . col_varchar_nokey ) AS field2 346FROM ( A AS alias1 , C AS alias2 ) 347ORDER BY alias1.col_time_key, alias1.col_datetime_key, alias1.pk ASC; 348DROP TABLE A,C; 349 350--echo # 351--echo # Bug#20566325: WL8149: INNODB: FAILING ASSERTION: 352--echo # COL_NR < TABLE->N_DEF 353--echo # 354CREATE TABLE A ( 355pk INTEGER AUTO_INCREMENT, 356col_varchar_nokey VARCHAR(1) NOT NULL, 357col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 358(CONCAT(col_varchar_nokey, col_varchar_nokey)), 359PRIMARY KEY (pk) 360); 361 362INSERT /*! IGNORE */ INTO A (col_varchar_nokey) VALUES ('k'); 363 364CREATE TABLE CC ( 365pk INTEGER AUTO_INCREMENT, 366col_datetime_nokey DATETIME /*! NULL */, 367col_time_nokey TIME /*! NULL */, 368col_time_key TIME GENERATED ALWAYS AS 369(ADDTIME(col_datetime_nokey, col_time_nokey)), 370col_varchar_nokey VARCHAR(1) /*! NULL */, 371col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 372(CONCAT(col_varchar_nokey, col_varchar_nokey)), 373PRIMARY KEY (pk)); 374 375INSERT INTO CC (col_time_nokey,col_datetime_nokey,col_varchar_nokey) VALUES 376('13:06:13.033877','1900-01-01 00:00:00', 'p'), 377(NULL, '2007-05-25 11:58:54.015689', 'g'); 378 379SELECT 380table1.col_time_key AS field1, 381'z' AS field2 382FROM 383(CC AS table1 LEFT OUTER JOIN (A AS table2 STRAIGHT_JOIN CC AS table3 ON 384(table3.col_varchar_key = table2.col_varchar_nokey)) ON 385(table3.col_varchar_key = table2.col_varchar_nokey)) 386WHERE 387table2.pk != 6 388AND table1.col_varchar_key IN ('l', 's' , 'b' ) 389AND table3.col_varchar_key != table1.col_varchar_key 390ORDER BY table1.col_varchar_key , field1 , field2; 391 392DROP TABLE A,CC; 393 394if ($support_virtual_index) 395{ 396--echo # 397--echo # Bug#20573302: WL8149: SEGV IN HA_INNOBASE:: 398--echo # BUILD_TEMPLATE AT INNOBASE/HANDLER/HA_INNODB.CC:665 399--echo # 400CREATE TABLE c ( 401 pk INTEGER AUTO_INCREMENT, 402 col_int_nokey INTEGER NOT NULL, 403 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 404 405 col_date_nokey DATE NOT NULL, 406 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, 407 408 col_datetime_nokey DATETIME NOT NULL, 409 col_time_nokey TIME NOT NULL, 410 411 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 412 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 413 414 col_varchar_nokey VARCHAR(1) NOT NULL, 415 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)), 416 417 PRIMARY KEY (pk), 418 KEY (col_int_key), 419 KEY (col_varchar_key), 420 KEY (col_date_key), 421 KEY (col_time_key), 422 KEY (col_datetime_key), 423 KEY (col_int_key, col_varchar_key), 424 KEY (col_int_key, col_varchar_key, col_date_key, 425 col_time_key, col_datetime_key)); 426 427INSERT /*! IGNORE */ INTO c ( 428 col_int_nokey, 429 col_date_nokey, 430 col_time_nokey, 431 col_datetime_nokey, 432 col_varchar_nokey 433 ) VALUES 434(1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), 435(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), 436(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), 437(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'), 438(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), 439(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'), 440(6, '2007-06-18', NULL, '2002-08-20 22:48:00.035785', 'd'), 441(2, '2002-10-13', '00:00:00', '1900-01-01 00:00:00', 's'), 442(4, '1900-01-01', '15:57:25.019666', '2005-08-15 00:00:00', 'r'), 443(8, NULL, '07:05:51.006712', '1900-01-01 00:00:00', 'm'), 444(4, '2006-03-09', '19:22:21.057406', '2008-05-16 08:09:06.002924', 'b'), 445(4, '2001-06-05', '03:53:16.001370', '2001-01-20 12:47:23.022022', 'x'), 446(7, '2006-05-28', '09:16:38.034570', '2008-07-02 00:00:00', 'g'), 447(4, '2001-04-19', '15:37:26.028315', '1900-01-01 00:00:00', 'p'), 448(1, '1900-01-01', '00:00:00', '2002-12-08 11:34:58.001571', 'q'), 449(9, '2004-08-20', '05:03:03.047452', '1900-01-01 00:00:00', 'w'), 450(4, '2004-10-10', '02:59:24.063764', '1900-01-01 00:00:00', 'd'), 451(8, '2000-04-02', '00:01:58.064243', '2002-08-25 20:35:06.064634', 'e'), 452(4, '2006-11-02', '00:00:00', '2001-10-22 11:13:24.048128', 'b'), 453(8, '2009-01-28', '02:20:16.024931', '2003-03-12 02:00:34.029335', 'y'); 454 455CREATE TABLE cc ( 456 pk INTEGER AUTO_INCREMENT, 457 col_int_nokey INTEGER NOT NULL, 458 col_int_key INTEGER GENERATED ALWAYS AS (col_int_nokey) VIRTUAL, 459 460 col_date_nokey DATE NOT NULL, 461 col_date_key DATE GENERATED ALWAYS AS (DATE_ADD(col_date_nokey,interval 30 day)) VIRTUAL, 462 463 col_datetime_nokey DATETIME NOT NULL, 464 col_time_nokey TIME NOT NULL, 465 466 col_datetime_key DATETIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 467 col_time_key TIME GENERATED ALWAYS AS (ADDTIME(col_datetime_nokey, col_time_nokey)), 468 469 col_varchar_nokey VARCHAR(1) NOT NULL, 470 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS (CONCAT(col_varchar_nokey, col_varchar_nokey)), 471 472 PRIMARY KEY (pk), 473 KEY (col_int_key), 474 KEY (col_varchar_key), 475 KEY (col_date_key), 476 KEY (col_time_key), 477 KEY (col_datetime_key), 478 KEY (col_int_key, col_varchar_key), 479 KEY (col_int_key, col_varchar_key, col_date_key, 480 col_time_key, col_datetime_key)); 481 482INSERT /*! IGNORE */ INTO cc ( 483 col_int_nokey, 484 col_date_nokey, 485 col_time_nokey, 486 col_datetime_nokey, 487 col_varchar_nokey 488 ) VALUES 489(0, '2003-02-06', '22:02:09.059926', '2003-08-07 14:43:09.011144', 'x'), 490(0, '2005-04-16', '19:33:15.014160', '2005-12-11 00:00:00', 'n'), 491(1, '2005-07-23', '22:03:16.058787', '2005-12-26 20:48:07.043628', 'w'), 492(7, '2001-11-15', '06:31:23.027263', '2008-06-12 06:41:21.012493', 's'), 493(0, '2006-03-24', '02:19:08.013275', '2007-10-11 18:46:28.030000', 'a'), 494(4, '2008-07-10', NULL, '2006-04-04 22:22:40.057947', 'd'), 495(1, '2009-12-07', NULL, '2002-08-10 20:52:58.035137', 'w'), 496(1, '2008-05-01', '10:28:01.038587', '2008-10-03 11:17:23.005299', 'j'), 497(1, '2008-06-22', '00:00:00', '2009-01-06 20:11:01.034339', 'm'), 498(4, '2001-11-11', '15:02:50.048785', '2009-09-19 00:00:00', 'k'), 499(7, '2000-12-21', '05:29:13.012729', '2007-09-02 12:14:27.029187', 't'), 500(4, '2007-09-03', '23:45:33.048507', '2003-09-26 00:00:00', 'k'), 501(2, '2003-02-18', '19:10:53.057455', '2001-11-18 18:10:16.063189', 'e'), 502(0, '2008-12-01', '01:45:27.037313', '2005-02-15 04:08:17.015554', 'i'), 503(1, '2008-10-18', '03:56:03.060218', '2009-06-13 23:04:40.013006', 't'), 504(91, '2004-08-28', '12:43:17.023797', '1900-01-01 00:00:00', 'm'), 505(6, '2006-10-05', '13:33:46.053634', '2005-03-20 02:48:24.045653', 'z'), 506(3, '2003-05-16', NULL, '2002-03-16 11:47:27.045297', 'c'), 507(6, '2008-10-10', NULL, '2000-05-22 00:00:00', 'i'), 508(8, '2002-01-19', '05:18:40.006865', '2009-02-12 00:00:00', 'v'); 509 510--replace_column 10 # 11 # 511EXPLAIN 512SELECT subquery2_t2.col_int_key AS subquery2_field1 513FROM (c AS subquery2_t1 RIGHT JOIN 514 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 515 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 516 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 517ORDER BY subquery2_field1; 518 519SELECT subquery2_t2.col_int_key AS subquery2_field1 520FROM (c AS subquery2_t1 RIGHT JOIN 521 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 522 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 523 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 524ORDER BY subquery2_field1; 525SELECT subquery2_t2.col_int_key AS subquery2_field1 526FROM (c AS subquery2_t1 RIGHT JOIN 527 (c AS subquery2_t2 LEFT JOIN cc AS subquery2_t3 ON 528 (subquery2_t3.col_int_nokey = subquery2_t2.col_int_key )) ON 529 (subquery2_t3.col_varchar_key = subquery2_t2.col_varchar_key)) 530ORDER BY subquery2_field1; 531 532DROP TABLE c,cc; 533 534--echo # 535--echo # Bug#2081065: WL8149:RESULT DIFF SEEN FOR SIMPLE 536--echo # RANGE QUERIES WITH ORDER BY 537--echo # 538CREATE TABLE cc ( 539 pk INTEGER AUTO_INCREMENT, 540 col_int_nokey INTEGER NOT NULL, 541 col_int_key INTEGER GENERATED ALWAYS AS 542 (col_int_nokey + col_int_nokey) VIRTUAL, 543 PRIMARY KEY (pk), 544 KEY (col_int_key) 545); 546INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5); 547--replace_column 10 # 11 # 548EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3; 549SELECT pk FROM cc WHERE col_int_key > 3; 550--replace_column 10 # 11 # 551EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; 552SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; 553DROP TABLE cc; 554 555--echo # 556--echo # Bug#20849676 :WL8149:ASSERTION `!TABLE || (!TABLE->READ_SET 557--echo # || BITMAP_IS_SET(TABLE->READ_SET 558--echo # 559CREATE TABLE c ( 560 pk INTEGER AUTO_INCREMENT, 561 col_int_nokey INTEGER NOT NULL, 562 col_int_key INTEGER GENERATED ALWAYS AS 563 (col_int_nokey + col_int_nokey) VIRTUAL, 564 col_varchar_nokey VARCHAR(1) NOT NULL, 565 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 566 (CONCAT(col_varchar_nokey, col_varchar_nokey)), 567 PRIMARY KEY (pk), 568 KEY (col_int_key), 569 KEY (col_varchar_key), 570 KEY (col_int_key, col_varchar_key) 571) ; 572 573INSERT INTO c (col_int_nokey, col_varchar_nokey) VALUES 574(1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'),(6, 'd'), 575(2, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'),(1, 'q'), 576(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y'); 577 578CREATE TABLE a ( 579 pk INTEGER AUTO_INCREMENT, 580 col_datetime_nokey DATETIME NOT NULL, 581 col_time_nokey TIME NOT NULL, 582 col_datetime_key DATETIME GENERATED ALWAYS AS 583 (ADDTIME(col_datetime_nokey, col_time_nokey)), 584 col_time_key TIME GENERATED ALWAYS AS 585 (ADDTIME(col_datetime_nokey, col_time_nokey)), 586 col_varchar_nokey VARCHAR(1) NOT NULL, 587 col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 588 (CONCAT(col_varchar_nokey, col_varchar_nokey)), 589 PRIMARY KEY (pk), 590 KEY (col_varchar_key), 591 KEY (col_time_key), 592 KEY (col_datetime_key), 593 KEY (col_varchar_key, col_time_key, col_datetime_key) 594); 595 596INSERT INTO a ( 597 col_time_nokey, 598 col_datetime_nokey, 599 col_varchar_nokey) VALUES 600('04:08:02.046897', '2001-11-04 19:07:55.051133', 'k'); 601 602ANALYZE TABLE a, c; 603 604--replace_column 10 # 605--disable_warnings 606EXPLAIN 607SELECT 608table1.pk AS field1 , 609table1.col_datetime_key AS field2 610FROM 611( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT 612SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2 613STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key = 614SUBQUERY1_t2.col_varchar_key ) ) 615ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key 616OR SUBQUERY1_t1.col_int_key <> 1 ) ) 617WHERE SUBQUERY1_t2.pk >= 9 ) AS table3 618ON (table3.col_int_key = table2.col_int_key ) ) ) 619ON (table3.col_int_nokey = table2.pk ) ) 620GROUP BY field1, field2; 621SELECT 622table1.pk AS field1 , 623table1.col_datetime_key AS field2 624FROM 625( a AS table1 LEFT JOIN ( ( c AS table2 STRAIGHT_JOIN ( SELECT 626SUBQUERY1_t1.* FROM ( c AS SUBQUERY1_t1 INNER JOIN ( c AS SUBQUERY1_t2 627STRAIGHT_JOIN c AS SUBQUERY1_t3 ON (SUBQUERY1_t3.col_varchar_key = 628SUBQUERY1_t2.col_varchar_key ) ) 629ON (SUBQUERY1_t3.pk = SUBQUERY1_t2.col_int_key 630OR SUBQUERY1_t1.col_int_key <> 1 ) ) 631WHERE SUBQUERY1_t2.pk >= 9 ) AS table3 632ON (table3.col_int_key = table2.col_int_key ) ) ) 633ON (table3.col_int_nokey = table2.pk ) ) 634GROUP BY field1, field2; 635 636--enable_warnings 637DROP TABLE IF EXISTS c,a; 638CREATE TABLE c ( 639col_int_nokey INTEGER NOT NULL, 640col_int_key INTEGER GENERATED ALWAYS AS 641 (col_int_nokey + col_int_nokey) VIRTUAL, 642col_varchar_nokey VARCHAR(1) NOT NULL, 643col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 644 (CONCAT(col_varchar_nokey, col_varchar_nokey)), 645 646KEY (col_int_key), 647KEY (col_int_key, col_varchar_key) 648) ; 649 650INSERT INTO c ( 651col_int_nokey, 652col_varchar_nokey 653) VALUES (1, 'c'),(8, 'm'),(9, 'd'),(24, 'd'),(6, 'y'),(1, 't'), 654(6, 'd'),(2, 's'),(4, 'r'),(8, 'm'),(4, 'b'),(4, 'x'),(7, 'g'),(4, 'p'), 655(1, 'q'),(9, 'w'),(4, 'd'),(8, 'e'),(4, 'b'),(8, 'y'); 656 657CREATE TABLE cc ( 658col_int_nokey INTEGER, 659col_int_key INTEGER GENERATED ALWAYS AS 660(col_int_nokey + col_int_nokey) VIRTUAL, 661col_varchar_nokey VARCHAR(1), 662col_varchar_key VARCHAR(2) GENERATED ALWAYS AS 663(CONCAT(col_varchar_nokey, col_varchar_nokey)), 664KEY (col_int_key), 665KEY (col_varchar_key), 666KEY (col_int_key, col_varchar_key), 667KEY (col_int_key, col_int_nokey), 668KEY (col_varchar_key, col_varchar_nokey) 669); 670INSERT INTO cc ( 671col_int_nokey, 672col_varchar_nokey 673) VALUES (8, 'p'),(9, 'g'),(9, 'i'),(4, 'p'),(7, 'h'),(1, 'e'),(8, 'e'),(6, 'u'), 674(6, 'j'),(6, 'e'),(1, 'z'),(227, 'w'),(NULL, 't'),(9, 'i'),(1, 'i'),(8, 'i'), 675(5, 'b'),(8,'m'),(7, 'j'),(2, 'v'); 676ANALYZE TABLE c, cc; 677 678--replace_column 10 # 679--disable_warnings 680 681let query=SELECT 682alias2 . col_varchar_key AS field1 683FROM ( cc AS alias1 , cc AS alias2 ) 684WHERE 685( alias2 . col_int_key , alias1 . col_int_nokey ) 686NOT IN 687( 688SELECT 689DISTINCT SQ1_alias2 . col_int_nokey AS SQ1_field1 , 690SQ1_alias1 . col_int_key AS SQ1_field2 691FROM ( cc AS SQ1_alias1 , c AS SQ1_alias2 ) 692GROUP BY SQ1_field1 , SQ1_field2 693) 694GROUP BY field1; 695 696eval EXPLAIN $query; 697eval $query; 698 699DROP TABLE IF EXISTS c,cc; 700 701SET @save_old_sql_mode= @@sql_mode; 702SET sql_mode=""; 703CREATE TABLE d ( 704 col_int int(11) DEFAULT NULL, 705 col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 706 pk int(11) NOT NULL AUTO_INCREMENT, 707 col_int_key int(11) GENERATED ALWAYS AS (col_int+col_int) VIRTUAL, 708 col_varchar_10_utf8_key varchar(10) CHARACTER SET utf8 GENERATED ALWAYS AS (REPEAT(SUBSTRING(col_varchar_10_utf8, -1), 5)) VIRTUAL, 709 PRIMARY KEY (pk), 710 KEY col_int_key (col_int_key), 711 KEY col_varchar_10_utf8_key (col_varchar_10_utf8_key), 712 KEY cover_key1 (col_int_key, col_varchar_10_utf8_key) 713); 714 715INSERT 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); 716 717SELECT 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 ; 718 719DROP TABLE d; 720 721--echo # 722--echo # Bug#21153237: WL8149: QUERIES USING FILESORT 723--echo # ON VIRTUAL GC HAVING INDEX GIVES WRONG RESULTS 724--echo # 725CREATE TABLE j ( 726col_int int(11), 727pk int(11) NOT NULL, 728col_varchar_10_utf8 varchar(10) CHARACTER SET utf8 DEFAULT NULL, 729col_varchar_255_utf8_key varchar(255) CHARACTER SET utf8 GENERATED ALWAYS AS 730(col_varchar_10_utf8) VIRTUAL, 731PRIMARY KEY (pk), 732KEY cover_key1 (col_int, col_varchar_255_utf8_key)); 733 734INSERT INTO j(col_int, pk, col_varchar_10_utf8) VALUES(9, 1, '951910400'), 735(-1934295040, 2, '1235025920'),(-584581120, 3, '-1176633344'),(3, 4, '1074462720'); 736 737--replace_column 10 # 738EXPLAIN SELECT col_varchar_255_utf8_key FROM j ORDER BY 1; 739SELECT col_varchar_255_utf8_key FROM j ORDER BY col_varchar_255_utf8_key; 740 741DROP TABLE j; 742 743set sql_mode= @save_old_sql_mode; 744--enable_warnings 745} 746 747CREATE TABLE cc ( 748 pk int(11) NOT NULL AUTO_INCREMENT, 749 col_int_nokey int(11) NOT NULL, 750 col_int_key int(11) GENERATED ALWAYS AS (col_int_nokey) STORED, 751 col_date_nokey date NOT NULL, 752 col_date_key date GENERATED ALWAYS AS (col_date_nokey) STORED, 753 col_datetime_nokey datetime NOT NULL, 754 col_time_nokey time NOT NULL, 755 col_datetime_key datetime GENERATED ALWAYS AS (col_datetime_nokey)STORED, 756 col_time_key time GENERATED ALWAYS AS (col_time_nokey) STORED, 757 col_varchar_nokey varchar(1) NOT NULL, 758 col_varchar_key varchar(1) GENERATED ALWAYS AS (col_varchar_nokey)STORED, 759 PRIMARY KEY (pk), 760 KEY gc_idx1 (col_int_key), 761 KEY gc_idx2 (col_varchar_key), 762 KEY gc_idx3 (col_date_key), 763 KEY gc_idx4 (col_time_key), 764 KEY gc_idx5 (col_datetime_key), 765 KEY gc_idx6 (col_varchar_key,col_int_key), 766 KEY gc_idx7 (col_date_key,col_datetime_key,col_time_key), 767 KEY gc_idx8(col_int_key,col_varchar_key,col_date_key,col_time_key, 768 col_datetime_key) 769); 770 771INSERT INTO cc ( 772 col_int_nokey, 773 col_date_nokey, 774 col_time_nokey, 775 col_datetime_nokey, 776 col_varchar_nokey 777) VALUES (1, '2009-12-01', '00:21:38.058143', '2007-05-28 00:00:00', 'c'), 778(8, '2004-12-17', '04:08:02.046897', '2009-07-25 09:21:20.064099', 'm'), 779(9, '2000-03-14', '16:25:11.040240', '2002-01-16 00:00:00', 'd'), 780(24, '2000-10-08', '10:14:58.018534', '2006-10-12 04:32:53.031976', 'd'), 781(6, '2006-05-25', '19:47:59.011283', '2001-02-15 03:08:38.035426', 'y'), 782(1, '2008-01-23', '11:14:24.032949', '2004-10-02 20:31:15.022553', 't'); 783SET @save_old_sql_mode= @@sql_mode; 784SET sql_mode=""; 785 786# Warnings arrive in unpredictable order with NDB and cannot be sorted 787if ($testing_ndb) 788{ 789--disable_warnings 790} 791SELECT DISTINCT alias1.col_varchar_key AS field1 792FROM ( cc AS alias1 STRAIGHT_JOIN 793 (( cc AS alias2 STRAIGHT_JOIN cc AS alias3 ON 794 (alias3.col_varchar_key > alias2.col_varchar_key ) ) ) ON 795 (( alias3 .pk >= alias2.col_int_nokey ) AND 796 (alias3 .pk >= alias2.col_int_nokey ) )) 797WHERE alias1.col_varchar_key <= 'v' 798GROUP BY field1 HAVING field1 = 91 799ORDER BY field1, alias1.col_date_key, field1 ASC, field1 DESC, 800 alias1.col_time_key ASC, field1; 801DROP TABLE cc; 802SET sql_mode=@save_old_sql_mode; 803if ($testing_ndb) 804{ 805--enable_warnings 806} 807 808--echo # 809--echo # Bug#20797941: WL8149:ASSERTION !TABLE || 810--echo # (!TABLE->READ_SET || BITMAP_IS_SET(TABLE->READ_SET 811--echo # 812CREATE TABLE t(a int, b int as(a+1)); 813INSERT INTO t(a) values(1),(2); 814SELECT * FROM t ORDER BY b; 815DROP TABLE t; 816 817if ($support_virtual_index) 818{ 819--echo # 820--echo # Testing a few index-based accesses on the virtual column 821--echo # 822 823CREATE TABLE t1 ( 824id int(11) NOT NULL, 825b int(11) GENERATED ALWAYS AS (id+1) VIRTUAL, 826UNIQUE KEY (b) ); 827 828--error ER_BAD_NULL_ERROR 829INSERT INTO t1 (id) VALUES(NULL); 830 831INSERT INTO t1 (id) VALUES(2),(3); 832 833# constant table read with one index lookup 834EXPLAIN SELECT * FROM t1 FORCE INDEX(b) WHERE b=3; 835 836# eq_ref 837EXPLAIN SELECT * FROM t1 AS t2 STRAIGHT_JOIN t1 FORCE INDEX(b) WHERE t1.b=t2.b; 838 839# covering index scan 840EXPLAIN SELECT b FROM t1 FORCE INDEX(b); 841 842# range scan 843INSERT INTO t1 (id) VALUES(4),(5),(6),(7),(8),(9),(10); 844EXPLAIN SELECT b FROM t1 FORCE INDEX(b) WHERE b BETWEEN 1 AND 5; 845 846# index-subquery 847EXPLAIN SELECT * FROM t2 AS t1 WHERE b NOT IN (SELECT b FROM t1 FORCE INDEX(b)); 848 849DROP TABLE t1; 850} 851 852DROP TABLE t2, t3; 853 854--echo # 855--echo # Bug#21317507:GC: STORED COLUMN REJECTED, BUT VIRTUAL IS ACCEPTED 856--echo # 857--disable_abort_on_error 858CREATE TABLE t1(a INT); 859INSERT INTO t1 VALUES(2147483647); 860ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL; 861ALTER TABLE t1 DROP COLUMN b; 862ALTER TABLE t1 ADD COLUMN c SMALLINT AS (a) VIRTUAL; 863ALTER TABLE t1 DROP COLUMN c; 864ALTER TABLE t1 ADD COLUMN d SMALLINT AS (a) VIRTUAL; 865ALTER TABLE t1 DROP COLUMN d; 866ALTER TABLE t1 ADD COLUMN c INT AS(a) VIRTUAL; 867ALTER TABLE t1 CHANGE c c SMALLINT AS(a) VIRTUAL; 868ALTER TABLE t1 MODIFY c TINYINT AS(a) VIRTUAL; 869SELECT * FROM t1; 870DROP TABLE t1; 871CREATE TABLE t1(a INT); 872INSERT INTO t1 VALUES(2147483647); 873ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; 874ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; 875ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 876ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; 877ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; 878ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; 879--enable_abort_on_error 880DROP TABLE t1; 881 882--echo # 883--echo # Bug#21980430 GCOLS: CRASHING 884--echo # 885CREATE TABLE t ( 886 a INT, 887 b BLOB, 888 c BLOB GENERATED ALWAYS AS (a+b) VIRTUAL, 889 UNIQUE KEY i0008 (a) 890); 891 892INSERT INTO t(a,b) VALUES(1,'cccc'); 893let $query= 894SELECT /*+ bka() */ 1 AS c FROM t AS b RIGHT JOIN t AS c ON b.a > c.c 895WHERE b.b>c.a; 896eval EXPLAIN $query; 897eval $query; 898DROP TABLE t; 899 900# Force DS-MRR to be used 901set @optimizer_switch_save = @@optimizer_switch; 902set optimizer_switch='mrr_cost_based=off'; 903 904# Reduce the size of the DS-MRR sort buffer to force multiple rounds 905set @read_rnd_buffer_size_save= @@read_rnd_buffer_size; 906set read_rnd_buffer_size=32; 907 908CREATE TABLE t0 ( 909 i1 INTEGER NOT NULL 910); 911 912INSERT INTO t0 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 913 914CREATE TABLE t1 ( 915 pk INTEGER NOT NULL, 916 i1 INTEGER NOT NULL, 917 i2 INTEGER NOT NULL, 918 v1 INTEGER GENERATED ALWAYS AS (i2 + 1) VIRTUAL, 919 v2 INTEGER GENERATED ALWAYS AS (i1 / (i1 - i2 + 57)) VIRTUAL, 920 PRIMARY KEY (pk), 921 INDEX idx(i1) 922); 923 924INSERT INTO t1 (pk, i1, i2) 925SELECT a0.i1 + a1.i1*10 + a2.i1*100, 926 a0.i1 + a1.i1*10, 927 a0.i1 + a1.i1*10 928FROM t0 AS a0, t0 AS a1, t0 AS a2; 929 930# Do a DS-MRR scan on an index on a non-generated column 931# (this caused Division by 0 errors to be reported). 932let query1= 933SELECT * FROM t1 934WHERE i1 > 41 AND i1 <= 43; 935 936eval EXPLAIN $query1; 937--sorted_result 938eval $query1; 939 940if ($support_virtual_index) 941{ 942ALTER TABLE t1 ADD INDEX idx2(v1); 943} 944 945# Do a DS-MRR scan on an index on a virtual column 946# (this query returned too few records). 947let query2= 948SELECT * FROM t1 949WHERE v1 > 41 AND v1 <= 43; 950 951--replace_column 9 # 952eval EXPLAIN $query2; 953--sorted_result 954eval $query2; 955 956DROP TABLE t0, t1; 957 958# Restore defaults 959set optimizer_switch= @optimizer_switch_save; 960set @@read_rnd_buffer_size= @read_rnd_buffer_size_save; 961 962--echo # 963--echo # Bug#21872184 CONDITIONAL JUMP AT JOIN_CACHE::WRITE_RECORD_DATA IN 964--echo # SQL_JOIN_BUFFER.CC 965--echo # 966 967--echo # 968--echo # Test 1: Dynamic range scan with one covering index 969--echo # 970 971# This is the original test case which produces the valgrind error when 972# inserting data into the join buffer. The test failure only occurs with 973# InnoDB since it is only InnoDB that currently supports indexes on 974# virtual columns and is the only storage engine that includes the 975# primary key in each secondary key. 976 977CREATE TABLE t1 ( 978 i1 INTEGER NOT NULL, 979 c1 VARCHAR(1) NOT NULL 980); 981 982INSERT INTO t1 983VALUES (10, 'c'), (10, 'i'), (2, 't'), (4, 'g'); 984 985CREATE TABLE t2 ( 986 i1 INTEGER NOT NULL, 987 c1 VARCHAR(1) NOT NULL 988); 989 990INSERT INTO t2 991VALUES (2, 'k'), (9, 'k'), (7, 'o'), (5, 'n'), (7, 'e'); 992 993CREATE TABLE t3 ( 994 pk INTEGER NOT NULL, 995 i1 INTEGER, 996 i2_key INTEGER GENERATED ALWAYS AS (i1 + i1) VIRTUAL, 997 PRIMARY KEY (pk) 998); 999 1000if ($support_virtual_index) 1001{ 1002--echo # Add a covering index. The reason for this index being covering is that 1003--echo # secondary indexes in InnoDB include the primary key. 1004ALTER TABLE t3 ADD INDEX v_idx (i2_key); 1005} 1006 1007INSERT INTO t3 (pk, i1) 1008VALUES (1, 1), (2, 48), (3, 228), (4, 3), (5, 5), 1009 (6, 39), (7, 6), (8, 8), (9, 3); 1010 1011CREATE TABLE t4 ( 1012 i1 INTEGER NOT NULL, 1013 c1 VARCHAR(1) NOT NULL 1014); 1015 1016INSERT INTO t4 1017VALUES (1, 'j'), (2, 'c'), (0, 'a'); 1018 1019ANALYZE TABLE t1, t2, t3, t4; 1020 1021# Hint is added to avoid materialization of the subquery 1022let query= 1023SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1024FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1025WHERE ( t3.pk IN 1026 ( 1027 SELECT /*+ QB_NAME(subq1) */ t4.i1 1028 FROM t4 1029 WHERE t4.c1 < 'o' 1030 ) 1031) 1032AND t1.i1 <= t3.i2_key; 1033 1034eval EXPLAIN $query; 1035--sorted_result 1036eval $query; 1037 1038--echo # 1039--echo # Test 2: Two alternative covering indexes for the range scan 1040--echo # 1041 1042# Adding second covering index 1043if ($support_virtual_index) 1044{ 1045ALTER TABLE t3 ADD INDEX v_idx2 (i2_key, i1); 1046} 1047 1048# Hint is added to avoid materialization of the subquery 1049let query= 1050SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1051FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1052WHERE ( t3.pk IN 1053 ( 1054 SELECT /*+ QB_NAME(subq1) */ t4.i1 1055 FROM t4 1056 WHERE t4.c1 < 'o' 1057 ) 1058) 1059AND t1.i1 <= t3.i2_key; 1060 1061eval EXPLAIN $query; 1062--sorted_result 1063eval $query; 1064 1065--echo # 1066--echo # Test 3: One covering index including the base column for the virtual 1067--echo # column 1068--echo # 1069 1070if ($support_virtual_index) 1071{ 1072--echo # Drop the index with only the virtual column 1073ALTER TABLE t3 DROP INDEX v_idx; 1074} 1075 1076# Hint is added to avoid materialization of the subquery 1077let query= 1078SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1079FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1080WHERE ( t3.pk IN 1081 ( 1082 SELECT /*+ QB_NAME(subq1) */ t4.i1 1083 FROM t4 1084 WHERE t4.c1 < 'o' 1085 ) 1086) 1087AND t1.i1 <= t3.i2_key; 1088 1089eval EXPLAIN $query; 1090--sorted_result 1091eval $query; 1092 1093--echo # 1094--echo # Test 4: One non-covering index 1095--echo # 1096 1097if ($support_virtual_index) 1098{ 1099--echo # Drop the index on two columns, add index on just one virtual column 1100ALTER TABLE t3 DROP INDEX v_idx2; 1101ALTER TABLE t3 ADD INDEX v_idx (i2_key); 1102} 1103 1104--echo # Add more data to the table so that it will run the dynamic range scan 1105--echo # as both table scan and range scan (the purpose of this is to make the 1106--echo # table scan more expensive). 1107INSERT INTO t3 (pk, i1) 1108VALUES (10,1), (11,1), (12,1), (13,1), (14,1),(15,1), (16,1),(17,1), (18,1), 1109 (19,1), (20,1), (21,1), (22,1), (23,1), (24,1),(25,1),(26,1),(27,1), 1110 (28,1), (29,1); 1111 1112--echo # Change the query to read an extra column (t3.i1) making the index 1113--echo # non-covering. 1114# Hint is added to avoid materialization of the subquery 1115let query= 1116SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 1117FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1118WHERE ( t3.pk IN 1119 ( 1120 SELECT /*+ QB_NAME(subq1) */ t4.i1 1121 FROM t4 1122 WHERE t4.c1 < 'o' 1123 ) 1124) 1125AND t1.i1 <= t3.i2_key; 1126 1127eval EXPLAIN $query; 1128--sorted_result 1129eval $query; 1130 1131--echo # 1132--echo # Test 5: Test where the added primary key to secondary indexes is 1133--echo # used after it has been included in the join buffer 1134--echo # 1135 1136# This test is only relevant for storage engines that add the primary key 1137# to all secondary keys (e.g. InnoDB). For these engines, the fields in the 1138# primary key might be included when deciding that a secondary index is 1139# covering for the query. This is the case for most of the secondary indexes 1140# on t3 in this test. But in the above queries, the subquery is non-dependent 1141# and the "t3.pk IN .." will be evaluated after rows for t3 are read. At this 1142# time t3.pk is in the record buffer. t3.pk is not used after it has been 1143# inserted into the join buffer. To test that t3.pk is actually correctly 1144# included in the join buffer we change the subquery to be dependent and 1145# only evaluated after the join has been done. 1146# The purpose of this test is to ensure that we correctly handle and 1147# include primary key fields that are added to a covering secondary index. 1148 1149# The difference between this query and the query in test 1 is that 1150# an extra query condition is added to the subquery. 1151# Hint is added to avoid materialization of the subquery 1152let query= 1153SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 1154FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 1155WHERE ( t3.pk IN 1156 ( 1157 SELECT /*+ QB_NAME(subq1) */ t4.i1 1158 FROM t4 1159 WHERE t4.c1 < 'o' and t4.i1 < (t2.i1 + 1) 1160 ) 1161) 1162AND t1.i1 <= t3.i2_key; 1163 1164eval EXPLAIN $query; 1165--sorted_result 1166eval $query; 1167 1168DROP TABLE t1, t2, t3, t4; 1169 1170--disable_query_log 1171set @@optimizer_switch=@local_optimizer_switch; 1172--enable_query_log 1173