List:Bugs« Previous MessageNext Message »
From:rafarife Date:May 16 2003 8:08am
Subject:Error in the union of two or more queries
View as plain text  
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:   <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
Thread
Error in the union of two or more queriesrafarife16 May
  • Re: Error in the union of two or more queriesSinisa Milivojevic24 May