From 36f8bfb22eda01d6d09c29007fe98d4a29610076 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 14 Jun 2007 18:55:07 -0700 Subject: Fixed bug #27932: the function LOCATE returned NULL if any of its arguments was evaluated to NULL, while the predicate LOCATE(str,NULL) IS NULL erroneously was evaluated to FALSE. This happened because the Item_func_locate::fix_length_and_dec method by mistake set the value of the maybe_null flag for the function item to 0. In consequence of this the function was considered as the one that could not ever return NULL. mysql-test/r/func_str.result: Added a test case for bug #27932. mysql-test/t/func_str.test: Added a test case for bug #27932. --- mysql-test/r/func_str.result | 76 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 76 insertions(+) (limited to 'mysql-test/r/func_str.result') diff --git a/mysql-test/r/func_str.result b/mysql-test/r/func_str.result index d8afbe13c76..e79670b3f76 100644 --- a/mysql-test/r/func_str.result +++ b/mysql-test/r/func_str.result @@ -2061,4 +2061,80 @@ C 2707236321 DROP TABLE t1, t2; DROP VIEW v1; +SELECT LOCATE('foo', NULL) FROM DUAL; +LOCATE('foo', NULL) +NULL +SELECT LOCATE(NULL, 'o') FROM DUAL; +LOCATE(NULL, 'o') +NULL +SELECT LOCATE(NULL, NULL) FROM DUAL; +LOCATE(NULL, NULL) +NULL +SELECT LOCATE('foo', NULL) IS NULL FROM DUAL; +LOCATE('foo', NULL) IS NULL +1 +SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL; +LOCATE(NULL, 'o') IS NULL +1 +SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL; +LOCATE(NULL, NULL) IS NULL +1 +SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL; +ISNULL(LOCATE('foo', NULL)) +1 +SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL; +ISNULL(LOCATE(NULL, 'o')) +1 +SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL; +ISNULL(LOCATE(NULL, NULL)) +1 +SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL; +LOCATE('foo', NULL) <=> NULL +1 +SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL; +LOCATE(NULL, 'o') <=> NULL +1 +SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL; +LOCATE(NULL, NULL) <=> NULL +1 +CREATE TABLE t1 (id int NOT NULL PRIMARY KEY, a varchar(10), p varchar(10)); +INSERT INTO t1 VALUES (1, 'foo', 'o'); +INSERT INTO t1 VALUES (2, 'foo', NULL); +INSERT INTO t1 VALUES (3, NULL, 'o'); +INSERT INTO t1 VALUES (4, NULL, NULL); +SELECT id, LOCATE(a,p) FROM t1; +id LOCATE(a,p) +1 0 +2 NULL +3 NULL +4 NULL +SELECT id, LOCATE(a,p) IS NULL FROM t1; +id LOCATE(a,p) IS NULL +1 0 +2 1 +3 1 +4 1 +SELECT id, ISNULL(LOCATE(a,p)) FROM t1; +id ISNULL(LOCATE(a,p)) +1 0 +2 1 +3 1 +4 1 +SELECT id, LOCATE(a,p) <=> NULL FROM t1; +id LOCATE(a,p) <=> NULL +1 0 +2 1 +3 1 +4 1 +SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL; +id +2 +3 +4 +SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL; +id +2 +3 +4 +DROP TABLE t1; End of 5.0 tests -- cgit v1.2.1