3103
3103
t3.c IN ('bb','ee');
3105
3105
DROP TABLE t1,t2,t3;
3108
# WL3527: Extend IGNORE INDEX so places where index is ignored can
3111
CREATE TABLE t1 (a INT, b INT, KEY (a)); INSERT INTO t1 VALUES (1,1),(2,2);
3112
EXPLAIN SELECT 1 FROM t1 WHERE a = 1;
3113
EXPLAIN SELECT 1 FROM t1 IGNORE INDEX FOR JOIN (a) WHERE a = 1;
3114
EXPLAIN SELECT 1 FROM t1 USE INDEX FOR JOIN (a) WHERE a = 1;
3115
EXPLAIN SELECT 1 FROM t1 FORCE INDEX FOR JOIN (a) WHERE a = 1;
3108
3119
# Bug#25172: Not checked buffer size leads to a server crash
3300
3311
DROP TABLE t1,t2;
3315
# Bug #26963: join with predicates that contain fields from equalities evaluated
3316
# to constants after constant table substitution
3320
access_id int NOT NULL default '0',
3321
name varchar(20) default NULL,
3322
rank int NOT NULL default '0',
3327
faq_group_id int NOT NULL default '0',
3328
faq_id int NOT NULL default '0',
3329
access_id int default NULL,
3330
UNIQUE KEY idx1 (faq_id),
3331
KEY idx2 (faq_group_id,faq_id)
3334
INSERT INTO t1 VALUES
3335
(1,'Everyone',2),(2,'Help',3),(3,'Technical Support',1),(4,'Chat User',4);
3336
INSERT INTO t2 VALUES
3337
(261,265,1),(490,494,1);
3341
FROM t1 INNER JOIN t2 IGNORE INDEX (idx1)
3342
ON (t1.access_id = t2.access_id)
3344
ON (t.faq_group_id = t2.faq_group_id AND
3345
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3347
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3350
FROM t1 INNER JOIN t2
3351
ON (t1.access_id = t2.access_id)
3353
ON (t.faq_group_id = t2.faq_group_id AND
3354
find_in_set(t.access_id, '1,4') < find_in_set(t2.access_id, '1,4'))
3356
t2.access_id IN (1,4) AND t.access_id IS NULL AND t2.faq_id in (265);
3362
# Bug #19372: Optimizer does not use index anymore when WHERE index NOT IN
3365
CREATE TABLE t1 (a INT, b INT, KEY inx (b,a));
3367
INSERT INTO t1 VALUES (1,1), (1,2), (1,3), (1,4), (1,5), (1, 6), (1,7);
3368
EXPLAIN SELECT COUNT(*) FROM t1 f1 INNER JOIN t1 f2
3369
ON ( f1.b=f2.b AND f1.a<f2.a )
3370
WHERE 1 AND f1.b NOT IN (100,2232,3343,51111);
3302
3373
--echo End of 5.0 tests