1# Initialise 2--disable_warnings 3drop table if exists t1, t2; 4--enable_warnings 5# 6# test of IN (NULL) 7# 8 9select 1 in (1,2,3); 10select 10 in (1,2,3); 11select NULL in (1,2,3); 12select 1 in (1,NULL,3); 13select 3 in (1,NULL,3); 14select 10 in (1,NULL,3); 15select 1.5 in (1.5,2.5,3.5); 16select 10.5 in (1.5,2.5,3.5); 17select NULL in (1.5,2.5,3.5); 18select 1.5 in (1.5,NULL,3.5); 19select 3.5 in (1.5,NULL,3.5); 20select 10.5 in (1.5,NULL,3.5); 21 22CREATE TABLE t1 (a int, b int, c int); 23insert into t1 values (1,2,3), (1,NULL,3); 24select 1 in (a,b,c) from t1; 25select 3 in (a,b,c) from t1; 26select 10 in (a,b,c) from t1; 27select NULL in (a,b,c) from t1; 28drop table t1; 29CREATE TABLE t1 (a float, b float, c float); 30insert into t1 values (1.5,2.5,3.5), (1.5,NULL,3.5); 31select 1.5 in (a,b,c) from t1; 32select 3.5 in (a,b,c) from t1; 33select 10.5 in (a,b,c) from t1; 34drop table t1; 35CREATE TABLE t1 (a varchar(10), b varchar(10), c varchar(10)); 36insert into t1 values ('A','BC','EFD'), ('A',NULL,'EFD'); 37select 'A' in (a,b,c) from t1; 38select 'EFD' in (a,b,c) from t1; 39select 'XSFGGHF' in (a,b,c) from t1; 40drop table t1; 41 42CREATE TABLE t1 (field char(1)); 43INSERT INTO t1 VALUES ('A'),(NULL); 44SELECT * from t1 WHERE field IN (NULL); 45SELECT * from t1 WHERE field NOT IN (NULL); 46SELECT * from t1 where field = field; 47SELECT * from t1 where field <=> field; 48DELETE FROM t1 WHERE field NOT IN (NULL); 49SELECT * FROM t1; 50drop table t1; 51 52create table t1 (id int(10) primary key); 53insert into t1 values (1),(2),(3),(4),(5),(6),(7),(8),(9); 54select * from t1 where id in (2,5,9); 55drop table t1; 56 57create table t1 ( 58a char(1) character set latin1 collate latin1_general_ci, 59b char(1) character set latin1 collate latin1_swedish_ci, 60c char(1) character set latin1 collate latin1_danish_ci 61); 62insert into t1 values ('A','B','C'); 63insert into t1 values ('a','c','c'); 64--error 1267 65select * from t1 where a in (b); 66--error 1270 67select * from t1 where a in (b,c); 68--error 1271 69select * from t1 where 'a' in (a,b,c); 70select * from t1 where 'a' in (a); 71select * from t1 where a in ('a'); 72select * from t1 where 'a' collate latin1_general_ci in (a,b,c); 73select * from t1 where 'a' collate latin1_bin in (a,b,c); 74select * from t1 where 'a' in (a,b,c collate latin1_bin); 75explain extended select * from t1 where 'a' in (a,b,c collate latin1_bin); 76drop table t1; 77 78set names utf8; 79create table t1 (a char(10) character set utf8 not null); 80insert into t1 values ('bbbb'),(_koi8r'����'),(_latin1'����'); 81select a from t1 where a in ('bbbb',_koi8r'����',_latin1'����') order by a; 82drop table t1; 83# Bug#7834 Illegal mix of collations in IN operator 84create table t1 (a char(10) character set latin1 not null); 85insert into t1 values ('a'),('b'),('c'); 86select a from t1 where a IN ('a','b','c') order by a; 87drop table t1; 88set names latin1; 89 90select '1.0' in (1,2); 91select 1 in ('1.0',2); 92select 1 in (1,'2.0'); 93select 1 in ('1.0',2.0); 94select 1 in (1.0,'2.0'); 95select 1 in ('1.1',2); 96select 1 in ('1.1',2.0); 97 98# Test case for bug #6365 99 100create table t1 (a char(2) character set binary); 101insert into t1 values ('aa'), ('bb'); 102select * from t1 where a in (NULL, 'aa'); 103drop table t1; 104 105# BUG#13419 106create table t1 (id int, key(id)); 107insert into t1 values (1),(2),(3); 108select count(*) from t1 where id not in (1); 109select count(*) from t1 where id not in (1,2); 110drop table t1; 111 112 113# 114# BUG#17047: CHAR() and IN() can return NULL without signaling NULL 115# result 116# 117# The problem was in the IN() function that ignored maybe_null flags 118# of all arguments except the first (the one _before_ the IN 119# keyword, '1' in the test case below). 120# 121--disable_warnings 122DROP TABLE IF EXISTS t1; 123--enable_warnings 124 125CREATE TABLE t1 SELECT 1 IN (2, NULL); 126--echo SELECT should return NULL. 127SELECT * FROM t1; 128 129DROP TABLE t1; 130 131 132--echo End of 4.1 tests 133 134 135# 136# Bug #11885: WHERE condition with NOT IN (one element) 137# 138 139CREATE TABLE t1 (a int PRIMARY KEY); 140INSERT INTO t1 VALUES (44), (45), (46); 141 142SELECT * FROM t1 WHERE a IN (45); 143SELECT * FROM t1 WHERE a NOT IN (0, 45); 144SELECT * FROM t1 WHERE a NOT IN (45); 145 146CREATE VIEW v1 AS SELECT * FROM t1 WHERE a NOT IN (45); 147SHOW CREATE VIEW v1; 148SELECT * FROM v1; 149 150DROP VIEW v1; 151DROP TABLE t1; 152 153# BUG#15872: Excessive memory consumption of range analysis of NOT IN 154create table t1 (a int); 155insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); 156create table t2 (a int, filler char(200), key(a)); 157 158insert into t2 select C.a*2, 'no' from t1 A, t1 B, t1 C; 159insert into t2 select C.a*2+1, 'yes' from t1 C; 160 161explain 162select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); 163select * from t2 where a NOT IN (0, 2,4,6,8,10,12,14,16,18); 164 165explain select * from t2 force index(a) where a NOT IN (2,2,2,2,2,2); 166explain select * from t2 force index(a) where a <> 2; 167 168drop table t2; 169 170# 171# Repeat the test for DATETIME 172# 173create table t2 (a datetime, filler char(200), key(a)); 174 175insert into t2 select '2006-04-25 10:00:00' + interval C.a minute, 176 'no' from t1 A, t1 B, t1 C where C.a % 2 = 0; 177 178insert into t2 select '2006-04-25 10:00:00' + interval C.a*2+1 minute, 179 'yes' from t1 C; 180 181explain 182select * from t2 where a NOT IN ( 183 '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 184 '2006-04-25 10:06:00', '2006-04-25 10:08:00'); 185select * from t2 where a NOT IN ( 186 '2006-04-25 10:00:00','2006-04-25 10:02:00','2006-04-25 10:04:00', 187 '2006-04-25 10:06:00', '2006-04-25 10:08:00'); 188drop table t2; 189 190# 191# Repeat the test for CHAR(N) 192# 193create table t2 (a varchar(10), filler char(200), key(a)); 194 195insert into t2 select 'foo', 'no' from t1 A, t1 B; 196insert into t2 select 'barbar', 'no' from t1 A, t1 B; 197insert into t2 select 'bazbazbaz', 'no' from t1 A, t1 B; 198 199insert into t2 values ('fon', '1'), ('fop','1'), ('barbaq','1'), 200 ('barbas','1'), ('bazbazbay', '1'),('zz','1'); 201 202explain select * from t2 where a not in('foo','barbar', 'bazbazbaz'); 203 204drop table t2; 205 206# 207# Repeat for DECIMAL 208# 209create table t2 (a decimal(10,5), filler char(200), key(a)); 210 211insert into t2 select 345.67890, 'no' from t1 A, t1 B; 212insert into t2 select 43245.34, 'no' from t1 A, t1 B; 213insert into t2 select 64224.56344, 'no' from t1 A, t1 B; 214 215insert into t2 values (0, '1'), (22334.123,'1'), (33333,'1'), 216 (55555,'1'), (77777, '1'); 217 218explain 219select * from t2 where a not in (345.67890, 43245.34, 64224.56344); 220select * from t2 where a not in (345.67890, 43245.34, 64224.56344); 221 222drop table t2; 223 224# Try a very big IN-list 225create table t2 (a int, key(a), b int); 226insert into t2 values (1,1),(2,2); 227 228set @cnt= 1; 229set @str="update t2 set b=1 where a not in ("; 230select count(*) from ( 231 select @str:=concat(@str, @cnt:=@cnt+1, ",") 232 from t1 A, t1 B, t1 C, t1 D) Z; 233 234set @str:=concat(@str, "10000)"); 235select substr(@str, 1, 50); 236prepare s from @str; 237execute s; 238deallocate prepare s; 239set @str=NULL; 240 241drop table t2; 242drop table t1; 243 244# BUG#19618: Crash in range optimizer for 245# "unsigned_keypart NOT IN(negative_number,...)" 246# (introduced in fix BUG#15872) 247create table t1 ( 248 some_id smallint(5) unsigned, 249 key (some_id) 250); 251insert into t1 values (1),(2); 252select some_id from t1 where some_id not in(2,-1); 253select some_id from t1 where some_id not in(-4,-1,-4); 254select some_id from t1 where some_id not in(-4,-1,3423534,2342342); 255 256# 257# BUG#24261: crash when WHERE contains NOT IN ('<negative value>') for unsigned column type 258# 259 260select some_id from t1 where some_id not in('-1', '0'); 261 262drop table t1; 263 264# 265# BUG#20420: optimizer reports wrong keys on left join with IN 266# 267CREATE TABLE t1 (a int, b int, PRIMARY KEY (a)); 268INSERT INTO t1 VALUES (1,1),(2,1),(3,1),(4,1),(5,1),(6,1); 269 270CREATE TABLE t2 (a int, b int, PRIMARY KEY (a)); 271INSERT INTO t2 VALUES (3,2),(4,2),(100,100),(101,201),(102,102); 272 273CREATE TABLE t3 (a int PRIMARY KEY); 274INSERT INTO t3 VALUES (1),(2),(3),(4); 275 276CREATE TABLE t4 (a int PRIMARY KEY,b int); 277INSERT INTO t4 VALUES (1,1),(2,2),(1000,1000),(1001,1001),(1002,1002), 278 (1003,1003),(1004,1004); 279 280EXPLAIN SELECT STRAIGHT_JOIN * FROM t3 281 JOIN t1 ON t3.a=t1.a 282 JOIN t2 ON t3.a=t2.a 283 JOIN t4 WHERE t4.a IN (t1.b, t2.b); 284 285SELECT STRAIGHT_JOIN * FROM t3 286 JOIN t1 ON t3.a=t1.a 287 JOIN t2 ON t3.a=t2.a 288 JOIN t4 WHERE t4.a IN (t1.b, t2.b); 289 290EXPLAIN SELECT STRAIGHT_JOIN 291 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 292 FROM t3, t1, t2 293 WHERE t3.a=t1.a AND t3.a=t2.a; 294 295SELECT STRAIGHT_JOIN 296 (SELECT SUM(t4.a) FROM t4 WHERE t4.a IN (t1.b, t2.b)) 297 FROM t3, t1, t2 298 WHERE t3.a=t1.a AND t3.a=t2.a; 299 300DROP TABLE t1,t2,t3,t4; 301 302# 303# BUG#19342: IN works incorrectly for BIGINT UNSIGNED values 304# 305CREATE TABLE t1(a BIGINT UNSIGNED); 306INSERT INTO t1 VALUES (0xFFFFFFFFFFFFFFFF); 307 308SELECT * FROM t1 WHERE a=-1 OR a=-2 ; 309SELECT * FROM t1 WHERE a IN (-1, -2); 310 311CREATE TABLE t2 (a BIGINT UNSIGNED); 312insert into t2 values(13491727406643098568), 313 (0x7fffffefffffffff), 314 (0x7ffffffeffffffff), 315 (0x7fffffffefffffff), 316 (0x7ffffffffeffffff), 317 (0x7fffffffffefffff), 318 (0x7ffffffffffeffff), 319 (0x7fffffffffffefff), 320 (0x7ffffffffffffeff), 321 (0x7fffffffffffffef), 322 (0x7ffffffffffffffe), 323 (0x7fffffffffffffff), 324 (0x8000000000000000), 325 (0x8000000000000001), 326 (0x8000000000000002), 327 (0x8000000000000300), 328 (0x8000000000000400), 329 (0x8000000000000401), 330 (0x8000000000004001), 331 (0x8000000000040001), 332 (0x8000000000400001), 333 (0x8000000004000001), 334 (0x8000000040000001), 335 (0x8000000400000001), 336 (0x8000004000000001), 337 (0x8000040000000001); 338 339SELECT HEX(a) FROM t2 WHERE a IN 340 (CAST(0xBB3C3E98175D33C8 AS UNSIGNED), 341 42); 342 343SELECT HEX(a) FROM t2 WHERE a IN 344 (CAST(0xBB3C3E98175D33C8 AS UNSIGNED), 345 CAST(0x7fffffffffffffff AS UNSIGNED), 346 CAST(0x8000000000000000 AS UNSIGNED), 347 CAST(0x8000000000000400 AS UNSIGNED), 348 CAST(0x8000000000000401 AS UNSIGNED), 349 42); 350 351SELECT HEX(a) FROM t2 WHERE a IN 352 (CAST(0x7fffffffffffffff AS UNSIGNED), 353 CAST(0x8000000000000001 AS UNSIGNED)); 354SELECT HEX(a) FROM t2 WHERE a IN 355 (CAST(0x7ffffffffffffffe AS UNSIGNED), 356 CAST(0x7fffffffffffffff AS UNSIGNED)); 357SELECT HEX(a) FROM t2 WHERE a IN 358 (0x7ffffffffffffffe, 359 0x7fffffffffffffff, 360 'abc'); 361 362CREATE TABLE t3 (a BIGINT UNSIGNED); 363INSERT INTO t3 VALUES (9223372036854775551); 364 365SELECT HEX(a) FROM t3 WHERE a IN (9223372036854775807, 42); 366 367CREATE TABLE t4 (a DATE); 368INSERT INTO t4 VALUES ('1972-02-06'), ('1972-07-29'); 369SELECT * FROM t4 WHERE a IN ('1972-02-06','19772-07-29'); 370 371DROP TABLE t1,t2,t3,t4; 372 373# 374# BUG#27362: IN with a decimal expression that may return NULL 375# 376 377CREATE TABLE t1 (id int not null); 378INSERT INTO t1 VALUES (1),(2); 379 380SELECT id FROM t1 WHERE id IN(4564, (SELECT IF(1=0,1,1/0)) ); 381 382DROP TABLE t1; 383 384--echo End of 5.0 tests 385 386 387# 388# Bug#18360: Type aggregation for IN and CASE may lead to a wrong result 389# 390create table t1(f1 char(1)); 391insert into t1 values ('a'),('b'),('1'); 392select f1 from t1 where f1 in ('a',1); 393select f1, case f1 when 'a' then '+' when 1 then '-' end from t1; 394create index t1f1_idx on t1(f1); 395select f1 from t1 where f1 in ('a',1); 396explain select f1 from t1 where f1 in ('a',1); 397select f1 from t1 where f1 in ('a','b'); 398explain select f1 from t1 where f1 in ('a','b'); 399select f1 from t1 where f1 in (2,1); 400explain select f1 from t1 where f1 in (2,1); 401create table t2(f2 int, index t2f2(f2)); 402insert into t2 values(0),(1),(2); 403select f2 from t2 where f2 in ('a',2); 404explain select f2 from t2 where f2 in ('a',2); 405select f2 from t2 where f2 in ('a','b'); 406explain select f2 from t2 where f2 in ('a','b'); 407select f2 from t2 where f2 in (1,'b'); 408explain select f2 from t2 where f2 in (1,'b'); 409drop table t1, t2; 410 411# 412# Bug #31075: crash in get_func_mm_tree 413# 414 415create table t1 (a time, key(a)); 416insert into t1 values (),(),(),(),(),(),(),(),(),(); 417select a from t1 where a not in (a,a,a) group by a; 418drop table t1; 419 420# 421# Bug #37761: IN handles NULL differently for table-subquery and value-list 422# 423 424create table t1 (id int); 425select * from t1 where NOT id in (select null union all select 1); 426select * from t1 where NOT id in (null, 1); 427drop table t1; 428 429# 430# Bug #41363: crash of mysqld on windows with aggregate in case 431# 432 433CREATE TABLE t1(c0 INTEGER, c1 INTEGER, c2 INTEGER); 434INSERT INTO t1 VALUES(1, 1, 1), (1, 1, 1); 435 436SELECT CASE AVG (c0) WHEN c1 * c2 THEN 1 END FROM t1; 437SELECT CASE c1 * c2 WHEN SUM(c0) THEN 1 WHEN AVG(c0) THEN 2 END FROM t1; 438SELECT CASE c1 WHEN c1 + 1 THEN 1 END, ABS(AVG(c0)) FROM t1; 439 440DROP TABLE t1; 441 442# 443# Bug #44399: crash with statement using TEXT columns, aggregates, GROUP BY, 444# and HAVING 445# 446 447CREATE TABLE t1(a TEXT, b INT, c INT UNSIGNED, d DECIMAL(12,2), e REAL); 448INSERT INTO t1 VALUES('iynfj', 1, 1, 1, 1); 449INSERT INTO t1 VALUES('innfj', 2, 2, 2, 2); 450SELECT SUM( DISTINCT a ) FROM t1 GROUP BY a HAVING a IN ( AVG( 1 ), 1 + a); 451SELECT SUM( DISTINCT b ) FROM t1 GROUP BY b HAVING b IN ( AVG( 1 ), 1 + b); 452SELECT SUM( DISTINCT c ) FROM t1 GROUP BY c HAVING c IN ( AVG( 1 ), 1 + c); 453SELECT SUM( DISTINCT d ) FROM t1 GROUP BY d HAVING d IN ( AVG( 1 ), 1 + d); 454SELECT SUM( DISTINCT e ) FROM t1 GROUP BY e HAVING e IN ( AVG( 1 ), 1 + e); 455SELECT SUM( DISTINCT e ) FROM t1 GROUP BY b,c,d HAVING (b,c,d) IN 456 ((AVG( 1 ), 1 + c, 1 + d), (AVG( 1 ), 2 + c, 2 + d)); 457DROP TABLE t1; 458 459--echo # 460--echo # Bug #44139: Table scan when NULL appears in IN clause 461--echo # 462 463--disable_warnings 464 465CREATE TABLE t1 ( 466 c_int INT NOT NULL, 467 c_decimal DECIMAL(5,2) NOT NULL, 468 c_float FLOAT(5, 2) NOT NULL, 469 c_bit BIT(10) NOT NULL, 470 c_date DATE NOT NULL, 471 c_datetime DATETIME NOT NULL, 472 c_timestamp TIMESTAMP NOT NULL, 473 c_time TIME NOT NULL, 474 c_year YEAR NOT NULL, 475 c_char CHAR(10) NOT NULL, 476 INDEX(c_int), INDEX(c_decimal), INDEX(c_float), INDEX(c_bit), INDEX(c_date), 477 INDEX(c_datetime), INDEX(c_timestamp), INDEX(c_time), INDEX(c_year), 478 INDEX(c_char)); 479 480INSERT IGNORE INTO t1 (c_int) VALUES (1), (2), (3), (4), (5); 481INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1; 482INSERT IGNORE INTO t1 (c_int) SELECT 0 FROM t1; 483 484--enable_warnings 485 486EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); 487EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, 1, 2, 3); 488 489EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, 2, 3); 490EXPLAIN SELECT * FROM t1 WHERE c_int IN (1, NULL, 2, NULL, 3, NULL); 491EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL); 492EXPLAIN SELECT * FROM t1 WHERE c_int IN (NULL, NULL); 493 494EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (1, 2, 3); 495EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, 1, 2, 3); 496EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL); 497EXPLAIN SELECT * FROM t1 WHERE c_decimal IN (NULL, NULL); 498 499EXPLAIN SELECT * FROM t1 WHERE c_float IN (1, 2, 3); 500EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, 1, 2, 3); 501EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL); 502EXPLAIN SELECT * FROM t1 WHERE c_float IN (NULL, NULL); 503 504EXPLAIN SELECT * FROM t1 WHERE c_bit IN (1, 2, 3); 505EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, 1, 2, 3); 506EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL); 507EXPLAIN SELECT * FROM t1 WHERE c_bit IN (NULL, NULL); 508 509EXPLAIN SELECT * FROM t1 WHERE c_date 510 IN ('2009-09-01', '2009-09-02', '2009-09-03'); 511EXPLAIN SELECT * FROM t1 WHERE c_date 512 IN (NULL, '2009-09-01', '2009-09-02', '2009-09-03'); 513EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL); 514EXPLAIN SELECT * FROM t1 WHERE c_date IN (NULL, NULL); 515 516EXPLAIN SELECT * FROM t1 WHERE c_datetime 517 IN ('2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); 518EXPLAIN SELECT * FROM t1 WHERE c_datetime 519 IN (NULL, '2009-09-01 00:00:01', '2009-09-02 00:00:01', '2009-09-03 00:00:01'); 520EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL); 521EXPLAIN SELECT * FROM t1 WHERE c_datetime IN (NULL, NULL); 522 523EXPLAIN SELECT * FROM t1 WHERE c_timestamp 524 IN ('2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); 525EXPLAIN SELECT * FROM t1 WHERE c_timestamp 526 IN (NULL, '2009-09-01 00:00:01', '2009-09-01 00:00:02', '2009-09-01 00:00:03'); 527EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL); 528EXPLAIN SELECT * FROM t1 WHERE c_timestamp IN (NULL, NULL); 529 530EXPLAIN SELECT * FROM t1 WHERE c_year IN (1, 2, 3); 531EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, 1, 2, 3); 532EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL); 533EXPLAIN SELECT * FROM t1 WHERE c_year IN (NULL, NULL); 534 535EXPLAIN SELECT * FROM t1 WHERE c_char IN ('1', '2', '3'); 536EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, '1', '2', '3'); 537EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL); 538EXPLAIN SELECT * FROM t1 WHERE c_char IN (NULL, NULL); 539 540DROP TABLE t1; 541 542--echo # 543--echo # Bug#54477: Crash on IN / CASE with NULL arguments 544--echo # 545 546CREATE TABLE t1 (a INT); 547INSERT INTO t1 VALUES (1), (2); 548 549SELECT 1 IN (NULL, a) FROM t1; 550 551SELECT a IN (a, a) FROM t1 GROUP BY a WITH ROLLUP; 552 553SELECT CASE a WHEN a THEN a END FROM t1 GROUP BY a WITH ROLLUP; 554 555DROP TABLE t1; 556 557--echo # 558--echo # Bug #11766212 59270: NOT IN (YEAR( ... ), ... ) PRODUCES MANY VALGRIND WARNINGS 559--echo # 560 561SELECT 1 IN (YEAR(FROM_UNIXTIME(NULL)) ,1); 562 563--echo # 564 565--echo # 566--echo # Bug#13012483: EXPLAIN EXTENDED, PREPARED STATEMENT, CRASH IN CHECK_SIMPLE_EQUALITY 567--echo # 568 569CREATE TABLE t1 (a INT); 570PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; 571EXECUTE s; 572 573DROP TABLE t1; 574 575--echo # End of test BUG#13012483 576 577--echo # 578--echo End of 5.1 tests 579 580# 581# lp:817966 int_column IN (string_constant) 582# 583# rather illogically, when BIGINT field is compared to a string, 584# the string is converted to an integer, not to a double. 585# When some other integer field (not BIGINT) is compared to a string, 586# or when the BIGINT is not a field, but an expression, both 587# operands are compared as doubles. The latter behavior is correct, 588# according to the manual. 589# 590create table t1 (a bigint, b int); 591insert t1 values (1,1),(2,2),(3,3); 592select * from t1 where a in ('2.1'); 593select * from t1 where b in ('2.1'); 594select * from t1 where a='2.1'; 595select * from t1 where b='2.1'; 596select * from t1 where IF(1,a,a)='2.1'; 597drop table t1; 598--echo # 599--echo # LP bug#992380 Crash when creating PS for a query with 600--echo # subquery in WHERE (see also mysql bug#13012483) 601--echo # 602CREATE TABLE t1 (a INT); 603PREPARE s FROM "SELECT 1 FROM t1 WHERE 1 < ALL (SELECT @:= (1 IN (SELECT 1 FROM t1)) FROM t1)"; 604EXECUTE s; 605 606DROP TABLE t1; 607 608--echo # 609--echo # End of 5.3 tests 610--echo # 611 612# 613# Bug#26361149 MYSQL SERVER CRASHES AT: COL IN(IFNULL(CONST, COL), NAME_CONST('NAME', NULL)) 614# 615create table t1 (a int); 616insert t1 values (1),(2),(3); 617select * from t1 where 1 in (a, name_const('a', null)); 618drop table t1; 619 620--echo # 621--echo # End of 5.5 tests 622--echo # 623 624--echo # 625--echo # MDEV-10020 InnoDB NOT IN Query Crash When One Item Is NULL 626--echo # 627CREATE TABLE t1 628( 629 a INT(11), 630 b VARCHAR(10), 631 KEY (b) 632); 633INSERT INTO t1 VALUES (1,'x'),(2,'y'),(3,'z'); 634SELECT * FROM t1 WHERE b NOT IN (NULL, '', 'A'); 635DROP TABLE t1; 636 637--echo # 638--echo # End of 10.0 tests 639--echo # 640 641--echo # 642--echo # MDEV-8755 Equal field propagation is not performed any longer for the IN list when multiple comparison types 643--echo # 644CREATE TABLE t1 (a INT); 645INSERT INTO t1 VALUES (1),(2); 646--echo # Ok to propagate equalities into the left IN argument in case of a single comparison type 647EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,3); 648--echo # Ok to propagate equalities into IN () list, even if multiple comparison types 649EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND 1 IN (1,a,'3'); 650--echo # Not Ok to propagate equalities into the left IN argument in case of multiple comparison types 651EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=1 AND a IN (1,2,'3'); 652DROP TABLE t1; 653 654--echo # 655--echo # Start of 10.3 tests 656--echo # 657 658--echo # 659--echo # MDEV-11514 IN with a mixture of TIME and DATETIME returns a wrong result 660--echo # 661 662SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32'); 663PREPARE stmt FROM "SELECT TIME'10:20:30' IN (102030,TIME'10:20:31',TIMESTAMP'2001-01-01 10:20:32')"; 664EXECUTE stmt; 665EXECUTE stmt; 666DEALLOCATE PREPARE stmt; 667 668--echo # 669--echo # MDEV-11497 Wrong result for (int_expr IN (mixture of signed and unsigned expressions)) 670--echo # 671CREATE TABLE t1 (a BIGINT, b BIGINT UNSIGNED); 672INSERT INTO t1 VALUES (-9223372036854775808,18446744073709551615); 673SELECT * FROM t1 WHERE -1 IN (a,b); 674PREPARE stmt FROM 'SELECT * FROM t1 WHERE -1 IN (a,b)'; 675EXECUTE stmt; 676EXECUTE stmt; 677DEALLOCATE PREPARE stmt; 678DROP TABLE t1; 679 680--echo # 681--echo # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime) 682--echo # 683 684# This is to make sure that TIME_FUZZY_DATE is always passed to str_to_time(), 685# so empty strings are compared as TIME'00:00:00' all around the code: 686# when using Arg_comparator (e.g. in binary comparison operators), and 687# when not using it (e.g. in IN predicate). 688 689SELECT 690 TIME'00:00:00'='' AS c1_true, 691 TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true, 692 TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false; 693 694 695--echo # 696--echo # End of 10.3 tests 697--echo # 698 699--echo # 700--echo # Start of 10.4 tests 701--echo # 702 703--echo # 704--echo # MDEV-16454 Bad results for IN with ROW 705--echo # 706SELECT (18446744073709551615,0) IN ((18446744073709551614,0),(-1,0)); 707 708SELECT '0x' IN (0); 709SELECT '0x' IN (0,1); 710SELECT ('0x',1) IN ((0,1)); 711SELECT ('0x',1) IN ((0,1),(1,1)); 712 713 714--echo # 715--echo # End of 10.4 tests 716--echo # 717