5408
5432
DROP TABLE t1,t2a,t2b,t2c;
5409
5433
# End BUG#52329
5435
# Bug#45174: Incorrectly applied equality propagation caused wrong
5436
# result on a query with a materialized semi-join.
5439
varchar_nokey varchar(1) NOT NULL
5441
INSERT INTO t1 VALUES
5442
('v'), ('u'), ('n'), ('l'), ('h'), ('u'), ('n'), ('j'), ('k'),
5443
('e'), ('i'), ('u'), ('n'), ('b'), ('x'), (''), ('q'), ('u');
5446
varchar_key varchar(1) NOT NULL,
5447
varchar_nokey varchar(1) NOT NULL,
5449
KEY varchar_key(varchar_key)
5451
INSERT INTO t2 VALUES
5452
(11,'m','m'), (12,'j','j'), (13,'z','z'), (14,'a','a'), (15,'',''),
5453
(16,'e','e'), (17,'t','t'), (19,'b','b'), (20,'w','w'), (21,'m','m'),
5454
(23,'',''), (24,'w','w'), (26,'e','e'), (27,'e','e'), (28,'p','p');
5455
SELECT varchar_nokey
5457
WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
5459
WHERE varchar_nokey < 'n' XOR pk);
5461
explain SELECT varchar_nokey
5463
WHERE (varchar_nokey, varchar_nokey) IN (SELECT varchar_key, varchar_nokey
5465
WHERE varchar_nokey < 'n' XOR pk);
5466
id select_type table type possible_keys key key_len ref rows Extra
5467
1 PRIMARY t1 ALL NULL NULL NULL NULL 18 Using where
5468
2 SUBQUERY t2 ALL NULL NULL NULL NULL 15 Using where
5470
# End of the test for bug#45174.
5472
# Bug#50019: Wrong result for IN-query with materialization
5474
CREATE TABLE t1(i INT);
5475
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
5476
CREATE TABLE t2(i INT);
5477
INSERT INTO t2 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
5478
CREATE TABLE t3(i INT);
5479
INSERT INTO t3 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
5480
SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
5482
WHERE t2.i + t3.i = 5);
5488
explain SELECT * FROM t1 WHERE t1.i IN (SELECT t2.i
5490
WHERE t2.i + t3.i = 5);
5491
id select_type table type possible_keys key key_len ref rows Extra
5492
1 PRIMARY t1 ALL NULL NULL NULL NULL 10 Using where
5493
2 SUBQUERY t2 ALL NULL NULL NULL NULL 10
5494
2 SUBQUERY t3 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (BNL, regular buffers)
5495
DROP TABLE t1,t2,t3;
5496
# End of the test for bug#50019.
5498
# Bug#52068: Optimizer generates invalid semijoin materialization plan
5500
CREATE TABLE ot1(a INTEGER);
5501
INSERT INTO ot1 VALUES(5), (8);
5502
CREATE TABLE it2(a INTEGER);
5503
INSERT INTO it2 VALUES(9), (5), (1), (8);
5504
CREATE TABLE it3(a INTEGER);
5505
INSERT INTO it3 VALUES(7), (1), (0), (5), (1), (4);
5506
CREATE TABLE ot4(a INTEGER);
5507
INSERT INTO ot4 VALUES(1), (3), (5), (7), (9), (7), (3), (1);
5508
SELECT * FROM ot1,ot4
5509
WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
5522
explain SELECT * FROM ot1,ot4
5523
WHERE (ot1.a,ot4.a) IN (SELECT it2.a,it3.a
5525
id select_type table type possible_keys key key_len ref rows Extra
5526
1 PRIMARY ot1 ALL NULL NULL NULL NULL 2
5527
1 PRIMARY ot4 ALL NULL NULL NULL NULL 8 Using where; Using join buffer (BNL, regular buffers)
5528
2 SUBQUERY it2 ALL NULL NULL NULL NULL 4
5529
2 SUBQUERY it3 ALL NULL NULL NULL NULL 6 Using join buffer (BNL, regular buffers)
5530
DROP TABLE IF EXISTS ot1, ot4, it2, it3;
5531
# End of the test for bug#52068.
5410
5532
set optimizer_switch=default;