[sqlite] Correlated subquery refers to wrong rowid - bug?

On 2016/03/09 5:39 PM, Jean-Christophe Deschamps wrote: > > Sorry guys, I don't know why I wrote that. In fact I know: I shouldn't > be talking over the phone while reading the list. > > Of course I use correlated subqueries a lot, but never had to > re-select a column from the enclosing query.

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > which outputs one result (2), although the expected result would be > > empty. Sorry for my "what bug?" post. I forgot that the output was wrong! --jkl

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 09 Mar 2016 15:32:01 +0100 Jean-Christophe Deschamps wrote: > > select id from a where id not in (select a.id from b); > As I understand it, there is no more an a.id column in table b. It > looks like SQLite is trying to get clever ignoring the "a." qualifier. It's not ignoring the q

[sqlite] Correlated subquery refers to wrong rowid - bug?

On Wed, 9 Mar 2016 10:13:28 -0500 Richard Hipp wrote: > > select id from a where id not in (select a.id from b); > > > > which outputs one result (2), although the expected result would be > > empty. > > > > Thanks for the bug report. What bug? The query is valid SQL, and produces the correc

[sqlite] Correlated subquery refers to wrong rowid - bug?

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

[sqlite] Correlated subquery refers to wrong rowid - bug?

At 16:14 09/03/2016, you wrote: >On 3/9/2016 9:58 AM, R Smith wrote: >>On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >>>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

[sqlite] Correlated subquery refers to wrong rowid - bug?

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" As I understand it, there is no more an a.id colum

[sqlite] Correlated subquery refers to wrong rowid - bug?

In the question David Paton asks about a query like this: create table a(id integer primary key); create table b(id integer primary key); insert into a values (1), (2); insert into b values (1); select

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 10:39 AM, Jean-Christophe Deschamps wrote: > Of course I use correlated subqueries a lot, but never had to re-select > a column from the enclosing query. Indeed, I'd say that most of the time > one uses columns from enclosing query as expressions, e.g. in comparison > operators or funct

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/16, Richard Hipp wrote: > > The fix is here: https://www.sqlite.org/src/info/1ed6b06ea3c432f9 > The "Pre-release Snapshot" at https://www.sqlite.org/download.html contains the fix. See https://www.sqlite.org/draft/releaselog/3_12_0.html for a summary of other changes in the Pre-release Sn

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/2016 9:58 AM, R Smith wrote: > On 2016/03/09 4:35 PM, Igor Tandetnik wrote: >> 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 i

[sqlite] Correlated subquery refers to wrong rowid - bug?

On 3/9/16, Clemens Ladisch wrote: > In the question > > David Paton asks about a query like this: > > create table a(id integer primary key); > create table b(id integer primary key); > insert into a values (

[sqlite] Correlated subquery refers to wrong rowid - bug?

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