New with Recursive CTE clause with SQLite

SQLite 3.8.3 adds support for CTE. I tried some sample CTEs on this page and they work fine. However, after reading the documentation and trying to adjust some examples, I can’t 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:

< p>

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

Now I fill it with a few lines:

< pre>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 that 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 error :

no such column: test1.id

test and test1 both have an id field, why does it claim that it does not exist? I have checked the documentation many times, but I have not seen my error. What am I doing wrong?

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;

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

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

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

no such column: test1.id

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;