SQLite Forum

Reference to RECURSIVE table column apparently not allowed in nested SELECT
Login

Reference to RECURSIVE table column apparently not allowed in nested SELECT

(1) By Richard PArkins (rparkins) on 2020-12-11 12:33:31 [link] [source]

The following statement works as expected

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT 1 ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;

and produces a single row with fields '1,2,3,4,5,6,7,8,9,10' and a null (which happens to be the Page field of the first record of GetreuerMusicMeister). The following statement

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT cnt.x ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;
fails with the error no such column: cnt.x Unable to execute statement

It looks as if the table "cnt" is somehow no longer visible inside the nested SELECT statement used as an expression.

(2) By Richard Hipp (drh) on 2020-12-11 13:02:59 in reply to 1 [link] [source]

Correct.

Quoting from the documentation:

[T]he recursive table must appear exactly once in the FROM clause of each top-level SELECT statement in the recursive-select and must not appear anywhere else in either the initial-select or the recursive-select, including subqueries.

I think you will find a similar limitation in the recursive common table expression implementation of every other SQL RDBMS out there. If you find another that will let you do what you demonstrate, please let me know.

(3) By Richard PArkins (rparkins) on 2020-12-11 13:45:00 in reply to 2 [link] [source]

Well, if "The recursive table" includes references to columns in it, then this rule is broken in the first example given in section 3.1

WITH RECURSIVE
  cnt(x) AS (VALUES(1) UNION ALL SELECT x+1 FROM cnt WHERE x<1000000)
SELECT x FROM cnt;
where "x" appears twice outside the FROM clause

If I remove the "cnt." from my example

WITH RECURSIVE
  cnt ( x , list , done , Pagelist ) AS (
    VALUES ( 1 , 1 , 0 , '' ) UNION ALL
    SELECT x + 1 AS x ,
    ( list || ',' || ( cnt.x + 1 ) ) AS list,
    ( x == 9 ) AS done ,
    ( SELECT Page FROM GetreuerMusicMeister LIMIT x ) AS Pagelist
    FROM cnt WHERE x < 10
  )
SELECT list , Pagelist FROM cnt WHERE done == 1 ;
it still doesn't work, but the error message refers to "x" rather than "cnt.x".

So the rule seems to be applied more strictly in subqueries.

(4) By Ryan Smith (cuz) on 2020-12-11 15:17:25 in reply to 2 [source]

Richard I think you are wrong and perhaps you looked over the example too quick.

The "cnt" Table is only ever used once as a Table reference, thereafter only the column cnt.x was referenced, it's perfectly acceptable and works very well in PostGres, MariaDB, MySQL, MSSQL, etc. etc.

The fault in the OP's query is not his table reference but indeed that he tried to use a column reference in a LIMIT clause, which is 100% not allowed in the SQL languages mentioned (there might be others that do allow it, but I don't know of them).

Had he used that column reference nearly anywhere else in that sub query, it would work in SQLite as much as in every other RDBMS. It's easily demonstrable if needed, but I imagine the case will be apparent on a mere moment's reflection.

(5) By ddevienne on 2020-12-11 15:23:50 in reply to 4 [link] [source]

Hmmm, but in https://sqlite.org/lang_select.html LIMIT takes an expr which normally should allow referencing columns, no?

(8) By Ryan Smith (cuz) on 2020-12-11 15:37:01 in reply to 5 [link] [source]

Well Dominique, I was going to point out that while the base construct is a an "expression", that it can't reference its own query result, even of a previously established outer-query, and that this is made clear in the text further down...

But, surprisingly, I cannot actually find any such contradiction to the premise in any further down text.

From my reading of the current documentation, you are correct and there doesn't seem to be anything suggesting that "expr" in a LIMIT clause has any trouble with deriving it's value from the query itself in SQLite.

Interesting. Some experimentation seems to be needed.

(9) By Keith Medcalf (kmedcalf) on 2020-12-11 15:43:42 in reply to 4 [link] [source]

It should probably be documented that limit and offset require "constant expressions". At the moment the documentation simply requires that they contain an expression that evaluates to an integer, and an expression includes a column reference.

The error message is merely wrong -- the error should be something along the lines that is is not a constant expression or something to that effect, not that the column cannot be found.

(13) By Richard PArkins (rparkins) on 2020-12-12 20:09:56 in reply to 4 [link] [source]

