List:Commits« Previous MessageNext Message »
From:Jorgen Loland Date:March 7 2012 7:17am
Subject:bzr push into mysql-trunk branch (jorgen.loland:3724 to 3725) Bug#13731380
View as plain text  
 3725 Jorgen Loland	2012-03-07
      BUG#13731380 - RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() 
                     FOR OPEN RANGE
      
      Yet another case where the range optimizer did not realize 
      that the ORed range was open, i.e. always true. The case in 
      bug the query contained predicates:
      
      "WHERE col <> 3 or col < 4"
      
      The fix is to modify key_or() to return NULL for this case
      as well. See related bug 13354910.
     @ mysql-test/include/range.inc
        Added test for BUG#13731380
     @ mysql-test/r/range_all.result
        Added test for BUG#13731380
     @ mysql-test/r/range_icp.result
        Added test for BUG#13731380
     @ mysql-test/r/range_icp_mrr.result
        Added test for BUG#13731380
     @ mysql-test/r/range_mrr.result
        Added test for BUG#13731380
     @ mysql-test/r/range_mrr_cost.result
        Added test for BUG#13731380
     @ mysql-test/r/range_none.result
        Added test for BUG#13731380
     @ sql/handler.cc
        Reintroduce ASSERT that checks that we don't do records_in_range
        for open/always true ranges
     @ sql/opt_range.cc
        Reintroduce ASSERT that checks that we don't do records_in_range
        for open/always true ranges
        Return NULL from key_or() if key1 OR key2 form an open range.
     @ unittest/gunit/opt_range-t.cc
        Removed Debug_sel_arg since it doesn't hide much. Use SEL_ARG
        instead.
        Add test for key_or() where two keys form a full range.

    modified:
      mysql-test/include/range.inc
      mysql-test/r/range_all.result
      mysql-test/r/range_icp.result
      mysql-test/r/range_icp_mrr.result
      mysql-test/r/range_mrr.result
      mysql-test/r/range_mrr_cost.result
      mysql-test/r/range_none.result
      sql/handler.cc
      sql/opt_range.cc
      unittest/gunit/opt_range-t.cc
 3724 Sunny Bains	2012-03-07
      Bug#13792503 - INNODB.INNODB_BUG60049 TEST FAILS ON TRUNK SPORADICALLY
            
      Force the purge coordinator to make one more pass at purging when
      the server state changes to shutdown.
            
      Add a truncate parameter to trx_purge() so that the purge coordinator
      thread (the caller) can decide when to truncate.
            
      rb://966 Approved by Jimmy Yang.

    modified:
      storage/innobase/include/trx0purge.h
      storage/innobase/srv/srv0srv.cc
      storage/innobase/trx/trx0purge.cc
=== modified file 'mysql-test/include/range.inc'
--- a/mysql-test/include/range.inc	2012-01-26 10:08:12 +0000
+++ b/mysql-test/include/range.inc	2012-03-07 07:16:42 +0000
@@ -1740,3 +1740,12 @@ UPDATE v3 SET f3=0, f4=4 WHERE f2=68 ORD
 SELECT * FROM v3;
 DROP TABLE t1;
 DROP VIEW v3;
+
+--echo #
+--echo # BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+--echo #
+
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+DROP TABLE t1;

=== modified file 'mysql-test/r/range_all.result'
--- a/mysql-test/r/range_all.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_all.result	2012-03-07 07:16:42 +0000
@@ -2382,4 +2382,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp.result'
--- a/mysql-test/r/range_icp.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_icp.result	2012-03-07 07:16:42 +0000
@@ -2382,4 +2382,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_icp_mrr.result'
--- a/mysql-test/r/range_icp_mrr.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_icp_mrr.result	2012-03-07 07:16:42 +0000
@@ -2382,4 +2382,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr.result'
--- a/mysql-test/r/range_mrr.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_mrr.result	2012-03-07 07:16:42 +0000
@@ -2382,4 +2382,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_mrr_cost.result'
--- a/mysql-test/r/range_mrr_cost.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_mrr_cost.result	2012-03-07 07:16:42 +0000
@@ -2382,4 +2382,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'mysql-test/r/range_none.result'
--- a/mysql-test/r/range_none.result	2012-02-29 11:17:52 +0000
+++ b/mysql-test/r/range_none.result	2012-03-07 07:16:42 +0000
@@ -2381,4 +2381,15 @@ f1	f2	f3	f4
 10	0	0	0
 DROP TABLE t1;
 DROP VIEW v3;
