`splitgraph.exceptions.UnsupportedSQLError` when using Splitfiles

[ltrgoddard]

I've been enjoying exploring Splitgraph so far—great work! One thing that's bitten me a few times, though, is splitgraph.exceptions.UnsupportedSQLError when attempting to build Splitfiles. Here are a couple of examples using relatively straightforward Postgres syntax for subqueries and array indexing:

FROM splitgraph/congress_tweets:latest IMPORT {
  select
    t.id,
    t.text
  from (select * from tweets) as t
  limit 10
} AS tweets_subquery

FROM splitgraph/congress_tweets:latest IMPORT {
  select
    t.id,
    (string_to_array(t.text, ' '))[1]
  from tweets as t
  limit 10
} AS tweets_array

They throw Unsupported statement type RangeSubselect and Unsupported statement type A_Indirection respectively. This isn't a big problem in this case as the queries can be rewritten relatively simply, but I've found the latter issue to require a hacky workaround when using xpath(), which always returns an XML array, even for scalar values.

It would be helpful to have a reference page in the docs showing exactly which bits of Postgres syntax are not (yet) supported in Splitfiles, to make it easier to assess whether they're the right tool for the job.

[mildbyte]

Thanks for reporting this!

The SQL support in Splitfiles actually defers to PostgreSQL, but we restrict the AST nodes you can use in them. The list is here: https://github.com/splitgraph/splitgraph/blob/master/splitgraph/core/sql/_blacklist.py#L4-L45. The only reason is to prevent users from writing statements that might not play well with Splitfile caching behaviour.

I raised #412 to fix this. This is a stop-gap -- I think a better long-term strategy here is to just allow any SelectStmt through (which is what we do on data.splitgraph.com:5432).

[ltrgoddard]

That all makes sense—and thanks for the quick fix!

[mildbyte]

(I'll leave this open to track when we fix the AST handling in general so that we don't have to hunt down all node classes that people might want to use in SELECT statements.)