Bug in JOINed UNION

hi
if the second SELECT part of a UNION-query contains a JOIN from a table
having NULL values and mysql tries to write these NULL values into the
result table which does not allow NULL values for this field, all
subsequent fields will contain the values of the preceding row.

How-To-Repeat:

table definitions and data:
===========================
CREATE TABLE tabMaster (
id int(3) unsigned default '0'
) TYPE=MyISAM;

INSERT INTO tabMaster (id) VALUES("1");

CREATE TABLE tabChild (
id int(3) unsigned default '0',
id_master int(5) default '0',
text1 varchar(5) default NULL,
text2 varchar(5) default NULL
) TYPE=MyISAM;

INSERT INTO tabChild (id, id_master, text1, text2) VALUES("1", "1",
"foo1", "bar1");
INSERT INTO tabChild (id, id_master, text1, text2) VALUES("2", "1",
"foo2", "bar2");
INSERT INTO tabChild (id, id_master, text1, text2) VALUES("3", "1", NULL,
"bar3");
INSERT INTO tabChild (id, id_master, text1, text2) VALUES("4", "1",
"foo4", "bar4");
===========================

if using the following query, the results will be correct (because of a
table definition which allows NULL values in column 'text1'):
SELECT 1 AS id_master, 1 AS id, NULL AS text1, 'ABCDE' AS text2
UNION
SELECT id_master, tabChild.id, text1, text2 FROM tabMaster LEFT JOIN
tabChild ON tabMaster.id = tabChild.id_master

while the following query will generate wrong results in column 'text2',
row 4:
SELECT 1 AS id_master, 1 AS id, 'ABCDE' AS text1, 'ABCDE' AS text2
UNION
SELECT id_master, tabChild.id, text1, text2 FROM tabMaster LEFT JOIN
tabChild ON tabMaster.id = tabChild.id_master

we think, you should be able to reproduce the error with this example. if
not, please don't hesitate to ask us again.

thanks to Wendy Reets for giving us the critical hint!

here some informational stuff about our machine and mysql-release:
>Release: mysql-4.0.10-gamma-standard (Official MySQL-standard
binary)

>C compiler: 2.95.3
>C++ compiler: 2.95.3
>Environment:
System: Linux paprika 2.4.18-4GB #1 Wed Mar 27 13:57:05 UTC 2002 i686
unknown
Architecture: i686

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i486-suse-linux/2.95.3/specs
gcc version 2.95.3 20010315 (SuSE)
Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc'
CXXFLAGS='-O2 -mcpu=pentiumpro
-felide-constructors' LDFLAGS='' ASFLAGS=''
LIBC:
-rwxr-xr-x 1 root root 1394302 Oct 2 10:06 /lib/libc.so.6
-rw-r--r-- 1 root root 25361748 Oct 2 09:36 /usr/lib/libc.a
-rw-r--r-- 1 root root 178 Mar 23 2002 /usr/lib/libc.so
Configure command: ./configure '--prefix=/usr/local/mysql'
'--with-comment=Official MySQL-standard b
inary' '--with-extra-charsets=complex' '--with-server-suffix=-standard'
'--enable-thread-safe-client
' '--enable-local-infile' '--enable-assembler' '--disable-shared'
'--with-client-ldflags=-all-static
' '--with-mysqld-ldflags=-all-static' '--with-innodb' 'CFLAGS=-O2
-mcpu=pentiumpro' 'CXXFLAGS=-O2 -m
cpu=pentiumpro -felide-constructors' 'CXX=gcc'


regards
martin rueegg
lukas.rueegg

--
_
_ __ (_)_ ____ _____ _ ____ __
| '_ \| \ \/ /\ \ /\ / / _ \| '__\ \/ /
| |_) | |> < \ V V / (_) | | > <
| .__/|_/_/\_\ \_/\_/ \___/|_| /_/\_\
|_| m u l t i m e d i a


lukas rueegg
ch-8005 zurich

lru_mysql@pixworx.ch

[ There are 3 kinds of people in this world: those who can count and those
who can't. ]








------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13811@lists.mysql.com
To unsubscribe, e-mail

Re: Bug in JOINed UNION

lukas rueegg [pixworx multimedia] writes:
> hi
> if the second SELECT part of a UNION-query contains a JOIN from a table
> having NULL values and mysql tries to write these NULL values into the
> result table which does not allow NULL values for this field, all
> subsequent fields will contain the values of the preceding row.
>
> How-To-Repeat:
>
> -mcpu=pentiumpro' 'CXXFLAGS=-O2 -m
> cpu=pentiumpro -felide-constructors' 'CXX=gcc'
>
>
> regards
> martin rueegg
> lukas.rueegg
>
> --
> _
> _ __ (_)_ ____ _____ _ ____ __
> | '_ \| \ \/ /\ \ /\ / / _ \| '__\ \/ /
> | |_) | |> < \ V V / (_) | | > <
> | .__/|_/_/\_\ \_/\_/ \___/|_| /_/\_\
> |_| m u l t i m e d i a
>

Thank you for your message.

As our manual clearly states the entire result types for UNION are
defined by first SELECT.

So, this is expected behaviour. Furthermore, SQL standards require
that if any differences in type occur, the error should be returned
immediately.

MySQL tries to do better.

Still, as this is not very clearly defined for columns not being NULL,
we could introduce the following change in 4.1 :
===== sql/sql_union.cc 1.64 vs edited =====
*** /tmp/sql_union.cc-1.64-755 Sat Feb 15 16:05:54 2003
--- edited/sql/sql_union.cc Fri Feb 21 18:03:00 2003
***************
*** 146,152 ****
if (setup_tables(first_table) ||
setup_wild(thd, first_table, sl->item_list, 0, sl->with_wild))
goto err;
!
item_list= sl->item_list;
sl->with_wild= 0;
if (setup_ref_array(thd, &sl->ref_pointer_array,
--- 146,155 ----
if (setup_tables(first_table) ||
setup_wild(thd, first_table, sl->item_list, 0, sl->with_wild))
goto err;
! List_iterator it(sl->item_list);
! Item *item;
! while((item=it++))
! item->maybe_null=1;
item_list= sl->item_list;
sl->with_wild= 0;
if (setup_ref_array(thd, &sl->ref_pointer_array,

which returns the following results, with second select:

id_master id text1 text2
1 1 ABCDE ABCDE
1 1 foo1 bar1
1 2 foo2 bar2
1 3 NULL bar3
1 4 foo4 bar4


However, this still has to be checked out with standards

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13815@lists.mysql.com
To unsubscribe, e-mail