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 11CREATE TABLE t1 (id int(11) NOT NULL PRIMARY KEY, name varchar(20), 12 INDEX (name)) ENGINE=InnoDB; 13CREATE TABLE t2 (id int(11) NOT NULL PRIMARY KEY, fkey int(11), 14 FOREIGN KEY (fkey) REFERENCES t2(id)) ENGINE=InnoDB; 15INSERT INTO t1 VALUES (1,'A1'),(2,'A2'),(3,'B'); 16INSERT INTO t2 VALUES (1,1),(2,2),(3,2),(4,3),(5,3); 17 18-- disable_result_log 19ANALYZE TABLE t1; 20ANALYZE TABLE t2; 21-- enable_result_log 22 23EXPLAIN 24SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 25 WHERE t1.name LIKE 'A%'; 26 27EXPLAIN 28SELECT COUNT(*) FROM t2 LEFT JOIN t1 ON t2.fkey = t1.id 29 WHERE t1.name LIKE 'A%' OR FALSE; 30 31DROP TABLE t1,t2; 32 33--echo # 34--echo # BUG#58456: Assertion 0 in QUICK_INDEX_MERGE_SELECT::need_sorted_output 35--echo # in opt_range.h 36--echo # 37 38CREATE TABLE t1 ( 39 col_int INT, 40 col_int_key INT, 41 pk INT NOT NULL, 42 PRIMARY KEY (pk), 43 KEY col_int_key (col_int_key) 44) ENGINE=InnoDB; 45 46INSERT INTO t1 VALUES (NULL,1,1), (6,2,2), (5,3,3), (NULL,4,4); 47INSERT INTO t1 VALUES (1,NULL,6), (8,5,7), (NULL,8,8), (8,NULL,5); 48 49-- disable_result_log 50ANALYZE TABLE t1; 51-- enable_result_log 52 53CREATE TABLE t2 ( 54 pk INT PRIMARY KEY 55) ENGINE=InnoDB; 56 57let $query= 58SELECT t1.pk 59FROM t2 LEFT JOIN t1 ON t2.pk = t1.col_int 60WHERE t1.col_int_key BETWEEN 5 AND 6 61 AND t1.pk IS NULL OR t1.pk IN (5) 62ORDER BY pk; 63 64--echo 65--eval EXPLAIN $query 66--echo 67--eval $query 68--echo 69 70DROP TABLE t1,t2; 71 72--echo # End BUG#58456 73 74--echo # 75--echo # Bug #20939184:INNODB: UNLOCK ROW COULD NOT FIND A 2 MODE LOCK ON THE 76--echo # RECORD 77--echo # 78CREATE TABLE t1 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1,c2) ); 79CREATE TABLE t2 (c1 INT, c2 INT, c3 INT, PRIMARY KEY (c1), KEY (c2)); 80INSERT INTO t1 VALUES (1,2,3),(2,3,4),(3,4,5); 81INSERT INTO t2 SELECT * FROM t1; 82SET TRANSACTION ISOLATION LEVEL READ COMMITTED; 83START TRANSACTION; 84#unlocks rows in table t2 where c1 = 1 85SELECT * FROM t1 LEFT JOIN t2 ON t1.c2=t2.c2 AND t2.c1=1 FOR UPDATE; 86UPDATE t1 LEFT JOIN t2 ON t1.c1 = t2.c2 AND t2.c1 = 3 SET t1.c3 = RAND()*10; 87COMMIT; 88SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 89DROP TABLE t1,t2; 90