/ View Ticket
Login
Ticket UUID: 1a1308d2538d7ebb467fb112ba0abc1ba9f91507
Title: Nested correlated subquery fails when indices used
Status: Closed Type: Code_Defect
Severity: Severe Priority: Immediate
Subsystem: Unknown Resolution: Fixed
Last Modified: 2011-09-19 17:38:04
Version Found In:
Description:
Consider the following SQL:
CREATE TABLE t1(a,b);
INSERT INTO t1 VALUES(1,2);
INSERT INTO t1 VALUES(2,4);
INSERT INTO t1 VALUES(99,101);
INSERT INTO t1 VALUES(98,97);
CREATE TABLE t2(e,f);
INSERT INTO t2 VALUES(10,-8);
CREATE INDEX t1b ON t1(b);

SELECT 'one:', * FROM t1 WHERE b IN (SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2) GROUP BY e); SELECT 'two:', * FROM t1 WHERE +b IN (SELECT x FROM (SELECT e+f+a AS x, e FROM t2 ORDER BY 1 LIMIT 2) GROUP BY e);

The two queries should give identical results. The only difference is the unary "+" operator on the "b" of the LHS of the IN operator in the second query. That unary + disables the use of indices for trying to evaluate the IN operator.

But they give different results. We believe the second one (with the unary +) is giving the correct result.

This problem was introduced by check-in [daf730d1defa7] which was a refactoring of the name resolution logic. Prior to that check-in, these queries gave an error "no such column: a". So the problem is probably older than the check-in cited, but was not accessible until that check-in.

The problem was discovered while writing additional test cases for ticket [002caede898aee] though this problem appears to be unrelated to that other ticket.