Error in the union of two or more queries

Description:
Hello,

I have to execute the following query:

Select t1.Nom as Nom, Table1.Cod as Cod,
Table1.Dom as Dom
From Table1
Left Join t1 On Table1.Dom = t1.Cod
Where Table1.Field1 = 'Value'
Union
Select t1.Nom as Nom, Table2.Cod as Cod,
Table2.Dom as Dom
From Table2
Left Join t1 On Table2.Dom = t1.Cod
Where Table2.Field1 = 'Value'

The primary key in all the tables is Cod.

In my case, there is not join between Table1 and
t1, nor either between Table2 and t1 because the
'Dom' field value is the empty string for the
selection.

If I execute the query I haven´t any result although
I have data for the other fields that is, for the
field Cod. If I change the fields order and
I put the field t1.Nom at the end, I obtain the
correct result. Moreover, If I change the query to
following:

Select IfNull(t1.Nom,'') as Nom, Table1.Cod, Table1.Dom
From Table1
Left Join t1 On Table1.Dom = t1.Cod
Where Table1.Field1 = 'Value'
Union
Select IfNull(t1.Nom,'') as Nom, Table2.Cod, Table2.Dom
From Table2
Left Join t1 On Table2.Dom = t1.Cod
Where Table2.Field1 = 'Value'

I obtain the correct result too.

This error only occurs when I execute an union of two
or more queries but not if I execute only a query.

Is it necessary to include Ifnull() in the query
or is it really an error?

Thanks in advance,
Lourdes.


How-To-Repeat:
Select ...

Fix:
-

Synopsis:Subject:Error in the union of two or more queries

Submitter-Id:
Originator: Lourdesz
Organization: Pecomark
MySQL support: none
Severity: non-critical
Priority: medium
Category: mysqld-max-nt
Class: sw-bug
Release: mysqld 4.0.12 beta(InnoDB)

Exectutable: mysqld-max-nt
Environment: Pentium III-MMX, 500 MHZ, 540 MB
System: Windows 2000
Compiler: -
Architecture: i

____________________________________________________________ ______
Try AOL and get 1045 hours FREE for 45 days!
http://free.aol.com/tryaolfree/index.adp?375380

Get AOL Instant Messenger 5.1 free of charge. Download Now!
http://aim.aol.com/aimnew/Aim/register.adp?promo=380455

--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org

Re: Error in the union of two or more queries

rafarife@netscape.net writes:
> Description:
> Hello,
>
> I have to execute the following query:
>

[skip]

> I obtain the correct result too.
>
> This error only occurs when I execute an union of two
> or more queries but not if I execute only a query.
>
> Is it necessary to include Ifnull() in the query
> or is it really an error?
>
> Thanks in advance,
> Lourdes.
>
>
> How-To-Repeat:
> Select ...

Hi!

Thank you for your mail.

This is a known issue with UNION's. We have solved problems with
NULL's stemming out from missing values, but yet have to solve a
problem with NULL's that come out from columns containing NULL's. We
are working on it and will yet have to decide whether this behaviour
will be changed in 4.1 or 5.0. It will most probably be changed in
4.1.

--

Regards,

--
For technical support contracts, go to https://order.mysql.com/?ref=msmi
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com


--
MySQL Bugs Mailing List
For list archives: http://lists.mysql.com/bugs
To unsubscribe: http://lists.mysql.com/bugs?unsub=gcdmb-bugs@m.gmane.org