+#
+# BUG#13731380: RANGE OPTIMIZER CALLS RECORDS_IN_RANGE() FOR OPEN RANGE
+#
+CREATE TABLE t1 (pk INT PRIMARY KEY);
+INSERT INTO t1 VALUES (1),(3),(5);
+SELECT * FROM t1 WHERE pk <> 3 OR pk < 4;
+pk
+1
+3
+5
+DROP TABLE t1;
 set optimizer_switch=default;

=== modified file 'sql/handler.cc'
--- a/sql/handler.cc	2012-03-01 13:53:55 +0000
+++ b/sql/handler.cc	2012-03-07 07:16:42 +0000
@@ -4644,8 +4644,7 @@ handler::multi_range_read_info_const(uin
     else
     {
       DBUG_EXECUTE_IF("crash_records_in_range", DBUG_SUICIDE(););
-      // Fails - reintroduce when fixed
-      // DBUG_ASSERT(min_endp || max_endp);
+      DBUG_ASSERT(min_endp || max_endp);
       if (HA_POS_ERROR == (rows= this->records_in_range(keyno, min_endp, 
                                                         max_endp)))
       {

=== modified file 'sql/opt_range.cc'
--- a/sql/opt_range.cc	2012-02-29 06:58:34 +0000
+++ b/sql/opt_range.cc	2012-03-07 07:16:42 +0000
@@ -4825,8 +4825,7 @@ static double ror_scan_selectivity(const
                      rec_per_key[tuple_arg->part]))    // (3)
       {
         DBUG_EXECUTE_IF("crash_records_in_range", DBUG_SUICIDE(););
-        // Fails - reintroduce when fixed
-        // DBUG_ASSERT(min_range.length > 0);
+        DBUG_ASSERT(min_range.length > 0);
         records= (table->file->
                   records_in_range(scan->keynr, &min_range, &max_range));
       }
@@ -7649,11 +7648,15 @@ key_or(RANGE_OPT_PARAM *param, SEL_ARG *
         cur_key1= last;
 
         /*
-          We need the minimum endpoint of first so we can compare it
-          with the minimum endpoint of the enclosing cur_key2 range.
+          Extend last to cover the entire range of
+          [min(first.min_value,cur_key2.min_value)...last.max_value].
+          If this forms a full range (the range covers all possible
+          values) we return no SEL_ARG RB-tree.
         */
-        last->copy_min(first);
-        bool full_range= last->copy_min(cur_key2);
+        bool full_range= last->copy_min(first);
+        if (!full_range)
+          full_range= last->copy_min(cur_key2);
+
         if (!full_range)
         {
           if (last->next && cur_key2->cmp_max_to_min(last->next) >= 0)

=== modified file 'unittest/gunit/opt_range-t.cc'
--- a/unittest/gunit/opt_range-t.cc	2012-02-22 14:30:56 +0000
+++ b/unittest/gunit/opt_range-t.cc	2012-03-07 07:16:42 +0000
@@ -98,6 +98,7 @@ TEST_F(SelArgTest, AllocateImplicit)
   because of limits in google test.
  */
 const SEL_TREE *null_tree= NULL;
+const SEL_ARG  *null_arg= NULL;
 
 
 class Mock_field_long : public Field_long
@@ -158,21 +159,24 @@ public:
 };
 
 
-class Debug_sel_arg : public SEL_ARG
+static void print_selarg_ranges(String *s, SEL_ARG *sel_arg, 
+                                const KEY_PART_INFO *kpi)
 {
-public:
-  Debug_sel_arg(Field *f, const uchar *min_val, const uchar *max_val, 
-               const KEY_PART_INFO *kpi_)
-    : SEL_ARG(f, min_val, max_val), kpi(kpi_)
-  {}
-
-  void print(String *s)
+  for (SEL_ARG *cur= sel_arg->first(); 
+       cur != &null_element; 
+       cur= cur->right)
   {
-    append_range(s, kpi, min_value, max_value, min_flag | max_flag);
+    String current_range;
+    append_range(&current_range, kpi, cur->min_value, cur->max_value, 
+                 cur->min_flag | cur->max_flag);
+
+    if (s->length() > 0)
+      s->append(STRING_WITH_LEN("\n"));
+
+    s->append(current_range);
   }
-private:
-  const KEY_PART_INFO * const kpi;
-};
+}
+
 
 TEST_F(SelArgTest, SimpleCond)
 {
@@ -207,34 +211,34 @@ TEST_F(SelArgTest, SelArgOnevalue)
   uchar range_val7[field_long7.KEY_LENGTH];
   field_long7.get_key_image(range_val7, kpi.length, Field::itRAW);
 
-  Debug_sel_arg sel_arg7(&field_long7, range_val7, range_val7, &kpi);
+  SEL_ARG sel_arg7(&field_long7, range_val7, range_val7);
   String range_string;
-  sel_arg7.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg7, &kpi);
   const char expected[]= "7 <= field_name <= 7";
   EXPECT_STREQ(expected, range_string.c_ptr());
 
   sel_arg7.min_flag|= NO_MIN_RANGE;
   range_string.length(0);
-  sel_arg7.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg7, &kpi);
   const char expected2[]= "field_name <= 7";
   EXPECT_STREQ(expected2, range_string.c_ptr());
 
   sel_arg7.max_flag= NEAR_MAX;
   range_string.length(0);
-  sel_arg7.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg7, &kpi);
   const char expected3[]= "field_name < 7";
   EXPECT_STREQ(expected3, range_string.c_ptr());
 
   sel_arg7.min_flag= NEAR_MIN;
   sel_arg7.max_flag= NO_MAX_RANGE;
   range_string.length(0);
