Bug #386 UNION returning records records which are not returned by underlaying selects
Submitted: 7 May 2003 5:22 Modified: 13 May 2003 8:49
Reporter: Peter Zaitsev (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.0.13 OS:Linux (Linux)
Assigned to: Michael Widenius CPU Architecture:Any

[7 May 2003 5:22] Peter Zaitsev
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)
[13 May 2003 8:49] Michael Widenius
Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html