release/src/router/mysql/mysql-test/t/subselect3.test

   1 --disable_warnings
   2 drop table if exists t0, t1, t2, t3, t4, t5;
   3 --enable_warnings
   4
   5 #
   6 # 1. Subquery with GROUP/HAVING
   7 #
   8 create table t1 (oref int, grp int, ie int) ;
   9 insert into t1 (oref, grp, ie) values
  10  (1, 1, 1),
  11  (1, 1, 1),
  12  (1, 2, NULL),
  13
  14  (2, 1, 3),
  15
  16  (3, 1, 4),
  17  (3, 2, NULL);
  18
  19 # Ok, for
  20 #   select max(ie) from t1 where oref=PARAM group by grp
  21 # we'll have:
  22 # PARAM      subquery result
  23 #   1   ->   {(1), (NULL)}    matching + NULL
  24 #   2   ->   {(3)}            non-matching
  25 #   3   ->   {(3), (NULL)}    non-matching + NULL
  26 #   4   ->   {}               empty set
  27
  28 create table t2 (oref int, a int);
  29 insert into t2 values
  30   (1, 1),
  31   (2, 2),
  32   (3, 3),
  33   (4, NULL),
  34   (2, NULL);
  35
  36 # true, false, null, false, null
  37 select a, oref, a in (select max(ie)
  38   from t1 where oref=t2.oref group by grp) Z from t2;
  39
  40 # This must have a trigcond
  41 explain extended
  42 select a, oref, a in (select max(ie)
  43   from t1 where oref=t2.oref group by grp) Z from t2;
  44
  45 # This must not have a trigcond:
  46 explain extended
  47 select a, oref from t2
  48 where a in (select max(ie) from t1 where oref=t2.oref group by grp);
  49 select a, oref, a in (
  50   select max(ie) from t1 where oref=t2.oref group by grp union
  51   select max(ie) from t1 where oref=t2.oref group by grp
  52   ) Z from t2;
  53
  54 # Non-correlated subquery, 2 NULL evaluations
  55 create table t3 (a int);
  56 insert into t3 values (NULL), (NULL);
  57 flush status;
  58 select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
  59 show status like 'Handler_read_rnd_next';
  60 select ' ^ This must show 11' Z;
  61
  62 # This must show trigcond:
  63 explain extended select a in (select max(ie) from t1 where oref=4 group by grp) from t3;
  64
  65 drop table t1, t2, t3;
  66
  67 #
  68 #  2. Subquery handled with 'index_subquery':
  69 #
  70 create table t1 (a int, oref int, key(a));
  71 insert into t1 values
  72   (1, 1),
  73   (1, NULL),
  74   (2, 3),
  75   (2, NULL),
  76   (3, NULL);
  77
  78 create table t2 (a int, oref int);
  79 insert into t2 values (1, 1), (2,2), (NULL, 3), (NULL, 4);
  80
  81 select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
  82
  83 # The next explain shows "using index" but that is just incorrect display
  84 # (there is a bug filed about this).
  85 explain extended
  86 select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
  87
  88 flush status;
  89 select oref, a from t2 where a in (select a from t1 where oref=t2.oref);
  90 # This will only show access to t2:
  91 show status like '%Handler_read_rnd_next';
  92
  93 # Check that repeated NULL-scans are not cached (subq. is not correlated):
  94 delete from t2;
  95 insert into t2 values (NULL, 0),(NULL, 0), (NULL, 0), (NULL, 0);
  96
  97 flush status;
  98 select oref, a, a in (select a from t1 where oref=t2.oref) Z from t2;
  99 show status like '%Handler_read%';
 100 select 'No key lookups, seq reads: 29= 5 reads from t2 + 4 * 6 reads from t1.' Z;
 101
 102 drop table t1, t2;
 103
 104 #
 105 # 3. Subquery handled with 'unique_index_subquery':
 106 #
 107 create table t1 (a int, b int, primary key (a));
 108 insert into t1 values (1,1), (3,1),(100,1);
 109
 110 create table t2 (a int, b int);
 111 insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
 112
 113 select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
 114
 115 drop table t1, t2;
 116
 117 #
 118 # 4. Subquery that is a join, with ref access
 119 #
 120 create table t1 (a int, b int, key(a));
 121 insert into t1 values
 122   (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 123
 124 create table t2 like t1;
 125 insert into t2 select * from t1;
 126 update t2 set b=1;
 127
 128 create table t3 (a int, oref int);
 129 insert into t3 values (1, 1), (NULL,1), (NULL,0);
 130 select a, oref,
 131        t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
 132 from t3;
 133
 134 # This must have trigcond in WHERE and HAVING:
 135 explain extended
 136 select a, oref,
 137        t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
 138 from t3;
 139
 140 drop table t1, t2, t3;
 141
 142
 143 #
 144 # BUG#24085: Wrong query result for "NULL IN (SELECT ... UNION SELECT ...)"
 145 #
 146
 147 # case 1: NULL IN (SELECT not_null_val FROM ...) w/o HAVING/GROUP-BY/etc
 148 create table t1 (a int NOT NULL, b int NOT NULL, key(a));
 149 insert into t1 values
 150   (0,0),(1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7),(8,8),(9,9);
 151
 152 create table t2 like t1;
 153 insert into t2 select * from t1;
 154 update t2 set b=1;
 155
 156 create table t3 (a int, oref int);
 157 insert into t3 values (1, 1), (NULL,1), (NULL,0);
 158 select a, oref,
 159        t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
 160 from t3;
 161
 162 --echo This must show a trig_cond:
 163 explain extended
 164 select a, oref,
 165        t3.a in (select t1.a from t1, t2 where t1.b=t2.a and t2.b=t3.oref) Z
 166 from t3;
 167 drop table t1,t2,t3;
 168
 169
 170 # case 2: NULL IN (SELECT not_null_val FROM) where SELECT has GROUP BY
 171 create table t1 (oref int, grp int);
 172 insert into t1 (oref, grp) values
 173  (1, 1),
 174  (1, 1);
 175
 176 # Ok, for
 177 #   select count(*) from t1 group by grp having grp=PARAM
 178 # we'll have:
 179 #  PARAM    subuqery result
 180 #    1  ->    {(2)}
 181 #    2  ->    {} - empty set
 182 create table t2 (oref int, a int);
 183 insert into t2 values
 184   (1, NULL),
 185   (2, NULL);
 186
 187 select a, oref,
 188        a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
 189
 190 --echo This must show a trig_cond:
 191 explain extended
 192 select a, oref,
 193        a in (select count(*) from t1 group by grp having grp=t2.oref) Z from t2;
 194
 195 drop table t1, t2;
 196
 197 create table t1 (a int, b int, primary key (a));
 198 insert into t1 values (1,1), (3,1),(100,1);
 199 create table t2 (a int, b int);
 200 insert into t2 values (1,1),(2,1),(NULL,1),(NULL,0);
 201
 202 select a,b, a in (select a from t1 where t1.b = t2.b union select a from
 203 t1 where t1.b = t2.b) Z from t2 ;
 204 select a,b, a in (select a from t1 where t1.b = t2.b) Z from t2 ;
 205 drop table t1, t2;
 206
 207
 208 #
 209 # BUG#24127: Incorrect results of row-based subqueries with NULLs on the left side.
 210 #
 211 create table t3 (a int);
 212 insert into t3 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 213 create table t2 (a int, b int, oref int);
 214 insert into t2 values (NULL,1, 100), (NULL,2, 100);
 215
 216 create table t1 (a int, b int, c int, key(a,b));
 217 insert into t1 select 2*A, 2*A, 100 from t3;
 218
 219 # First test index subquery engine
 220 explain extended select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
 221 select a,b, oref, (a,b) in (select a,b from t1 where c=t2.oref) Z from t2;
 222
 223 # Then check that we do turn off 'ref' scans in the subquery
 224 create table t4 (x int);
 225 insert into t4 select A.a + 10*B.a from t1 A, t1 B;
 226 explain extended
 227   select a,b, oref,
 228          (a,b) in (select a,b from t1,t4 where c=t2.oref) Z
 229   from t2;
 230 select a,b, oref,
 231        (a,b) in (select a,b from t1,t4 where c=t2.oref) Z
 232 from t2;
 233
 234 drop table t1,t2,t3,t4;
 235
 236 # More tests for tricky multi-column cases, where some of pushed-down
 237 # equalities are used for index lookups and some arent.
 238 create table t1 (oref char(4), grp int, ie1 int, ie2 int);
 239 insert into t1 (oref, grp, ie1, ie2) values
 240  ('aa', 10, 2, 1),
 241  ('aa', 10, 1, 1),
 242  ('aa', 20, 2, 1),
 243  ('bb', 10, 3, 1),
 244  ('cc', 10, 4, 2),
 245  ('cc', 20, 3, 2),
 246
 247  ('ee', 10, 2, 1),
 248  ('ee', 10, 1, 2),
 249
 250  ('ff', 20, 2, 2),
 251  ('ff', 20, 1, 2);
 252 create table t2 (oref char(4), a int, b int);
 253 insert into t2 values
 254   ('ee', NULL, 1),
 255   ('bb', 2, 1),
 256   ('ff', 2, 2),
 257   ('cc', 3, NULL),
 258   ('bb', NULL, NULL),
 259   ('aa', 1, 1),
 260   ('dd', 1, NULL);
 261 alter table t1 add index idx(ie1,ie2);
 262
 263 # cc 3 NULL NULL
 264 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=3 and b is null ;
 265 insert into t2 values ('new1', 10,10);
 266 insert into t1 values ('new1', 1234, 10, NULL);
 267 # new1, 10, 10, NULL,
 268 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
 269 explain extended
 270 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2 where a=10 and b=10;
 271 drop table t1, t2;
 272
 273 # Now test different column types:
 274 create table t1 (oref char(4), grp int, ie int);
 275 insert into t1 (oref, grp, ie) values
 276  ('aa', 10, 2),
 277  ('aa', 10, 1),
 278  ('aa', 20, NULL),
 279
 280  ('bb', 10, 3),
 281
 282  ('cc', 10, 4),
 283  ('cc', 20, NULL),
 284
 285  ('ee', 10, NULL),
 286  ('ee', 10, NULL),
 287
 288  ('ff', 20, 2),
 289  ('ff', 20, 1);
 290
 291 create table t2 (oref char(4), a int);
 292 insert into t2 values
 293   ('ee', NULL),
 294   ('bb', 2),
 295   ('ff', 2),
 296   ('cc', 3),
 297   ('aa', 1),
 298   ('dd', NULL),
 299   ('bb', NULL);
 300
 301 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 302
 303 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 304
 305 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 306
 307
 308 select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
 309
 310 select oref, a from t2 where
 311   a in (select min(ie) from t1 where oref=t2.oref group by grp);
 312
 313 select oref, a from t2 where
 314   a not in (select min(ie) from t1 where oref=t2.oref group by grp);
 315
 316 #
 317 update t1 set ie=3 where oref='ff' and ie=1;
 318
 319 select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
 320 grp) Z from t2;
 321
 322
 323 select oref, a from t2 where a in (select min(ie) from t1 where
 324 oref=t2.oref group by grp);
 325
 326 select oref, a from t2 where a not in (select min(ie) from t1 where
 327 oref=t2.oref group by grp);
 328
 329 select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by
 330 grp having min(ie) > 1) Z from t2;
 331
 332 select oref, a from t2 where a in (select min(ie) from t1 where
 333 oref=t2.oref group by grp having min(ie) > 1);
 334
 335 select oref, a from t2 where a not in (select min(ie) from t1 where
 336 oref=t2.oref group by grp having min(ie) > 1);
 337
 338 #
 339 alter table t1 add index idx(ie);
 340
 341 explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 342
 343 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 344
 345 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 346
 347 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 348
 349
 350 alter table t1 drop index idx;
 351 alter table t1 add index idx(oref,ie);
 352
 353 explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 354
 355 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 356
 357 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 358
 359 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 360
 361 explain
 362 select oref, a,
 363        a in (select min(ie) from t1 where oref=t2.oref
 364              group by grp having min(ie) > 1) Z
 365 from t2;
 366
 367 select oref, a,
 368        a in (select min(ie) from t1 where oref=t2.oref
 369              group by grp having min(ie) > 1) Z
 370 from t2;
 371
 372 select oref, a from t2 where a in (select min(ie) from t1 where oref=t2.oref
 373                                    group by grp having min(ie) > 1);
 374
 375 select oref, a from t2 where a not in (select min(ie) from t1 where oref=t2.oref
 376                                        group by grp having min(ie) > 1);
 377
 378 drop table t1,t2;
 379
 380 create table t1 (oref char(4), grp int, ie1 int, ie2 int);
 381 insert into t1 (oref, grp, ie1, ie2) values
 382  ('aa', 10, 2, 1),
 383  ('aa', 10, 1, 1),
 384  ('aa', 20, 2, 1),
 385
 386  ('bb', 10, 3, 1),
 387
 388  ('cc', 10, 4, 2),
 389  ('cc', 20, 3, 2),
 390
 391  ('ee', 10, 2, 1),
 392  ('ee', 10, 1, 2),
 393
 394  ('ff', 20, 2, 2),
 395  ('ff', 20, 1, 2);
 396
 397 create table t2 (oref char(4), a int, b int);
 398 insert into t2 values
 399   ('ee', NULL, 1),
 400   ('bb', 2, 1),
 401   ('ff', 2, 2),
 402   ('cc', 3, NULL),
 403   ('bb', NULL, NULL),
 404   ('aa', 1, 1),
 405   ('dd', 1, NULL);
 406
 407 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
 408
 409 select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
 410
 411 select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
 412
 413 select oref, a, b,
 414              (a,b) in (select min(ie1),max(ie2) from t1
 415                        where oref=t2.oref group by grp) Z
 416 from t2;
 417
 418 select oref, a, b from t2 where
 419   (a,b) in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
 420
 421 select oref, a, b from t2 where
 422   (a,b) not in (select min(ie1), max(ie2) from t1 where oref=t2.oref group by grp);
 423
 424 alter table t1 add index idx(ie1,ie2);
 425
 426 explain select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
 427
 428 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
 429
 430 select oref, a, b from t2 where (a,b) in (select ie1,ie2 from t1 where oref=t2.oref);
 431
 432 select oref, a, b from t2 where (a,b) not in (select ie1,ie2 from t1 where oref=t2.oref);
 433
 434 explain extended
 435 select oref, a, b, (a,b) in (select ie1,ie2 from t1 where oref=t2.oref) Z from t2;
 436
 437 drop table t1,t2;
 438
 439 create table t1 (oref char(4), grp int, ie int primary key);
 440 insert into t1 (oref, grp, ie) values
 441  ('aa', 10, 2),
 442  ('aa', 10, 1),
 443
 444  ('bb', 10, 3),
 445
 446  ('cc', 10, 4),
 447  ('cc', 20, 5),
 448  ('cc', 10, 6);
 449
 450 create table t2 (oref char(4), a int);
 451 insert into t2 values
 452   ('ee', NULL),
 453   ('bb', 2),
 454   ('cc', 5),
 455   ('cc', 2),
 456   ('cc', NULL),
 457   ('aa', 1),
 458   ('bb', NULL);
 459
 460 explain select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 461
 462 select oref, a, a in (select ie from t1 where oref=t2.oref) Z from t2;
 463
 464 select oref, a from t2 where a in (select ie from t1 where oref=t2.oref);
 465
 466 select oref, a from t2 where a not in (select ie from t1 where oref=t2.oref);
 467
 468 explain
 469 select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
 470
 471 select oref, a, a in (select min(ie) from t1 where oref=t2.oref group by grp) Z from t2;
 472
 473 drop table t1,t2;
 474
 475 #
 476 # BUG#24420: row-based IN suqueries with aggregation when the left operand
 477 #            of the subquery predicate may contain NULL values
 478 #
 479
 480 create table t1 (a int, b int);
 481 insert into t1 values (0,0), (2,2), (3,3);
 482 create table t2 (a int, b int);
 483 insert into t2 values (1,1), (3,3);
 484
 485 select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
 486
 487 insert into t2 values (NULL,4);
 488 select a, b, (a,b) in (select a, min(b) from t2 group by a) Z from t1;
 489
 490 drop table t1,t2;
 491
 492 #
 493 # Bug #24484: Aggregate function used in column list subquery gives erroneous
 494 # error
 495 #
 496 CREATE TABLE t1 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
 497 INSERT INTO t1 VALUES (1,1,'a'), (1,2,'b'), (1,3,'c'), (1,4,'d'), (1,5,'e'),
 498   (2,1,'f'), (2,2,'g'), (2,3,'h'), (3,4,'i'),(3,3,'j'), (3,2,'k'), (3,1,'l'),
 499   (1,9,'m');
 500 CREATE TABLE t2 (a int, b INT, c CHAR(10) NOT NULL, PRIMARY KEY (a, b));
 501 INSERT INTO t2 SELECT * FROM t1;
 502
 503 # Gives error, but should work since it is (a, b) is the PK so only one
 504 # given match possible
 505 SELECT a, MAX(b), (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b))
 506   as test FROM t1 GROUP BY a;
 507 SELECT * FROM t1 GROUP by t1.a
 508   HAVING (MAX(t1.b) > (SELECT MAX(t2.b) FROM t2 WHERE t2.c < t1.c
 509                                                 HAVING MAX(t2.b+t1.a) < 10));
 510
 511 SELECT a,b,c FROM t1 WHERE b in (9,3,4) ORDER BY b,c;
 512
 513 SELECT a, MAX(b),
 514  (SELECT COUNT(DISTINCT t.c) FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b)
 515   LIMIT 1)
 516   as cnt,
 517  (SELECT t.b FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
 518   as t_b,
 519  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1)
 520   as t_b,
 521  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) ORDER BY t.c LIMIT 1)
 522   as t_b
 523  FROM t1 GROUP BY a;
 524
 525 SELECT a, MAX(b),
 526  (SELECT t.c FROM t1 AS t WHERE t1.a=t.a AND t.b=MAX(t1.b) LIMIT 1) as test
 527  FROM t1 GROUP BY a;
 528
 529
 530 DROP TABLE t1, t2;
 531
 532
 533 #
 534 # Bug #27870: crash of an equijoin query with WHERE condition containing
 535 #             a subquery predicate of the form <join attr> NOT IN (SELECT ...)
 536 #
 537
 538 CREATE TABLE t1 (a int);
 539 CREATE TABLE t2 (b int, PRIMARY KEY(b));
 540 INSERT INTO t1 VALUES (1), (NULL), (4);
 541 INSERT INTO t2 VALUES (3), (1),(2), (5), (4), (7), (6);
 542
 543 EXPLAIN EXTENDED
 544 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 545 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1));
 546 SELECT a FROM t1, t2 WHERE a=b AND (b NOT IN (SELECT a FROM t1 WHERE a > 4));
 547
 548 DROP TABLE t1,t2;
 549
 550 #
 551 # Bug #28375: crash for NOT IN subquery predicate when left operand becomes NULL
 552 #
 553
 554 CREATE TABLE t1 (id int);
 555 CREATE TABLE t2 (id int PRIMARY KEY);
 556 CREATE TABLE t3 (id int PRIMARY KEY, name varchar(10));
 557 INSERT INTO t1 VALUES (2), (NULL), (3), (1);
 558 INSERT INTO t2 VALUES (234), (345), (457);
 559 INSERT INTO t3 VALUES (222,'bbb'), (333,'ccc'), (111,'aaa');
 560
 561 EXPLAIN
 562 SELECT * FROM t1
 563   WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
 564                         WHERE t3.name='xxx' AND t2.id=t3.id);
 565 SELECT * FROM t1
 566   WHERE t1.id NOT IN (SELECT t2.id FROM t2,t3
 567                         WHERE t3.name='xxx' AND t2.id=t3.id);
 568
 569 SELECT (t1.id IN (SELECT t2.id FROM t2,t3
 570                     WHERE t3.name='xxx' AND t2.id=t3.id)) AS x
 571   FROM t1;
 572
 573 DROP TABLE t1,t2,t3;
 574
 575 #
 576 # Bug #22855: Optimizer doesn't rewrite NOT IN subselects to a correlated
 577 # subquery
 578 #
 579 CREATE TABLE t1 (a INT NOT NULL);
 580 INSERT INTO t1 VALUES (1),(-1), (65),(66);
 581
 582 CREATE TABLE t2 (a INT UNSIGNED NOT NULL PRIMARY KEY);
 583 INSERT INTO t2 VALUES (65),(66);
 584
 585 SELECT a FROM t1 WHERE a NOT IN (65,66);
 586 SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
 587 EXPLAIN SELECT a FROM t1 WHERE a NOT IN (SELECT a FROM t2);
 588
 589 DROP TABLE t1, t2;
 590
 591 #
 592 # Bug #34763: item_subselect.cc:1235:Item_in_subselect::row_value_transformer:
 593 #             Assertion failed, unexpected error message:
 594 #             ERROR 1247 (42S22): Reference '<list ref>' not supported (forward
 595 #             reference in item list)
 596 #
 597 CREATE TABLE t1 (a INT);
 598 INSERT INTO t1 VALUES(1);
 599
 600 CREATE TABLE t2 (placeholder CHAR(11));
 601 INSERT INTO t2 VALUES("placeholder");
 602
 603 SELECT ROW(1, 2) IN (SELECT t1.a, 2)         FROM t1 GROUP BY t1.a;
 604 SELECT ROW(1, 2) IN (SELECT t1.a, 2 FROM t2) FROM t1 GROUP BY t1.a;
 605
 606 DROP TABLE t1, t2;
 607
 608 #
 609 # Bug #36005: crash in subselect with single row
 610 #             (subselect_single_select_engine::exec)
 611 #
 612
 613 CREATE TABLE t1 (a INT);
 614 INSERT INTO t1 VALUES (1),(2),(3);
 615 CREATE TABLE t2 SELECT * FROM t1;
 616
 617 SELECT 1 FROM t1 WHERE t1.a NOT IN (SELECT 1 FROM t1, t2 WHERE 0);
 618
 619 DROP TABLE t1, t2;
 620
 621 #
 622 # Bug #37894: Assertion in init_read_record_seq in handler.h line 1444
 623 #
 624
 625 CREATE TABLE t1 (
 626   pk INT PRIMARY KEY,
 627   int_key INT,
 628   varchar_key VARCHAR(5) UNIQUE,
 629   varchar_nokey VARCHAR(5)
 630 );
 631 INSERT INTO t1 VALUES (9, 7,NULL,NULL), (10,8,'p' ,'p');
 632
 633 SELECT varchar_nokey
 634 FROM t1
 635 WHERE NULL NOT IN (
 636  SELECT INNR.pk FROM t1 AS INNR2
 637    LEFT JOIN t1 AS INNR ON ( INNR2.int_key = INNR.int_key )
 638    WHERE INNR.varchar_key > 'n{'
 639 );
 640
 641 DROP TABLE t1;
 642
 643 #
 644 # Bug #39069: <row constructor> IN <table-subquery> seriously messed up
 645 #
 646
 647 CREATE TABLE t1 (a INT);
 648 INSERT INTO t1 VALUES (1), (2), (11);
 649
 650 --echo # 2nd and 3rd columns should be same
 651 SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1 GROUP BY t1.a;
 652 SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1 GROUP BY t1.a;
 653 SELECT a, ROW(11, 12) = (SELECT a, 22), ROW(11, 12) IN (SELECT a, 22) FROM t1;
 654 SELECT a, ROW(11, 12) = (SELECT a, 12), ROW(11, 12) IN (SELECT a, 12) FROM t1;
 655
 656 # The x alias is used below to workaround bug #40674.
 657 # Regression tests for sum function on outer column in subselect from dual:
 658 SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 22), ROW(11, 12) IN (SELECT MAX(x), 22) FROM t1;
 659 --echo # 2nd and 3rd columns should be same for x == 11 only
 660 SELECT a AS x, ROW(11, 12) = (SELECT MAX(x), 12), ROW(11, 12) IN (SELECT MAX(x), 12) FROM t1;
 661
 662 DROP TABLE t1;
 663
 664 --echo # both columns should be same
 665 SELECT ROW(1,2) = (SELECT NULL, NULL), ROW(1,2) IN (SELECT NULL, NULL);
 666 SELECT ROW(1,2) = (SELECT   1,  NULL), ROW(1,2) IN (SELECT    1, NULL);
 667 SELECT ROW(1,2) = (SELECT NULL,    2), ROW(1,2) IN (SELECT NULL,    2);
 668 SELECT ROW(1,2) = (SELECT NULL,    1), ROW(1,2) IN (SELECT NULL,    1);
 669 SELECT ROW(1,2) = (SELECT    1,    1), ROW(1,2) IN (SELECT    1,    1);
 670 SELECT ROW(1,2) = (SELECT    1,    2), ROW(1,2) IN (SELECT    1,    2);
 671
 672 #
 673 # Bug #37362      Crash in do_field_eq
 674 #
 675 CREATE TABLE t1 (a INT, b INT, c INT);
 676 INSERT INTO t1 VALUES (1,1,1), (1,1,1);
 677
 678 --error 1054
 679 EXPLAIN EXTENDED
 680   SELECT c FROM
 681     ( SELECT
 682       (SELECT COUNT(a) FROM
 683         (SELECT COUNT(b) FROM t1) AS x GROUP BY c
 684       ) FROM t1 GROUP BY b
 685     ) AS y;
 686 SHOW WARNINGS;
 687
 688 DROP TABLE t1;
 689
 690
 691 --echo End of 5.0 tests
 692
 693 #
 694 # BUG#36135 "void Diagnostics_area::set_eof_status(THD*): Assertion `!is_set()' failed."
 695 #
 696 create table t0 (a int);
 697 insert into t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);
 698
 699 create table t1 (
 700   a int(11) default null,
 701   b int(11) default null,
 702   key (a)
 703 );
 704 # produce numbers 0..999
 705 insert into t1 select A.a+10*(B.a+10*C.a),A.a+10*(B.a+10*C.a) from t0 A, t0 B, t0 C;
 706
 707 create table t2 (a int(11) default null);
 708 insert into t2 values (0),(1);
 709
 710 create table t3 (a int(11) default null);
 711 insert into t3 values (0),(1);
 712
 713 create table t4 (a int(11) default null);
 714 insert into t4 values (0),(1);
 715
 716 create table t5 (a int(11) default null);
 717 insert into t5 values (0),(1),(0),(1);
 718
 719 # this must not fail assertion
 720 --error 1242
 721 select * from t2, t3
 722 where
 723     t2.a < 10 and
 724     t3.a+1 = 2 and
 725     t3.a in (select t1.b from t1
 726                  where t1.a+1=t1.a+1 and
 727                        t1.a < (select t4.a+10
 728                                   from t4, t5 limit 2));
 729
 730 drop table t0, t1, t2, t3, t4, t5;
 731
 732 --echo #
 733 --echo # BUG#48177 - SELECTs with NOT IN subqueries containing NULL
 734 --echo #             values return too many records
 735 --echo #
 736
 737 CREATE TABLE t1 (
 738   i1 int DEFAULT NULL,
 739   i2 int DEFAULT NULL
 740 ) ;
 741
 742 INSERT INTO t1 VALUES (1,    NULL);
 743 INSERT INTO t1 VALUES (2,    3);
 744 INSERT INTO t1 VALUES (4,    NULL);
 745 INSERT INTO t1 VALUES (4,    0);
 746 INSERT INTO t1 VALUES (NULL, NULL);
 747
 748 CREATE TABLE t2 (
 749   i1 int DEFAULT NULL,
 750   i2 int DEFAULT NULL
 751 ) ;
 752
 753 INSERT INTO t2 VALUES (4, NULL);
 754 INSERT INTO t2 VALUES (5, 0);
 755
 756 --echo
 757 --echo Data in t1
 758 SELECT i1, i2 FROM t1;
 759
 760 --echo
 761 --echo Data in subquery (should be filtered out)
 762 SELECT i1, i2 FROM t2 ORDER BY i1;
 763
 764 FLUSH STATUS;
 765
 766 --echo
 767 SELECT i1, i2
 768 FROM t1
 769 WHERE (i1, i2)
 770       NOT IN (SELECT i1, i2 FROM t2);
 771
 772 --echo
 773 --echo # Check that the subquery only has to be evaluated once
 774 --echo # for all-NULL values even though there are two (NULL,NULL) records
 775 --echo # Baseline:
 776 SHOW STATUS LIKE '%Handler_read_rnd_next';
 777
 778 --echo
 779 INSERT INTO t1 VALUES (NULL, NULL);
 780 FLUSH STATUS;
 781
 782 --echo
 783 SELECT i1, i2
 784 FROM t1
 785 WHERE (i1, i2)
 786       NOT IN (SELECT i1, i2 FROM t2);
 787
 788 --echo
 789 --echo # Handler_read_rnd_next should be one more than baseline
 790 --echo # (read record from t1, but do not read from t2)
 791 SHOW STATUS LIKE '%Handler_read_rnd_next';
 792
 793
 794 DROP TABLE t1,t2;
 795
 796 --echo End of 5.1 tests