Bug #13033 | Incorrect execution of joins | ||
---|---|---|---|
Submitted: | 7 Sep 2005 10:03 | Modified: | 22 Sep 2005 9:36 |
Reporter: | Ilya Pyatigorskiy | Email Updates: | |
Status: | Won't fix | Impact on me: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.0.24-nt, 4.1.15-BK | OS: | Linux (Linux & Windows) |
Assigned to: | CPU Architecture: | Any |
[7 Sep 2005 10:03]
Ilya Pyatigorskiy
[7 Sep 2005 11:35]
Valeriy Kravchuk
I was able to repeat it on 4.1.15-BK on Linux as described also: [openxs@Fedora 4.1]$ bin/mysql -uroot Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.15-debug Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> CREATE TABLE `T1` ( -> `a` int(11) NOT NULL default '0' -> ) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0,01 sec) mysql> INSERT INTO `T1` VALUES (1); Query OK, 1 row affected (0,00 sec) mysql> CREATE TABLE `T2` ( -> `a` int(11) NOT NULL default '0' -> )) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0,01 sec) mysql> INSERT INTO `T2` VALUES (1); Query OK, 1 row affected (0,00 sec) mysql> ININSERT INTO `T2` VALUES (2); Query OK, 1 row affected (0,00 sec) mysql> CREATE TABLE `T3` ( -> `a` int(11) NOT NULL default '0' -> ) TYPE=MyISAM; Query OK, 0 rows affected, 1 warning (0,01 sec) mysql> INSERT INTO `T3` VALUES (2); Query OK, 1 row affected (0,00 sec) mysql> SELECT * -> FROM T1 -> JOIN T2 ON T1.a = T2.a -> RIGHT JOIN T3 ON T3.a = T1.a; +---+------+---+ | a | a | a | +---+------+---+ | 1 | NULL | 2 | +---+------+---+ 1 row in set (0,02 sec) But, on 5.0.13-BK it works as expected: mysql> SELECT * -> FFROM T1 -> JOIN T2 ON T1.a = T2.a -> RIGHT JOIN T3 ON T3.a = T1.a; +------+------+---+ | a | a | a | +------+------+---+ | NULL | NULL | 2 | +------+------+---+ 1 row in set (0,04 sec) mysql> select version(); +-------------------+ | version() | +-------------------+ | 5.0.13-beta-debug | +-------------------+ 1 row in set (0,00 sec) It should be due to http://dev.mysql.com/doc/mysql/en/news-5-0-12.html: "Natural joins and joins with USING, including outer join variants, now are processed according to the SQL:2003 standard. " So, I am not sure it will be back ported and fixed in 4.0 or 4.1
[7 Sep 2005 14:56]
Brian Aker
Hi! This was fixed for 5.0. The patch for it is rather large and we can not apply it to a production release like 4.1. -Brian