>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)
>Server: /usr/bin/mysqladmin Ver 8.40 Distrib 4.0.22, for pc-linux on i386
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.22-log
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 2 days 16 hours 48 min 4 sec
Threads: 2 Questions: 324 Slow queries: 0 Opens: 56 Flush tables: 1 Open
tables: 43 Queries per second avg: 0.001
>C compiler: gcc (GCC) 3.3.5 (Debian 1:3.3.5-2)
>C++ compiler: gcc (GCC) 3.3.5 (Debian 1:3.3.5-2)
>Environment:
System: Linux unagi.w3.org 2.6.7t42 #1 Tue Nov 2 02:11:32 JST 2004 i686
GNU/Linux
Architecture: i686
Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-linux/3.3.5/specs
Configured with: ../src/configure -v
--enable-languages=c,c++,java,f77,pascal,objc,ada,treelang --prefix=/usr
--mandir=/usr/share/man --infodir=/usr/share/info
--with-gxx-include-dir=/usr/include/c++/3.3 --enable-shared --with-system-zlib
--enable-nls --without-included-gettext --enable-__cxa_atexit
--enable-clocale=gnu --enable-debug --enable-java-gc=boehm
--enable-java-awt=xlib --enable-objc-gc i486-linux
Thread model: posix
gcc version 3.3.5 (Debian 1:3.3.5-2)
Compilation info: CC='gcc' CFLAGS='-DBIG_JOINS=1' CXX='gcc'
CXXFLAGS='-DBIG_JOINS=1 -felide-constructors -fno-rtti' LDFLAGS='' ASFLAGS=''
LIBC:
lrwxrwxrwx 1 root root 13 2004-11-02 01:19 /lib/libc.so.6 -> libc-2.3.2.so
-rw-r--r-- 1 root root 1244080 2004-10-14 04:40 /lib/libc-2.3.2.so
-rw-r--r-- 1 root root 2640954 2004-10-14 04:41 /usr/lib/libc.a
-rw-r--r-- 1 root root 204 2004-10-14 04:16 /usr/lib/libc.so
Configure command: ./configure '--build=i386-linux' '--host=i386-linux'
'--prefix=/usr' '--exec-prefix=/usr' '--libexecdir=/usr/sbin'
'--datadir=/usr/share' '--sysconfdir=/etc/mysql'
'--localstatedir=/var/lib/mysql' '--includedir=/usr/include'
'--infodir=/usr/share/info' '--mandir=/usr/share/man' '--enable-shared'
'--enable-static' '--enable-thread-safe-client' '--enable-assembler'
'--enable-local-infile' '--with-raid'
'--with-unix-socket-path=/var/run/mysqld/mysqld.sock'
'--with-mysqld-user=mysql' '--with-libwrap' '--with-client-ldflags=-lstdc++'
'--with-embedded-server' '--with-vio' '--with-openssl' '--without-docs'
'--without-bench' '--without-readline' '--with-extra-charsets=all'
'--with-berkeley-db' '--with-innodb' 'CC=gcc' 'CFLAGS=-DBIG_JOINS=1'
'CXXFLAGS=-DBIG_JOINS=1 -felide-constructors -fno-rtti' 'CXX=gcc'
'build_alias=i386-linux' 'host_alias=i386-linux'
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
