[sqlite] trying a recursive view with version 3.9.0
11 okt 2015, 17:41, R.Smith: > On 2015-10-11 05:23 PM, E.Pasma wrote: >> 11 okt 2015, om 15:27, R.Smith: >>> >>> 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). > Hello again, sorry for not highlighting the Segmentation Fault. In WIndows it is likely a dr Watson case and in unix a bus error. The segmentation fault comes in the bash unix shelll from OS X. And in terms of spreadsheets the issue is that such error MAY occur when computing A2 from some formula including A2. I would not even care if it occured consistently, but it only occurs when the column names of the views are given after the view's name. Back in terms of SQL again. E. Pasma
[sqlite] trying a recursive view with version 3.9.0
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. Apologies, please ignore previous - I realised afterward it is the segfault that is the problem, not the reason for the error.
[sqlite] trying a recursive view with version 3.9.0
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
[sqlite] trying a recursive view with version 3.9.0
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.
[sqlite] trying a recursive view with version 3.9.0
On 10/11/15, E.Pasma wrote: > > One minor shortcoming however. If combined with the other new feature: > The CREATE VIEW statement now accepts an optional list of column names > following the view name. > the clarifying error message no longer appears and instead comes a > kind of bus error. I suppose this should be fixed. > Thanks for the bug report! The problem was that the new CREATE VIEW syntax that allowed column names to be specified with the view name was (accidentally) bypassing the circular definition check. Then later, SQLite would go into an infinite recursion trying to expand the view, resulting in a segfault when the stack overflowed. The problem has now been fixed on trunk and a new snapshot of the amalgamation is available at https://www.sqlite.org/download.html Please keep testing! We hope to release version 3.9.0 within the next few days, and it is far better to find and fix bugs before the release than afterwards! -- D. Richard Hipp drh at sqlite.org
[sqlite] trying a recursive view with version 3.9.0
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.
[sqlite] trying a recursive view with version 3.9.0
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. One minor shortcoming however. If combined with the other new feature: The CREATE VIEW statement now accepts an optional list of column names following the view name. the clarifying error message no longer appears and instead comes a kind of bus error. I suppose this should be fixed. I am still puzzled by this error message (view %s is circularly defined) itself as it already exists in earlier sqlite versions. But I can not make it appear there. Above case raises "no such table: main.v". Thanks, Edzard
