sql - MySQL Subquery returning incorrect result? -

i've got following mysql query / subquery:

select id, user_id, another_id, myvalue, created, modified, (     select id      users_values parentusersvalue     parentusersvalue.user_id = usersvalue.user_id     , parentusersvalue.another_id = usersvalue.another_id      , parentusersvalue.id < usersvalue.id      order id desc      limit 1 ) old_id  users_values usersvalue created >= '2009-12-20'  , created <= '2010-01-21'  , user_id = 9917 , another_id = 23 

given criteria listed, result subquery (old_id) should null (no matches found in table). instead of mysql returning null, seems drop "where parentusersvalue.user_id = usersvalue.user_id" clause , pick first value matches other 2 fields. mysql bug, or reason expected behavior?

update:

create table users_values (     id int(11) not null auto_increment,     user_id int(11) default null,     another_id int(11) default null,     myvalue double default null,     created datetime default null,     modified datetime default null,     primary key (id) ) engine=innodb auto_increment=2801 default charset=latin1 

explain extended:

id  select_type table   type    possible_keys   key key_len ref rows    filtered    1   primary usersvalue  index_merge user_id,another_id  user_id,another_id  5,5 null    1   100.00  using intersect(user_id,another_id); using 2   dependent subquery  parentusersvalue    index   primary,user_id,another_id  primary 4   null    1   100.00  using 

explain extended warning 1003:

select `mydb`.`usersvalue`.`id` `id`,`mydb`.`usersvalue`.`user_id` `user_id`,`mydb`.`usersvalue`.`another_id` `another_id`,`mydb`.`usersvalue`.`myvalue` `myvalue`,`mydb`.`usersvalue`.`created` `created`,`mydb`.`usersvalue`.`modified` `modified`,(select `mydb`.`parentusersvalue`.`id` `id` `mydb`.`users_values` `parentusersvalue` ((`mydb`.`parentusersvalue`.`user_id` = `mydb`.`usersvalue`.`user_id`) , (`mydb`.`parentusersvalue`.`another_id` = `mydb`.`usersvalue`.`another_id`) , (`mydb`.`parentusersvalue`.`id` < `mydb`.`usersvalue`.`id`)) order `mydb`.`parentusersvalue`.`id` desc limit 1) `old_id` `mydb`.`users_values` `usersvalue` ((`mydb`.`usersvalue`.`another_id` = 23) , (`mydb`.`usersvalue`.`user_id` = 9917) , (`mydb`.`usersvalue`.`created` >= '2009-12-20') , (`mydb`.`usersvalue`.`created` <= '2010-01-21')) 

this returns correct results (null) me:

create table users_values (id int not null primary key, user_id int not null, another_id int not null, created datetime not null);  insert    users_values values (1, 9917, 23, '2010-01-01');  select  *,         (         select  id            users_values parentusersvalue           parentusersvalue.user_id = usersvalue.user_id                 , parentusersvalue.another_id = usersvalue.another_id                 , parentusersvalue.id < usersvalue.id         order id                 desc         limit 1         ) old_id    users_values usersvalue   created >= '2009-12-20'         , created <= '2010-01-21'         , user_id = 9917         , another_id = 23 

could please run query:

select  count(*)    users_values usersvalue   user_id = 9917         , another_id = 23 

and make sure returns 1?

note subquery not filter on created, subquery can return values out of range main query defines.

update:

this bug in mysql.

most reason access path chosen usersvalues index_intersect.

this selects appropriate ranges both indexes , build intersection.

due bug, dependent subquery evaluated before intersection completes, that's why results correct another_id wrong user_id.

could please check if problem persists when force primary scan on usersvalues:

select  *,         (         select  id            users_values parentusersvalue           parentusersvalue.user_id = usersvalue.user_id                 , parentusersvalue.another_id = usersvalue.another_id                 , parentusersvalue.id < usersvalue.id         order id                 desc         limit 1         ) old_id    users_values usersvalue force index (primary)   created >= '2009-12-20'         , created <= '2010-01-21'         , user_id = 9917         , another_id = 23 

also, query should create composite index on (user_id, another_id, id) rather 2 distinct indexes on user_id , another_id.

create index , rewrite query little:

select  *,         (         select  id            users_values parentusersvalue           parentusersvalue.user_id = usersvalue.user_id                 , parentusersvalue.another_id = usersvalue.another_id                 , parentusersvalue.id < usersvalue.id         order                 user_id desc, another_id desc, id desc         limit 1         ) old_id    users_values usersvalue   created >= '2009-12-20'         , created <= '2010-01-21'         , user_id = 9917         , another_id = 23 

the user_id desc, another_id desc clauses logically redundant, make index used ordering.