| Bug #9096 | select doesn't return all matched records if prepared statements is used | ||
|---|---|---|---|
| Submitted: | 10 Mar 2005 16:08 | Modified: | 9 May 2005 2:47 |
| Reporter: | Victoria Reznichenko | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | Any (any) |
| Assigned to: | Konstantin Osipov | CPU Architecture: | Any |
[10 Mar 2005 16:15]
MySQL Verification Team
bug_9096.zip was uploaded to ftp server.
[30 Mar 2005 19:53]
Konstantin Osipov
I was able to repeat the bug.
A compact test case is below.
drop table if exists t1;
create table t1 (
c1 int(11) not null, c2 int(11) not null, c3 int(11) not null,
c4 int(11) not null, c5 int(11) not null, c6 int(11),
primary key (c1,c2,c5,c3),
key c2 (c2), key c1 (c1), key c5 (c5), key c5_2 (c5)
);
insert into t1 values (200887, 860, 1, 10, 31, 2);
insert into t1 values (200887, 200887, 1, 1, 31, 2);
select * from t1
where (c1=200887 and c2=200887 and c5=31 and c3=1) or
(c1=200887 and c2=860 and c5=31 and c3=1);
prepare stmt from
"select * from t1
where (c1=200887 and c2=200887 and c5=31 and c3=1) or
(c1=200887 and c2=860 and c5=31 and c3=1)";
execute stmt;
execute stmt;
prepare stmt from
"select * from t1
where (c1=200887 and c2=? and c5=31 and c3=1) or
(c1=200887 and c2=? and c5=31 and c3=1)";
set @a=200887, @b=860;
execute stmt using @a, @b;
execute stmt using @a, @b;
deallocate prepare stmt;
drop table t1;
[30 Mar 2005 20:01]
Konstantin Osipov
Even simplier: prepare stmt from "select * from t2 where (c1=200887 and c2=?) or c2=?"; execute stmt using @c2, @c2_2;
[17 Apr 2005 2:23]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/24089
[4 May 2005 7:46]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/internals/24541
[4 May 2005 8:05]
Konstantin Osipov
Fixed in 4.1.12 and 5.0.6
[9 May 2005 2:47]
Paul DuBois
Noted in 4.1.12, 5.0.6 changelogs.
Description: This SELECT statement returns 2 rows: mysql> SELECT CHILD_MEMBER_RK,PARENT_MEMBER_RK,HIERARCHY_RK,REVISION,CHILD_ORDER _NO,VALID_FROM_DTTM,VALID_TO_DTTM,SOURCE_SYSTEM_RK -> FROM CHILD_PARENT -> WHERE (CHILD_MEMBER_RK=200887 AND PARENT_MEMBER_RK=200887 AND HIERARCHY_R K=31 AND REVISION=1) -> OR (CHILD_MEMBER_RK=200887 AND PARENT_MEMBER_RK=860 AND HIERARCHY_RK=31 A ND REVISION=1); +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ | CHILD_MEMBER_RK | PARENT_MEMBER_RK | HIERARCHY_RK | REVISION | CHILD_ORDER_NO | VALID_FROM_DTTM | VALID_TO_DTTM | SOURCE_SYSTEM_RK | +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ | 200887 | 860 | 31 | 1 | 10 | 2005-01-05 12:29:04 | 2005-01-05 12:48:03 | 2 | | 200887 | 200887 | 31 | 1 | 1 | 2005-01-05 11:45:34 | 2005-01-05 11:49:58 | 2 | +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ 2 rows in set (0.01 sec) If I rewrite it with prepared statements, it returns only 1 row: mysql> prepare bar from 'SELECT CHILD_MEMBER_RK,PARENT_MEMBER_RK,HIERARCHY_RK,RE VISION,CHILD_ORDER_NO,VALID_FROM_DTTM,VALID_TO_DTTM,SOURCE_SYSTEM_RK '> FROM CHILD_PARENT '> WHERE (CHILD_MEMBER_RK=? AND PARENT_MEMBER_RK=? AND '> HIERARCHY_RK=? AND REVISION=?) '> OR (CHILD_MEMBER_RK=? AND PARENT_MEMBER_RK=? AND HIERARCHY_RK=? '> AND REVISION=?)'; Query OK, 0 rows affected (0.00 sec) Statement prepared mysql> mysql> set @param1 = 200887; Query OK, 0 rows affected (0.00 sec) mysql> set @param2 = 200887; Query OK, 0 rows affected (0.00 sec) mysql> set @param3 = 31; Query OK, 0 rows affected (0.01 sec) mysql> set @param4 = 1; Query OK, 0 rows affected (0.00 sec) mysql> set @param5 = 200887; Query OK, 0 rows affected (0.00 sec) mysql> set @param6 = 860; Query OK, 0 rows affected (0.00 sec) mysql> set @param7 = 31; Query OK, 0 rows affected (0.00 sec) mysql> set @param8 = 1; Query OK, 0 rows affected (0.00 sec) mysql> execute bar using @param1, @param2, @param3, @param4, @param5, -> @param6, @param7, @param8; +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ | CHILD_MEMBER_RK | PARENT_MEMBER_RK | HIERARCHY_RK | REVISION | CHILD_ORDER_NO | VALID_FROM_DTTM | VALID_TO_DTTM | SOURCE_SYSTEM_RK | +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ | 200887 | 200887 | 31 | 1 | 1 | 2005-01-05 11:45:34 | 2005-01-05 11:49:58 | 2 | +-----------------+------------------+--------------+----------+---------------- +---------------------+---------------------+------------------+ 1 row in set (0.00 sec) How to repeat: 1. Restore table (uploaded) 2. prepare bar from 'SELECT CHILD_MEMBER_RK,PARENT_MEMBER_RK,HIERARCHY_RK,REVISION,CHILD_ORDER_NO,VALID_FROM_DTTM,VALID_TO_DTTM,SOURCE_SYSTEM_RK FROM CHILD_PARENT WHERE (CHILD_MEMBER_RK=? AND PARENT_MEMBER_RK=? AND HIERARCHY_RK=? AND REVISION=?) OR (CHILD_MEMBER_RK=? AND PARENT_MEMBER_RK=? AND HIERARCHY_RK=? AND REVISION=?)'; set @param1 = 200887; set @param2 = 200887; set @param3 = 31; set @param4 = 1; set @param5 = 200887; set @param6 = 860; set @param7 = 31; set @param8 = 1; execute bar using @param1, @param2, @param3, @param4, @param5, @param6, @param7, @param8;