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
