I ran into a similar issue: DELETE FROM ATable WHERE EXISTS(SELECT 1 FROM TMPTable AS t WHERE id = t.id) ;
Syntactically I was expecting id to be the ATable.id as I had aliased the TMPTable with t. But the result was a non-correlated subquery, id = t.id was always true. The fix is clear, yet a bit unintuitive in this circumstance: Why not request that all aliased tables use their table alias? On Thu, May 2, 2013 at 12:41 AM, Richard Hipp <d...@sqlite.org> wrote: > On Wed, May 1, 2013 at 3:23 PM, Anderson Medeiros Gomes > <amg1...@gmail.com>wrote: > > > Hi. I think I found a bug in SQLite, so I'm reporting it in this message. > > > > The print screen I have attached shows a query that SQLite executes and > > brings no results. I believe SQLite should trigger an error while parsing > > my input, because I used an unknown column in the subquery. > > > > SQLite is giving the correct response here. The "foocolumn" in the > subquery refers out to the containing query. We say that the subquery is a > "correlated subquery" because it contains references to the outer query. > > In your case, the query is logically equivalent to: > > SELECT foocolumn FROM footable WHERE 123 NOT IN (SELECT 123 FROM > bartable); > > Since bartable is not empty, the NOT EXISTS is always false and the query > returns no rows. > > > > > > > This is the print screen's textual representation: > > > > $ sqlite3 /tmp/test.sqlite > > SQLite version 3.7.16.2 2013-04-12 11:52:43 > > Enter ".help" for instructions > > Enter SQL statements terminated with a ";" > > sqlite> CREATE TABLE footable (foocolumn INTEGER); > > sqlite> CREATE TABLE bartable (barcolumn INTEGER); > > sqlite> INSERT INTO footable (foocolumn) VALUES (1); > > sqlite> INSERT INTO bartable (barcolumn) VALUES (2); > > sqlite> *SELECT foocolumn FROM footable WHERE foocolumn NOT IN (SELECT > > foocolumn FROM bartable);* > > sqlite> exit > > ...> ; > > Error: near "exit": syntax error > > sqlite> .quit > > > > > > > > -- > > Anderson Medeiros Gomes > > amg1...@gmail.com > > > > _______________________________________________ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > > -- > D. Richard Hipp > d...@sqlite.org > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
