MySQL Bugs: #57726: Incorrect query result

Bug #57726	Incorrect query result
Submitted:	26 Oct 2010 6:31	Modified:	26 Nov 2010 11:16
Reporter:	Ingo Busse	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.1.49	OS:	Other (Ubuntu 10.10)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Query delivers wrong result!

In Ubuntu 10.10 with mysql-server 4.1.49 the attached query returns no result while in Ubuntu 10.04 with mysql-server 5.1.41 it properly returned the entry with this_.id=1.

Note that when omitting the last part of the where clause (and this_.id=1) the query correctly lists all entries. But when adding the additional part to select a specific one nothing is returned.

Already reported here: https://bugs.launchpad.net/bugs/663192 Sample Db can be downloaded from there.

Note that the query is generated by the hibernate framework and thus is quite common.

How to repeat:
Run the following query against sample db

    select
        this_.id as id61_1_,
        aclkunde3_.akte_acl_kunde_id as akte1_3_,
        aclkunde_a1_.id as kunde2_3_,
        aclkunde_a1_.id as id13_0_,
        aclkunde_a1_.kundennummer as kundennu3_13_0_,
        aclkunde_a1_1_.anrede as anrede14_0_,
        aclkunde_a1_1_.vorname as vorname14_0_,
        aclkunde_a1_1_.zuname as zuname14_0_,
        aclkunde_a1_2_.anrede as anrede15_0_,
        aclkunde_a1_2_.vorname as vorname15_0_,
        aclkunde_a1_2_.zuname as zuname15_0_,
        case
            when aclkunde_a1_1_.id is not null then 1
            when aclkunde_a1_2_.id is not null then 2
            when aclkunde_a1_.id is not null then 0
        end as clazz_0_
    from
        akte this_
    left outer join
        akte_kunde aclkunde3_
            on this_.id=aclkunde3_.akte_acl_kunde_id
    left outer join
        kunde aclkunde_a1_
            on aclkunde3_.kunde_id=aclkunde_a1_.id
    left outer join
        anwalt aclkunde_a1_1_
            on aclkunde_a1_.id=aclkunde_a1_1_.id
    left outer join
        kunde_glaeubiger aclkunde_a1_2_
            on aclkunde_a1_.id=aclkunde_a1_2_.id
    where
        (
            aclkunde_a1_.id=1
        )
        and this_.id=1;

Tables dump

Attachment: demo_db.sql (application/octet-stream, text), 82.03 KiB.

Thank you for the bug report.

mysql 5.0 >  select
    ->         this_.id as id61_1_,
    ->         aclkunde3_.akte_acl_kunde_id as akte1_3_,
    ->         aclkunde_a1_.id as kunde2_3_,
    ->         aclkunde_a1_.id as id13_0_,
    ->         aclkunde_a1_.kundennummer as kundennu3_13_0_,
    ->         aclkunde_a1_1_.anrede as anrede14_0_,
    ->         aclkunde_a1_1_.vorname as vorname14_0_,
    ->         aclkunde_a1_1_.zuname as zuname14_0_,
    ->         aclkunde_a1_2_.anrede as anrede15_0_,
    ->         aclkunde_a1_2_.vorname as vorname15_0_,
    ->         aclkunde_a1_2_.zuname as zuname15_0_,
    ->         case
    ->             when aclkunde_a1_1_.id is not null then 1
    ->             when aclkunde_a1_2_.id is not null then 2
    ->             when aclkunde_a1_.id is not null then 0
    ->         end as clazz_0_
    ->     from
    ->         akte this_
    ->     left outer join
    ->         akte_kunde aclkunde3_
    ->             on this_.id=aclkunde3_.akte_acl_kunde_id
    ->     left outer join
    ->         kunde aclkunde_a1_
    ->             on aclkunde3_.kunde_id=aclkunde_a1_.id
    ->     left outer join
    ->         anwalt aclkunde_a1_1_
    ->             on aclkunde_a1_.id=aclkunde_a1_1_.id
    ->     left outer join
    ->         kunde_glaeubiger aclkunde_a1_2_
    ->             on aclkunde_a1_.id=aclkunde_a1_2_.id
    ->     where
    ->         (
    ->             aclkunde_a1_.id=1
    ->         )
    ->         and this_.id=1;
+---------+----------+-----------+---------+-----------------+-------------+--------------+-------------+-------------+--------------+-------------+----------+
| id61_1_ | akte1_3_ | kunde2_3_ | id13_0_ | kundennu3_13_0_ | anrede14_0_ | vorname14_0_ | zuname14_0_ | anrede15_0_ | vorname15_0_ | zuname15_0_ | clazz_0_ |
+---------+----------+-----------+---------+-----------------+-------------+--------------+-------------+-------------+--------------+-------------+----------+
|       1 |        1 |         1 |       1 | 3000001         |           1 | Max          | Mustermann  |        NULL | NULL         | NULL        |        1 |
+---------+----------+-----------+---------+-----------------+-------------+--------------+-------------+-------------+--------------+-------------+----------+
1 row in set (0.02 sec)

mysql 5.0 > show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.0.92-Win X64-log  |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

mysql 5.1 > select
    ->         this_.id as id61_1_,
    ->         aclkunde3_.akte_acl_kunde_id as akte1_3_,
    ->         aclkunde_a1_.id as kunde2_3_,
    ->         aclkunde_a1_.id as id13_0_,
    ->         aclkunde_a1_.kundennummer as kundennu3_13_0_,
    ->         aclkunde_a1_1_.anrede as anrede14_0_,
    ->         aclkunde_a1_1_.vorname as vorname14_0_,
    ->         aclkunde_a1_1_.zuname as zuname14_0_,
    ->         aclkunde_a1_2_.anrede as anrede15_0_,
    ->         aclkunde_a1_2_.vorname as vorname15_0_,
    ->         aclkunde_a1_2_.zuname as zuname15_0_,
    ->         case
    ->             when aclkunde_a1_1_.id is not null then 1
    ->             when aclkunde_a1_2_.id is not null then 2
    ->             when aclkunde_a1_.id is not null then 0
    ->         end as clazz_0_
    ->     from
    ->         akte this_
    ->     left outer join
    ->         akte_kunde aclkunde3_
    ->             on this_.id=aclkunde3_.akte_acl_kunde_id
    ->     left outer join
    ->         kunde aclkunde_a1_
    ->             on aclkunde3_.kunde_id=aclkunde_a1_.id
    ->     left outer join
    ->         anwalt aclkunde_a1_1_
    ->             on aclkunde_a1_.id=aclkunde_a1_1_.id
    ->     left outer join
    ->         kunde_glaeubiger aclkunde_a1_2_
    ->             on aclkunde_a1_.id=aclkunde_a1_2_.id
    ->     where
    ->         (
    ->             aclkunde_a1_.id=1
    ->         )
    ->         and this_.id=1;
Empty set (0.00 sec)

mysql 5.1 >show variables like "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| innodb_version          | 1.0.12              |
| protocol_version        | 10                  |
| version                 | 5.1.53-Win X64      |
| version_comment         | Source distribution |
| version_compile_machine | unknown             |
| version_compile_os      | Win64               |
+-------------------------+---------------------+
6 rows in set (0.00 sec)

mysql 5.1 >

This bug is no longer present in version 5.1.54 while it exists in 5.1.53. A developer need only find which patch fixed the bug and close it as a duplicate. Hence I set risk to None and effort to Low.

The problem fixed in Bug#56423, closed as duplicate.