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(¤t_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#13731380 | Jorgen Loland | 8 Mar |
