SQLite WITH + UNION issue my UNION queries cannot work when tables created by WITH are used, is this a SQLite limitation? for example - 

This works:

with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one 


This does not work:

--union cannot see tables made by with
with
T_one as (
    select * from T
),
T_two as (
    select * from T
)
select * from T_one union select * from T_two



  (sqlite3.OperationalError) no such table: T_one [SQL: u'--union cannot see tables made by with\nwith\nT_one as (\n    select * from T\n),\nT_two as (\n    select * from T\n)\nselect * from T_one union select * from T_two']
I was running the query inside python 2.7.10, which comes with sqlite 3.8.3.1. It turns out that if I run this directly with the sqlite 3.8.10.2 that comes with OSX, it works fine.    

I upgraded my python sqlite to 3.9.1 and it works now.  

Instructions per trac.edgewall.org/wiki/PySqlite


Download pysqlite-2.8.1.tar.gz from
trac.edgewall.org/wiki/PySqlite
Download sqlite-amalgamation-3090100.zip from
sqlite.org/download.html  
Unzip both and put them in the same folder 
Run this command from the folder:

python setup.py build_static install 


Note: I'm using OSX El Capitain, and a separate python installation (not the built in apple one) downloaded from python.org, and my .bash_profile is set up to point to this python installation