# subquery doesn't work with SQLExecute

I want to select, say, rows 6 through 12 of a sqlite database. So I tried a subquery, but it does not work:

SQLExecute[conn,"SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);"


and generates

JDBC::error: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)


but the same SQL code works fine from within sqlite3 itself.

Every variation of subquery I've tried gives the same error. The error message makes it sound like SQLite is complaining, rather than JDBC, or Mathematica, but I don't see how that can be since it works fine if I run sqlite3 from the commandline. Non-subquery selects work, so there's nothing wrong with the connection.

I guess I do not understand how to use SQLExecute.

How do I extract a fixed range of rows from a SQLite database?

I'm using 12.0.0 for Mac OS X x86 (64-bit) (April 7, 2019) and sqlite3 version 3.27.2.

Explicitly, this fails:

In[425]:= conn = OpenSQLConnection[JDBC["SQLite", "chains2d005.sqlite"]];

In[426]:= SQLExecute[conn, "SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);"]

During evaluation of In[426]:= JDBC::error: [SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

Out[426]= \$Failed


But this works:

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> SELECT * FROM (SELECT row_number() OVER (ORDER BY id) AS RowNum, * FROM chains) WHERE (6 <= RowNum AND RowNum <= 12);


I copy/paste the SQL so it is the same, although I've stared at it so long now, maybe I'm missing something.

• Can you provide the connection details? It's not obvious you are in fact doing exactly the same thing. – Julian Moore Dec 15 '19 at 19:56
• I think the problem is that the subquery needs an alias. Are you sure the exact same query runs from the sqlite3 client? – Rohit Namjoshi Dec 15 '19 at 20:33
• I added more details to the question. – Scott Crittenden Dec 15 '19 at 21:50
• What is the result of SQLExecute[conn, "select sqlite_version();"] – Rohit Namjoshi Dec 16 '19 at 17:31
• This error is most likely due to your SQLite driver being too old. SQLite only started supporting window functions since 3.25.0. Your database is up to date, yes, but the JDBC driver is not. You will need to update the JDBC driver used for SQLIte. The details of how to do that can be found in documentation. – Leonid Shifrin Dec 17 '19 at 9:01