|Title:||Nested correlated subquery fails when indices used|
|Last Modified:||2011-09-19 17:38:04|
|Version Found In:|
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);
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.