MySQL Subquery returning incorrect result?

Question

I've got the following MySQL query / subquery:

SELECT id, user_id, another_id, myvalue, created, modified,
(
    SELECT id 
    FROM users_values AS ParentUsersValue
    WHERE ParentUsersValue.user_id = UsersValue.user_id
    AND ParentUsersValue.another_id = UsersValue.another_id 
    AND ParentUsersValue.id < UsersValue.id 
    ORDER BY id DESC 
    LIMIT 1
) AS old_id

FROM users_values AS UsersValue
WHERE created >= '2009-12-20' 
AND created <= '2010-01-21' 
AND user_id = 9917
AND another_id = 23

Given the criteria listed, the result for the subquery (old_id) should be null (no matches would be found in my table). Instead of MySQL returning null, it just seems to drop the "WHERE ParentUsersValue.user_id = UsersValue.user_id" clause and pick the first value that matches the other two fields. Is this a MySQL bug, or is this for some reason the 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    Extra
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 where
2   DEPENDENT SUBQUERY  ParentUsersValue    index   PRIMARY,user_id,another_id  PRIMARY 4   NULL    1   100.00  Using where

EXPLAIN EXTENDED Warning 1003:

select `mydb`.`UsersValue`.`id` AS `id`,`mydb`.`UsersValue`.`user_id` AS `user_id`,`mydb`.`UsersValue`.`another_id` AS `another_id`,`mydb`.`UsersValue`.`myvalue` AS `myvalue`,`mydb`.`UsersValue`.`created` AS `created`,`mydb`.`UsersValue`.`modified` AS `modified`,(select `mydb`.`ParentUsersValue`.`id` AS `id` from `mydb`.`users_values` `ParentUsersValue` where ((`mydb`.`ParentUsersValue`.`user_id` = `mydb`.`UsersValue`.`user_id`) and (`mydb`.`ParentUsersValue`.`another_id` = `mydb`.`UsersValue`.`another_id`) and (`mydb`.`ParentUsersValue`.`id` < `mydb`.`UsersValue`.`id`)) order by `mydb`.`ParentUsersValue`.`id` desc limit 1) AS `old_id` from `mydb`.`users_values` `UsersValue` where ((`mydb`.`UsersValue`.`another_id` = 23) and (`mydb`.`UsersValue`.`user_id` = 9917) and (`mydb`.`UsersValue`.`created` >= '2009-12-20') and (`mydb`.`UsersValue`.`created` <= '2010-01-21'))

Answer 1

Did you try running the subquery only to see if you are getting the right results? Could you show us the schema for your users_values table?

Also, try replacing your SELECT id in your subquery by SELECT ParentUsersValue.id

Answer 2

This returns correct results (NULL) for 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
INTO    users_values VALUES (1, 9917, 23, '2010-01-01');

SELECT  *,
        (
        SELECT  id
        FROM    users_values AS ParentUsersValue
        WHERE   ParentUsersValue.user_id = UsersValue.user_id
                AND ParentUsersValue.another_id = UsersValue.another_id
                AND ParentUsersValue.id < UsersValue.id
        ORDER BY id
                DESC
        LIMIT 1
        ) AS old_id
FROM    users_values AS UsersValue
WHERE   created >= '2009-12-20'
        AND created <= '2010-01-21'
        AND user_id = 9917
        AND another_id = 23

Could you please run this query:

SELECT  COUNT(*)
FROM    users_values AS UsersValue
WHERE   user_id = 9917
        AND another_id = 23

and make sure it returns 1?

Note that your subquery does not filter on created, so the subquery can return values out of the range the main query defines.

Update:

This is definitely a bug in MySQL.

Most probably the reason is that the access path chosen for UsersValues is index_intersect.

This selects appropriate ranges from both indexes and build their intersection.

Due to the bug, the dependent subquery is evaluated before the intersection completes, that's why you get the results with the correct another_id but wrong user_id.

Could you please check if the problem persists when you force PRIMARY scan on the UsersValues:

SELECT  *,
        (
        SELECT  id
        FROM    users_values AS ParentUsersValue
        WHERE   ParentUsersValue.user_id = UsersValue.user_id
                AND ParentUsersValue.another_id = UsersValue.another_id
                AND ParentUsersValue.id < UsersValue.id
        ORDER BY id
                DESC
        LIMIT 1
        ) AS old_id
FROM    users_values AS UsersValue FORCE INDEX (PRIMARY)
WHERE   created >= '2009-12-20'
        AND created <= '2010-01-21'
        AND user_id = 9917
        AND another_id = 23

Also, for this query you should create a composite index on (user_id, another_id, id) rather than two distinct indexes on user_id and another_id.

Create the index and rewrite the query a little:

SELECT  *,
        (
        SELECT  id
        FROM    users_values AS ParentUsersValue
        WHERE   ParentUsersValue.user_id = UsersValue.user_id
                AND ParentUsersValue.another_id = UsersValue.another_id
                AND ParentUsersValue.id < UsersValue.id
        ORDER BY
                user_id DESC, another_id DESC, id DESC
        LIMIT 1
        ) AS old_id
FROM    users_values AS UsersValue
WHERE   created >= '2009-12-20'
        AND created <= '2010-01-21'
        AND user_id = 9917
        AND another_id = 23

The user_id DESC, another_id DESC clauses are logically redundant, but they will make the index to be used for ordering.