diff options
| -rw-r--r-- | mysql-test/r/type_datetime.result | 23 | ||||
| -rw-r--r-- | mysql-test/t/type_datetime.test | 16 | ||||
| -rw-r--r-- | sql/item_cmpfunc.cc | 20 |
3 files changed, 52 insertions, 7 deletions
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result index d485c468a39..6a692ed58e4 100644 --- a/mysql-test/r/type_datetime.result +++ b/mysql-test/r/type_datetime.result @@ -560,6 +560,29 @@ select * from t2 where id in (select id from t2 as x1 where (t2.cur_date is null)); id cur_date drop table t1,t2; +SELECT +CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, +CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2, +CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3, +CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, +CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5, +CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6, +CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, +CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8, +CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9; +n1 n2 n3 n4 n5 n6 n7 n8 n9 +0 0 1 NULL NULL NULL NULL NULL NULL +Warnings: +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' +Warning 1292 Incorrect datetime value: 'NULL' End of 5.0 tests set @org_mode=@@sql_mode; create table t1 (da date default '1962-03-03 23:33:34', dt datetime default '1962-03-03'); diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test index 4fdd96b8729..eb1b7bde844 100644 --- a/mysql-test/t/type_datetime.test +++ b/mysql-test/t/type_datetime.test @@ -388,6 +388,22 @@ where id in (select id from t2 as x1 where (t2.cur_date is null)); drop table t1,t2; + +# +# Bug #37526: asymertic operator <=> in trigger +# +SELECT + CAST('NULL' AS DATE) <=> CAST('2008-01-01' AS DATE) n1, + CAST('2008-01-01' AS DATE) <=> CAST('NULL' AS DATE) n2, + CAST('NULL' AS DATE) <=> CAST('NULL' AS DATE) n3, + CAST('NULL' AS DATE) <> CAST('2008-01-01' AS DATE) n4, + CAST('2008-01-01' AS DATE) <> CAST('NULL' AS DATE) n5, + CAST('NULL' AS DATE) <> CAST('NULL' AS DATE) n6, + CAST('NULL' AS DATE) < CAST('2008-01-01' AS DATE) n7, + CAST('2008-01-01' AS DATE) < CAST('NULL' AS DATE) n8, + CAST('NULL' AS DATE) < CAST('NULL' AS DATE) n9; + + --echo End of 5.0 tests # # Test of storing datetime into date fields diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc index dec07a587a5..bd6065c9403 100644 --- a/sql/item_cmpfunc.cc +++ b/sql/item_cmpfunc.cc @@ -1029,19 +1029,24 @@ get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg, 1 if items are equal or both are null 0 otherwise If is_nulls_eq is FALSE: - -1 a < b or one of items is null + -1 a < b or at least one item is null 0 a == b 1 a > b + See the table: + is_nulls_eq | 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 | + a_is_null | 1 | 0 | 1 | 0 | 1 | 0 | 1 | 0 | + b_is_null | 1 | 1 | 0 | 0 | 1 | 1 | 0 | 0 | + result | 1 | 0 | 0 |0/1|-1 |-1 |-1 |-1/0/1| */ int Arg_comparator::compare_datetime() { - bool is_null= FALSE; + bool a_is_null, b_is_null; ulonglong a_value, b_value; /* Get DATE/DATETIME/TIME value of the 'a' item. */ - a_value= (*get_value_func)(thd, &a, &a_cache, *b, &is_null); - if (!is_nulls_eq && is_null) + a_value= (*get_value_func)(thd, &a, &a_cache, *b, &a_is_null); + if (!is_nulls_eq && a_is_null) { if (owner) owner->null_value= 1; @@ -1049,14 +1054,15 @@ int Arg_comparator::compare_datetime() } /* Get DATE/DATETIME/TIME value of the 'b' item. */ - b_value= (*get_value_func)(thd, &b, &b_cache, *a, &is_null); - if (is_null) + b_value= (*get_value_func)(thd, &b, &b_cache, *a, &b_is_null); + if (a_is_null || b_is_null) { if (owner) owner->null_value= is_nulls_eq ? 0 : 1; - return is_nulls_eq ? 1 : -1; + return is_nulls_eq ? (a_is_null == b_is_null) : -1; } + /* Here we have two not-NULL values. */ if (owner) owner->null_value= 0; |