possible problem with UNION and LEFT JOIN
am 01.05.2003 16:16:00 von Martin FrostI 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:indbhdlilihkkakm kfnd
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 ##
--
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