question: malformed insert statement when calling create_table with Postgres backend

[dantegates]

What happened?

When trying to create tables form expressions which compile to SQL containing a CTE clause, the insert statements render with a syntax error - placing the insert into ... in the middle of the statement, rather the beginning.

E.g. the following code

ibis_connection = ibis.postgres.connect(...)
X = ibis_connection.table('X')
X.join(X.mutate(a=X['id'] % 1))
X = X.join(X.mutate(a=X['id'] + 1), ['id'])
ibis.show_sql(X)

> WITH t0 AS (
>   SELECT
>     t1.id AS id,
>     t1.id + 1 AS a
>   FROM dg_test AS t1
> )
> SELECT
>   t1.id,
>   t0.a
> FROM dg_test AS t1
> JOIN t0
>   ON t1.id = t0.id

When I call ibis_connection.create_table('test', X), sqlalchemy throws SyntaxError: syntax error at or near "INSERT" due the following insert statement (note that insert into should begin the statement)

WITH t0 AS 
(SELECT t1.id AS id, t1.id + %(param_1)s AS a 
FROM dg_test AS t1)
 INSERT INTO _ibis_tmp_table_insert_efv33lul9euv9qwo963bop9rm (id, a) SELECT t1.id, t0.a 
FROM dg_test AS t1 JOIN t0 ON t1.id = t0.id

What version of ibis are you using?

6.0.0

What backend(s) are you using, if any?

Postgres

Relevant log output

---------------------------------------------------------------------------
SyntaxError                               Traceback (most recent call last)
~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1899                 if not evt_handled:
-> 1900                     self.dialect.do_execute(
   1901                         cursor, statement, parameters, context

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    735     def do_execute(self, cursor, statement, parameters, context=None):
--> 736         cursor.execute(statement, parameters)
    737 

SyntaxError: syntax error at or near "INSERT"
LINE 4:  INSERT INTO _ibis_tmp_table_insert_efv33lul9euv9qwo963bop9r...
         ^


The above exception was the direct cause of the following exception:

ProgrammingError                          Traceback (most recent call last)
/var/folders/3_/t5j_szld1slc2jm0vb2r4s180000gn/T/ipykernel_11283/1057590007.py in <cell line: 5>()
      3 X = X.join(X.mutate(a=X['id'] + 1), ['id'])
      4 ibis.show_sql(X)
----> 5 ibis_connection.create_table('test1', X)

~/virtualenvs/v3.8/lib/python3.8/site-packages/ibis/backends/base/sql/alchemy/__init__.py in create_table(self, name, obj, schema, database, temp, overwrite)
    316                 try:
    317                     with self.begin() as bind:
--> 318                         bind.execute(method(tmptable.insert()))
    319 
    320                         # 2. recreate the existing table

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in execute(self, statement, *multiparams, **params)
   1378             )
   1379         else:
-> 1380             return meth(self, multiparams, params, _EMPTY_EXECUTION_OPTS)
   1381 
   1382     def _execute_function(self, func, multiparams, params, execution_options):

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/sql/elements.py in _execute_on_connection(self, connection, multiparams, params, execution_options, _force)
    332     ):
    333         if _force or self.supports_execution:
--> 334             return connection._execute_clauseelement(
    335                 self, multiparams, params, execution_options
    336             )

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_clauseelement(self, elem, multiparams, params, execution_options)
   1570             linting=self.dialect.compiler_linting | compiler.WARN_LINTING,
   1571         )
-> 1572         ret = self._execute_context(
   1573             dialect,
   1574             dialect.execution_ctx_cls._init_compiled,

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1941 
   1942         except BaseException as e:
-> 1943             self._handle_dbapi_exception(
   1944                 e, statement, parameters, cursor, context
   1945             )

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _handle_dbapi_exception(self, e, statement, parameters, cursor, context)
   2122                 util.raise_(newraise, with_traceback=exc_info[2], from_=e)
   2123             elif should_wrap:
-> 2124                 util.raise_(
   2125                     sqlalchemy_exception, with_traceback=exc_info[2], from_=e
   2126                 )

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/util/compat.py in raise_(***failed resolving arguments***)
    209 
    210         try:
--> 211             raise exception
    212         finally:
    213             # credit to

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/base.py in _execute_context(self, dialect, constructor, statement, parameters, execution_options, *args, **kw)
   1898                             break
   1899                 if not evt_handled:
-> 1900                     self.dialect.do_execute(
   1901                         cursor, statement, parameters, context
   1902                     )

~/virtualenvs/v3.8/lib/python3.8/site-packages/sqlalchemy/engine/default.py in do_execute(self, cursor, statement, parameters, context)
    734 
    735     def do_execute(self, cursor, statement, parameters, context=None):
--> 736         cursor.execute(statement, parameters)
    737 
    738     def do_execute_no_params(self, cursor, statement, context=None):

ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "INSERT"
LINE 4:  INSERT INTO _ibis_tmp_table_insert_efv33lul9euv9qwo963bop9r...
         ^

[SQL: WITH t0 AS 
(SELECT t1.id AS id, t1.id + %(param_1)s AS a 
FROM X AS t1)
 INSERT INTO _ibis_tmp_table_insert_efv33lul9euv9qwo963bop9rm (id, a) SELECT t1.id, t0.a 
FROM X AS t1 JOIN t0 ON t1.id = t0.id]
[parameters: {'param_1': 1}]
(Background on this error at: https://sqlalche.me/e/14/f405)

Code of Conduct

• I agree to follow this project's Code of Conduct

[lostmygithubaccount]

thanks for the report @dantegates! we'll look into this and update here

[cpcloud]

I am able to execute this INSERT INTO as is.

I suspect this is an issue with the postgres version you're using: it looks like support for this was added in postgres 9.1.

Are you using postgres 9 or earlier?

The oldest version of postgres supported by postgres itself is 11, so we won't be doing anything beyond best effort for versions older than 11 in ibis.

[dantegates]

@cpcloud thanks for looking into this. For posterity, I was technically executing this against a Redshift backend, so perhaps that was why you were unable to reproduce the error? In any event, in my case this looks to be an issue with sqlalchemy rather than ibis.

[lostmygithubaccount]

@dantegates glad it's fixed, did you have to upgrade sqlalchemy or is this an open issue on them? it would be interesting for us to track -- we should probably document that Redshift (and presumably Aurora) are supported through the Postgres backend if we don't currently note that anywhere

[dantegates]

A coworker was able to patch sqlalchemy 1.4.49 by following comments in this issue and this commit which addresses the issue for oracle and mysql. When he opens up a PR into sqlalchemy I'll link here for other users.

[dantegates]

Just to follow up on this, then SQL alchemy developers confirmed this is really an issue with using the postgres driver to work with Redshift. So it's not really a bug as everything is working as intended.