From: Date: February 15 2008 7:59pm
Subject: bk commit into 5.0 tree (holyfoot:1.2584) BUG#32942
List-Archive: http://lists.mysql.com/commits/42395
X-Bug: 32942
Message-Id: <20080215185901.3BFA32C380A5@hfmain.localdomain>

Below is the list of changes that have just been committed into a local
5.0 repository of hf. When hf 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, 2008-02-15 22:58:56+04:00, holyfoot@stripped +3 -0
  Bug #32942 now() - interval '7200' second NOT pre-calculated, causing "full table scan"
  
  Problem is not about intervals and doesn't actually cause 'full table scan'.
  We have an optimization for DISTINCT when we have
  'DISTINCT field_from_first_join_table' we don't need to read all the
  rows from the JOIN-ed table if we found one conforming row.
  It stopped working in 5.0 as we return NESTED_LOOP_OK if we came upon
  that case in the evaluate_join_record() and that doesn't break the
  recordreading loop in sub_select().
  
  Fixed by returning NESTED_LOOP_NO_MORE_ROWS in this case.

  mysql-test/r/select.result@stripped, 2008-02-15 22:58:55+04:00, holyfoot@stripped +15 -0
    Bug #32942 now() - interval '7200' second is NOT pre-calculated, causing "full table scan".
    
    test result

  mysql-test/t/select.test@stripped, 2008-02-15 22:58:55+04:00, holyfoot@stripped +18 -0
    Bug #32942 now() - interval '7200' second is NOT pre-calculated, causing "full table scan"
    
    test case

  sql/sql_select.cc@stripped, 2008-02-15 22:58:55+04:00, holyfoot@stripped +1 -1
    Bug #32942 now() - interval '7200' second NOT pre-calculated, causing "full table scan"
    
    return NESTED_LOOP_NO_MORE_ROWS when we don't need to read rows from
    this table anymore

diff -Nrup a/mysql-test/r/select.result b/mysql-test/r/select.result
--- a/mysql-test/r/select.result	2007-11-18 00:01:31 +04:00
+++ b/mysql-test/r/select.result	2008-02-15 22:58:55 +04:00
@@ -4328,4 +4328,19 @@ SELECT * FROM t1 WHERE c1 > NULL + 1;
 c1
 DROP TABLE t1;
 
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, c INT, KEY(a));
+INSERT INTO t1 VALUES (1, 1), (2, 2);
+INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
+(2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
+(3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
+(4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
+FLUSH STATUS;
+SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
+b
+1
+2
+SHOW STATUS LIKE 'Handler_read*';
+Variable_name	Value
+DROP TABLE t1, t2;
 End of 5.0 tests
diff -Nrup a/mysql-test/t/select.test b/mysql-test/t/select.test
--- a/mysql-test/t/select.test	2007-11-18 00:01:30 +04:00
+++ b/mysql-test/t/select.test	2008-02-15 22:58:55 +04:00
@@ -3672,4 +3672,22 @@ DROP TABLE t1;
 
 --echo
 
+#
+# Bug #32942 now() - interval '7200' second is NOT pre-calculated, causing "full table scan"
+#
+
+CREATE TABLE t1 (a INT, b INT);
+CREATE TABLE t2 (a INT, c INT, KEY(a));
+
+INSERT INTO t1 VALUES (1, 1), (2, 2);
+INSERT INTO t2 VALUES (1, 1), (1, 2), (1, 3), (1, 4), (1, 5),
+                      (2, 1), (2, 2), (2, 3), (2, 4), (2, 5),
+                      (3, 1), (3, 2), (3, 3), (3, 4), (3, 5),
+                      (4, 1), (4, 2), (4, 3), (4, 4), (4, 5);
+
+FLUSH STATUS;
+SELECT DISTINCT b FROM t1 LEFT JOIN t2 USING(a) WHERE c <= 3;
+SHOW STATUS LIKE 'Handler_read*';
+DROP TABLE t1, t2;
+
 --echo End of 5.0 tests
diff -Nrup a/sql/sql_select.cc b/sql/sql_select.cc
--- a/sql/sql_select.cc	2007-12-13 14:49:12 +04:00
+++ b/sql/sql_select.cc	2008-02-15 22:58:55 +04:00
@@ -10791,7 +10791,7 @@ evaluate_join_record(JOIN *join, JOIN_TA
         we found a row, as no new rows can be added to the result.
       */
       if (not_used_in_distinct && found_records != join->found_records)
-        return NESTED_LOOP_OK;
+        return NESTED_LOOP_NO_MORE_ROWS;
     }
     else
       join_tab->read_record.file->unlock_row();