Using the new SQLite WITH RECURSIVE CTE clause

Question

SQLite 3.8.3 added support for CTE. I tried some of the CTE examples on this page and they work great. However, after reading the documentation and trying to adapt some of the examples, I cannot create a simple test.

First I create a simple table with two fields: idand parent. This will create a simple tree or linked list of entries:

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

Now I fill it with a few lines:

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 strings 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

test test1 id, , ? . ?

Answer 1

SELECT test1:

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;

, WHERE , , .