Description:
COALESCE returning a zero instead of first non-null value. I managed a work around with a couple of nested IF statements.
I accept that this may be fixed in the latest version but felt it best to report it anyway in case not. I currently have 4.0.21 which I realise is not very up to date but it's a critical live system and don't want to go messing with database upgrades just yet.
How to repeat:
Run this query on the tables (dumps below - they're only small, fortunately, because there's just some test data in there). You can see the incorrect results I obtained pasted below.
mysql> SELECT a.val_int,a.val_text,a.val_date,
-> COALESCE(a.val_int,a.val_text,a.val_date) AS coalesce_val,
-> IF (a.val_int IS NULL,IF(a.val_text IS NULL,a.val_date,a.val_text),a.val_int) AS if_val,
-> a.ID AS attrID,
-> a.dni,ac.class AS attr_class,ad.attr_name,
-> COUNT(f.facility_typeID) AS num_facils
-> FROM skycom_facilities_attrs a
-> INNER JOIN skycom_facilities_attr_defs ad ON ad.ID=a.attr_typeID
-> INNER JOIN skycom_facilities_attr_classes ac ON ac.ID=ad.classID
-> LEFT JOIN skycom_facilities f ON f.facility_typeID IN (4) AND f.dni=a.dni AND f.custID=a.custID AND f.status='active'
-> WHERE a.custID='16526'
-> GROUP BY a.ID
-> HAVING num_facils=0;
+---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+
| val_int | val_text | val_date | coalesce_val | if_val | attrID | dni | attr_class | attr_name | num_facils |
+---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+
| 1 | NULL | NULL | 1 | 1 | 15 | 448452247805 | DNIENBL | active | 0 |
| NULL | scissors | NULL | 0 | scissors | 16 | 448452247805 | DNIENBL | things | 0 |
| NULL | shirt | NULL | 0 | shirt | 17 | 448452247805 | DNIENBL | things | 0 |
| NULL | bearing | NULL | 0 | bearing | 18 | 448452247805 | DNIENBL | things | 0 |
+---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+
4 rows in set (0.00 sec)
-- MySQL dump 9.11
--
-- Host: localhost Database: domainsp2
-- ------------------------------------------------------
-- Server version 4.0.21
--
-- Table structure for table `skycom_facilities_attrs`
--
CREATE TABLE `skycom_facilities_attrs` (
`ID` int(10) unsigned NOT NULL auto_increment,
`custID` int(10) unsigned NOT NULL default '0',
`dni` bigint(20) unsigned NOT NULL default '0',
`attr_typeID` smallint(5) unsigned NOT NULL default '0',
`val_int` bigint(20) default NULL,
`val_text` text,
`val_date` date default NULL,
`status` enum('active','suspended') default 'active',
PRIMARY KEY (`ID`),
KEY `custID` (`custID`,`dni`)
) TYPE=MyISAM;
--
-- Dumping data for table `skycom_facilities_attrs`
--
INSERT INTO `skycom_facilities_attrs` VALUES (15,16526,448452247805,1,1,NULL,NULL,'suspended');
INSERT INTO `skycom_facilities_attrs` VALUES (16,16526,448452247805,2,NULL,'scissors',NULL,'suspended');
INSERT INTO `skycom_facilities_attrs` VALUES (17,16526,448452247805,2,NULL,'shirt',NULL,'suspended');
INSERT INTO `skycom_facilities_attrs` VALUES (18,16526,448452247805,2,NULL,'bearing',NULL,'suspended');
--
-- Table structure for table `skycom_facilities_attr_defs`
--
CREATE TABLE `skycom_facilities_attr_defs` (
`ID` smallint(5) unsigned NOT NULL auto_increment,
`classID` smallint(5) unsigned NOT NULL default '0',
`attr_name` varchar(25) NOT NULL default '',
`datatype` enum('int','text','date') NOT NULL default 'int',
`present_as` enum('scalar','array') NOT NULL default 'scalar',
`val_min` text,
`val_max` text,
`len_min` smallint(5) unsigned default NULL,
`len_max` smallint(5) unsigned default NULL,
`count_min` smallint(5) unsigned default NULL,
`count_max` smallint(5) unsigned default NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAM;
--
-- Dumping data for table `skycom_facilities_attr_defs`
--
INSERT INTO `skycom_facilities_attr_defs` VALUES (1,1,'active','int','scalar','0','1',NULL,NULL,NULL,1);
INSERT INTO `skycom_facilities_attr_defs` VALUES (2,1,'things','text','array',NULL,NULL,NULL,15,NULL,NULL);
--
-- Table structure for table `skycom_facilities_attr_classes`
--
CREATE TABLE `skycom_facilities_attr_classes` (
`ID` smallint(5) unsigned NOT NULL auto_increment,
`class` varchar(25) NOT NULL default '',
PRIMARY KEY (`ID`)
) TYPE=MyISAM;
--
-- Dumping data for table `skycom_facilities_attr_classes`
--
INSERT INTO `skycom_facilities_attr_classes` VALUES (1,'DNIENBL');
Description: COALESCE returning a zero instead of first non-null value. I managed a work around with a couple of nested IF statements. I accept that this may be fixed in the latest version but felt it best to report it anyway in case not. I currently have 4.0.21 which I realise is not very up to date but it's a critical live system and don't want to go messing with database upgrades just yet. How to repeat: Run this query on the tables (dumps below - they're only small, fortunately, because there's just some test data in there). You can see the incorrect results I obtained pasted below. mysql> SELECT a.val_int,a.val_text,a.val_date, -> COALESCE(a.val_int,a.val_text,a.val_date) AS coalesce_val, -> IF (a.val_int IS NULL,IF(a.val_text IS NULL,a.val_date,a.val_text),a.val_int) AS if_val, -> a.ID AS attrID, -> a.dni,ac.class AS attr_class,ad.attr_name, -> COUNT(f.facility_typeID) AS num_facils -> FROM skycom_facilities_attrs a -> INNER JOIN skycom_facilities_attr_defs ad ON ad.ID=a.attr_typeID -> INNER JOIN skycom_facilities_attr_classes ac ON ac.ID=ad.classID -> LEFT JOIN skycom_facilities f ON f.facility_typeID IN (4) AND f.dni=a.dni AND f.custID=a.custID AND f.status='active' -> WHERE a.custID='16526' -> GROUP BY a.ID -> HAVING num_facils=0; +---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+ | val_int | val_text | val_date | coalesce_val | if_val | attrID | dni | attr_class | attr_name | num_facils | +---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+ | 1 | NULL | NULL | 1 | 1 | 15 | 448452247805 | DNIENBL | active | 0 | | NULL | scissors | NULL | 0 | scissors | 16 | 448452247805 | DNIENBL | things | 0 | | NULL | shirt | NULL | 0 | shirt | 17 | 448452247805 | DNIENBL | things | 0 | | NULL | bearing | NULL | 0 | bearing | 18 | 448452247805 | DNIENBL | things | 0 | +---------+----------+----------+--------------+----------+--------+--------------+------------+-----------+------------+ 4 rows in set (0.00 sec) -- MySQL dump 9.11 -- -- Host: localhost Database: domainsp2 -- ------------------------------------------------------ -- Server version 4.0.21 -- -- Table structure for table `skycom_facilities_attrs` -- CREATE TABLE `skycom_facilities_attrs` ( `ID` int(10) unsigned NOT NULL auto_increment, `custID` int(10) unsigned NOT NULL default '0', `dni` bigint(20) unsigned NOT NULL default '0', `attr_typeID` smallint(5) unsigned NOT NULL default '0', `val_int` bigint(20) default NULL, `val_text` text, `val_date` date default NULL, `status` enum('active','suspended') default 'active', PRIMARY KEY (`ID`), KEY `custID` (`custID`,`dni`) ) TYPE=MyISAM; -- -- Dumping data for table `skycom_facilities_attrs` -- INSERT INTO `skycom_facilities_attrs` VALUES (15,16526,448452247805,1,1,NULL,NULL,'suspended'); INSERT INTO `skycom_facilities_attrs` VALUES (16,16526,448452247805,2,NULL,'scissors',NULL,'suspended'); INSERT INTO `skycom_facilities_attrs` VALUES (17,16526,448452247805,2,NULL,'shirt',NULL,'suspended'); INSERT INTO `skycom_facilities_attrs` VALUES (18,16526,448452247805,2,NULL,'bearing',NULL,'suspended'); -- -- Table structure for table `skycom_facilities_attr_defs` -- CREATE TABLE `skycom_facilities_attr_defs` ( `ID` smallint(5) unsigned NOT NULL auto_increment, `classID` smallint(5) unsigned NOT NULL default '0', `attr_name` varchar(25) NOT NULL default '', `datatype` enum('int','text','date') NOT NULL default 'int', `present_as` enum('scalar','array') NOT NULL default 'scalar', `val_min` text, `val_max` text, `len_min` smallint(5) unsigned default NULL, `len_max` smallint(5) unsigned default NULL, `count_min` smallint(5) unsigned default NULL, `count_max` smallint(5) unsigned default NULL, PRIMARY KEY (`ID`) ) TYPE=MyISAM; -- -- Dumping data for table `skycom_facilities_attr_defs` -- INSERT INTO `skycom_facilities_attr_defs` VALUES (1,1,'active','int','scalar','0','1',NULL,NULL,NULL,1); INSERT INTO `skycom_facilities_attr_defs` VALUES (2,1,'things','text','array',NULL,NULL,NULL,15,NULL,NULL); -- -- Table structure for table `skycom_facilities_attr_classes` -- CREATE TABLE `skycom_facilities_attr_classes` ( `ID` smallint(5) unsigned NOT NULL auto_increment, `class` varchar(25) NOT NULL default '', PRIMARY KEY (`ID`) ) TYPE=MyISAM; -- -- Dumping data for table `skycom_facilities_attr_classes` -- INSERT INTO `skycom_facilities_attr_classes` VALUES (1,'DNIENBL');