Possible MySQL 4.0.12 bug regarding UNION operand ordering

When I use UNION with a SELECT that LEFT JOIN's empty fields, I get
invalid data in the rows of the other SELECT depending on UNION operand
order.

In the case described below, family_fam has no row matching fam_ID=0, so
it should return NULL for the value of all LEFT JOIN'ed fields. This is
desired so that the UNION will work (matching columns for the SELECTs)
but without returning any data in these columns.

If I put the SELECT with the empty LEFT JOIN'ed field values first, it
works fine:

(SELECT * FROM person_per
LEFT JOIN family_fam ON fam_ID = 0
WHERE per_fam_ID = 0)
UNION
(SELECT * FROM person_per
LEFT JOIN family_fam ON fam_ID = per_fam_ID
WHERE per_fam_ID > 0)

(note: I fully realize this example doesn't make real-world sense. I
have stripped these two selects down to their absolute basics for ease
of discussion.)

Both of these selects work properly by themselves as well. However, if
I reverse the order of the SELECT's (ie. B UNION A), the fields which
should be NULL due to the non-existant fam_ID = 0 row will now contain
data from the LEFT JOIN of the *last row* of the first (B) SELECT
statement.

Example data might look like this, where per_Name comes from person_per
and fam_Name comes from family_fam

Working case:

per_Name, fam_Name
--------------------
Johnson, NULL
Edwards, NULL
Adams, Adams
Blah, BlahFamily
Stone, Richards

Non-working case:

per_Name, fam_Name
--------------------
Adams, Adams
Blah, BlahFamily
Stone, Richards
Johnson, Richards <- These fam_Name values should be NULL like above!
Edwards, Richards <-

This doesn't seem to be the prescribed behavior. Perhaps some variables
internally are not being cleared within a loop somewhere?

--
Chris


--
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: Possible MySQL 4.0.12 bug regarding UNION operand ordering

On Tue, 2003-04-29 at 10:38, Chris wrote:
> When I use UNION with a SELECT that LEFT JOIN's empty fields, I get
> invalid data in the rows of the other SELECT depending on UNION operand
> order.
>
> In the case described below, family_fam has no row matching fam_ID=0, so
> it should return NULL for the value of all LEFT JOIN'ed fields. This is
> desired so that the UNION will work (matching columns for the SELECTs)
> but without returning any data in these columns.

Chris,

Could you please send us complete repeatable example which we can test
by "mysql test < example.sql"

We have fixed one JOIN related bug in MySQL 4.0.13 but it would be nice
to make sure this test case is also tested.

It could be it is not a but as well but we need repeatable example in
order to investigate it carefully.


Thank you for cooperation.

--
MySQL 2003 Users Conf. -> http://www.mysql.com/events/uc2003/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Peter Zaitsev
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
/_/ /_/\_, /___/\___\_\___/ Moscow, Russia
<___/ 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

Re: Possible MySQL 4.0.12 bug regarding UNION operand ordering

Chris writes:
> When I use UNION with a SELECT that LEFT JOIN's empty fields, I get
> invalid data in the rows of the other SELECT depending on UNION operand
> order.
>
> In the case described below, family_fam has no row matching fam_ID=0, so
> it should return NULL for the value of all LEFT JOIN'ed fields. This is
> desired so that the UNION will work (matching columns for the SELECTs)
> but without returning any data in these columns.
>
> If I put the SELECT with the empty LEFT JOIN'ed field values first, it
> works fine:
>
> (SELECT * FROM person_per
> LEFT JOIN family_fam ON fam_ID = 0
> WHERE per_fam_ID = 0)
> UNION
> (SELECT * FROM person_per
> LEFT JOIN family_fam ON fam_ID = per_fam_ID
> WHERE per_fam_ID > 0)
>
> (note: I fully realize this example doesn't make real-world sense. I
> have stripped these two selects down to their absolute basics for ease
> of discussion.)
>
> Both of these selects work properly by themselves as well. However, if
> I reverse the order of the SELECT's (ie. B UNION A), the fields which
> should be NULL due to the non-existant fam_ID = 0 row will now contain
> data from the LEFT JOIN of the *last row* of the first (B) SELECT
> statement.
>
> Example data might look like this, where per_Name comes from person_per
> and fam_Name comes from family_fam
>
> Working case:
>
> per_Name, fam_Name
> --------------------
> Johnson, NULL
> Edwards, NULL
> Adams, Adams
> Blah, BlahFamily
> Stone, Richards
>
> Non-working case:
>
> per_Name, fam_Name
> --------------------
> Adams, Adams
> Blah, BlahFamily
> Stone, Richards
> Johnson, Richards <- These fam_Name values should be NULL like above!
> Edwards, Richards <-
>
> This doesn't seem to be the prescribed behavior. Perhaps some variables
> internally are not being cleared within a loop somewhere?
>
> --
> Chris
>

HI!

Yes, this is a known problem that is fixed partially in 4.1.

Full fix, with full proper support for all column types, will come in
5.0.

--

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