From: Date: September 9 2008 6:10pm Subject: bzr push into mysql-5.0 branch (ramil:2683 to 2684) Bug#37526 List-Archive: http://lists.mysql.com/commits/53629 X-Bug: 37526 Message-Id: <200809091610.m89GA7OG026914@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit 2684 Ramil Kalimullin 2008-09-09 Fix for bug#37526: asymertic operator <=> in trigger Problem: <=> operator may return wrong results comparing NULL and a DATE/DATETIME/TIME value. Fix: properly check NULLs. modified: mysql-test/r/type_datetime.result mysql-test/t/type_datetime.test sql/item_cmpfunc.cc 2683 Ramil Kalimullin 2008-09-05 Fix for bug #39021: SELECT REGEXP BINARY NULL never returns Problem: SELECT ... REGEXP BINARY NULL may lead to server crash/hang. Fix: properly handle NULL regular expressions. modified: mysql-test/r/func_regexp.result mysql-test/t/func_regexp.test sql/item_cmpfunc.cc sql/item_cmpfunc.h === modified file 'mysql-test/r/type_datetime.result' --- a/mysql-test/r/type_datetime.result 2007-12-08 21:05:00 +0000 +++ b/mysql-test/r/type_datetime.result 2008-09-09 15:05:27 +0000 @@ -560,4 +560,27 @@ 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 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' +Warning 1292 Truncated incorrect datetime value: 'NULL' End of 5.0 tests === modified file 'mysql-test/t/type_datetime.test' --- a/mysql-test/t/type_datetime.test 2007-12-13 10:52:49 +0000 +++ b/mysql-test/t/type_datetime.test 2008-09-09 15:05:27 +0000 @@ -388,4 +388,20 @@ where id in (select id from t2 as x1 whe 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 === modified file 'sql/item_cmpfunc.cc' --- a/sql/item_cmpfunc.cc 2008-09-05 08:30:01 +0000 +++ b/sql/item_cmpfunc.cc 2008-09-09 15:05:27 +0000 @@ -966,19 +966,24 @@ get_datetime_value(THD *thd, Item ***ite 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; @@ -986,14 +991,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;