-  sel_arg7.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg7, &kpi);
   const char expected4[]= "7 < field_name";
   EXPECT_STREQ(expected4, range_string.c_ptr());
 
   sel_arg7.min_flag= 0;
   range_string.length(0);
-  sel_arg7.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg7, &kpi);
   const char expected5[]= "7 <= field_name";
   EXPECT_STREQ(expected5, range_string.c_ptr());
 }
@@ -254,42 +258,42 @@ TEST_F(SelArgTest, SelArgBetween)
   uchar range_val5[field_long5.KEY_LENGTH];
   field_long5.get_key_image(range_val5, kpi.length, Field::itRAW);
 
-  Debug_sel_arg sel_arg35(&field_long3, range_val3, range_val5, &kpi);
+  SEL_ARG sel_arg35(&field_long3, range_val3, range_val5);
 
   String range_string;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected[]= "3 <= field_name <= 5";
   EXPECT_STREQ(expected, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg35.min_flag= NEAR_MIN;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected2[]= "3 < field_name <= 5";
   EXPECT_STREQ(expected2, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg35.max_flag= NEAR_MAX;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected3[]= "3 < field_name < 5";
   EXPECT_STREQ(expected3, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg35.min_flag= 0;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected4[]= "3 <= field_name < 5";
   EXPECT_STREQ(expected4, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg35.min_flag= NO_MIN_RANGE;
   sel_arg35.max_flag= 0;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected5[]= "field_name <= 5";
   EXPECT_STREQ(expected5, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg35.min_flag= 0;
   sel_arg35.max_flag= NO_MAX_RANGE;
-  sel_arg35.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg35, &kpi);
   const char expected6[]= "3 <= field_name";
   EXPECT_STREQ(expected6, range_string.c_ptr());
 }
@@ -308,18 +312,18 @@ TEST_F(SelArgTest, CopyMax)
   uchar range_val5[field_long5.KEY_LENGTH];
   field_long5.get_key_image(range_val5, kpi.length, Field::itRAW);
 
-  Debug_sel_arg sel_arg3(&field_long3, range_val3, range_val3, &kpi);
+  SEL_ARG sel_arg3(&field_long3, range_val3, range_val3);
   sel_arg3.min_flag= NO_MIN_RANGE;
-  Debug_sel_arg sel_arg5(&field_long5, range_val5, range_val5, &kpi);
+  SEL_ARG sel_arg5(&field_long5, range_val5, range_val5);
   sel_arg5.min_flag= NO_MIN_RANGE;
 
   String range_string;
-  sel_arg3.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg3, &kpi);
   const char expected[]= "field_name <= 3";
   EXPECT_STREQ(expected, range_string.c_ptr());
 
   range_string.length(0);
-  sel_arg5.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg5, &kpi);
   const char expected2[]= "field_name <= 5";
   EXPECT_STREQ(expected2, range_string.c_ptr());
 
@@ -335,14 +339,14 @@ TEST_F(SelArgTest, CopyMax)
   EXPECT_FALSE(full_range);
 
   range_string.length(0);
-  sel_arg3.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg3, &kpi);
   const char expected3[]= "field_name <= 5";
   EXPECT_STREQ(expected3, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg5.min_flag= 0;
   sel_arg5.max_flag= NO_MAX_RANGE;
-  sel_arg5.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg5, &kpi);
   const char expected4[]= "5 <= field_name";
   EXPECT_STREQ(expected4, range_string.c_ptr());
 
