title: Using SQLite's new WITH RECURSIVE CTE clause
body: <p>SQLite 3.8.3 added support for CTEs.  I tried some of the <a href="http://www.sqlite.org/lang_with.html" rel="noreferrer">sample CTEs on this page</a> 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.</p>
  
  <p>First, I create a simple table with two fields: <code>id</code> and <code>parent</code>.  This will create a simple tree or linked list of records:</p>
  
  <pre><code>CREATE TABLE test(id INTEGER PRIMARY KEY ASC,parent INTEGER);
  </code></pre>
  
  <p>Now I populate it with a few rows:</p>
  
  <pre><code>INSERT INTO test (parent) VALUES (NULL); 
  INSERT INTO test (parent) VALUES (1); 
  INSERT INTO test (parent) VALUES (2); 
  INSERT INTO test (parent) VALUES (3); 
  </code></pre>
  
  <p>After which I have a table that looks like this:</p>
  
  <pre><code>---+-------
  id | parent
  ---+-------
   1 | NULL
   2 |  1
   3 |  2
   4 |  3
  </code></pre>
  
  <p>Now I want to generate a list of rows along the path between 3 and 1:</p>
  
  <pre><code>WITH RECURSIVE test1(id,parent) AS (
      VALUES(3,2) 
      UNION ALL 
      SELECT * FROM test WHERE test.parent=test1.id) 
  SELECT * FROM test1;
  </code></pre>
  
  <p>But I get the error:</p>
  
  <pre><code>no such column: test1.id
  </code></pre>
  
  <p>Both <code>test</code> and <code>test1</code> have an <code>id</code> 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?</p>