Why can't I use WITH statement in sqlite3?

Question

I'm trying to execute the following query in sqlite3:

WITH
    MATCHES AS(
        SELECT      CSV2.*
                    , CSV1.ROW as ROW_1
                    , CSV1.C4 as C4_1
                    , CSV1.C5 as C5_1
        FROM        CSV2
        LEFT JOIN   CSV1
        ON          CSV1.C4 LIKE '%' || CSV2.C2 || '%'
    ),
    EXACT AS(
        SELECT      *
        FROM        MATCHES
        WHERE       C4_1 = C5_1
    ),
    MIN_ROW AS(
        SELECT      C1
                    , min(ROW_1) as ROW_1
        FROM        MATCHES
        WHERE       C1 NOT IN (SELECT C1 FROM EXACT)
        GROUP BY    C1, C2, C3, C4, C5
    )
    SELECT      *
    FROM        EXACT
    UNION
    SELECT      MATCHES.*
    FROM        MIN_ROW
    INNER JOIN  MATCHES
    ON          MIN_ROW.C1 = MATCHES.C1
    AND         (MIN_ROW.ROW_1 = MATCHES.ROW_1 OR MIN_ROW.ROW_1 IS NULL)
    ORDER BY    C1

But I receive a syntax error near "WITH" statement.

I am executing the query using Python and the following versions:

sqlite3.version is 2.6.0 and sqlite3.sqlite_version is 3.6.21

Answer 1

sqlite3.version has given me 2.6.0 and sqlite3.sqlite_version has given 3.6.21. I have no idea why there are two different versions for it. I'm working on Python 2.7.

The "why" is explained in the docs: the first is the version of the Python sqlite3 module (or, rather, the separately-developed pysqlite module that the stdlib module tracks); the second is the version number of the SQLite library itself that Python (or pysqlite) was built against. Python can build against pretty much any version of sqlite.* So, you can have Python 2.7.9 with SQLite 3.2, or 2.7.5 with SQLite 3.8.

Most Windows users use the official Python binary releases, rather than building it themselves. These usually have a pretty up-to-date version, but if you're using an ancient binary, it was probably built with an ancient sqlite.

And that's the key here. If you look at the changelog, you can see that "Added support for common table expressions and the WITH clause" didn't happen until 3.8.3, and you're using 3.6.21.

So, what are your options?

• Get a newer Python binary that's built with sqlite 3.8.3 or later. I think the current 2.7.9 official binary for Windows should qualify.
• Build Python yourself against a newer sqlite.
• Get or build the third-party pysqlite module with sqlite 3.8.3 or later, and use pysqlite.sqlite3 instead of just sqlite3. I'd assume that the package in Christoph Gohlke's repository is new enough, but I won't promise that.

---

_{* Well, 3.0 or later. For a while, Python 2.7 was broken if you tried to build against 3.0-3.4, but they intentionally fixed that, to allow people to build the newest Python with their platform's ancient sqlite.}