On Wed, May 18, 2011 at 5:11 PM, Petite Abeille <petite.abei...@gmail.com> wrote: > Where does the start_gap and end_gap come from? They are only declared in the > select part of the inner select statement, and nowhere in the from part. But > nonetheless, SQLite manages to use these non existing columns in the where > clause. What gives? > > In other words, how come the following works in SQLite: > > select 1 as value where value > 0; > > Or even weirder: > select 1 as value where value = 0; > > There is no from clause, there is no column 'value' per se, but nonetheless > that non existing column can be referenced in the where clause. What gives?
You don't need a table source in order to evaluate expressions, nor to have a WHERE condition. A SELECT without table sources will produce a single row defined by the column expressions, and filtered by the WHERE clause, if there is one. So a SELECT without table sources can produce zero or one rows. Why would you want to do that? I can think of some reasons: - So you can invoke a user-defined function and use that to produce a zero- or one-row result for use in IN clauses and so on. - So you can define a zero-row VIEW, which you might want to do if you want to allow only INSERTs on a view (with INSTEAD OF INSERT triggers) as a way to emulate stored procedures. (I've done this plenty, and indeed, I rely on this approach in my DB triggers patch to make the patch very small.) Nico -- _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
