Hi,

There is a critical bug in MySQL server 4.1.1. Basically, it happens that when a UNION is combined with JOINs, it gives back inconsistent results. I am not sure that this is a universal description of this bug, but I can provide a replication case. The following example will explain better than any words. The wrong behavior is only showing up in 4.1.1, while in 4.1.0 it works as expected.

Best regards

Giuseppe Maxia

From: g.ma...@stardata.it To: mys...@lists.mysql.com Subject: UNION inconsistent results in MySQL 4.1.1

A Union with JOINs returns inconsistent values, giving for each row either NULLs or the last value in the table. (Notice that the problem is only in 4.1.1, not in 4.1.0)

############################## ########## FIRST CASE: MyISAM ##############################

mysql> show create table tab1\G *************************** 1. row *************************** Table: tab1 Create Table: CREATE TABLE `tab1` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`ID`), KEY `name` (`name`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table tab2\G *************************** 1. row *************************** Table: tab2 Create Table: CREATE TABLE `tab2` ( `ID` int(11) NOT NULL default '0', `ID2` char(2) default NULL, PRIMARY KEY (`ID`), KEY `ID2` (`ID2`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table tab3\G *************************** 1. row *************************** Table: tab3 Create Table: CREATE TABLE `tab3` ( `ID` int(11) NOT NULL default '0', `name2` varchar(50) NOT NULL default '' ) TYPE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> select * from tab1; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> show create table tab2\G *************************** 1. row *************************** Table: tab2 Create Table: CREATE TABLE `tab2` ( `ID` int(11) NOT NULL default '0', `ID2` char(2) default NULL, PRIMARY KEY (`ID`), KEY `ID2` (`ID2`) ) TYPE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> select * from tab2; +-----+------+ | ID | ID2 | +-----+------+ | 7 | MI | | 72 | MI | | 78 | MI | +-----+------+ 3 rows in set (0.00 sec)

mysql> select * from tab3; Empty set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI' UNION SELECT tab1.ID, name2 AS name FROM tab3 INNER JOIN tab2 ON tab2.ID = tab3.ID INNER JOIN tab1 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | CCC | | 72 | CCC | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

############################## ########## SECOND CASE: InnoDB ##############################

mysql> show create table tab1\G *************************** 1. row *************************** Table: tab1 Create Table: CREATE TABLE `tab1` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`ID`), KEY `name` (`name`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table tab2\G *************************** 1. row *************************** Table: tab2 Create Table: CREATE TABLE `tab2` ( `ID` int(11) NOT NULL default '0', `ID2` char(2) default NULL, PRIMARY KEY (`ID`), KEY `ID2` (`ID2`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.01 sec)

mysql> show create table tab3\G *************************** 1. row *************************** Table: tab3 Create Table: CREATE TABLE `tab3` ( `ID` int(11) NOT NULL default '0', `name2` varchar(50) NOT NULL default '', PRIMARY KEY (`ID`,`name2`) ) TYPE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec)

mysql> select * from tab1; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> select * from tab2; +----+------+ | ID | ID2 | +----+------+ | 7 | MI | | 72 | MI | | 78 | MI | +----+------+ 3 rows in set (0.00 sec)

mysql> select * from tab3; Empty set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI' UNION SELECT tab1.ID, name2 AS name FROM tab3 INNER JOIN tab2 ON tab2.ID = tab3.ID INNER JOIN tab1 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | NULL | | 72 | NULL | | 78 | NULL | +----+------+ 3 rows in set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI' UNION SELECT tab1.ID, name2 AS name FROM tab3 INNER JOIN tab2 ON tab2.ID = tab3.ID INNER JOIN tab1 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | NULL | | 72 | NULL | | 78 | NULL | +----+------+ 3 rows in set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI' UNION SELECT tab1.ID, name2 AS name FROM tab3 JOIN tab2 ON tab2.ID = tab3.ID JOIN tab1 ON tab1.ID = tab2.ID ; Empty set (0.00 sec)

############################################# ### THIRD CASE: the correct behavior in 4.1.0 #############################################

mysql> show create table tab1\G *************************** 1. row *************************** Table: tab1 Create Table: CREATE TABLE `tab1` ( `ID` int(11) NOT NULL auto_increment, `name` varchar(50) default NULL, PRIMARY KEY (`ID`), KEY `name` (`name`) ) TYPE=InnoDB CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table tab2\G *************************** 1. row *************************** Table: tab2 Create Table: CREATE TABLE `tab2` ( `ID` int(11) NOT NULL default '0', `ID2` char(2) default NULL, PRIMARY KEY (`ID`), KEY `ID2` (`ID2`) ) TYPE=InnoDB CHARSET=latin1 1 row in set (0.00 sec)

mysql> show create table tab3\G *************************** 1. row *************************** Table: tab3 Create Table: CREATE TABLE `tab3` ( `ID` int(11) NOT NULL default '0', `name2` varchar(50) NOT NULL default '', PRIMARY KEY (`ID`,`name2`) ) TYPE=InnoDB CHARSET=latin1 1 row in set (0.00 sec)

mysql> select * from tab1; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> select * from tab2; +----+------+ | ID | ID2 | +----+------+ | 7 | MI | | 72 | MI | | 78 | MI | +----+------+ 3 rows in set (0.00 sec)

mysql> select * from tab3; Empty set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

mysql> SELECT tab1.ID, name FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE ID2 = 'MI' UNION SELECT tab1.ID, name2 AS name FROM tab3 INNER JOIN tab2 ON tab2.ID = tab3.ID INNER JOIN tab1 ON tab1.ID = tab2.ID WHERE ID2 = 'MI'; +----+------+ | ID | name | +----+------+ | 7 | AAA | | 72 | BBB | | 78 | CCC | +----+------+ 3 rows in set (0.00 sec)

No known fix, except using temporary tables instead of UNION

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.1.1-alpha-standard Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 15 min 44 sec

Threads: 2 Questions: 226 Slow queries: 0 Opens: 174 Flush tables: 1 Open
tables: 64 Queries per second avg: 0.239

<machine, os, target, libraries (multiple lines)> System: Linux LTGmax 2.4.18-6mdk #1 Fri Mar 15 02:59:08 CET 2002 i686 unknown Architecture: i686

Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i586-mandrake-linux-gnu/2.96/specs gcc version 2.96 20000731 (Mandrake Linux 8.2 2.96-0.76mdk) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce'
CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce
-felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 Sep 17 2002 /lib/libc.so.6 ->
libc-2.2.4.so -rwxr-xr-x 1 root root 1275300 Mar 7 2002 /lib/libc-2.2.4.so -rw-r--r-- 1 root root 27274138 Mar 7 2002 /usr/lib/libc.a -rw-r--r-- 1 root root 178 Mar 7 2002 /usr/lib/libc.so Configure command: ./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--with-server-suffix=-standard' '--without-embedded-server'
'--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'
'--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share'
'--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC='
'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486
-fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti
' 'CXX='

Speaker at the 2nd MySQL Users Conference, Orlando, FL, 2004 http://www.mysql.com/events/uc2004/schedule-wednesday.html