Bug #1833 NULL values in SELECTs within UNIONs cause incorrect values from other SELECTs
Submitted: 13 Nov 2003 19:53 Modified: 14 Nov 2003 10:36
Reporter: Paul Coldrey Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:4.0.16 OS:Windows (Windows XP Pro)
Assigned to: CPU Architecture:Any

[13 Nov 2003 19:53] Paul Coldrey
Description:
When using a UNION, if a column in one of the SELECTs is NULL then corresponding columns in the other SELECTs don't always get returned properly (Specifically it appears SELECTs after the SELECT containing the NULL do not return correct values).

How to repeat:
CREATE Database BugMe;
USE BugMe;

CREATE TABLE Data (
	GroupId INTEGER DEFAULT NULL,
	Value INTEGER
);

INSERT INTO Data(Value) VALUES (1), (2);

INSERT INTO Data(GroupId, Value) VALUES (1,1), (1,2), (1,3), (2,4), (2,5), (2,6);

--
-- this select returns the max and min correctly
--

SELECT Value, 0, 0
FROM Data 
WHERE GroupId IS NULL
UNION
SELECT Avg(Value), Min(Value), Max(Value)
FROM Data
WHERE GroupId IS NOT NULL
GROUP BY GroupId 

--
-- this works too
--

SELECT Avg(Value), Min(Value), Max(Value)
FROM Data
WHERE GroupId IS NOT NULL
GROUP BY GroupId 
UNION
SELECT Value, NULL, NULL
FROM Data 
WHERE GroupId IS NULL

--
-- this one return NON-NULL but "empty" values for max and min
--

SELECT Value, NULL, NULL
FROM Data 
WHERE GroupId IS NULL
UNION
SELECT Avg(Value), Min(Value), Max(Value)
FROM Data
WHERE GroupId IS NOT NULL
GROUP BY GroupId 

Suggested fix:
In most cases it may be possible to avoid this problem be ordering the SELECT statements in the UNION judiciously - obviously this may not always be possible (where different columns are missing from different SELECTs). Alternatively a value other than NULL can be returned for the empty positions in a SELECT, but once again, this may not suit your purpose either.
[13 Nov 2003 21:08] Paul Coldrey
I also just discovered that if you type:

SELECT Value, 'a' as V2 FROM Data WHERE Value = 1
UNION
SELECT Value, 'ab' as V2 FROM Data WHERE Value = 2;

you get:

Value    |  V2
-----------------
  1      |   a
  2      |   a

Rather than

Value    |  V2
-----------------
  1      |   a
  2      |   ab

Also, notable is that if the second select contained a date value in a column where the first select has 'a', then the date field would be trimming to 1 character wide. If the first select has 'ab' you'd get the first two characters of the date.
[14 Nov 2003 10:36] Dean Ellis
This is a duplicate of issue #96.

Issue is caused by MySQL's handling of literal values and is slated to be changed in a future release.

Thank you.