diff options
author | Alexander Barkov <bar@mariadb.org> | 2016-03-23 08:26:40 +0400 |
---|---|---|
committer | Alexander Barkov <bar@mariadb.org> | 2016-03-23 08:26:40 +0400 |
commit | e4435b5ec304be1439475f6f6084fbf9f1fd9e1f (patch) | |
tree | f4ff2eb2f4c5fb4c0661d22f8e1c273b680769f8 | |
parent | f66303dcf9fc9b7d5244be8b3c724387a5da7988 (diff) | |
download | mariadb-git-e4435b5ec304be1439475f6f6084fbf9f1fd9e1f.tar.gz |
MDEV-9604 crash in Item::save_in_field with empty enum value
1. Fixing Field_time::get_equal_const_item() to pass TIME_FUZZY_DATES
and TIME_INVALID_DATES to get_time_with_conversion().
This is needed to make the recursively called Item::get_date() return
non-NULL values on garbage input. This makes Field_time::get_equal_const_item()
work consistently with how Item::val_time_packed() works.
2. Fixing Item::get_date() to return TIME'00:00:00' rather than
DATE'0000-00-00' on empty or garbage input when:
- TIME_FUZZY_DATES is enabled
- The caller requested a TIME value (by passing TIME_TIME_ONLY).
This is needed to avoid conversion of DATE'0000-00-00' to TIME
in get_time_with_conversion(), which would erroneously try to subtract
CURRENT_DATE from DATE'0000-00-00' and return TIME'-838:59:59' rather than
the desired zero value TIME'00:00:00'.
#1 and #2 fix these type of scripts to return one row with both
MyISAM and InnoDB, with and without an index on t1.b:
CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b));
INSERT INTO t1 VALUES ('','00:00:00',0);
SELECT * FROM t1 WHERE b='';
SELECT * FROM t1 WHERE a=b;
SELECT * FROM t1 IGNORE INDEX(b) WHERE b='';
SELECT * FROM t1 IGNORE INDEX(b) WHERE a=b;
Additionally, #1 and #2 fix the originally reported in MDEV-9604 crash
in Item::save_in_field(), because now execution goes through a different
path, so save_in_field() is called for a Item_time_literal instance
(which is non-NULL) rather than a Item_cache_str instance (which could
return NULL without setting null_value).
3. Fixing Field_temporal::get_equal_const_item_datetime() to enable
equal field propagation for DATETIME and TIMESTAMP in case of
comparison (e.g. when ANY_SUBST), for symmetry with
Field_newdate::get_equal_const_item(). This fixes a number of problems
with empty set returned on comparison to empty/garbage input.
Now all SELECT queries in this script return one row for MyISAM and InnoDB,
with and without an index on t1.b:
CREATE TABLE t1 (a ENUM('a'), b DATETIME, c INT, KEY(b));
INSERT INTO t1 VALUES ('','0000-00-00 00:00:00',0);
SELECT * FROM t1 WHERE b='';
SELECT * FROM t1 WHERE a=b;
SELECT * FROM t1 IGNORE INDEX(b) WHERE b='';
SELECT * FROM t1 IGNORE INDEX(b) WHERE a=b;
-rw-r--r-- | mysql-test/r/type_datetime.result | 17 | ||||
-rw-r--r-- | mysql-test/r/type_temporal_innodb.result | 156 | ||||
-rw-r--r-- | mysql-test/r/type_timestamp.result | 15 | ||||
-rw-r--r-- | mysql-test/t/type_temporal_innodb.test | 60 | ||||
-rw-r--r-- | sql/field.cc | 17 | ||||
-rw-r--r-- | sql/item.cc | 16 |
6 files changed, 263 insertions, 18 deletions
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index 155e9535a0a..16990c2414d 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -747,7 +747,7 @@ EXPLAIN EXTENDED SELECT * FROM t1 FORCE INDEX(attime) WHERE AtTime = '2010-02-22 id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ref AtTime AtTime 6 const 1 100.00 Warnings: -Note 1003 select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = '2010-02-22 18:40:07') +Note 1003 select `test`.`t1`.`Id` AS `Id`,`test`.`t1`.`AtTime` AS `AtTime` from `test`.`t1` FORCE INDEX (`attime`) where (`test`.`t1`.`AtTime` = TIMESTAMP'2010-02-22 18:40:07') DROP TABLE t1; SET NAMES latin1; # @@ -963,21 +963,20 @@ a 2001-01-01 00:00:00 Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '2001-01-01 00:00:00x') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '2001-01-01 00:00:00x') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> (30 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> (30 + rand()))) DROP TABLE t1; CREATE TABLE t1 (a DATETIME);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -993,20 +992,20 @@ SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = ' 2001-01-01 00:00:00') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' 2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (19 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (19 + rand()))) EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Incorrect datetime value: ' garbage ' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' garbage ') and (length(`test`.`t1`.`a`) = (30 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00') and (<cache>(length(TIMESTAMP'0000-00-00 00:00:00')) = (30 + rand()))) DROP TABLE t1; CREATE TABLE t1 (a DATETIME);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -1076,13 +1075,13 @@ SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIME'00:00:00') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIME'00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) DROP TABLE t1; # # MDEV-8795 Equal expression propagation does not work for temporal literals diff --git a/mysql-test/r/type_temporal_innodb.result b/mysql-test/r/type_temporal_innodb.result new file mode 100644 index 00000000000..425e499a5d3 --- /dev/null +++ b/mysql-test/r/type_temporal_innodb.result @@ -0,0 +1,156 @@ +# +# MDEV-9604 crash in Item::save_in_field with empty enum value +# +SELECT TIME'00:00:00'=''; +TIME'00:00:00'='' +1 +Warnings: +Warning 1292 Truncated incorrect time value: '' +CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','00:00:00',0); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1 WHERE b=''; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Truncated incorrect time value: '' +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 00:00:00 0 +Warnings: +Warning 1292 Truncated incorrect time value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Truncated incorrect time value: '' +DROP TABLE t1; +SELECT DATE'0000-00-00'=''; +DATE'0000-00-00'='' +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +CREATE TABLE t1 (a ENUM('a'), b DATE, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','0000-00-00',0); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1 WHERE b=''; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 0000-00-00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +DROP TABLE t1; +SELECT TIMESTAMP'0000-00-00 00:00:00'=''; +TIMESTAMP'0000-00-00 00:00:00'='' +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +CREATE TABLE t1 (a ENUM('a'), b DATETIME, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','0000-00-00 00:00:00',0); +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT * FROM t1 WHERE b=''; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT * FROM t1 WHERE a=b; +a b c + 0000-00-00 00:00:00 0 +Warnings: +Warning 1292 Incorrect datetime value: '' +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +1 +1 +Warnings: +Warning 1292 Incorrect datetime value: '' +DROP TABLE t1; diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result index fcdef627ded..a579f6930a0 100644 --- a/mysql-test/r/type_timestamp.result +++ b/mysql-test/r/type_timestamp.result @@ -818,21 +818,20 @@ a 2001-01-01 00:00:00 Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a) != 20 AND a='2001-01-01 00:00:00x'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = '2001-01-01 00:00:00x') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)!=30+RAND() AND a='2001-01-01 00:00:00x'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Truncated incorrect datetime value: '2001-01-01 00:00:00x' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = '2001-01-01 00:00:00x') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> (30 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) <> (30 + rand()))) DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -848,20 +847,20 @@ SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = ' 2001-01-01 00:00:00') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=19+RAND() AND a=' 2001-01-01 00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' 2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (19 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (19 + rand()))) EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=30+RAND() AND a=' garbage '; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: Warning 1292 Incorrect datetime value: ' garbage ' -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = ' garbage ') and (length(`test`.`t1`.`a`) = (30 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'0000-00-00 00:00:00') and (<cache>(length(TIMESTAMP'0000-00-00 00:00:00')) = (30 + rand()))) DROP TABLE t1; CREATE TABLE t1 (a TIMESTAMP);; INSERT INTO t1 VALUES ('2001-01-01 00:00:00'),('2001-01-01 00:00:01'); @@ -931,13 +930,13 @@ SELECT * FROM t1 WHERE LENGTH(a)=19 AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIME'00:00:00') +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where (`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') EXPLAIN EXTENDED SELECT * FROM t1 WHERE LENGTH(a)=40+RAND() AND a=TIME'00:00:00'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIME'00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) +Note 1003 select `test`.`t1`.`a` AS `a` from `test`.`t1` where ((`test`.`t1`.`a` = TIMESTAMP'2001-01-01 00:00:00') and (<cache>(length(TIMESTAMP'2001-01-01 00:00:00')) = (40 + rand()))) DROP TABLE t1; # # End of 10.1 tests diff --git a/mysql-test/t/type_temporal_innodb.test b/mysql-test/t/type_temporal_innodb.test new file mode 100644 index 00000000000..ac5daca6508 --- /dev/null +++ b/mysql-test/t/type_temporal_innodb.test @@ -0,0 +1,60 @@ +--source include/have_innodb.inc + +# +# testing of temporal data types with InnoDB +# + + +--echo # +--echo # MDEV-9604 crash in Item::save_in_field with empty enum value +--echo # + +SELECT TIME'00:00:00'=''; + +CREATE TABLE t1 (a ENUM('a'), b TIME, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','00:00:00',0); +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; + +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +DROP TABLE t1; + + +SELECT DATE'0000-00-00'=''; + +CREATE TABLE t1 (a ENUM('a'), b DATE, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','0000-00-00',0); +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; + +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +DROP TABLE t1; + + +SELECT TIMESTAMP'0000-00-00 00:00:00'=''; + +CREATE TABLE t1 (a ENUM('a'), b DATETIME, c INT, KEY(b)) ENGINE=InnoDB; +INSERT INTO t1 VALUES ('','0000-00-00 00:00:00',0); +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; + +ALTER TABLE t1 ENGINE=MyISAM; +SELECT * FROM t1 WHERE b=''; +SELECT * FROM t1 IGNORE KEY (b) WHERE b=''; +SELECT * FROM t1 WHERE a=b; +SELECT 1 FROM t1 WHERE (SELECT a FROM t1 group by c) = b; +DROP TABLE t1; diff --git a/sql/field.cc b/sql/field.cc index 895f8a00f10..ffa7beb275b 100644 --- a/sql/field.cc +++ b/sql/field.cc @@ -5624,6 +5624,18 @@ Item *Field_temporal::get_equal_const_item_datetime(THD *thd, } break; case ANY_SUBST: + if (!is_temporal_type_with_date(const_item->field_type())) + { + MYSQL_TIME ltime; + if (const_item->get_date_with_conversion(<ime, + TIME_FUZZY_DATES | + TIME_INVALID_DATES)) + return NULL; + return new (thd->mem_root) + Item_datetime_literal_for_invalid_dates(thd, <ime, + ltime.second_part ? + TIME_SECOND_PART_DIGITS : 0); + } break; } return const_item; @@ -5932,7 +5944,10 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx, { MYSQL_TIME ltime; // Get the value of const_item with conversion from DATETIME to TIME - if (const_item->get_time_with_conversion(thd, <ime, TIME_TIME_ONLY)) + if (const_item->get_time_with_conversion(thd, <ime, + TIME_TIME_ONLY | + TIME_FUZZY_DATES | + TIME_INVALID_DATES)) return NULL; /* Replace a DATE/DATETIME constant to a TIME constant: diff --git a/sql/item.cc b/sql/item.cc index 7313c5f2b27..f1362e7e5da 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -1301,6 +1301,22 @@ err: if allowed, otherwise - null. */ bzero((char*) ltime,sizeof(*ltime)); + if (fuzzydate & TIME_TIME_ONLY) + { + /* + In the following scenario: + - The caller expected to get a TIME value + - Item returned a not NULL string or numeric value + - But then conversion from string or number to TIME failed + we need to change the default time_type from MYSQL_TIMESTAMP_DATE + (which was set in bzero) to MYSQL_TIMESTAMP_TIME and therefore + return TIME'00:00:00' rather than DATE'0000-00-00'. + If we don't do this, methods like Item::get_time_with_conversion() + will erroneously subtract CURRENT_DATE from '0000-00-00 00:00:00' + and return TIME'-838:59:59' instead of TIME'00:00:00' as a result. + */ + ltime->time_type= MYSQL_TIMESTAMP_TIME; + } return null_value|= !(fuzzydate & TIME_FUZZY_DATES); } |