mysql-4.1.0-alpha query results discrepancy

I ran into a reproducable and confounding bug in 4.1.0 in which

A)
SELECT transaction_table.id_field, transaction_table.location_field,
transaction_table.date_field
FROM complete_db.transaction_table AS transaction_table,
(
SELECT distinct transaction_table.id_field
FROM complete_db.transaction_table AS transaction_table,
complete_db.reference_code_table AS reference_code_table
WHERE transaction_table.date_field
>= DATE_SUB(curdate(), INTERVAL 6 DAY)
AND transaction_table.location_field IN (55,56,58,59)
AND transaction_table.code_field =
reference_code_table.code_field
AND reference_code_table.group_field IN ('BRA', 'HYP', 'SRS',
'CTM', 'IRT')
) AS treated
WHERE transaction_table.id_field = treated.id_field
AND transaction_table.location_field IN (63, 154, 80)

Differs completely from

B)
SELECT transaction_table.id_field, transaction_table.location_field,
transaction_table.date_field, transaction_table.staff_field
FROM complete_db.transaction_table AS transaction_table,
(
SELECT distinct transaction_table.id_field
FROM complete_db.transaction_table AS transaction_table,
complete_db.reference_code_table AS reference_code_table
WHERE transaction_table.date_field
>= DATE_SUB(curdate(), INTERVAL 6 DAY)
AND transaction_table.location_field IN (55,56,58,59)
AND transaction_table.code_field =
reference_code_table.code_field
AND reference_code_table.group_field IN ('BRA', 'HYP', 'SRS',
'CTM', 'IRT')
) AS treated
WHERE transaction_table.id_field = treated.id_field
AND transaction_table.location_field IN (63, 154, 80)

The result for A) returns correct id_field results, however it returns a
uniform (and incorrect) value for the location_field and date_field.
eg.
id_field location_field date_field
12345 56 06/05/2003
23456 56 06/05/2003
34567 56 06/05/2003
.... 56 06/05/2003
99999 56 06/05/2003

The result for B) includes an unused and unrelated column, and returns
correct results for all three important fields
id_field location_field date_field dont_care
12345 56 06/03/2003 xxx
23456 58 05/29/2003 xxx
34567 55 06/01/2003 xxx
.... ?? ??/??/????
99999 59 04/10/2003 xxx


When I moved the data to an identically indexed subset (only data
relevant to the query) of this table, both queries A) and B) worked as
expected. I would be more than willing to provide any additional
information as required.


--
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: mysql-4.1.0-alpha query results discrepancy

Nicolas Hughes writes:
> I ran into a reproducable and confounding bug in 4.1.0 in which
>

[skip]
>
> When I moved the data to an identically indexed subset (only data
> relevant to the query) of this table, both queries A) and B) worked as
> expected. I would be more than willing to provide any additional
> information as required.
>
>
> --
> MySQL Bugs Mailing List
> For list archives: http://lists.mysql.com/bugs
> To unsubscribe: http://lists.mysql.com/bugs?unsub=sinisa@cytanet.com.cy


Hi!

Thank you for your bug report.

What you are reporting looks VERY much like a bug in derived tables
that was fixed recently.

This bug pops up whenever a table is used in outer select and in
derived teble SELECT and that table has an index. I guess that your
transaction_table has index on id_field.

A fix is not yet pushed ot our internal source tree, but should be
available with 4.1.1, whose release is not scheduled yet.

A possible workaround is that you use IGNORE INDEX on id_field in the
outer SELECT.

--

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