1--source include/have_debug.inc 2 3SET SESSION debug_dbug="+d,Item_func_in"; 4SET SESSION debug_dbug="+d,Predicant_to_list_comparator"; 5 6--echo # Constant predicant, compatible types, bisect 7SELECT 1 IN (1,2); 8SELECT 1 IN (1,2,NULL); 9SELECT 1 NOT IN (1,2); 10SELECT 1 NOT IN (1,2,NULL); 11 12SELECT 1.0 IN (1.0,2.0); 13SELECT 1.0 IN (1.0,2.0,NULL); 14SELECT 1.0 NOT IN (1.0,2.0); 15SELECT 1.0 NOT IN (1.0,2.0,NULL); 16 17SELECT 1e0 IN (1e0,2e0); 18SELECT 1e0 IN (1e0,2e0,NULL); 19SELECT 1e0 NOT IN (1e0,2e0); 20SELECT 1e0 NOT IN (1e0,2e0,NULL); 21 22SELECT 'a' IN ('a','b'); 23SELECT 'a' IN ('a','b',NULL); 24SELECT 'a' NOT IN ('a','b'); 25SELECT 'a' NOT IN ('a','b',NULL); 26 27SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30'); 28SELECT TIMESTAMP'2001-01-01 10:20:30' IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL); 29SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30'); 30SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN ('2001-01-01 10:20:30','2001-02-02 10:20:30',NULL); 31 32SELECT TIME'10:20:30' IN ('10:20:30','10:20:30'); 33SELECT TIME'10:20:30' IN ('10:20:30','10:20:30',NULL); 34SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30'); 35SELECT TIME'10:20:30' NOT IN ('10:20:30','10:20:30',NULL); 36 37SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02'); 38SELECT DATE'2001-01-01' IN ('2001-01-01','2001-02-02',NULL); 39SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02'); 40SELECT DATE'2001-01-01' NOT IN ('2001-01-01','2001-02-02',NULL); 41 42 43--echo # Column predicant, compatible types, bisect 44 45# Special case: mixture of unsigned, signed and decimal 46CREATE TABLE t1 (a INT UNSIGNED); 47# a=1.0 is done as decimal, because decimal bits int 48# a=1 is done as decimal as well, because of different unsigned flag 49SELECT a IN (1.0, 1) FROM t1; 50DROP TABLE t1; 51 52CREATE TABLE t1 (a INT); 53SELECT a IN (1,2,3) FROM t1; 54SELECT a IN (1,2,3,NULL) FROM t1; 55SELECT a IN (1.0, CAST(1 AS UNSIGNED)) FROM t1; 56SELECT a IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1; 57SELECT a NOT IN (1,2,3) FROM t1; 58SELECT a NOT IN (1,2,3,NULL) FROM t1; 59SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED)) FROM t1; 60SELECT a NOT IN (1.0, CAST(1 AS UNSIGNED),NULL) FROM t1; 61DROP TABLE t1; 62 63CREATE TABLE t1 (a DOUBLE); 64SELECT a IN (1e0,2,3.0) FROM t1; 65SELECT a IN (1e0,2,3.0,NULL) FROM t1; 66SELECT a NOT IN (1e0,2,3.0) FROM t1; 67SELECT a NOT IN (1e0,2,3.0,NULL) FROM t1; 68DROP TABLE t1; 69 70CREATE TABLE t1 (a DECIMAL(10,1)); 71SELECT a IN (1,2.0,3.0) FROM t1; 72SELECT a IN (1,2.0,3.0,NULL) FROM t1; 73SELECT a NOT IN (1,2.0,3.0) FROM t1; 74SELECT a NOT IN (1,2.0,3.0,NULL) FROM t1; 75DROP TABLE t1; 76 77CREATE TABLE t1 (a VARCHAR(10)); 78SELECT a IN ('a','b','c') FROM t1; 79SELECT a IN ('a','b','c',NULL) FROM t1; 80SELECT a NOT IN ('a','b','c') FROM t1; 81SELECT a NOT IN ('a','b','c',NULL) FROM t1; 82DROP TABLE t1; 83 84CREATE TABLE t1 (a DATE); 85SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1; 86SELECT a IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1; 87SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0) FROM t1; 88SELECT a NOT IN ('2001-01-01',DATE'2001-01-02',20010102,20010102.0,20010102e0,NULL) FROM t1; 89DROP TABLE t1; 90 91CREATE TABLE t1 (a TIME); 92SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1; 93SELECT a IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1; 94SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0) FROM t1; 95SELECT a NOT IN ('10:20:30',TIME'10:20:30',102030,102030.0,102030e0,NULL) FROM t1; 96DROP TABLE t1; 97 98CREATE TABLE t1 (a DATETIME); 99SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1; 100SELECT a IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1; 101SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0) FROM t1; 102SELECT a NOT IN ('2001-01-01',TIMESTAMP'2001-01-01 10:20:30',DATE'2001-01-01',TIME'10:20:30',20010101102030,20010101102030.0,20010101102030e0,NULL) FROM t1; 103DROP TABLE t1; 104 105--echo # Constant predicant, compatible types, no bisect 106--echo # Bisect is not used because of non-constant expressions in the list 107CREATE TABLE t1 (a INT); 108SELECT 1 IN (a,1,2,3) FROM t1; 109SELECT 1 IN (a,1,2,3,NULL) FROM t1; 110SELECT 1 NOT IN (a,1,2,3) FROM t1; 111SELECT 1 NOT IN (a,1,2,3,NULL) FROM t1; 112DROP TABLE t1; 113 114CREATE TABLE t1 (a DOUBLE); 115SELECT 1 IN (a,1e0,2e0,3e0) FROM t1; 116SELECT 1 IN (a,1e0,2e0,3e0,NULL) FROM t1; 117SELECT 1 NOT IN (a,1e0,2e0,3e0) FROM t1; 118SELECT 1 NOT IN (a,1e0,2e0,3e0,NULL) FROM t1; 119DROP TABLE t1; 120 121CREATE TABLE t1 (a DECIMAL(10,1)); 122SELECT 1 IN (a,1.0,2.0,3.0) FROM t1; 123SELECT 1 IN (a,1.0,2.0,3.0,NULL) FROM t1; 124SELECT 1 NOT IN (a,1.0,2.0,3.0) FROM t1; 125SELECT 1 NOT IN (a,1.0,2.0,3.0,NULL) FROM t1; 126DROP TABLE t1; 127 128CREATE TABLE t1 (a VARCHAR(10)); 129SELECT 'a' IN (a,'b','c') FROM t1; 130SELECT 'a' IN (a,'b','c',NULL) FROM t1; 131SELECT 'a' NOT IN (a,'b','c') FROM t1; 132SELECT 'a' NOT IN (a,'b','c',NULL) FROM t1; 133DROP TABLE t1; 134 135CREATE TABLE t1 (a DATE); 136SELECT DATE'2001-01-01' IN (a,'2001-01-01') FROM t1; 137SELECT DATE'2001-01-01' IN (a,'2001-01-01',NULL) FROM t1; 138SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01') FROM t1; 139SELECT DATE'2001-01-01' NOT IN (a,'2001-01-01',NULL) FROM t1; 140DROP TABLE t1; 141 142CREATE TABLE t1 (a TIME); 143SELECT TIME'10:20:30' IN (a,'10:20:30') FROM t1; 144SELECT TIME'10:20:30' IN (a,'10:20:30',NULL) FROM t1; 145SELECT TIME'10:20:30' NOT IN (a,'10:20:30') FROM t1; 146SELECT TIME'10:20:30' NOT IN (a,'10:20:30',NULL) FROM t1; 147DROP TABLE t1; 148 149CREATE TABLE t1 (a DATETIME); 150SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1; 151SELECT TIMESTAMP'2001-01-01 10:20:30' IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1; 152SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30') FROM t1; 153SELECT TIMESTAMP'2001-01-01 10:20:30' NOT IN (a,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1; 154DROP TABLE t1; 155 156 157--echo # Constant predicant, incompatible types, no bisect 158SELECT 1 IN (1,2e0); 159SELECT 1 IN (1,2e0,NULL); 160SELECT 1 NOT IN (1,2e0); 161SELECT 1 NOT IN (1,2e0,NULL); 162 163SELECT 1.0 IN (1.0,2e0); 164SELECT 1.0 IN (1.0,2e0,NULL); 165SELECT 1.0 NOT IN (1.0,2e0); 166SELECT 1.0 NOT IN (1.0,2e0,NULL); 167 168SELECT 1e0 IN (1.0,TIME'10:20:30'); 169SELECT 1e0 IN (1.0,TIME'10:20:30',NULL); 170SELECT 1e0 NOT IN (1.0,TIME'10:20:30'); 171SELECT 1e0 NOT IN (1.0,TIME'10:20:30',NULL); 172 173SELECT 'a' IN ('a',2); 174SELECT 'a' IN ('a',2,NULL); 175SELECT 'a' NOT IN ('a',2); 176SELECT 'a' NOT IN ('a',2,NULL); 177 178SELECT TIME'10:20:30' IN (1,TIME'10:20:30'); 179SELECT TIME'10:20:30' IN (1,TIME'10:20:30',NULL); 180SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32'); 181SELECT TIME'10:20:30' IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL); 182SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32'); 183SELECT TIME'10:20:30' IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL); 184SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30'); 185SELECT TIME'10:20:30' NOT IN (1,TIME'10:20:30',NULL); 186SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32'); 187SELECT TIME'10:20:30' NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL); 188SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32'); 189SELECT TIME'10:20:30' NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL); 190 191--echo # Column predicant, incompatible types, no bisect 192CREATE TABLE t1 (a INT); 193SELECT a IN (1,1e0) FROM t1; 194SELECT a IN (1,1e0,NULL) FROM t1; 195SELECT a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1; 196SELECT a IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1; 197SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1; 198SELECT a IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1; 199SELECT a NOT IN (1,1e0) FROM t1; 200SELECT a NOT IN (1,1e0,NULL) FROM t1; 201SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1; 202SELECT a NOT IN (CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1; 203SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED)) FROM t1; 204SELECT a NOT IN (CAST(1 AS DECIMAL),CAST(1 AS SIGNED), CAST(1 AS UNSIGNED),NULL) FROM t1; 205 206SELECT a IN (1,1.0) FROM t1; 207SELECT a IN (1,1.0,NULL) FROM t1; 208SELECT a NOT IN (1,1.0) FROM t1; 209SELECT a NOT IN (1,1.0,NULL) FROM t1; 210 211SELECT a IN (1,'1') FROM t1; 212SELECT a IN (1,'1',NULL) FROM t1; 213SELECT a NOT IN (1,'1') FROM t1; 214SELECT a NOT IN (1,'1',NULL) FROM t1; 215 216SELECT a IN (1,TIME'10:20:30') FROM t1; 217SELECT a IN (1,TIME'10:20:30',NULL) FROM t1; 218SELECT a NOT IN (1,TIME'10:20:30') FROM t1; 219SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1; 220DROP TABLE t1; 221 222CREATE TABLE t1 (a DECIMAL(10,0)); 223SELECT a IN (1,1e0) FROM t1; 224SELECT a IN (1,1e0,NULL) FROM t1; 225SELECT a NOT IN (1,1e0) FROM t1; 226SELECT a NOT IN (1,1e0,NULL) FROM t1; 227 228SELECT a IN (1,'1') FROM t1; 229SELECT a IN (1,'1',NULL) FROM t1; 230SELECT a NOT IN (1,'1') FROM t1; 231SELECT a NOT IN (1,'1',NULL) FROM t1; 232 233SELECT a IN (1,TIME'10:20:30') FROM t1; 234SELECT a IN (1,TIME'10:20:30',NULL) FROM t1; 235SELECT a NOT IN (1,TIME'10:20:30') FROM t1; 236SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1; 237DROP TABLE t1; 238 239CREATE TABLE t1 (a DOUBLE); 240SELECT a IN (1,TIME'10:20:30') FROM t1; 241SELECT a IN (1,TIME'10:20:30',NULL) FROM t1; 242SELECT a NOT IN (1,TIME'10:20:30') FROM t1; 243SELECT a NOT IN (1,TIME'10:20:30',NULL) FROM t1; 244 245SELECT a IN (1,DATE'2001-01-01') FROM t1; 246SELECT a IN (1,DATE'2001-01-01',NULL) FROM t1; 247SELECT a NOT IN (1,DATE'2001-01-01') FROM t1; 248SELECT a NOT IN (1,DATE'2001-01-01',NULL) FROM t1; 249 250SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1; 251SELECT a IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1; 252SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30') FROM t1; 253SELECT a NOT IN (1,TIMESTAMP'2001-01-01 10:20:30',NULL) FROM t1; 254DROP TABLE t1; 255 256CREATE TABLE t1 (a VARCHAR(10)); 257SELECT a IN ('a',1) FROM t1; 258SELECT a IN ('a',TIME'10:20:30') FROM t1; 259SELECT a NOT IN ('a',1) FROM t1; 260SELECT a NOT IN ('a',TIME'10:20:30') FROM t1; 261DROP TABLE t1; 262 263CREATE TABLE t1 (a TIME); 264SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1; 265SELECT a IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1; 266SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1; 267SELECT a IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1; 268SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1; 269SELECT a NOT IN (102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1; 270SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32') FROM t1; 271SELECT a NOT IN (102030, 102030, TIME'10:20:30',TIMESTAMP'2001-01-01 10:20:32',NULL) FROM t1; 272DROP TABLE t1; 273 274# 275# ROW tests 276# 277# ROW has additional conditions when bisect is possible (see item_cmpfunc.h): 278# 279# ((is_top_level_item && not_negated) || // 3 280# (arg0_can_not_be_null && list_does_not_have_nulls) // 4 281 282# Testing all combinations of the condition components 283# 284# tli - is_top_level_item 285# nneg - not_negated 286# a0nnul - arg0_can_not_be_null 287# lnnul - list_does_not_have_nulls 288# cond3 - condition 3 is true? 289# cond4 - condition 4 is true? 290# bisect - bisect is possible (cond3 orded with cond4) 291 292# Note: 293# - using an expression in SELECT list makes top_level_item() to be false 294# - using an expression in WHERE clause makes top_leve_item() to be true 295 296--echo # Not top level, negated: cond3 is false 297 298# tli nneg a0nnul lnnul cond3 cond4 bisect 299# --- --- ------ ----- ----- ----- ------ 300# 0 0 0 0 0 0 0 301# 0 0 0 1 0 0 0 302# 0 0 1 0 0 0 0 303# 0 0 1 1 0 1 1 304 305# ROW with scalar elements 306CREATE TABLE t1 (a INT); 307SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1; 308SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1; 309DROP TABLE t1; 310CREATE TABLE t1 (a INT NOT NULL, b INT); 311SELECT ROW(a,a) NOT IN ((1,1),(2,NULL)) FROM t1; 312SELECT ROW(a,a) NOT IN ((1,1),(2,2)) FROM t1; 313DROP TABLE t1; 314 315# ROW with a nested ROW 316CREATE TABLE t1 (a INT); 317SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1; 318SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1; 319DROP TABLE t1; 320CREATE TABLE t1 (a INT NOT NULL); 321SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))) FROM t1; 322SELECT ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))) FROM t1; 323DROP TABLE t1; 324 325--echo # Not top level, not negated: cond3 is false 326 327# tli nneg a0nnul lnnul cond3 cond4 bisect 328# --- --- ------ ----- ----- ----- ------ 329# 0 1 0 0 0 0 0 330# 0 1 0 1 0 0 0 331# 0 1 1 0 0 0 0 332# 0 1 1 1 0 1 1 333 334# ROW with scalar elements 335CREATE TABLE t1 (a INT); 336SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1; 337SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1; 338DROP TABLE t1; 339CREATE TABLE t1 (a INT NOT NULL); 340SELECT ROW(a,a) IN ((1,1),(2,NULL)) FROM t1; 341SELECT ROW(a,a) IN ((1,1),(2,2)) FROM t1; 342DROP TABLE t1; 343 344# ROW with a nested ROW 345CREATE TABLE t1 (a INT); 346SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1; 347SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1; 348DROP TABLE t1; 349CREATE TABLE t1 (a INT NOT NULL); 350SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))) FROM t1; 351SELECT ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))) FROM t1; 352DROP TABLE t1; 353 354 355--echo # Top level, negated: cond3 is false 356 357# tli nneg a0nnul lnnul cond3 cond4 bisect 358# --- --- ------ ----- ----- ----- ------ 359# 1 0 0 0 0 0 0 360# 1 0 0 1 0 0 0 361# 1 0 1 0 0 0 0 362# 1 0 1 1 0 1 1 363 364# ROW with scalar elements 365CREATE TABLE t1 (a INT); 366SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL)); 367SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2)); 368DROP TABLE t1; 369CREATE TABLE t1 (a INT NOT NULL); 370SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,NULL)); 371SELECT 1 FROM t1 WHERE ROW(a,a) NOT IN ((1,1),(2,2)); 372DROP TABLE t1; 373 374# ROW with a nested ROW 375CREATE TABLE t1 (a INT); 376SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))); 377SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))); 378DROP TABLE t1; 379CREATE TABLE t1 (a INT NOT NULL); 380SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,NULL))); 381SELECT 1 FROM t1 WHERE ROW(a,(a,a)) NOT IN ((1,(1,1)),(2,(2,2))); 382DROP TABLE t1; 383 384--echo # Top level, not negated: cond3 is true 385 386# tli nneg a0nnul lnnul cond3 cond4 bisect 387# --- --- ------ ----- ----- ----- ------ 388# 1 1 0 0 1 0 1 389# 1 1 0 1 1 1 1 390# 1 1 1 0 1 0 1 391# 1 1 1 1 1 1 1 392 393# ROW with scalar elements 394CREATE TABLE t1 (a INT); 395SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL)); 396SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2)); 397DROP TABLE t1; 398CREATE TABLE t1 (a INT NOT NULL); 399SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,NULL)); 400SELECT 1 FROM t1 WHERE ROW(a,a) IN ((1,1),(2,2)); 401DROP TABLE t1; 402 403# ROW with a nested ROW 404CREATE TABLE t1 (a INT); 405SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))); 406SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))); 407DROP TABLE t1; 408CREATE TABLE t1 (a INT NOT NULL); 409SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,NULL))); 410SELECT 1 FROM t1 WHERE ROW(a,(a,a)) IN ((1,(1,1)),(2,(2,2))); 411DROP TABLE t1; 412 413--echo # 414--echo # MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result 415--echo # 416 417SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32'); 418 419PREPARE stmt FROM "SELECT 420 TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')"; 421EXECUTE stmt; 422EXECUTE stmt; 423DEALLOCATE PREPARE stmt; 424 425CREATE TABLE t1 (a VARCHAR(10)); 426INSERT INTO t1 VALUES ('A'),('B'),('A'); 427# Compatible types 428SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A'); 429SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b); 430SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a); 431SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b); 432# Incompatible types 433SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,'A',10); 434SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN ('A',b,10); 435SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (b,a,10); 436SELECT a,NULL AS b FROM t1 GROUP BY a HAVING 'A' IN (a,b,10); 437DROP TABLE t1; 438 439--echo # 440--echo # MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions)) 441--echo # 442CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); 443INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615); 444SELECT * FROM t1 WHERE -1 IN (a,b); 445DROP TABLE t1; 446 447 448--echo # 449--echo # MDEV-11554 Wrong result for CASE on a mixture of signed and unsigned expressions 450--echo # 451 452CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); 453INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615); 454SELECT 455 CASE -1 456 WHEN -9223372036854775808 THEN 'one' 457 WHEN 18446744073709551615 THEN 'two' 458 END AS c; 459DROP TABLE t1; 460 461--echo # 462--echo # MDEV-11555 CASE with a mixture of TIME and DATETIME returns a wrong result 463--echo # 464SELECT 465 CASE TIME'10:20:30' 466 WHEN 102030 THEN 'one' 467 WHEN TIME'10:20:31' THEN 'two' 468 END AS good, 469 CASE TIME'10:20:30' 470 WHEN 102030 THEN 'one' 471 WHEN TIME'10:20:31' THEN 'two' 472 WHEN TIMESTAMP'2001-01-01 10:20:32' THEN 'three' 473 END AS was_bad_now_good; 474 475 476SET SESSION debug_dbug="-d,Predicant_to_list_comparator"; 477SET SESSION debug_dbug="-d,Item_func_in"; 478 479 480--echo # 481--echo # MDEV-19008 Slow EXPLAIN SELECT ... WHERE col IN (const1,const2,(subquery)) 482--echo # 483 484SET SESSION debug_dbug="+d,Item_subselect"; 485CREATE TABLE t1 (a INT); 486INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 487EXPLAIN SELECT * FROM t1 WHERE a IN (1,2,(SELECT MAX(a) FROM t1)); 488SELECT * FROM t1 WHERE a IN (1,2,(SELECT MAX(a) FROM t1)); 489DROP TABLE t1; 490SET SESSION debug_dbug="-d,Item_subselect"; 491 492 493--echo # 494--echo # MDEV-16408 Remove tests for Item::type() in Item_basic_value::eq() 495--echo # 496 497SET SESSION debug_dbug="+d,Item_basic_value"; 498CREATE TABLE t1 (a INT); 499INSERT INTO t1 VALUES (1),(2),(3); 500SELECT * FROM t1 WHERE a BETWEEN 1 AND 1.0; 501SELECT * FROM t1 WHERE a BETWEEN 1 AND 1; 502SELECT * FROM t1 WHERE a BETWEEN 0 AND 1; 503SELECT * FROM t1 WHERE a BETWEEN 0 AND -1; 504SELECT * FROM t1 WHERE a BETWEEN -1 AND -1; 505SELECT * FROM t1 WHERE a BETWEEN -0000000000000001 AND -1; 506SELECT * FROM t1 WHERE a BETWEEN -1 AND 18446744073709551615; 507SELECT * FROM t1 WHERE a BETWEEN -1 AND 18446744073709551616; 508SELECT * FROM t1 WHERE a BETWEEN 1e2 AND 100e0; 509 510EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN 1 AND ?' USING 1; 511EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 18446744073709551615; 512EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN -1 AND ?' USING 18446744073709551616; 513DROP TABLE t1; 514 515CREATE TABLE t1 (a VARCHAR(10)); 516INSERT INTO t1 VALUES ('0'),('1'),('2'); 517SELECT * FROM t1 WHERE a BETWEEN '0' AND '0'; 518SELECT * FROM t1 WHERE a BETWEEN '0' AND ' 0'; 519SELECT * FROM t1 WHERE a BETWEEN '0' AND '0 '; 520DROP TABLE t1; 521 522SET SESSION debug_dbug="-d,Item_basic_value"; 523 524 525--echo # 526--echo # MDEV-11362 True condition elimination does not work for DECIMAL and temporal dynamic SQL parameters 527--echo # 528 529SET SESSION debug_dbug="+d,Item_basic_value"; 530 531CREATE TABLE t1 (a DECIMAL(10,3)); 532INSERT INTO t1 VALUES (1),(2),(3); 533SELECT * FROM t1 WHERE a BETWEEN 1.0 AND 1.0; 534EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN 1.0 AND ?' USING 1.0; 535DROP TABLE t1; 536 537CREATE TABLE t1 (a TIME); 538INSERT INTO t1 VALUES ('00:00:00'),('00:00:01'); 539SELECT * FROM t1 WHERE a BETWEEN TIME'00:00:00' AND TIME'00:00:00'; 540EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIME''00:00:00'' AND ?' USING TIME'00:00:00'; 541DROP TABLE t1; 542 543CREATE TABLE t1 (a DATE); 544INSERT INTO t1 VALUES ('2001-01-01'),('2001-01-02'); 545SELECT * FROM t1 WHERE a BETWEEN DATE'2001-01-01' AND DATE'2001-01-01'; 546EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN DATE''2001-01-01'' AND ?' USING DATE'2001-01-01'; 547DROP TABLE t1; 548 549CREATE TABLE t1 (a DATETIME); 550INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:00'); 551SELECT * FROM t1 WHERE a BETWEEN TIMESTAMP'2001-01-01 00:00:00' AND TIMESTAMP'2001-01-01 00:00:00'; 552EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN TIMESTAMP''2001-01-01 00:00:00'' AND ?' USING TIMESTAMP'2001-01-01 00:00:00'; 553DROP TABLE t1; 554 555SET SESSION debug_dbug="-d,Item_basic_value"; 556 557 558--echo # 559--echo # MDEV-16426 Optimizer erroneously treats equal constants of different formats as same 560--echo # 561 562SET SESSION debug_dbug="+d,Item_basic_value"; 563 564CREATE TABLE t1 (a VARCHAR(10)); 565INSERT INTO t1 VALUES ('a'),('b'),('c'); 566SELECT * FROM t1 WHERE a BETWEEN 'a' AND 0x61; 567EXECUTE IMMEDIATE 'SELECT * FROM t1 WHERE a BETWEEN ''a'' AND ?' USING 0x61; 568DROP TABLE t1; 569 570SET SESSION debug_dbug="-d,Item_basic_value"; 571 572 573--echo # 574--echo # MDEV-16454 Bad results for IN with ROW 575--echo # 576 577SET SESSION debug_dbug="+d,cmp_item"; 578SET SESSION debug_dbug="+d,Item_func_in"; 579SET SESSION debug_dbug="+d,Predicant_to_list_comparator"; 580 581SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0)); 582SELECT (1,(0,0)) IN ((1,(1,0)),(0,(0,0))); 583SELECT (1,(0,0),3) IN ((1,(1,0),3),(0,(0,0),3)); 584 585SELECT '0x' IN (0); 586SELECT '0x' IN (0,1); 587SELECT ('0x',1) IN ((0,1)); 588SELECT ('0x',1) IN ((0,1),(1,1)); 589 590SET SESSION debug_dbug="-d,Predicant_to_list_comparator"; 591SET SESSION debug_dbug="-d,Item_func_in"; 592SET SESSION debug_dbug="-d,cmp_item"; 593