Bug #56899 IFNULL Evaluation inside CONCAT_WS Incorrect
Submitted: 21 Sep 2010 15:43 Modified: 21 Sep 2010 18:01
Reporter: Tobin Cataldo Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5.5-m3-log OS:Any (XP Pro)
Assigned to: CPU Architecture:Any
Tags: Concat_WS, ifnull, left join, order by

[21 Sep 2010 15:43] Tobin Cataldo 
Description:
A CONCAT_WS containing an IFNULL evaluation of a value from a LEFT JOIN is incorrect when coupled with an ORDER BY statement. 

Dropping the ORDER BY returns correct results. 
Substituting a CONCAT for the CONCAT_WS returns correct results.

mysql> DROP TABLE IF EXISTS `booklist_categories`;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `booklist_categories` (
    ->   `category_id` smallint(2) NOT NULL AUTO_INCREMENT,
    ->   `category` varchar(20) NOT NULL,
    ->   `sort_order` smallint(2) NOT NULL,
    ->   PRIMARY KEY (`category_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `booklist_categories` VALUES (1,'Easy Fiction/Picture',1),(2,'Youth Fiction',2),(3,'Teen Fiction',3),(4,'Youth Non-Fiction',4),(5,'Youth Biography',5),(6,'Adult Fiction',6),(7,'Adult Non-Fiction',7),(8,'Too New to Review',8);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> DROP TABLE IF EXISTS `booklist_subcategories`;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE `booklist_subcategories` (
    ->   `subcategory_id` smallint(2) NOT NULL AUTO_INCREMENT,
    ->   `subcategory` varchar(20) NOT NULL,
    ->   `category_id` smallint(2) NOT NULL,
    ->   PRIMARY KEY (`subcategory_id`),
    ->   KEY `cat` (`category_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO `booklist_subcategories` VALUES (1,'Arts',7),(2,'Biography',7),(3,'General Interest',7),(4,'Health',7),(5,'History',7);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

============ INCORRECT ============
mysql> SELECT CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`
    -> ORDER BY `booklist_categories`.`sort_order` ASC, `booklist_subcategories`.`subcategory` ASC;
+------+
| test |
+------+
| 1:   |
| 2:   |
| 3:   |
| 4:   |
| 5:   |
| 6:   |
| 7:   |
| 7:   |
| 7:   |
| 7:   |
| 7:   |
| 8:   |
+------+
12 rows in set (0.00 sec)

============ CORRECT (WITHOUT SORT) ============

mysql> 
mysql> SELECT CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`;
+------+
| test |
+------+
| 1:0  |
| 2:0  |
| 3:0  |
| 4:0  |
| 5:0  |
| 6:0  |
| 7:1  |
| 7:2  |
| 7:3  |
| 7:4  |
| 7:5  |
| 8:0  |
+------+
12 rows in set (0.00 sec)

============ CORRECT ============

mysql> 
mysql> SELECT CONCAT(`booklist_categories`.`category_id`,':', IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`
    -> ORDER BY `booklist_categories`.`sort_order` ASC, `booklist_subcategories`.`subcategory` ASC;
+------+
| test |
+------+
| 1:0  |
| 2:0  |
| 3:0  |
| 4:0  |
| 5:0  |
| 6:0  |
| 7:1  |
| 7:2  |
| 7:3  |
| 7:4  |
| 7:5  |
| 8:0  |
+------+
12 rows in set (0.00 sec)

mysql> notee

How to repeat:
DROP TABLE IF EXISTS `booklist_categories`;
CREATE TABLE `booklist_categories` (
  `category_id` smallint(2) NOT NULL AUTO_INCREMENT,
  `category` varchar(20) NOT NULL,
  `sort_order` smallint(2) NOT NULL,
  PRIMARY KEY (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `booklist_categories` VALUES (1,'Easy Fiction/Picture',1),(2,'Youth Fiction',2),(3,'Teen Fiction',3),(4,'Youth Non-Fiction',4),(5,'Youth Biography',5),(6,'Adult Fiction',6),(7,'Adult Non-Fiction',7),(8,'Too New to Review',8);

DROP TABLE IF EXISTS `booklist_subcategories`;
CREATE TABLE `booklist_subcategories` (
  `subcategory_id` smallint(2) NOT NULL AUTO_INCREMENT,
  `subcategory` varchar(20) NOT NULL,
  `category_id` smallint(2) NOT NULL,
  PRIMARY KEY (`subcategory_id`),
  KEY `cat` (`category_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `booklist_subcategories` VALUES (1,'Arts',7),(2,'Biography',7),(3,'General Interest',7),(4,'Health',7),(5,'History',7);

SELECT CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
FROM `booklist_categories`
LEFT JOIN `booklist_subcategories`
ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`
ORDER BY `booklist_categories`.`sort_order` ASC, `booklist_subcategories`.`subcategory` ASC;

SELECT CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
FROM `booklist_categories`
LEFT JOIN `booklist_subcategories`
ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`;

SELECT CONCAT(`booklist_categories`.`category_id`,':', IFNULL(`booklist_subcategories`.`subcategory_id`, 0)) as 'test'
FROM `booklist_categories`
LEFT JOIN `booklist_subcategories`
ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`
ORDER BY `booklist_categories`.`sort_order` ASC, `booklist_subcategories`.`subcategory` ASC;
[21 Sep 2010 17:05] Valeriy Kravchuk 
Please, check with a newer version, 5.5.6. Not repeatable for me with current mysql-5.5 tree from bzr:

macbook-pro:5.5 openxs$ bin/mysql -uroot testReading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.7-rc-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> DROP TABLE IF EXISTS `booklist_categories`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `booklist_categories` (
    ->   `category_id` smallint(2) NOT NULL AUTO_INCREMENT,
    ->   `category` varchar(20) NOT NULL,
    ->   `sort_order` smallint(2) NOT NULL,
    ->   PRIMARY KEY (`category_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `booklist_categories` VALUES (1,'Easy Fiction/Picture',1),(2,'Youth
    '> Fiction',2),(3,'Teen Fiction',3),(4,'Youth Non-Fiction',4),(5,'Youth
    '> Biography',5),(6,'Adult Fiction',6),(7,'Adult Non-Fiction',7),(8,'Too New to Review',8);
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> DROP TABLE IF EXISTS `booklist_subcategories`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `booklist_subcategories` (
    ->   `subcategory_id` smallint(2) NOT NULL AUTO_INCREMENT,
    ->   `subcategory` varchar(20) NOT NULL,
    ->   `category_id` smallint(2) NOT NULL,
    ->   PRIMARY KEY (`subcategory_id`),
    ->   KEY `cat` (`category_id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO `booklist_subcategories` VALUES (1,'Arts',7),(2,'Biography',7),(3,'General
    '> Interest',7),(4,'Health',7),(5,'History',7);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT
    -> CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`,
    -> 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`
    -> ORDER BY `booklist_categories`.`sort_order` ASC, `booklist_subcategories`.`subcategory`
    -> ASC;
+------+
| test |
+------+
| 1:0  |
| 2:0  |
| 3:0  |
| 4:0  |
| 5:0  |
| 6:0  |
| 7:1  |
| 7:2  |
| 7:3  |
| 7:4  |
| 7:5  |
| 8:0  |
+------+
12 rows in set (0.00 sec)

mysql> SELECT
    -> CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`,
    -> 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` = `booklist_subcategories`.`category_id`;
+------+
| test |
+------+
| 1:0  |
| 2:0  |
| 3:0  |
| 4:0  |
| 5:0  |
| 6:0  |
| 7:1  |
| 7:2  |
| 7:3  |
| 7:4  |
| 7:5  |
| 8:0  |
+------+
12 rows in set (0.00 sec)
[21 Sep 2010 18:01] Tobin Cataldo 
You are correct, fixed in 5.5.6.

mysql> SELECT
    -> CONCAT_WS(':',`booklist_categories`.`category_id`,IFNULL(`booklist_subcategories`.`subcategory_id`,
    -> 0)) as 'test'
    -> FROM `booklist_categories`
    -> LEFT JOIN `booklist_subcategories`
    -> ON `booklist_categories`.`category_id` =
    -> `booklist_subcategories`.`category_id`
    -> ORDER BY `booklist_categories`.`sort_order` ASC,
    -> `booklist_subcategories`.`subcategory` ASC;
+------+
| test |
+------+
| 1:0  |
| 2:0  |
| 3:0  |
| 4:0  |
| 5:0  |
| 6:0  |
| 7:1  |
| 7:2  |
| 7:3  |
| 7:4  |
| 7:5  |
| 8:0  |
+------+
12 rows in set (0.00 sec)
[27 Feb 2012 10:03] Orestis Karras 
At mysql version 5.5.20 the following query get error.

I think CONCAT_WS statement is the issue. Any help?

INSERT INTO jos_vm_product (vendor_id, product_parent_id, product_sku, product_name, product_s_desc, product_desc, product_weight_uom, product_lwh_uom,  product_available_date, product_availability,  product_in_stock, product_discount_id, product_tax_id, product_unit, child_options, quantity_options, product_order_levels, product_ean, product_rank, product_intrastat, product_warranty, product_url, product_thumb_image, product_full_image, '1') 
SELECT
 '1', '0', product_sku, 
product_name, product_s_desc, CONCAT_WS ( ',' ,category1 , category2, category3 , '<br />Κατασκευαστής :' , ventor_name ) AS product_desc , 'Κιλά', 'Εκατοστά', product_arrival,  product_availability, product_in_stock,  '0', '4', 'Τεμάχιο', 'N,N,N,N,N,Y,20%,10%,', 'none,0,0,1', '0,0', product_ean, product_rank, product_intrastat, product_warranty, product_data_sheet, product_image, product_image, product_auto_insert FROM newproducts