Sorry, my response was a bit hasty. I see now that cnt.x (a column reference as opposed to a reference to the whole table) should be allowed within the scope of a SELECT ... FROM cnt ....

However I was (I think quite reasonably) expecting that inside a recursive SELECT cnt.x should refer to the content of the currently active row in column cnt.x (see The basic algorithm 2.c) and not the whole column as you suggest. This does not seem to work inside a subquery.

I still think that there is a problem here, and in any case the error message given (no such column) is entirely unhelpful as other posters have suggested.

Of course my example is wrong anyway because, even if cnt.x was valid, when it is >1 the result of the subquery is a vector where a scalar value is required. The original intention was LIMIT 1 OFFSET cnt.x which delivers a scalar value. I was simplifying my original code in order to avoid an overly large post and I overdid it.

BTW I'm using sqlite 3.28.0, the latest version available as a prebuilt library on my Linix distro (OpenSUSE Leap 14.1).

I was somewhat struggling as this was my first attempt at a recursive select. I was eventually able to do what I wanted (extract lists of items from a table which contained items with pointers to next and previous ones).

The key was realising by looking at the Alice example that I could SELECT from more than one table at a time. This is of course allowed by the SELECT syntax diagram, but I had never had occasion to use it before: it avoids the troublesome subquery.

(6) By Keith Medcalf (kmedcalf) on 2020-12-11 15:25:52 in reply to 2 [link] [source]

It appears that in the current tip correlated subqueries are indeed permitted in recursive expressions and that they work just fine.

SQLite version 3.35.0 2020-12-11 14:46:25
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table x(x);
sqlite> insert into x values (6);
sqlite> with recursive y(y,z) as (values (1,1) union all select y+1, (select x from x where x == y) from y limit 10) select * from y;
┌────┬───┐
│ y  │ z │
├────┼───┤
│ 1  │ 1 │
│ 2  │   │
│ 3  │   │
│ 4  │   │
│ 5  │   │
│ 6  │   │
│ 7  │ 6 │
│ 8  │   │
│ 9  │   │
│ 10 │   │
└────┴───┘

which is what I would expect. Either this has been "fixed" or the nonsensical use by the OP is just tossing the wrong error message when attempting to bugger with the limit of a scalar subquery which must be scalar (ie, 1).

(7) By Richard Hipp (drh) on 2020-12-11 15:35:36 in reply to 6 [link] [source]

Correlated subqueries have always been permitted in recursive common table expressions. Your test script works fine on SQLite 3.8.3 (the release from 2014-02-03 which first introduced recursive common table expressions). The restriction applies to the recursive table - the table that is being computed - "y" in your example.

(10) By Ryan Smith (cuz) on 2020-12-11 15:43:44 in reply to 7 [link] [source]

Agreed, absolutely.

But the OP's original query that prompted the question honestly does not break that rule either, it is very much exactly semantically equivalent (except for the self-referencing limit) to Keith's test.

Hence the confusion.

(11) By Keith Medcalf (kmedcalf) on 2020-12-11 16:05:08 in reply to 10 [link] [source]

The documentation does say "scalar expression". My experimentation indicates that this must be a "constant scalar expression" and that the error message is merely wrong (as is the documentation).

(12.3) By Keith Medcalf (kmedcalf) on 2020-12-11 16:37:56 edited from 12.2 in reply to 10 [link] [source]

Note that "constant scalar expression" means that the following is permitted:

select * from x limit (select x from x);

but the following is not:

select * from x as o limit (select x from x where x == o.x);

and indeed this is the case and the latter will produce the same error message "Error: no such column: o.x" when really the error message should be "Error: not a constant expression: o.x" (as the simplest form). "Error: LIMIT/OFFSET must be a constant expression" would also be satisfactory and accurate.

This means, of course, that things such as:

select * from here limit (select pagesize from config) offset (?-1)*(select pagesize from config);

uses "constant scalar expressions" in the limit and offset clauses to allow one to dynamically select the "page" of results where the number of rows per page is stored in a configuration table, for naive application paging (which one should not ever do).

This, of course, leads to the question of the definition of the term "constant" in "constant scalar expression" which currently appears to mean "constant over the entire query including outer containing queries".

** Edited to include example for paging and definition of "constant"
** Edited to correct also the referent o.x