MySQL Bugs: #11469: wrong WHERE NULL-value optimization (CONCAT

Bug #11469	wrong WHERE NULL-value optimization (CONCAT_WS?)
Submitted:	20 Jun 2005 20:12	Modified:	29 Jun 2005 20:32
Reporter:	Alex Zimnitski	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.12a	OS:	Windows (windows 2000)
Assigned to:	Igor Babaev	CPU Architecture:	Any

Description:
wrong result

How to repeat:
CREATE TABLE `product_characteristic` (
  `id` int(11) NOT NULL auto_increment,
  `id_product_characteristic` int(11) NOT NULL default '0',
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
);
INSERT INTO `product_characteristic` VALUES (1, 0, 'Main');
INSERT INTO `product_characteristic` VALUES (2, 1, 'Toys');
INSERT INTO `product_characteristic` VALUES (3, 1, 'Games');

SELECT t1.`id`,CONCAT_WS( ' -> ', 
	t5.title,
	t4.title,
	t3.title,
	t2.title,
	t1.title) AS col1
FROM `product_characteristic` AS t1
	LEFT JOIN `product_characteristic` AS t2 ON t1.`id_product_characteristic`=t2.`id`
	LEFT JOIN `product_characteristic` AS t3 ON t2.`id_product_characteristic`=t3.`id`
	LEFT JOIN `product_characteristic` AS t4 ON t3.`id_product_characteristic`=t4.`id`
	LEFT JOIN `product_characteristic` AS t5 ON t4.`id_product_characteristic`=t5.`id` WHERE  ((SELECT COUNT(*)
FROM `product_characteristic` AS tt
WHERE tt.`id_product_characteristic`=t1.`id`)=0)  AND  
	(1 AND CONCAT_WS( ' -> ', 
	t5.title,
	t4.title,
	t3.title,
	t2.title,
	t1.title) LIKE '%toys%')  
ORDER BY col1;
SELECT t1.`id`,CONCAT_WS( ' -> ', 
	t5.title,
	t4.title,
	t3.title,
	t2.title,
	t1.title) AS col1
FROM `product_characteristic` AS t1
	LEFT JOIN `product_characteristic` AS t2 ON t1.`id_product_characteristic`=t2.`id`
	LEFT JOIN `product_characteristic` AS t3 ON t2.`id_product_characteristic`=t3.`id`
	LEFT JOIN `product_characteristic` AS t4 ON t3.`id_product_characteristic`=t4.`id`
	LEFT JOIN `product_characteristic` AS t5 ON t4.`id_product_characteristic`=t5.`id` WHERE  ((SELECT COUNT(*)
FROM `product_characteristic` AS tt
WHERE tt.`id_product_characteristic`=t1.`id`)=0)  AND  
	(1 AND CONCAT_WS( ' -> ', 
	IF(t5.title IS NULL,NULL,t5.title),
	IF(t4.title IS NULL,NULL,t4.title),
	IF(t3.title IS NULL,NULL,t3.title),
	IF(t2.title IS NULL,NULL,t2.title),
	IF(t1.title IS NULL,NULL,t1.title)) LIKE '%toys%')  
ORDER BY col1;

SELECT t1.`id`,CONCAT_WS( ' -> ', 
	t3.title,
	t2.title,
	t1.title) AS col1
FROM `product_characteristic` AS t1
	LEFT JOIN `product_characteristic` AS t2 ON
t1.`id_product_characteristic`=t2.`id`
	LEFT JOIN `product_characteristic` AS t3 ON
t2.`id_product_characteristic`=t3.`id`

returns 

+----+---------------+
| id | col1          |
+----+---------------+
|  1 | Main          |
|  2 | Main -> Toys  |
|  3 | Main -> Games |
+----+---------------+

so we'd expect row #2 to match the "like '%toys%'" condition, but 

SELECT t1.`id`,CONCAT_WS( ' -> ', 
	t3.title,
	t2.title,
	t1.title) AS col1
FROM `product_characteristic` AS t1
	LEFT JOIN `product_characteristic` AS t2 ON
t1.`id_product_characteristic`=t2.`id`
	LEFT JOIN `product_characteristic` AS t3 ON
t2.`id_product_characteristic`=t3.`id`
WHERE  CONCAT_WS( ' -> ', 
	t3.title,
	t2.title,
	t1.title) LIKE '%toys%';

returns an empty set. 
as soon as the t3.title column in the WHERE clause is replaced by

  IF(t3.title IS NULL, NULL, t3.title)

which is supposed to be a no-op as t3.title is NULL already so it
is just replacing NULL with NULL, the result is 

SELECT t1.`id`,CONCAT_WS( ' -> ', 
	t3.title,
	t2.title,
	t1.title) AS col1
FROM `product_characteristic` AS t1
	LEFT JOIN `product_characteristic` AS t2 ON
t1.`id_product_characteristic`=t2.`id`
	LEFT JOIN `product_characteristic` AS t3 ON
t2.`id_product_characteristic`=t3.`id`
WHERE  CONCAT_WS( ' -> ', 
	if(t3.title is null,NULL,t3.title),
	t2.title,
	t1.title) LIKE '%Toy%';

+----+--------------+
| id | col1         |
+----+--------------+
|  2 | Main -> Toys |
+----+--------------+
1 row in set (0,00 sec)

as expected

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/26512

ChangeSet
  1.2324 05/06/29 02:40:25 igor@rurik.mysql.com +3 -0
  func_str.test:
    Added test cases for bug #11469.
  item_strfunc.h:
    Fixed bug #11469: wrong implementation of the not_null_tables
    method for CONCAT_WS.

The fix will appear in 4.1.13 and 5.0.9.

Documented in 5.0.9 and 4.1.13 changelogs.