On 2016/03/09 4:35 PM, Igor Tandetnik wrote:
> On 3/9/2016 9:32 AM, Jean-Christophe Deschamps wrote:
>> At 15:16 09/03/2016, you wrote:
>>> select id from a where id not in (select a.id from b);
>>
>> Shouldn't the engine bark on this, like it does on the modified version:
>>
>> select id from a where id not in (select zzz.id from b);
>>
>> "no such column zzz.id"
>
> While there is no column named zzz.id, there is in fact a column named
> a.id. The query is syntactically valid.
>
>> As I understand it, there is no more an a.id column in table b.
>
> Yes, but why is that a problem? It is perfectly legal, and often
> useful, for a subquery to refer to columns from enclosing query.
> That's what makes it a *correlated* subquery.
True, but the OP's result is still in error. (Btw, I think this bug was
fixed recently, it certainly did come up before, in 3.9 somewhere I
think, tried to search the forum archive but no success).
To ask "SELECT a.id FROM a WHERE a.id NOT IN (SELECT a.ID FROM b)" means
that for every iteration over table a, the correlated query should look
in table b and return a.id (whatever that may be) as many times as there
are rows in table b. This means that for every iteration, the subquery
produces a repeating list of a.id values, and a.id MUST by definition
always be in that list, so that query should have zero rows of output.
To explain better, contrast with the same query when I use text values
in stead:
create table a(id TEXT);
create table b(id TEXT);
insert into a values ('AAA'), ('BBB'), ('CCC'), ('DDD');
insert into b values ('AAA'),('ZZZ');
select id from a where id not in (select a.id from b);
-- Zero output here, as expected
select id from a where id not in (select b.id from b);
-- id
-- -----
-- BBB
-- CCC
-- DDD
-- output as expected
As I recall, the bug only manifested when the rowid was implicated, such
as the OP's create TABLE a (id INTEGER PRIMARY KEY); definition implies.
