Description:
UNION returns different record than matched by original underlaying subselect.
Note this is not general size/type conversion issue we have in MySQL 4.0 UNION handling as changing the order of queries in UNION still leads to the same buggy result.
How to repeat:
-- MySQL dump 9.07
--
-- Host: localhost Database: vomsvo1
---------------------------------------------------------
-- Server version 4.0.12
--
-- Table structure for table 'ca'
--
CREATE TABLE c (
cid smallint(5) unsigned NOT NULL default '0',
cv varchar(250) NOT NULL default '',
PRIMARY KEY (cid),
UNIQUE KEY cv (cv)
) TYPE=InnoDB;
--
-- Dumping data for table 'ca'
--
INSERT INTO c VALUES (8,'dummy');
--
-- Table structure for table 'capabilities'
--
CREATE TABLE cap (
cid bigint(20) unsigned NOT NULL auto_increment,
cap varchar(255) NOT NULL default '',
PRIMARY KEY (cid),
KEY cap (cap)
) TYPE=InnoDB;
--
-- Dumping data for table 'capabilities'
--
--
-- Table structure for table 'groups'
--
CREATE TABLE g (
gid bigint(20) unsigned NOT NULL auto_increment,
gn varchar(255) NOT NULL default '',
must tinyint(4) default NULL,
PRIMARY KEY (gid),
KEY gn (gn)
) TYPE=InnoDB;
--
-- Dumping data for table 'groups'
--
INSERT INTO g VALUES (1,'V1',NULL);
--
-- Table structure for table 'm'
--
CREATE TABLE m (
uid bigint(20) unsigned NOT NULL default '0',
gid bigint(20) unsigned default NULL,
rid bigint(20) unsigned default NULL,
cid bigint(20) unsigned default NULL,
UNIQUE KEY m (uid,gid,rid,cid),
KEY uid (uid),
KEY rid (rid),
KEY cid (cid),
KEY container (gid,rid,cid)
) TYPE=InnoDB;
--
-- Dumping data for table 'm'
--
INSERT INTO m VALUES (1,1,NULL,NULL);
--
-- Table structure for table 'roles'
--
CREATE TABLE r (
rid bigint(20) unsigned NOT NULL auto_increment,
rl varchar(255) NOT NULL default '',
PRIMARY KEY (rid),
KEY rl (rl)
) TYPE=InnoDB;
--
-- Dumping data for table 'roles'
--
--
-- Table structure for table 'usr'
--
CREATE TABLE u (
uid bigint(20) unsigned NOT NULL auto_increment,
un varchar(250) NOT NULL default '',
uc smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (uid),
UNIQUE KEY nc (un,uc),
KEY un (un)
) TYPE=InnoDB;
--
-- Dumping data for table 'usr'
--
INSERT INTO u VALUES (1,'test',8);
>>> END DUMP
The execute the following query:
SELECT m.uid, r.rl, g.gn as g1, m.cid, m.gid as gg FROM g, u, c, m left
join r on r.rid = m.rid left join cap on cap.cid = m.cid WHERE
g.gid=m.gid AND u.uid = m.uid AND u.uc = c.cid AND c.cv = "dummy" AND
u.un = "test";
You should get the following output:
+-----+------+----+------+------+
| uid | rl | g1 | cid | gg |
+-----+------+----+------+------+
| 1 | NULL | V1 | NULL | 1 |
+-----+------+----+------+------+
1 row in set (0.00 sec)
Now execute this query:
SELECT m.uid, r.rl, g.gn as g1, m.cid, m.gid as gg FROM g, u, c, m left
join r on r.rid = m.rid left join cap on cap.cid = m.cid WHERE
g.gid=m.gid AND u.uid = m.uid AND g.must IS NOT NULL AND u.uc = c.cid
AND c.cv = "dummy" AND u.un = "test";
You should get the following output:
Empty set (0.00 sec)
Finally, if you execute the following query (the union of the two
above):
(SELECT m.uid, r.rl, g.gn as g1, m.cid, m.gid as gg FROM g, u, c, m left
join r on r.rid = m.rid left join cap on cap.cid = m.cid WHERE
g.gid=m.gid AND u.uid = m.uid AND g.must IS NOT NULL AND u.uc = c.cid
AND c.cv = "dummy" AND u.un = "test") UNION (SELECT m.uid, r.rl, g.gn as
g1, m.cid, m.gid as gg FROM g, u, c, m left join r on r.rid = m.rid left
join cap on cap.cid = m.cid WHERE g.gid=m.gid AND u.uid = m.uid AND
u.uc = c.cid AND c.cv = "dummy" AND u.un = "test");
You get:
+-----+----+----+------+------+
| uid | rl | g1 | cid | gg |
+-----+----+----+------+------+
| 1 | | | NULL | NULL |
+-----+----+----+------+------+
1 row in set (0.00 sec)
