From 8e8ece72eb0530bee9b200d92ba14a143cd1dec9 Mon Sep 17 00:00:00 2001 From: unknown Date: Tue, 1 May 2007 23:34:14 -0700 Subject: Fixed bug #28188: performance degradation for outer join queries to which 'not exists' optimization is applied. In fact 'not exists' optimization did not work anymore after the patch introducing the evaluate_join_record function had been applied. Corrected the evaluate_join_record function to respect the 'not_exists' optimization. mysql-test/r/join_outer.result: Added a test case for bug #28188. mysql-test/t/join_outer.test: Added a test case for bug #28188. sql/sql_select.cc: Fixed bug #28188: performance degradation for outer join queries to which 'not exists' optimization is applied. Corrected the evaluate_join_record function to respect the 'not_exists' optimization. --- mysql-test/t/join_outer.test | 20 ++++++++++++++++++++ 1 file changed, 20 insertions(+) (limited to 'mysql-test/t/join_outer.test') diff --git a/mysql-test/t/join_outer.test b/mysql-test/t/join_outer.test index a0620e144c2..51e79a20d65 100644 --- a/mysql-test/t/join_outer.test +++ b/mysql-test/t/join_outer.test @@ -825,3 +825,23 @@ SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='bla'; SELECT * FROM t1 LEFT JOIN t2 USING(f1) WHERE f1='Bla'; DROP TABLE t1,t2; + +# +# Bug 28188: 'not exists' optimization for outer joins +# + +CREATE TABLE t1 (id int PRIMARY KEY, a varchar(8)); +CREATE TABLE t2 (id int NOT NULL, b int NOT NULL, INDEX idx(id)); +INSERT INTO t1 VALUES + (1,'aaaaaaa'), (5,'eeeeeee'), (4,'ddddddd'), (2,'bbbbbbb'), (3,'ccccccc'); +INSERT INTO t2 VALUES + (3,10), (2,20), (5,30), (3,20), (5,10), (3,40), (3,30), (2,10), (2,40); + +EXPLAIN +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; + +flush status; +SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL; +show status like 'Handler_read%'; + +DROP TABLE t1,t2; -- cgit v1.2.1