On 2015-10-11 05:23 PM, E.Pasma wrote: > 11 okt 2015, om 15:27, R.Smith: >> >> On 2015-10-11 03:14 PM, E.Pasma wrote: >>> Hello, >>> >>> One of the expected changes in 3..9.0 is: >>> A view may now reference undefined tables and functions when >>> initially created. Missing tables and functions are reported when >>> the VIEW is used in a query. >>> I could not resist trying a recursive view like in a CTE: >>> >>> SQLite version 3.8.12 2015-10-07 13:24:27 >>> create view v as select 1 as c union all select c+1 from v where c<10; >>> select c from v; >>> Error: view v is circularly defined >>> >>> So this is clearly not intended. >> >> You cannot reference a View within itself - this is what circularly >> means. This is very different to being able to reference a table that >> doesn't exist yet. > > > That was only meant as an introduction. My only real point is that the > particular error is not always raised. > Here is a more to the point example: > > SQLite version 3.8.12 2015-10-07 13:24:27 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> create view v as select c+1 from v; > sqlite> select *from v; > Error: view v is circularly defined > sqlite> create view v2(c) as select c+1 from v2; > sqlite> select *from v2; > Segmentation fault > $ > > Sorry for being too verbose in the original mail.
You were not too verbose. The problem is still the same - You still cannot reference a View within itself - that is still what Circularly means. It is still very different to referencing a not-yet-existing table. Open Excel or Calc. Go to Cell A1 - type 100 in there. Now go to Cell A2, and type in there a function like this: = A1 + A2 When you try to commit it, an error will pop up saying you cannot use a circular reference - meaning you cannot compute the value of A2 by using A2 itself as one of the source fields (which will cause an infinite loop). This is exactly the same reason you cannot create a view V by a query that uses fields from within V itself. So it is not that you are simply trying to read a table that does not exist yet, it is that you are reading the same table that you are creating right now - which, as I've mentioned, is a very different thing. To put it differently: V is not /missing/ at the time of creation, it is /self-referencing/ - the first is allowed, the second is not. What in the documentation for 3.9.0 makes you think this should work? (Apologies for the long-winded explanation, but the short one missed a trick). Cheers! Ryan
