Bug #25227 Server side prepared statements + CASE + LEFT OUTER JOIN produces wrong result
Submitted: 21 Dec 2006 2:38 Modified: 21 Dec 2006 16:36
Reporter: Christopher Stach II Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Prepared statements Severity:S2 (Serious)
Version:>5.0.18, <=5.0.27 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: case, LEFT OUTER JOIN, prepared statement

[21 Dec 2006 2:38] Christopher Stach II
Description:
An odd combination of a server side prepared statement that is reused, a CASE statement, and a LEFT OUTER JOIN, produces an incorrect result from the CASE statement.  If the statement parameter is the same for both calls (same expected result), the result is correct.  I'm not positive that LEFT OUTER JOIN and CASE are both required, but this is a major simplification of what we ran across and it illustrates the problem.  (One odd thing was that it occasionally produced the incorrect result with a single call in our application, but I haven't seen that happen with my test case.)

How to repeat:
CREATE TABLE `base` (
        `id` INT NOT NULL AUTO_INCREMENT,
        PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `base` VALUES (1), (2);

CREATE TABLE `sub_a` (
        `base_id` INT NOT NULL,
        PRIMARY KEY  (`base_id`),
        FOREIGN KEY (`base_id`) REFERENCES `base` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sub_a` VALUES (1);

CREATE TABLE `sub_b` (
        `base_id` INT NOT NULL,
        PRIMARY KEY  (`base_id`),
        FOREIGN KEY (`base_id`) REFERENCES `base` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `sub_b` VALUES (2);

prepare foo from "select
        case
                when sub_a_0.base_id is not null then 1
                when sub_b_0.base_id is not null then 2
                when base_0.id is not null then 0
        end as derp
        from
                base base_0
                        left outer join
                                sub_a sub_a_0
                        on
                                base_0.id=sub_a_0.base_id
                        left outer join
                                sub_b sub_b_0
                        on
                                base_0.id=sub_b_0.base_id
        where
                base_0.id=?";

SET @id=1;
execute foo using @id;

SET @id=2;
execute foo using @id;

Suggested fix:
Not using server side prepared statements is a workaround.  Beyond that...
[21 Dec 2006 16:20] Valeriy Kravchuk
Thank you for a problem report. I've got the following results with 5.0.34-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.34-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `base` (
    ->         `id` INT NOT NULL AUTO_INCREMENT,
    ->         PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT INTO `base` VALUES (1), (2);
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE `sub_a` (
    ->         `base_id` INT NOT NULL,
    ->         PRIMARY KEY  (`base_id`),
    ->         FOREIGN KEY (`base_id`) REFERENCES `base` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO `sub_a` VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE `sub_b` (
    ->         `base_id` INT NOT NULL,
    ->         PRIMARY KEY  (`base_id`),
    ->         FOREIGN KEY (`base_id`) REFERENCES `base` (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO `sub_b` VALUES (2);
Query OK, 1 row affected (0.03 sec)

mysql> prepare foo from "select
    ">         case
    ">                 when sub_a_0.base_id is not null then 1
    ">                 when sub_b_0.base_id is not null then 2
    ">                 when base_0.id is not null then 0
    ">         end as derp
    ">         from
    ">                 base base_0
    ">                         left outer join
    ">                                 sub_a sub_a_0
    ">                         on
    ">                                 base_0.id=sub_a_0.base_id
    ">                         left outer join
    ">                                 sub_b sub_b_0
    ">                         on
    ">                                 base_0.id=sub_b_0.base_id
    ">         where
    ">                 base_0.id=?";
Query OK, 0 rows affected (0.01 sec)
Statement prepared

mysql> set @id=1;
Query OK, 0 rows affected (0.00 sec)

mysql> execute foo using @id;
+------+
| derp |
+------+
|    1 |
+------+
1 row in set (0.01 sec)

mysql> set @id=2;
Query OK, 0 rows affected (0.00 sec)

mysql> execute foo using @id;
+------+
| derp |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

They look correct for me. Did I miss something?
[21 Dec 2006 16:25] Christopher Stach II
That is correct, but I've only tested up to 5.0.27.  Might I ask what release this was fixed in?  I'm curious what it was (although the entire CASE rewrite might have done it.)
[21 Dec 2006 16:36] Valeriy Kravchuk
Looks like it was fixed after 5.0.27 (maybe as a result of other bugfix), but I am not sure what exact fix helped. Either wait for binaries of 5.0.34+ or just compile from current sources, if you really need this to work as expected.