Re: MySQL BUG - UNION inconsistency in server 4.1.1

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.

Thanks you in advance.

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


Regards,

For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/

Miguel Angel Sol=F3rzano
S=E3o Paulo - Brazil



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

Re: MySQL BUG - UNION inconsistency in server 4.1.1

At 18:56 2/4/2004, Giuseppe Maxia wrote:
Hi,

Bellow the output with a windows 4.1.2 server built from
source some 5 hours ago:

C:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.1.2-alpha-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> insert into tab1 values (7,"ABBIATEGRASSO"),
-> (72,"AGRATE BRIANZA"), (78,"AICURZIO");
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> insert into tab2 values (7,"MI"),
-> (72,"MI"), (78,"MI");
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> SELECT tab1.ID, name
-> FROM tab1
-> INNER JOIN tab2 ON tab1.ID =3D tab2.ID
-> WHERE ID2 =3D 'MI';
+----+----------------+
| ID | name |
+----+----------------+
| 7 | ABBIATEGRASSO |
| 72 | AGRATE BRIANZA |
| 78 | AICURZIO |
+----+----------------+
3 rows in set (0.03 sec)

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

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

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

mysql> select * from tab1;
+----+----------------+
| ID | name |
+----+----------------+
| 7 | ABBIATEGRASSO |
| 72 | AGRATE BRIANZA |
| 78 | XXX |
+----+----------------+
3 rows in set (0.00 sec)

mysql> alter table tab1 type=3Dinnodb;
Query OK, 3 rows affected, 1 warning (0.28 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> alter table tab2 type=3Dinnodb;
Query OK, 3 rows affected, 1 warning (0.26 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> alter table tab3 type=3Dinnodb;
Query OK, 0 rows affected, 1 warning (0.21 sec)
Records: 0 Duplicates: 0 Warnings: 0

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

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

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

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

Regards,

For technical support contracts, visit https://order.mysql.com/
Are you MySQL certified?, http://www.mysql.com/certification/

Miguel Angel Sol=F3rzano
S=E3o Paulo - Brazil



--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=3Dgcdmb-bugs@m.gmane.org

MySQL BUG - UNION inconsistency in server 4.1.1

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.maxia@stardata.it
To: mysql@lists.mysql.com
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:

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/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: MySQL BUG - UNION inconsistency in server 4.1.1

--=-mVyTcxchrQu9heayk7lm
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

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


--=-mVyTcxchrQu9heayk7lm
Content-Disposition: attachment; filename=test_case_4.1.1.txt
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=ISO-8859-1

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=3DMyISAM DEFAULT CHARSET=3Dlatin1
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=3DMyISAM DEFAULT CHARSET=3Dlatin1
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=3DMyISAM DEFAULT CHARSET=3Dlatin1
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=3DMyISAM DEFAULT CHARSET=3Dlatin1
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>=20
SELECT tab1.ID, name=20
FROM tab1=20
INNER JOIN tab2 ON tab1.ID =3D tab2.ID=20
WHERE ID2 =3D 'MI';
+----+------+
| ID | name |
+----+------+
| 7 | AAA |
| 72 | BBB |
| 78 | CCC |
+----+------+
3 rows in set (0.00 sec)

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

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

mysql>=20
SELECT tab1.ID, name=20
FROM tab1=20
INNER JOIN tab2 ON tab1.ID =3D tab2.ID=20
WHERE ID2 =3D 'MI'=20
UNION=20
SELECT tab1.ID, name2 AS name=20
FROM tab3=20
INNER JOIN tab2 ON tab2.ID =3D tab3.ID=20
INNER JOIN tab1 ON tab1.ID =3D tab2.ID=20
WHERE ID2 =3D '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)


--=-mVyTcxchrQu9heayk7lm
Content-Disposition: attachment; filename=test_case_4.1.1a.txt
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=ISO-8859-1

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=3DInnoDB DEFAULT CHARSET=3Dlatin1
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=3DInnoDB DEFAULT CHARSET=3Dlatin1
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=3DInnoDB DEFAULT CHARSET=3Dlatin1
1 row in set (0.00 sec)

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

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

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


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


--=-mVyTcxchrQu9heayk7lm
Content-Disposition: attachment; filename=test_case_4.1.0.txt
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain; charset=ISO-8859-1

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=3DInnoDB CHARSET=3Dlatin1
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=3DInnoDB CHARSET=3Dlatin1
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=3DInnoDB CHARSET=3Dlatin1
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>=20
SELECT tab1.ID, name=20
FROM tab1=20
INNER JOIN tab2 ON tab1.ID =3D tab2.ID=20
WHERE ID2 =3D 'MI';
+----+------+
| ID | name |
+----+------+
| 7 | AAA |
| 72 | BBB |
| 78 | CCC |
+----+------+
3 rows in set (0.00 sec)

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



--=-mVyTcxchrQu9heayk7lm
Content-Type: text/plain; charset=us-ascii

--
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
--=-mVyTcxchrQu9heayk7lm--