At 13:10 +0900 11/16/04, Eric Prud'hommeaux wrote:
>Description:
A UNION B reports the A results faithfully, but 0s and NULLs
in B's results aren't making it to the result set intact.
How-To-Repeat:
SELECT 1 AS a, NULL AS b
UNION
SELECT NULL AS a, 1 AS b;
+---+------+
| a | b |
+---+------+
| 1 | NULL |
| 0 | |
+---+------+
expected:
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| NULL | 1 |
+------+------+
SELECT 1 AS a, NULL AS b, NULL AS c
UNION
SELECT NULL AS a, 1 AS b, NULL AS c
UNION
SELECT NULL AS a, NULL AS b, 1 AS c;
+---+------+------+
| a | b | c |
+---+------+------+
| 1 | NULL | NULL |
| 0 | | NULL |
| 0 | NULL | |
+---+------+------+
contrast with:
SELECT 1 AS a, 2 AS b, 2 AS c
UNION
SELECT 2 AS a, 1 AS b, 2 AS c
UNION
SELECT 2 AS a, 2 AS b, 1 AS c;
+---+---+---+
| a | b | c |
+---+---+---+
| 1 | 2 | 2 |
| 2 | 1 | 2 |
| 2 | 2 | 1 |
+---+---+---+
which is what I expected.
Fix:
look for result sets being set instead of set to a truth value.
Submitter-Id: <submitter ID>
Originator: Eric Prud'hommeaux
Organization:
-eric
office: +1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
cell: +1.857.222.5741
([EMAIL PROTECTED])
Feel free to forward this message to any list for any purpose other than
email address distribution.
MySQL support: none
Synopsis: right side of UNION not reported faithfully
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-4.0.22 (Source distribution)
This is a problem that's fixed in MySQL 4.1. (4.1.2, I believe)
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
