| Bug #7875 | 3 tables,NATURAL or INNER JOIN with USING, wrong results/error message | ||
|---|---|---|---|
| Submitted: | 13 Jan 2005 14:33 | Modified: | 3 Feb 2005 9:42 |
| Reporter: | Matthias Leich | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | |
| Assigned to: | Assigned Account | CPU Architecture: | Any |
[13 Jan 2005 14:35]
Matthias Leich
test case
Attachment: ML8.test (application/test, text), 3.90 KiB.
[13 Jan 2005 18:13]
Sergei Golubchik
Looks like a duplicate of http://bugs.mysql.com/bug.php?id=6489
[13 Jan 2005 19:24]
Matthias Leich
I do not agree. The problem within bug#6489 is, that MySQL delivers too many result columns. The problem here is, that we get too many result rows. If you think my bug is influenced by the wrong handling of '*', please replace count(*) by t1.COSTCODE. The problem will not change. That means we get too many result rows or the wrong error message.
[13 Jan 2005 19:56]
Sergei Golubchik
Correct. It's still partially related to http://bugs.mysql.com/bug.php?id=4789 but error message in the USING part is not covered by that bugreport
[3 Feb 2005 9:35]
Sergey Petrunya
The problem with getting too many records is exactly the same as in BUG#4789
[3 Feb 2005 9:40]
Sergey Petrunya
And the source of the problem with error message is actually described at http://bugs.mysql.com/bug.php?id=4789, comment by Timour dated 1 Sep 2004.
[3 Feb 2005 9:42]
Sergey Petrunya
Considering the previous comments, setting as a duplicate of BUG#4789
Description: I have a test case derived from the NIST test dml134. SELECT count(*) FROM t1 NATURAL JOIN t2 NATURAL JOIN t3; --> wrong result 20 # The manual says NATURAL is equivalent to # INNER .. USING(<all columns occuring in both tables> SELECT count(*) FROM t1 INNER JOIN t2 USING(COSTCODE) INNER JOIN t3 USING(CONDCODE); --> wrong error message ERROR 42S22: Unknown column 'test.t2.CONDCODE' in 'on clause' My statement claims t2 USING(COSTCODE) ! # Another equivalent of the statement is SELECT count(*) FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.COSTCODE = t2.COSTCODE AND t1.CONDCODE = t3.CONDCODE; --> The expected result: 4 MySQL 5.0 shows the same wrong behaviour. My environment: - Intel PC with Linux(SuSE 9.1) - MySQL compiled from source Version 4.1 ChangeSet@1.2221, 2005-01-13 Version 5.0 around 2005-01-12 How to repeat: Please use my attached test file ML8.test , copy it to mysql-test/t ./mysql-test-run ML8