MySQL BUG - UNION inconsistency in server 4.1.1

Giuseppe Maxia <[email protected]>
Newsgroups gmane.comp.db.mysql.bugs
Message-ID <1080941359.3105.76.camel@LTGmax> 
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: [email protected]
To: [email protected]
Subject: UNION inconsistent results in MySQL 4.1.1

>Description:
	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)
>How-To-Repeat:
	
##############################
########## 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)


>Fix:
    No known fix, except using temporary tables instead of UNION

>Submitter-Id:	gmax
>Originator:	Giuseppe Maxia
>Organization:
 Stardata
>MySQL support: email support
>Synopsis:	Inconsisten UNION results
>Severity:	critical 
>Priority:	high
>Category:	mysql
>Class:		sw-bug
>Release:	mysql-4.1.1-alpha-standard (Official MySQL RPM)
>Server: /usr/bin/mysqladmin  Ver 8.40 Distrib 4.1.1-alpha, 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.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
>C compiler:    2.95.3
>C++ compiler:  2.95.3
>Environment:
	<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='


-- 
Giuseppe Maxia
CTO
http://www.StarData.it
       __              __     __
  ___ / /____ ________/ /__ _/ /____ _
 (_-</ __/ _ `/ __/ _  / _ `/ __/ _ `/
/___/\__/\_,_/_/  \_,_/\_,_/\__/\_,_/
       Database is our business

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



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.