1# 2# MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery 3# 4source include/have_debug.inc; 5source include/default_optimizer_switch.inc; 6source include/have_sequence.inc; 7 8create table t1 (a int, b int); 9 10insert into t1 11values (1,2), (4,6), (9,7), 12 (1,1), (2,5), (7,8); 13 14create table t2 (a int, b int, c int); 15 16insert into t2 17values (1,2,3), (5,1,2), (4,3,7), 18 (8,9,0), (10,7,1), (5,5,1); 19 20create table t3 (a int, b varchar(16), index idx(a)); 21insert into t3 values 22 (1, "abc"), (3, "egh"), (8, "axxx"), (10, "abc"), 23 (2, "ccw"), (8, "wqqe"), (7, "au"), (9, "waa"), 24 (3, "rass"), (9, "ert"), (9, "lok"), (8, "aww"), 25 (1, "todd"), (3, "rew"), (8, "aww"), (3, "sw"), 26 (11, "llk"), (7, "rbw"), (1, "sm"), (2, "jyp"), 27 (4, "yq"), (5, "pled"), (12, "ligin"), (12, "toww"), 28 (6, "mxm"), (15, "wanone"), (9, "sunqq"), (2, "abe"); 29 30--echo # optimization is not used 31 32let $query= select * from t1 where a in (1,2); 33eval $query; 34eval explain extended $query; 35 36--echo # set minimum number of values in VALUEs list when optimization works to 2 37 38set @@in_predicate_conversion_threshold= 2; 39 40--echo # single IN-predicate in WHERE-part 41 42let $query= select * from t1 where a in (1,2); 43 44let $optimized_query= 45select * from t1 46where a in 47 ( 48 select * 49 from (values (1),(2)) as tvc_0 50 ); 51 52eval $query; 53eval $optimized_query; 54eval explain extended $query; 55eval explain extended $optimized_query; 56 57--echo # AND-condition with IN-predicates in WHERE-part 58 59let $query= 60select * from t1 61where a in (1,2) and 62 b in (1,5); 63 64let $optimized_query= 65select * from t1 66where a in 67( 68 select * 69 from (values (1),(2)) as tvc_0 70) 71and b in 72( 73 select * 74 from (values (1),(5)) as tvc_1 75); 76 77eval $query; 78eval $optimized_query; 79eval explain extended $query; 80eval explain extended $optimized_query; 81 82--echo # subquery with IN-predicate 83 84let $query= 85select * from t1 86where a in 87( 88 select a 89 from t2 where b in (3,4) 90); 91 92let $optimized_query= 93select * from t1 94where a in 95( 96 select a from t2 97 where b in 98 ( 99 select * 100 from (values (3),(4)) as tvc_0 101 ) 102); 103 104eval $query; 105eval $optimized_query; 106eval explain extended $query; 107eval explain extended $optimized_query; 108 109--echo # derived table with IN-predicate 110 111let $query= 112select * from 113( 114 select * 115 from t1 116 where a in (1,2) 117) as dr_table; 118 119let $optimized_query= 120select * from 121( 122 select * 123 from t1 124 where a in 125 ( 126 select * 127 from (values (1),(2)) 128 as tvc_0 129 ) 130) as dr_table; 131 132eval $query; 133eval $optimized_query; 134eval explain extended $query; 135eval explain extended $optimized_query; 136 137--echo # non-recursive CTE with IN-predicate 138 139let $cte_query= 140with tvc_0 as 141( 142 select * 143 from t1 144 where a in (1,2) 145) 146select * from tvc_0; 147 148eval $cte_query; 149eval $optimized_query; 150eval explain extended $cte_query; 151eval explain extended $optimized_query; 152 153--echo # VIEW with IN-predicate 154 155create view v1 as 156 select * 157 from t1 158 where a in (1,2); 159 160create view v2 as 161 select * 162 from t1 163 where a in 164 ( 165 select * 166 from (values (1),(2)) 167 as tvc_0 168 ) 169; 170 171let $query= select * from v1; 172let $optimized_query= select * from v2; 173 174eval $query; 175eval $optimized_query; 176eval explain extended $query; 177eval explain extended $optimized_query; 178 179drop view v1,v2; 180 181--echo # subselect defined by derived table with IN-predicate 182 183let $query= 184select * from t1 185where a in 186( 187 select 1 188 from 189 ( 190 select * 191 from t1 192 where a in (1,2) 193 ) 194 as dr_table 195); 196 197let $optimized_query= 198select * from t1 199where a in 200( 201 select 1 202 from 203 ( 204 select * 205 from t1 206 where a in 207 ( 208 select * 209 from (values (1),(2)) 210 as tvc_0 211 ) 212 ) 213 as dr_table 214); 215 216eval $query; 217eval $optimized_query; 218eval explain extended $query; 219eval explain extended $optimized_query; 220 221--echo # derived table with IN-predicate and group by 222 223let $query= 224select * from 225( 226 select max(a),b 227 from t1 228 where b in (3,5) 229 group by b 230) as dr_table; 231 232let $optimized_query= 233select * from 234( 235 select max(a),b 236 from t1 237 where b in 238 ( 239 select * 240 from (values (3),(5)) 241 as tvc_0 242 ) 243 group by b 244) as dr_table; 245 246eval $query; 247eval $optimized_query; 248eval explain extended $query; 249eval explain extended $optimized_query; 250 251--echo # prepare statement 252 253prepare stmt from "select * from t1 where a in (1,2)"; 254execute stmt; 255execute stmt; 256deallocate prepare stmt; 257 258--echo # use inside out access from tvc rows 259 260let $query= select * from t3 where a in (1,4); 261set @@in_predicate_conversion_threshold= default; 262eval $query; 263eval explain extended $query; 264set @@in_predicate_conversion_threshold= 2; 265eval $query; 266eval explain extended $query; 267 268--echo # use vectors in IN predeicate 269 270set @@in_predicate_conversion_threshold= 4; 271 272let $query= 273select * from t1 where (a,b) in ((1,2),(3,4)); 274 275eval $query; 276eval explain extended $query; 277set @@in_predicate_conversion_threshold= 2; 278 279--echo # trasformation works for the one IN predicate and doesn't work for the other 280 281set @@in_predicate_conversion_threshold= 5; 282 283let $query= 284select * from t2 285where (a,b) in ((1,2),(8,9)) and 286 (a,c) in ((1,3),(8,0),(5,1)); 287 288eval $query; 289eval explain extended $query; 290set @@in_predicate_conversion_threshold= 2; 291 292--echo # 293--echo # mdev-14281: conversion of NOT IN predicate into subquery predicate 294--echo # 295 296let $query= 297select * from t1 298where (a,b) not in ((1,2),(8,9), (5,1)); 299let $optimized_query= 300select * from t1 301where (a,b) not in (select * from (values (1,2),(8,9), (5,1)) as tvc_0); 302 303eval $query; 304eval $optimized_query; 305eval explain extended $query; 306eval explain extended $optimized_query; 307 308let $query= 309select * from t1 310where b < 7 and (a,b) not in ((1,2),(8,9), (5,1)); 311eval $query; 312eval explain extended $query; 313 314let $query= 315select * from t2 316where (a,c) not in ((1,2),(8,9), (5,1)); 317eval $query; 318eval explain extended $query; 319 320drop table t1, t2, t3; 321 322set @@in_predicate_conversion_threshold= default; 323 324--echo # 325--echo # MDEV-14947: conversion to TVC with only NULL values 326--echo # 327 328CREATE TABLE t1 (i INT); 329INSERT INTO t1 VALUES (3), (2), (7); 330 331let $q= 332SELECT * FROM t1 WHERE i IN (NULL, NULL, NULL, NULL, NULL); 333 334eval $q; 335eval EXPLAIN EXTENDED $q; 336 337SET in_predicate_conversion_threshold= 5; 338 339eval $q; 340eval EXPLAIN EXTENDED $q; 341 342SET in_predicate_conversion_threshold= default; 343 344DROP TABLE t1; 345 346--echo # 347--echo # MDEV-14835: conversion to TVC with BIGINT or YEAR values 348--echo # 349 350SET @@in_predicate_conversion_threshold= 2; 351 352CREATE TABLE t1 (a BIGINT); 353CREATE TABLE t2 (y YEAR); 354 355INSERT INTO t1 VALUES (1), (2), (3); 356INSERT INTO t2 VALUES (2009), (2010), (2011); 357 358SELECT * FROM t1 WHERE a IN ('1','5','3'); 359 360SELECT * FROM t2 WHERE y IN ('2009','2011'); 361 362DROP TABLE t1,t2; 363 364SET @@in_predicate_conversion_threshold= default; 365 366--echo # 367--echo # MDEV-17222: conversion to TVC with no names for constants 368--echo # conversion to TVC with the same constants in the first row 369--echo # 370 371SET @@in_predicate_conversion_threshold= 2; 372 373CREATE TABLE t1 (f BINARY(16)) ENGINE=MYISAM; 374INSERT INTO t1 VALUES 375(x'BAE56AF2B1C2397D99D58E2A06761DDB'), (x'9B9B698BCCB939EE8F1EA56C1A2E5DAA'), 376(x'A0A1C4FE39A239BABD3E0D8985E6BEA5'); 377 378SELECT COUNT(*) FROM t1 WHERE f IN 379(x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2', 380 x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'); 381 382CREATE TABLE t2 (f1 BINARY(16), f2 BINARY(16)) ENGINE=MYISAM; 383INSERT INTO t2 VALUES 384(x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'), 385(x'86052C062AAF368D84247ED0F6346A70', x'BF5C35045C6037C79E11026ABB9A3A4E'); 386 387SELECT COUNT(*) FROM t2 WHERE (f1,f2) IN 388((x'9B9B698BCCB939EE8F1EA56C1A2E5DAA', x'E2362DBAB5EA30B5804917A0A7D881E2'), 389 (x'B78B2EEAD13635088D93EA3309E24802', x'BAE56AF2B1C2397D99D58E2A06761DDB'), 390 (x'55FB3B14D6B83D39859E42533906350D', x'00F3458C47FA39DDBEAD918A13F8342E'), 391 (x'1606014E7C4A312F83EDC9D91BBFCACA', x'33F6068E56FD3A1D8326517F0D81CB5A')); 392 393CREATE TABLE t3 (f1 int, f2 int) ENGINE=MYISAM; 394INSERT INTO t3 VALUES (2,5), (2,3), (1,2), (7,8), (1,1); 395 396SELECT * FROM t3 WHERE (f1,f2) IN ((2, 2), (1, 2), (3, 5), (1, 1)); 397 398DROP TABLE t1,t2,t3; 399 400SET @@in_predicate_conversion_threshold= default; 401 402--echo # 403--echo # MDEV-20900: IN predicate to IN subquery conversion causes performance regression 404--echo # 405 406create table t1(a int, b int); 407insert into t1 select seq-1, seq-1 from seq_1_to_10; 408 409set in_predicate_conversion_threshold=2; 410 411let $query= select * from t1 where t1.a IN ("1","2","3","4"); 412eval explain $query; 413eval $query; 414 415set in_predicate_conversion_threshold=0; 416eval explain $query; 417eval $query; 418 419set in_predicate_conversion_threshold=2; 420let $query= select * from t1 where (t1.a,t1.b) in (("1","1"),(2,2),(3,3),(4,4)); 421eval explain $query; 422eval $query; 423 424set in_predicate_conversion_threshold=0; 425eval explain $query; 426eval $query; 427 428drop table t1; 429SET @@in_predicate_conversion_threshold= default; 430 431