@@ -359,7 +363,7 @@ TEST_F(SelArgTest, CopyMax)
   EXPECT_TRUE(full_range);
 
   range_string.length(0);
-  sel_arg3.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg3, &kpi);
   const char expected5[]= "field_name";
   EXPECT_STREQ(expected5, range_string.c_ptr());
 }
@@ -378,18 +382,18 @@ TEST_F(SelArgTest, CopyMin)
   uchar range_val5[field_long5.KEY_LENGTH];
   field_long5.get_key_image(range_val5, kpi.length, Field::itRAW);
 
-  Debug_sel_arg sel_arg3(&field_long3, range_val3, range_val3, &kpi);
+  SEL_ARG sel_arg3(&field_long3, range_val3, range_val3);
   sel_arg3.max_flag= NO_MAX_RANGE;
-  Debug_sel_arg sel_arg5(&field_long5, range_val5, range_val5, &kpi);
+  SEL_ARG sel_arg5(&field_long5, range_val5, range_val5);
   sel_arg5.max_flag= NO_MAX_RANGE;
 
   String range_string;
-  sel_arg3.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg3, &kpi);
   const char expected[]= "3 <= field_name";
   EXPECT_STREQ(expected, range_string.c_ptr());
 
   range_string.length(0);
-  sel_arg5.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg5, &kpi);
   const char expected2[]= "5 <= field_name";
   EXPECT_STREQ(expected2, range_string.c_ptr());
 
@@ -405,14 +409,14 @@ TEST_F(SelArgTest, CopyMin)
   EXPECT_FALSE(full_range);
 
   range_string.length(0);
-  sel_arg5.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg5, &kpi);
   const char expected3[]= "3 <= field_name";
   EXPECT_STREQ(expected3, range_string.c_ptr());
 
   range_string.length(0);
   sel_arg3.max_flag= 0;
   sel_arg3.min_flag= NO_MIN_RANGE;
