Because then you would lose automatic matching of unique field names everywhere else...
-----Ursprüngliche Nachricht----- Von: Thomas Krueger [mailto:tom.krue...@gmail.com] Gesendet: Donnerstag, 02. Mai 2013 13:15 An: General Discussion of SQLite Database Betreff: Re: [sqlite] Bug report: query parser should trigger an error if a subquery selects unknown columns from table 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); CREATE TABLE > > sqlite> bartable (barcolumn INTEGER); INSERT INTO footable > > sqlite> (foocolumn) VALUES (1); INSERT INTO bartable (barcolumn) > > sqlite> VALUES (2); *SELECT foocolumn FROM footable WHERE foocolumn > > sqlite> 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 -------------------------------------------------------------------------- Gunter Hick Software Engineer Scientific Games International GmbH Klitschgasse 2 – 4, A - 1130 Vienna, Austria FN 157284 a, HG Wien Tel: +43 1 80100 0 E-Mail: h...@scigames.at This e-mail is confidential and may well also be legally privileged. If you have received it in error, you are on notice as to its status and accordingly please notify us immediately by reply e-mail and then delete this message from your system. Please do not copy it or use it for any purposes, or disclose its contents to any person as to do so could be a breach of confidence. Thank you for your cooperation. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
