diff options
author | Alexander Barkov <bar@mariadb.org> | 2017-05-07 17:44:27 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.org> | 2017-05-07 17:44:27 +0400 |
commit | 02ada41744382fea11621a5a8094cbbea06c9487 (patch) | |
tree | 070df5c1c45ffa2542a7a261228ca865d33b0b2b | |
parent | 4e9022b48beadbad5bfe32fe67107f930381b3fb (diff) | |
download | mariadb-git-02ada41744382fea11621a5a8094cbbea06c9487.tar.gz |
MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE)
-rw-r--r-- | mysql-test/r/type_date.result | 35 | ||||
-rw-r--r-- | mysql-test/t/type_date.test | 25 | ||||
-rw-r--r-- | sql/opt_range.cc | 68 | ||||
-rw-r--r-- | sql/opt_range.h | 11 |
4 files changed, 102 insertions, 37 deletions
diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result index 4b0e4a61c64..7c7494903b6 100644 --- a/mysql-test/r/type_date.result +++ b/mysql-test/r/type_date.result @@ -429,17 +429,26 @@ select @a; # # BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed # +SET TIMESTAMP=UNIX_TIMESTAMP('2017-01-03 00:00:00'); create table t1(a date,key(a)); insert into t1 values ('2012-01-01'),('2012-02-02'); explain select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1_0 ref a a 4 const 0 Using where; Using index +1 SIMPLE t1_0 ref a a 4 const 1 Using where; Using index 1 SIMPLE t2 index NULL a 4 NULL 2 Using index; Using join buffer (flat, BNL join) 1 SIMPLE t1 index NULL a 4 NULL 2 Using index; Using join buffer (incremental, BNL join) select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; 1 +SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-03 00:00:01'); +explain +select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; +1 drop table t1; +SET TIMESTAMP=DEFAULT; # # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null # MDEV-9972 Least function retuns date in date time format @@ -845,3 +854,27 @@ DROP TABLE t1; # # End of 10.1 tests # +# +# Start of 10.3 tests +# +# +# MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE) +# +CREATE TABLE t1(a DATE,KEY(a)); +INSERT INTO t1 VALUES ('2012-01-01'),('2012-02-02'); +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:01' AND TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:01') AND TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:00' AND TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 4 const 1 Using where; Using index +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:00') AND TRUE; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 4 const 1 Using where; Using index +DROP TABLE t1; +# +# End of 10.3 tests +# diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test index 11924f696db..7c4af618c23 100644 --- a/mysql-test/t/type_date.test +++ b/mysql-test/t/type_date.test @@ -369,12 +369,18 @@ select @a; --echo # --echo # BUG LP:1008487 virtual bool Item_cache::is_expensive(): Assertion `example' failed --echo # +SET TIMESTAMP=UNIX_TIMESTAMP('2017-01-03 00:00:00'); create table t1(a date,key(a)); insert into t1 values ('2012-01-01'),('2012-02-02'); explain select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; +SET TIMESTAMP=UNIX_TIMESTAMP('2012-01-03 00:00:01'); +explain +select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; +select 1 from t1 as t1_0 inner join t1 as t2 on (t1_0.a <=> now()) join t1 on 1; drop table t1; +SET TIMESTAMP=DEFAULT; --echo # --echo # MDEV-9521 Least function returns 0000-00-00 for null date columns instead of null @@ -580,3 +586,22 @@ DROP TABLE t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # Start of 10.3 tests +--echo # + +--echo # +--echo # MDEV-12721 Wrong execution plan for WHERE (date_field <=> timestamp_expr AND TRUE) +--echo # +CREATE TABLE t1(a DATE,KEY(a)); +INSERT INTO t1 VALUES ('2012-01-01'),('2012-02-02'); +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:01' AND TRUE; +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:01') AND TRUE; +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP'2001-01-01 00:00:00' AND TRUE; +EXPLAIN SELECT 1 FROM t1 WHERE t1.a <=> TIMESTAMP('2001-01-01', '00:00:00') AND TRUE; +DROP TABLE t1; + +--echo # +--echo # End of 10.3 tests +--echo # diff --git a/sql/opt_range.cc b/sql/opt_range.cc index b7b06080121..87cb16c96fe 100644 --- a/sql/opt_range.cc +++ b/sql/opt_range.cc @@ -8030,33 +8030,53 @@ Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, field->type_handler() == &type_handler_set) { if (type == EQ_FUNC || type == EQUAL_FUNC) - { - tree= new (alloc) SEL_ARG(field, 0, 0); - tree->type= SEL_ARG::IMPOSSIBLE; - } + tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); goto end; } if (err == 2 && field->cmp_type() == STRING_RESULT) { if (type == EQ_FUNC || type == EQUAL_FUNC) - { - tree= new (alloc) SEL_ARG(field, 0, 0); - tree->type= SEL_ARG::IMPOSSIBLE; - } + tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); else tree= NULL; /* Cannot infer anything */ goto end; } - if (field->cmp_type() != value->result_type()) + if (err == 3 && field->type() == FIELD_TYPE_DATE) + { + /* + We were saving DATETIME into a DATE column, the conversion went ok + but a non-zero time part was cut off. + + In MySQL's SQL dialect, DATE and DATETIME are compared as datetime + values. Index over a DATE column uses DATE comparison. Changing + from one comparison to the other is possible: + + datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10' + datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10' + + datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10' + datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10' + + but we'll need to convert '>' to '>=' and '<' to '<='. This will + be done together with other types at the end of this function + (grep for stored_field_cmp_to_item) + */ + if (type == EQ_FUNC || type == EQUAL_FUNC) + { + tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); + goto end; + } + // Continue with processing non-equality ranges + } + else if (field->cmp_type() != value->result_type()) { if ((type == EQ_FUNC || type == EQUAL_FUNC) && value->result_type() == item_cmp_type(field->result_type(), value->result_type())) { - tree= new (alloc) SEL_ARG(field, 0, 0); - tree->type= SEL_ARG::IMPOSSIBLE; + tree= new (alloc) SEL_ARG_IMPOSSIBLE(field); goto end; } else @@ -8066,31 +8086,7 @@ Item_bool_func::get_mm_leaf(RANGE_OPT_PARAM *param, for the cases like int_field > 999999999999999999999999 as well. */ tree= 0; - if (err == 3 && field->type() == FIELD_TYPE_DATE && - (type == GT_FUNC || type == GE_FUNC || - type == LT_FUNC || type == LE_FUNC) ) - { - /* - We were saving DATETIME into a DATE column, the conversion went ok - but a non-zero time part was cut off. - - In MySQL's SQL dialect, DATE and DATETIME are compared as datetime - values. Index over a DATE column uses DATE comparison. Changing - from one comparison to the other is possible: - - datetime(date_col)< '2007-12-10 12:34:55' -> date_col<='2007-12-10' - datetime(date_col)<='2007-12-10 12:34:55' -> date_col<='2007-12-10' - - datetime(date_col)> '2007-12-10 12:34:55' -> date_col>='2007-12-10' - datetime(date_col)>='2007-12-10 12:34:55' -> date_col>='2007-12-10' - - but we'll need to convert '>' to '>=' and '<' to '<='. This will - be done together with other types at the end of this function - (grep for stored_field_cmp_to_item) - */ - } - else - goto end; + goto end; } } diff --git a/sql/opt_range.h b/sql/opt_range.h index c1f7079ce7f..95e231433d2 100644 --- a/sql/opt_range.h +++ b/sql/opt_range.h @@ -594,6 +594,17 @@ public: }; +class SEL_ARG_IMPOSSIBLE: public SEL_ARG +{ +public: + SEL_ARG_IMPOSSIBLE(Field *field) + :SEL_ARG(field, 0, 0) + { + type= SEL_ARG::IMPOSSIBLE; + } +}; + + class RANGE_OPT_PARAM { public: |