4.0.12: NATURAL LEFT JOIN behaves strangely

>Description:
NATURAL LEFT JOIN in 4.0.12 behaves strangely, I think it is a bug.
It seems that when there are 3 tables all merged by natural left join,
the join is not done by the same columns and the result is a cross join.
It worked well in 3.23.48 (SuSE 8.0).
Maybe the problem is in my SQL queries but I do not think so.

See the descriptions of tables and the contents.

Please reply to my address too (I'm not subscribed to the mailing list).

Regards,
Josef

>How-To-Repeat:
These queries do not work well:

mysql> SELECT host.HID,HostType,FullName,FileName,FileSize,FileDate FROM file NATURAL LEFT JOIN path NATURAL LEFT JOIN host;
+------+----------+----------+----------+----------+-------- -------------+
| HID | HostType | FullName | FileName | FileSize | FileDate |
+------+----------+----------+----------+----------+-------- -------------+
| 5 | 2 | | file.txt | 0 | 2002-04-13 20:58:00 |
| 5 | 2 | | file.txt | 0 | 2002-04-13 20:58:00 |
| 5 | 2 | /dir1 | file.txt | 0 | 2002-04-13 20:58:00 |
| 5 | 2 | /dir1 | file.txt | 0 | 2002-04-13 20:58:00 |
| 5 | 2 | /dir2 | file.txt | 0 | 2002-04-13 20:58:00 |
| 5 | 2 | /dir2 | file.txt | 0 | 2002-04-13 20:58:00 |
+------+----------+----------+----------+----------+-------- -------------+
6 rows in set (0.02 sec)

mysql> SELECT FullName,FileName,FileSize,FileDate FROM file NATURAL LEFT JOIN path NATURAL LEFT JOIN host;
+----------+----------+----------+---------------------+
| FullName | FileName | FileSize | FileDate |
+----------+----------+----------+---------------------+
| | file.txt | 0 | 2002-04-13 20:58:00 |
| | file.txt | 0 | 2002-04-13 20:58:00 |
| /dir1 | file.txt | 0 | 2002-04-13 20:58:00 |
| /dir1 | file.txt | 0 | 2002-04-13 20:58:00 |
| /dir2 | file.txt | 0 | 2002-04-13 20:58:00 |
| /dir2 | file.txt | 0 | 2002-04-13 20:58:00 |
+----------+----------+----------+---------------------+
6 rows in set (0.02 sec)

This query works well:

mysql> SELECT FullName,FileName,FileSize,FileDate FROM file NATURAL LEFT JOIN path;
+----------+----------+----------+---------------------+
| FullName | FileName | FileSize | FileDate |
+----------+----------+----------+---------------------+
| /dir1 | file.txt | 0 | 2002-04-13 20:58:00 |
| /dir2 | file.txt | 0 | 2002-04-13 20:58:00 |
+----------+----------+----------+---------------------+
2 rows in set (0.02 sec)


Here is the contents and structure of tables:

mysql> select * from host;
+-----+-----------+----------+-------------+--------+------- --+-----------+-------+------------+---------------+
| HID | IP | HostType | ExpireCount | Period | Counter | ScanOrder | Flags | LastScan | TotalFileSize |
+-----+-----------+----------+-------------+--------+------- --+-----------+-------+------------+---------------+
| 5 | 127.0.0.1 | 2 | 0 | 1 | 0 | 0 | 12 | 2003-04-13 | 0 B |
+-----+-----------+----------+-------------+--------+------- --+-----------+-------+------------+---------------+
1 row in set (0.01 sec)

mysql> select * from path;
+-----+------------+-----+-----+-----------+----------+
| PID | Hash | HID | SID | ShortName | FullName |
+-----+------------+-----+-----+-----------+----------+
| 4 | 767742221 | 5 | 0 | | |
| 5 | 3999254257 | 5 | 0 | dir1 | /dir1 |
| 6 | 2002163531 | 5 | 0 | dir2 | /dir2 |
+-----+------------+-----+-----+-----------+----------+
3 rows in set (0.02 sec)

mysql> select * from file;
+-----+----------+-------------+------------+----------+---- -----------------+
| PID | FileName | FileNameEnd | DateAdded | FileSize | FileDate |
+-----+----------+-------------+------------+----------+---- -----------------+
| 5 | file.txt | txt. | 1050261518 | 0 | 2002-04-13 20:58:00 |
| 6 | file.txt | txt. | 1050261518 | 0 | 2002-04-13 20:58:00 |
+-----+----------+-------------+------------+----------+---- -----------------+
2 rows in set (0.02 sec)

mysql> describe host;
+---------------+---------------------+------+-----+-------- ----+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+---------------------+------+-----+-------- ----+----------------+
| HID | int(10) unsigned | | PRI | NULL | auto_increment |
| IP | char(16) | | | | |
| HostType | tinyint(3) unsigned | | | 0 | |
| ExpireCount | tinyint(3) unsigned | | | 0 | |
| Period | tinyint(3) unsigned | | | 0 | |
| Counter | tinyint(3) unsigned | | | 0 | |
| ScanOrder | tinyint(4) | | | 0 | |
| Flags | tinyint(3) unsigned | | | 0 | |
| LastScan | date | | | 0000-00-00 | |
| TotalFileSize | char(20) | | | ? | |
+---------------+---------------------+------+-----+-------- ----+----------------+
10 rows in set (0.02 sec)

mysql> describe path;
+-----------+------------------+------+-----+---------+----- -----------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+----- -----------+
| PID | int(10) unsigned | | PRI | NULL | auto_increment |
| Hash | int(10) unsigned | | MUL | 0 | |
| HID | int(10) unsigned | | MUL | 0 | |
| SID | int(10) unsigned | | MUL | 0 | |
| ShortName | varchar(255) | | | | |
| FullName | varchar(255) | | | | |
+-----------+------------------+------+-----+---------+----- -----------+
6 rows in set (0.01 sec)

mysql> describe file;
+-------------+------------------+------+-----+------------- --------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+------------- --------+-------+
| PID | int(10) unsigned | | MUL | 0 | |
| FileName | varchar(255) | | MUL | | |
| FileNameEnd | varchar(4) | | MUL | | |
| DateAdded | int(10) unsigned | | | 0 | |
| FileSize | int(10) unsigned | | MUL | 0 | |
| FileDate | datetime | | | 0000-00-00 00:00:00 | |
+-------------+------------------+------+-----+------------- --------+-------+
6 rows in set (0.01 sec)

>Fix:


>Submitter-Id:
>Originator: Josef Zlomek
>Organization:
>MySQL support: none
>Synopsis: wrong behaviour of NATURAL LEFT JOIN
>Severity: serious
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-4.0.12-standard (Official MySQL-standard binary)
>Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.48, for suse-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.0.12-standard
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock
Uptime: 6 min 48 sec

Threads: 5 Questions: 118 Slow queries: 0 Opens: 18 Flush tables: 1 Open tables: 12 Queries per second avg: 0.289
>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
AMD K6/450 MHz, SuSE Linux 8.0
glibc-2.2.5-158
System: Linux orion 2.4.20 #1 Wed Mar 26 17:44:18 CET 2003 i586 unknown
Architecture: i586

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/local/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/local/lib/gcc-lib/i586-pc-linux-gnu/3.2.2/specs
Configured with: ../configure --enable-threads=posix --disable-nls --disable-checking --enable-languages=c,c++,f77
Thread model: posix
gcc version 3.2.2
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1394302 Oct 2 2002 /lib/libc.so.6
-rw-r--r-- 1 root root 25361748 Oct 2 2002 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Apr 12 2002 /usr/lib/libc.so
-rw-r--r-- 1 root root 869190 Apr 12 2002 /usr/lib/libc-client.a
lrwxrwxrwx 1 root root 20 Jun 17 2002 /usr/lib/libc-client.so -> libc-client.so.2001a
-rwxr-xr-x 1 root root 725296 Apr 12 2002 /usr/lib/libc-client.so.2001a
Configure command: ./configure '--prefix=/usr/local/mysql' '--with-comment=Official MySQL-standard binary' '--with-extra-charsets=complex' '--with-server-suffix=-standard' '--enable-thread-safe-client' '--enable-local-infile' '--enable-assembler' '--disable-shared' '--with-client-ldflags=-all-static' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2 -mcpu=pentiumpro' 'CXXFLAGS=-O2 -mcpu=pentiumpro -felide-constructors' 'CXX=gcc'



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