From: Date: May 2 2007 8:34am
Subject: bk commit into 5.0 tree (igor:1.2469) BUG#28188
List-Archive: http://lists.mysql.com/commits/25883
X-Bug: 28188
Message-Id: <20070502063421.68340280D56@olga.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-05-01 23:34:14-07:00, igor@stripped +3 -0
  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@stripped, 2007-05-01 23:34:12-07:00, igor@stripped +25 -0
    Added a test case for bug #28188.

  mysql-test/t/join_outer.test@stripped, 2007-05-01 23:34:12-07:00, igor@stripped +20 -0
    Added a test case for bug #28188.

  sql/sql_select.cc@stripped, 2007-05-01 23:34:12-07:00, igor@stripped +2 -3
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug28188

--- 1.515/sql/sql_select.cc	2007-05-01 23:34:21 -07:00
+++ 1.516/sql/sql_select.cc	2007-05-01 23:34:21 -07:00
@@ -10526,7 +10526,6 @@
 evaluate_join_record(JOIN *join, JOIN_TAB *join_tab,
                      int error, my_bool *report_error)
 {
-  bool not_exists_optimize= join_tab->table->reginfo.not_exists_optimize;
   bool not_used_in_distinct=join_tab->not_used_in_distinct;
   ha_rows found_records=join->found_records;
   COND *select_cond= join_tab->select_cond;
@@ -10563,6 +10562,8 @@
       first_unmatched->found= 1;
       for (JOIN_TAB *tab= first_unmatched; tab <= join_tab; tab++)
       {
+        if (tab->table->reginfo.not_exists_optimize)
+          return NESTED_LOOP_NO_MORE_ROWS;
         /* Check all predicates that has just been activated. */
         /*
           Actually all predicates non-guarded by first_unmatched->found
@@ -10608,8 +10609,6 @@
     if (found)
     {
       enum enum_nested_loop_state rc;
-      if (not_exists_optimize)
-        return NESTED_LOOP_NO_MORE_ROWS;
       /* A match from join_tab is found for the current partial join. */
       rc= (*join_tab->next_select)(join, join_tab+1, 0);
       if (rc != NESTED_LOOP_OK && rc != NESTED_LOOP_NO_MORE_ROWS)

--- 1.53/mysql-test/r/join_outer.result	2007-05-01 23:34:21 -07:00
+++ 1.54/mysql-test/r/join_outer.result	2007-05-01 23:34:21 -07:00
@@ -1214,3 +1214,28 @@
 f1	f2	f3
 bla	blah	sheep
 DROP TABLE t1,t2;
+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;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	
+1	SIMPLE	t2	ref	idx	idx	4	test.t1.id	2	Using where; Not exists
+flush status;
+SELECT t1.id, a FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.b IS NULL;
+id	a
+1	aaaaaaa
+4	ddddddd
+show status like 'Handler_read%';
+Variable_name	Value
+Handler_read_first	0
+Handler_read_key	5
+Handler_read_next	0
+Handler_read_prev	0
+Handler_read_rnd	0
+Handler_read_rnd_next	6
+DROP TABLE t1,t2;

--- 1.39/mysql-test/t/join_outer.test	2007-05-01 23:34:21 -07:00
+++ 1.40/mysql-test/t/join_outer.test	2007-05-01 23:34:21 -07:00
@@ -825,3 +825,23 @@
 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;