I have found a problem with UNION and LEFT JOIN in MySQL 4.0.12 (using binary distribution on Solaris 2.8).

The problem is that fields that should be NULL in the result (because they come from a table on the right of a LEFT JOIN where there is no corresponding row), do not appear as NULL in the output. Without the UNION, everything works fine.

This may be related to the bug reported by "Chris" on 2003-04-29:

http://lists.mysql.com/list.php?9:mss:14344:indbhdlilihkkakmkfnd

I am getting slightly different behaviour, though.

Rather than seeing earlier rows duplicated in place of rows that should be NULL, as in the earlier report, I am seeing the default value for the column in the absence of the LEFT JOIN (zero for an integer column).

If I redefine the table on the right of the join so that the columns selected from it are allowed to be NULL, then I get the NULLs in the result just as expected. Other tests show that the problem appears to be that UNION is incorrectly determining the type of the columns on the right of the join (failing to notice that they can be NULL).

Test case (run with 'mysql -v test < script.sql'):

## BEGIN TEST CASE ##

DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( a INT NOT NULL ); INSERT INTO t1 VALUES (1),(2),(3),(4),(5);

DROP TABLE IF EXISTS t2; CREATE TABLE t2 ( a INT NOT NULL, b INT NOT NULL ); INSERT INTO t2 VALUES (1,10),(1,11),(1,12),(4,40),(4,41),(4,42),(4,43),(4,44);

DROP TABLE IF EXISTS t3; CREATE TABLE t3 ( a INT NOT NULL, b INT ); INSERT INTO t3 VALUES (1,10),(1,11),(1,12),(4,40),(4,41),(4,42),(4,43),(4,44);

# --- First selecting from t2, where column b is not allowed to be NULL

---

# this one works, giving null for t2.b for rows 2,3,5 SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a;

# this one is wrong, giving 0 (t2.b default value) for rows 2,3,5 (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a) UNION (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a);

# this one is wrong, giving 0 (t2.b default value) for rows 2,3,5 # so the problem isn't down to UNION vs UNION ALL (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a) UNION ALL (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a);

# this one is wrong, giving 0 (t2.b default value) for rows 2,3,5 (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a) UNION ALL (SELECT -1,-1);

# this one is wrong, giving 0 for rows 2,3,5 # I assume this means that the type assigned to an integer literal # is INT NOT NULL. (SELECT -1,-1) UNION ALL (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a);

# this one has the NULLs in the expected places - others are empty (SELECT -1,NULL) UNION ALL (SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a);

# --- Now selecting from t3, where column b is allowed to be NULL --

# this one now works, giving NULL for rows 2,3,5 (SELECT t1.a,t3.b FROM t1 LEFT JOIN t3 ON t1.a=t3.a) UNION (SELECT t1.a,t3.b FROM t1 LEFT JOIN t3 ON t1.a=t3.a);

# this one now works, giving NULL for rows 2,3,5 (SELECT t1.a,t3.b FROM t1 LEFT JOIN t3 ON t1.a=t3.a) UNION ALL (SELECT t1.a,t3.b FROM t1 LEFT JOIN t3 ON t1.a=t3.a);

# this one now works, giving NULL for rows 2,3,5 (SELECT t1.a,t3.b FROM t1 LEFT JOIN t3 ON t1.a=t3.a) UNION ALL (SELECT -1,-1);

# --- Now trying using a temporary table ---

# This approach works fine, since the CREATE TABLE ... SELECT correctly # detects that the second column can be NULL. CREATE TEMPORARY TABLE temp SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a; INSERT INTO temp SELECT t1.a,t2.b FROM t1 LEFT JOIN t2 ON t1.a=t2.a; SELECT * FROM temp; SHOW COLUMNS FROM temp; DROP TEMPORARY TABLE temp;

## END TEST CASE ##