Using SQLite's new WITH RECURSIVE CTE clause

Question

SQLite 3.8.3 added support for CTEs. I tried some of the sample CTEs on this page and they work fine. However, after reading the documentation and trying to adapt some of the examples I am unable to create a simple test.

First, I create a simple table with two fields: id and parent. This will create a simple tree or linked list of records:

CREATE TABLE test(id INTEGER PRIMARY KEY ASC,parent INTEGER);

Now I populate it with a few rows:

INSERT INTO test (parent) VALUES (NULL); 
INSERT INTO test (parent) VALUES (1); 
INSERT INTO test (parent) VALUES (2); 
INSERT INTO test (parent) VALUES (3); 

After which I have a table that looks like this:

---+-------
id | parent
---+-------
 1 | NULL
 2 |  1
 3 |  2
 4 |  3

Now I want to generate a list of rows along the path between 3 and 1:

WITH RECURSIVE test1(id,parent) AS (
    VALUES(3,2) 
    UNION ALL 
    SELECT * FROM test WHERE test.parent=test1.id) 
SELECT * FROM test1;

But I get the error:

no such column: test1.id

Both test and test1 have an id field, so why does it claim it does not exist? I have reviewed the documentation several times and don't see my mistake. What am I doing wrong?

Answer 1

It is necessary to include the test1 table in the SELECT:

WITH RECURSIVE test1(id,parent) AS (
    VALUES(3,2)
    UNION ALL 
    SELECT test.id,test.parent FROM test,test1 WHERE test1.parent=test.id)
SELECT * FROM test1;

Note that the WHERE clause has been reversed, the original test in the question returns from the current row to the end, rather than from the end back to the start.