Description:
2 tables with similar items, named USERCHECKITEM and SERVICECHECKITEM, both containg Attribute/Value pairs for resp. a user and a service.
Using UNION I wish to retreive the Attribute value pairs for a certain service for a certain user, excluding the a/v pair(s) from SERVICECHECKITEM for the same attribute already defined for the user.
The query works fine on MySQL 4.0, but returns empty values for some fields on 4.1.1.
Tim.
How to repeat:
Setup:
CREATE TABLE SERVICECHECKITEM (
ID int(11) NOT NULL auto_increment,
SVC_ID int(11) NOT NULL default '0',
Attribute varchar(255) NOT NULL default '',
Value varchar(255) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO SERVICECHECKITEM VALUES (2, 2, 'adsl_check_attr_1', 'adsl_check_val_1');
INSERT INTO SERVICECHECKITEM VALUES (3, 2, 'adsl_check_attr_2', 'adsl_check_val_2');
INSERT INTO SERVICECHECKITEM VALUES (4, 2, 'adsl_check_attr_3', 'adsl_check_val_3');
CREATE TABLE USERCHECKITEM (
ID int(11) NOT NULL auto_increment,
SVC_ID int(11) NOT NULL default '0',
USR_ID int(11) NOT NULL default '0',
Attribute varchar(255) NOT NULL default '',
Value varchar(255) NOT NULL default '',
PRIMARY KEY (ID)
) TYPE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO USERCHECKITEM VALUES (1, 2, 1, 'adsl_check_attr_1', 'adsl_check_val_1b');
INSERT INTO USERCHECKITEM VALUES (2, 2, 1, 'adsl_check_attr_2', 'adsl_check_val_2b');
Query:
(
SELECT U.Attribute, U.Value
FROM USERCHECKITEM U
WHERE USR_ID=1 AND SVC_ID =2
)
UNION
(
SELECT S.Attribute, S.Value
FROM SERVICECHECKITEM S
LEFT JOIN USERCHECKITEM U ON ( S.Attribute = U.Attribute AND U.SVC_ID = S.SVC_ID AND U.USR_ID =1 )
WHERE U.Attribute IS NULL AND S.SVC_ID =2
)
Expected result: (as shown by MySQL 4.0)
+-------------------+-------------------+
| Attribute | Value |
+-------------------+-------------------+
| adsl_check_attr_1 | adsl_check_val_1b |
| adsl_check_attr_2 | adsl_check_val_2b |
| adsl_check_attr_3 | adsl_check_val_3 |
+-------------------+-------------------+
Erronous result: (4.1.1)
+-------------------+------------------+
| Attribute | Value |
+-------------------+------------------+
| adsl_check_attr_1 | |
| adsl_check_attr_2 | |
| adsl_check_attr_3 | adsl_check_val_3 |
+-------------------+------------------+
