On 10/16/2015 3:15 PM, Don V Nielsen wrote: > Given the following: > > select * from tmp_addresses [x] > where > rowid in ( > select rowid > from tmp_addresses [a] > where a.version_id = '0060' > and [a].zip = [x].zip and [a].crrt = [x].crrt > limit ( > SELECT net_non_pieces > FROM crrt_net_non [b] > WHERE [b].zip = [x].zip AND [b].crrt = [x].crrt > ) > ) > > The above sql fails parsing because of the alias [x] usage in the limit > clause.
As I said in another thread, LIMIT clause appears to be special, in that its expression is expected to be self-contained and is processed in isolation, without taking context into account. > If I eliminate the [x] alias from the limit, i.e. "WHERE [b].zip = > zip AND [b].crrt = crrt", then the sql parses and executes. The condition is then equivalent to "WHERE [b].zip = [b].zip AND [b].crrt = [b].crrt", and is always true. You may as well omit the WHERE clause entirely. > I'm curious as to why the alias is not accessible from the limit sub-query? It makes some sense once you think about it. Consider the simplest case: select * from someTable limit someField; What does this query mean? How may rows should it return (considering that each row in someTable may have a different value for someField)? The easiest way to avoid such paradoxes would be to require that LIMIT clause be independent of the query it's limiting. Now, one could argue that SQLite is too strict: it would be meaningful for a LIMIT clause on a correlated subquery to have access to fields from the enclosing query (if not the subquery itself). This doesn't appear to be supported though. -- Igor Tandetnik
