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 = tab2.ID
     -> WHERE ID2 = '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 = 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 | ABBIATEGRASSO  |
| 72 | AGRATE BRIANZA |
| 78 | AICURZIO       |
+----+----------------+
3 rows in set (0.03 sec)

mysql> update tab1 set name = 'XXX' where ID = 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 = 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 | 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=innodb;
Query OK, 3 rows affected, 1 warning (0.28 sec)
Records: 3  Duplicates: 0  Warnings: 0

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

mysql> alter table tab3 type=innodb;
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 = 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 | 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 = tab2.ID
     -> WHERE ID2 = '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 = 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 | 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 = 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 ;
+----+----------------+
| 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órzano <[email protected]>
São Paulo - Brazil



-- 
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe:    http://lists.mysql.com/[email protected]