On Mon, Nov 15, 2004 at 10:26:29PM -0600, Paul DuBois wrote: > 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)
I can confirm this (sorry I didn't test before submitting):
after a make install:
unagi:/usr/local/src/mysql-4.1.8-nightly-20041115$
LD_LIBRARY_PATH=/usr/local/lib/mysql /usr/local/bin/mysqld_safe &
Starting mysqld daemon with databases from /usr/local/var
[1] 24404
unagi:/usr/local/src/mysql-4.1.8-nightly-20041115$
LD_LIBRARY_PATH=/usr/local/lib/mysql /usr/local/bin/mysql test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.8-nightly-20041115
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> 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 |
| NULL | 1 | NULL |
| NULL | NULL | 1 |
+------+------+------+
3 rows in set (0.01 sec)
--
-eric
office: +81.466.49.1170 W3C, Keio Research Institute at SFC,
Shonan Fujisawa Campus, Keio University,
5322 Endo, Fujisawa, Kanagawa 252-8520
JAPAN
+1.617.258.5741 NE43-344, MIT, Cambridge, MA 02144 USA
cell: +1.857.222.5741 (does not work in Asia)
([EMAIL PROTECTED])
Feel free to forward this message to any list for any purpose other than
email address distribution.
signature.asc
Description: Digital signature
