|
1
by brian
clean slate |
1 |
#
|
2 |
# test of left outer join for tests that depends on innodb
|
|
3 |
#
|
|
4 |
||
5 |
--source include/have_innodb.inc |
|
6 |
||
7 |
#
|
|
8 |
# Test for bug #17164: ORed FALSE blocked conversion of outer join into join
|
|
9 |
#
|
|
10 |
||
11 |
CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), |
|
12 |
INDEX (name)) ENGINE=InnoDB; |
|
13 |
CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), |
|
14 |
FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; |
|
15 |
INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); |
|
16 |
INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); |
|
17 |
||
18 |
EXPLAIN
|
|
19 |
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id |
|
20 |
WHERE t1.name LIKE 'A%'; |
|
21 |
||
22 |
EXPLAIN
|
|
23 |
SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id |
|
24 |
WHERE t1.name LIKE 'A%' OR FALSE; |
|
25 |
||
26 |
DROP TABLE t1,t2; |