From: Jorgen Loland Date: March 7 2012 7:17am Subject: bzr push into mysql-trunk branch (jorgen.loland:3724 to 3725) Bug#13731380 List-Archive: http://lists.mysql.com/commits/143117 X-Bug: 13731380 Message-Id: <20120307071719.1B9F4181@atum21.no.oracle.com> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 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).