2

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']

  • Which version of SQLite are you using? The latter query seems to work happily on SQLite 3.8.5 (via sql.js on sqlfiddle.com). – Dave Jones Oct 18 '15 at 8:37
  • I am 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. – Loi Oct 19 '15 at 1:04
  • So now my question would be: how to upgrade my python sqlite to 3.8.10 ? – Loi Oct 19 '15 at 3:40
  • I upgraded to the current version of sqlite (trac.edgewall.org/wiki/PySqlite) and it works now – Loi Oct 19 '15 at 4:42
  • 1
    Please add your own answer instead of suggesting an edit to the existing accepted answer. You can change the accepted answer as well, if you wish, once the 48 hour waiting period has elapsed. – Paul White Oct 19 '15 at 5:32
0

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

  1. Download pysqlite-2.8.1.tar.gz from trac.edgewall.org/wiki/PySqlite
  2. Download sqlite-amalgamation-3090100.zip from sqlite.org/download.html
  3. Unzip both and put them in the same folder
  4. 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.