-  sel_arg3.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg3, &kpi);
   const char expected4[]= "field_name <= 3";
   EXPECT_STREQ(expected4, range_string.c_ptr());
 
@@ -429,11 +433,84 @@ TEST_F(SelArgTest, CopyMin)
   EXPECT_TRUE(full_range);
 
   range_string.length(0);
-  sel_arg5.print(&range_string);
+  print_selarg_ranges(&range_string, &sel_arg5, &kpi);
   const char expected5[]= "field_name";
   EXPECT_STREQ(expected5, range_string.c_ptr());
 }
 
+
+TEST_F(SelArgTest, KeyOr1)
+{
+  Mock_field_long field_long3(thd(), new Item_int(3));
+  Mock_field_long field_long4(thd(), new Item_int(4));
+
+  KEY_PART_INFO kpi;
+  kpi.init_from_field(&field_long3);
+
+  uchar range_val3[field_long3.KEY_LENGTH];
+  field_long3.get_key_image(range_val3, kpi.length, Field::itRAW);
+
+  uchar range_val4[field_long4.KEY_LENGTH];
+  field_long4.get_key_image(range_val4, kpi.length, Field::itRAW);
+
+  SEL_ARG sel_arg_lt3(&field_long3, range_val3, range_val3);
+  sel_arg_lt3.part= 0;
+  sel_arg_lt3.min_flag= NO_MIN_RANGE;
+  sel_arg_lt3.max_flag= NEAR_MAX;
+
+  SEL_ARG sel_arg_gt3(&field_long3, range_val3, range_val3);
+  sel_arg_gt3.part= 0;
+  sel_arg_gt3.min_flag= NEAR_MIN;
+  sel_arg_gt3.max_flag= NO_MAX_RANGE;
+
+  SEL_ARG sel_arg_lt4(&field_long4, range_val4, range_val4);
+  sel_arg_lt4.part= 0;
+  sel_arg_lt4.min_flag= NO_MIN_RANGE;
+  sel_arg_lt4.max_flag= NEAR_MAX;
+
+  String range_string;
+  print_selarg_ranges(&range_string, &sel_arg_lt3, &kpi);
+  const char expected_lt3[]= "field_name < 3";
+  EXPECT_STREQ(expected_lt3, range_string.c_ptr());
+
+  range_string.length(0);
+  print_selarg_ranges(&range_string, &sel_arg_gt3, &kpi);
+  const char expected_gt3[]= "3 < field_name";
+  EXPECT_STREQ(expected_gt3, range_string.c_ptr());
+
+  range_string.length(0);
+  print_selarg_ranges(&range_string, &sel_arg_lt4, &kpi);
+  const char expected_lt4[]= "field_name < 4";
+  EXPECT_STREQ(expected_lt4, range_string.c_ptr());
+
+
+  /*
+    Ranges now:
+                       -inf ----------------34----------- +inf
+    sel_arg_lt3:       [-------------------->
+    sel_arg_gt3:                             <---------------]
+    sel_arg_lt4:       [--------------------->
+  */
+
+  SEL_ARG *tmp= key_or(NULL, &sel_arg_lt3, &sel_arg_gt3);
+
+  /*
+    Ranges now:
+                       -inf ----------------34----------- +inf
+    tmp:               [--------------------><---------------]
+    sel_arg_lt4:       [--------------------->
+  */
+  range_string.length(0);
+  print_selarg_ranges(&range_string, tmp, &kpi);
+  const char expected_merged[]= 
+    "field_name < 3\n"
+    "3 < field_name";
+  EXPECT_STREQ(expected_merged, range_string.c_ptr());
+
+  SEL_ARG *tmp2= key_or(NULL, tmp, &sel_arg_lt4);
+  EXPECT_EQ(null_arg, tmp2);
+}
+
 }
 
 

No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (jorgen.loland:3724 to 3725) Bug#13731380Jorgen Loland8 Mar