Bug #652 LEFT JOIN does not occur when column(s) selected is not *

• Submitted: 13 Jun 2003 11:48
• Modified: 14 Jun 2003 7:36
• Reporter: Brian Tsang
• Status: Not a Bug
• Category: MySQL Server
• Severity: S1 (Critical)
• Version: 4.0.13
• OS: Windows (Windows XP Pro (latest sp))
• CPU Architecture: Any

[13 Jun 2003 11:48] Brian Tsang

Description:
When SELECT statement whose column argument is not "*" is executed with a LEFT JOIN, the join doesn't occur.  More specifically, the columns to be joined are not actually joined: mysql performs the SELECT as though the JOIN...ON... clause did not exist.

See "How to repeat" for an example program with my output.

How to repeat:

----------- EXAMPLE PROGRAM ------------------------

/* Set up generic tables */
CREATE TABLE table1(
    id      INT NOT NULL,
    field1  INT NOT NULL,
    PRIMARY KEY(id)
    );

INSERT INTO table1 VALUES(1, 2);
INSERT INTO table1 VALUES(2, 3);
INSERT INTO table1 VALUES(3, 5);
INSERT INTO table1 VALUES(4, 7);
INSERT INTO table1 VALUES(5, 11);

CREATE TABLE table2(
    id      INT NOT NULL,
    field2 CHAR NOT NULL,
    PRIMARY KEY(id)
    );

INSERT INTO table2 VALUES(2, 'a');
INSERT INTO table2 VALUES(3, '4');
INSERT INTO table2 VALUES(5, '*');

/* This works */
SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;

/* These dont */
SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;

----------- EXPECTED OUTPUT ------------------------

mysql> /* This works */
    -> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
+----+--------+------+--------+
| id | field1 | id   | field2 |
+----+--------+------+--------+
|  3 |      5 |    3 | 4      |
|  4 |      7 | NULL | NULL   |
|  5 |     11 |    5 | *      |
+----+--------+------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> /* These dont */
    -> SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
+----+--------+------+--------+
| id | field1 | id   | field2 |
+----+--------+------+--------+
|  3 |      5 |    3 | 4      |
|  4 |      7 | NULL | NULL   |
|  5 |     11 |    5 | *      |
+----+--------+------+--------+
3 rows in set (0.00 sec)

mysql> SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
+----+------+--------+
| id | id   | field2 |
+----+------+--------+
|  3 |    3 | 4      |
|  4 | NULL | NULL   |
|  5 |    5 | *      |
+----+------+--------+
3 rows in set (0.00 sec)

----------- MY OUTPUT ------------------------

mysql> /* This works */
    -> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>2;
+----+--------+------+--------+
| id | field1 | id   | field2 |
+----+--------+------+--------+
|  2 |      3 |    2 | a      |
|  3 |      5 |    3 | 4      |
|  4 |      7 | NULL | NULL   |
|  5 |     11 |    5 | *      |
+----+--------+------+--------+
4 rows in set (0.00 sec)

mysql>
mysql> /* These dont */
    -> SELECT table1.*  FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
+----+--------+
| id | field1 |
+----+--------+
|  3 |      5 |
|  4 |      7 |
|  5 |     11 |
+----+--------+
3 rows in set (0.00 sec)

mysql> SELECT table1.id FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE field1>3;
+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
+----+
3 rows in set (0.00 sec)

Suggested fix:
N/A

[13 Jun 2003 12:13] Brian Tsang

This problem also occurs on mysql 3.23.56 on Redhat Linux 8.0.

[14 Jun 2003 6:14] MySQL Verification Team

I tested with latest 4.0.14 code and with your last two queries I get a proper output :

[/mnt/work/mysql-4.0]$ mysql bug --table < tmp/dump 
+----+--------+
| id | field1 |
+----+--------+
|  3 |      5 |
|  4 |      7 |
|  5 |     11 |
+----+--------+
+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
+----+

[14 Jun 2003 7:31] Brian Tsang

Mr Milivojevic,

You actually got the same result I got, not the proper/expected output.  The expected output had 4 columns for the 2nd query (id, field1, id, field2) and 3 columns for the 3rd query (id, id, field2).

[14 Jun 2003 7:36] MySQL Verification Team

You asked only for table1 columns in second query and only table1.id column in the third one.