Re: right side of UNION not reported faithfully

[Eric Prud'hommeaux]

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