Re: MySQL BUG - UNION inconsistency in server 4.1.1

Giuseppe Maxia <[email protected]>
Newsgroups gmane.comp.db.mysql.bugs
Message-ID <1080942963.3098.104.camel@LTGmax> 
On Thu, 2004-04-01 at 23:47, Miguel Angel Solorzano wrote:
> At 18:29 2/4/2004, Giuseppe Maxia wrote:
> Hi,
> >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.
> 
> Yes please provide a complete test case, opening a bug report.
> 

The test case was already embedded in my previous message.
Anyway I attach a second copy here.

Regards

Giuseppe

 
-- 
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]
test_case_4.1.1.txt (text/plain, 2.9 KB) 
Test case with server 4.1.1 - MyIsam tables
-------------------------------------------

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 | ABBIATEGRASSO  |
| 72 | AGRATE BRIANZA |
| 78 | AICURZIO       |
+----+----------------+
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)

mysql> update tab1 set name = 'XXX' where ID = 78;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

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 | XXX  |
| 72 | XXX  |
| 78 | XXX  |
+----+------+
3 rows in set (0.00 sec)

mysql> select * from tab1;
+----+------+
| ID | name |
+----+------+
|  7 | AAA  |
| 72 | BBB  |
| 78 | XXX  |
+----+------+
3 rows in set (0.01 sec)
test_case_4.1.1a.txt (text/plain, 2.5 KB) 
Test case with server 4.1.1 - InnoDB tables
-------------------------------------------

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> 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 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)
test_case_4.1.0.txt (text/plain, 1.9 KB) 
Test case with server 4.1.0 - InnoDB tables
-------------------------------------------

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)
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.