From: igor
Date: June 15 2007 1:55am
Subject: bk commit into 5.0 tree (igor:1.2522) BUG#27932
List-Archive: http://lists.mysql.com/commits/28832
X-Bug: 27932
Message-Id: <20070615015513.2119339FF42@olga.mysql.com>

Below is the list of changes that have just been committed into a local
5.0 repository of igor. When igor does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-06-14 18:55:07-07:00, igor@stripped +3 -0
  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@stripped, 2007-06-14 18:55:05-07:00, igor@stripped +76 -0
    Added a test case for bug #27932.

  mysql-test/t/func_str.test@stripped, 2007-06-14 18:55:05-07:00, igor@stripped +33 -0
    Added a test case for bug #27932.

  sql/item_func.cc@stripped, 2007-06-14 18:55:05-07:00, igor@stripped +0 -1
    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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	igor
# Host:	olga.mysql.com
# Root:	/home/igor/dev-opt/mysql-5.0-opt-bug27932

--- 1.349/sql/item_func.cc	2007-06-14 18:55:12 -07:00
+++ 1.350/sql/item_func.cc	2007-06-14 18:55:12 -07:00
@@ -2507,7 +2507,6 @@
 
 void Item_func_locate::fix_length_and_dec()
 {
-  maybe_null= 0;
   max_length= MY_INT32_NUM_DECIMAL_DIGITS;
   agg_arg_charsets(cmp_collation, args, 2, MY_COLL_CMP_CONV, 1);
 }

--- 1.133/mysql-test/r/func_str.result	2007-06-14 18:55:12 -07:00
+++ 1.134/mysql-test/r/func_str.result	2007-06-14 18:55:12 -07:00
@@ -2061,4 +2061,80 @@
 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

--- 1.105/mysql-test/t/func_str.test	2007-06-14 18:55:12 -07:00
+++ 1.106/mysql-test/t/func_str.test	2007-06-14 18:55:12 -07:00
@@ -1076,4 +1076,37 @@
 DROP TABLE t1, t2;
 DROP VIEW v1;
 
+#
+# Bug #27932: LOCATE with argument evaluated to NULL
+#
+
+SELECT LOCATE('foo', NULL) FROM DUAL;
+SELECT LOCATE(NULL, 'o') FROM DUAL;
+SELECT LOCATE(NULL, NULL) FROM DUAL;
+SELECT LOCATE('foo', NULL) IS NULL FROM DUAL;
+SELECT LOCATE(NULL, 'o') IS NULL FROM DUAL;
+SELECT LOCATE(NULL, NULL) IS NULL FROM DUAL;
+SELECT ISNULL(LOCATE('foo', NULL)) FROM DUAL;
+SELECT ISNULL(LOCATE(NULL, 'o')) FROM DUAL;
+SELECT ISNULL(LOCATE(NULL, NULL)) FROM DUAL;
+SELECT LOCATE('foo', NULL) <=> NULL FROM DUAL;
+SELECT LOCATE(NULL, 'o') <=> NULL FROM DUAL;
+SELECT LOCATE(NULL, NULL) <=> NULL FROM DUAL;
+
+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;
+SELECT id, LOCATE(a,p) IS NULL FROM t1;
+SELECT id, ISNULL(LOCATE(a,p)) FROM t1;
+SELECT id, LOCATE(a,p) <=> NULL FROM t1;
+SELECT id FROM t1 WHERE LOCATE(a,p) IS NULL;
+SELECT id FROM t1 WHERE LOCATE(a,p) <=> NULL; 
+
+DROP TABLE t1;
+
 --echo End of 5.0 tests