UNION of SELECTs contains spurious records
am 06.05.2003 17:24:44 von Vincenzo CiaschiniFrom: marotta
To: mysql@lists.mysql.com
Subject: UNION of SELECTs contains spurious records
>Description:
The UNION of two SELECTs contains lines that were not returned by none
of the two different SELECTs.
>How-To-Repeat:
Create a DB from the following dump:
>>>START DUMP
-- 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)
Obviously wrong
>>
>Fix:
>Submitter-Id:
>Originator:
>Organization:
>MySQL support: none
>Synopsis: Problems with UNION statement
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.12 (Official MySQL RPM)
>Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.12, for pc-linux on
i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.12
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 8 days 2 hours 16 min 56 sec
Threads: 1 Questions: 1641 Slow queries: 0 Opens: 375 Flush tables:
1 Open tables: 64 Queries per second avg: 0.002
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux datatag6 2.4.18-3smp #1 SMP Thu Apr 18 07:27:31 EDT 2002
i686 unknown
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs
gcc version 2.96 20000731 (Red Hat Linux 7.3 2.96-113)
Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer
-mpentium' CXX='g++' CXXFLAGS='-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 gen 13 15:55 /lib/libc.so.6
-> libc-2.2.5.so
-rwxr-xr-x 1 root root 1260480 ott 10 2002
/lib/libc-2.2.5.so
-rw-r--r-- 1 root root 2312442 ott 10 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 ott 10 2002 /usr/lib/libc.so
Configure command: ./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--without-berkeley-db' '--with-innodb' '--without-vio'
'--without-openssl' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/'
'--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--with-embedded-server' '--enable-thread-safe-client'
'--with-comment=Official MySQL RPM' 'CFLAGS=-O6 -fno-omit-frame-pointer
-mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer
-felide-constructors -fno-exceptions -fno-rtti -mpentium'
--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org