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: | |
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
[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.