SQLAlchemy: Investigate `CompileError: Unconsumed column names`

[amotl]

Introduction

Within the CrateDB-specific statement compiler for SQLAlchemy, there is a patch to support CrateDB's container data types OBJECT and ARRAY. It is located at CrateCompilerSA20.visit_update().

About

Parts of the patch remove a sanity check of SQLAlchemy, but that is currently needed to make things work. It can easily reproduced like this.

./bin/test -t test_nested_object_change_tracking

[...]
  File "/path/to/sqlalchemy/sql/compiler.py", line 703, in __init__
    self.string = self.process(self.statement, **compile_kwargs)
  File "/path/to/sqlalchemy/sql/compiler.py", line 748, in process
    return obj._compiler_dispatch(self, **kwargs)
  File "/path/to/sqlalchemy/sql/visitors.py", line 143, in _compiler_dispatch
    return meth(self, **kw)  # type: ignore  # noqa: E501
  File "/path/to/crate-python/src/crate/client/sqlalchemy/compat/core20.py", line 97, in visit_update
    crud_params_struct = _get_crud_params(
  File "/path/to/crate-python/src/crate/client/sqlalchemy/compat/core20.py", line 405, in _get_crud_params
    raise exc.CompileError(
sqlalchemy.exc.CompileError: Unconsumed column names: characters_name, data['nested']

Discussion references

We've observed it, and talked about it before.

• Add support for SQLAlchemy 1.4 crate-python#391 (review)
• SQLAlchemy backlog #74
• Add support for SQLAlchemy 2.0 crate-python#488 (comment)

Code references

The patch is the same for all support adapters.

https://github.com/crate/crate-python/blob/183a116afb62ee94717193303fb6dd7bb8821e58/src/crate/client/sqlalchemy/compat/core10.py#L223-L252

https://github.com/crate/crate-python/blob/183a116afb62ee94717193303fb6dd7bb8821e58/src/crate/client/sqlalchemy/compat/core14.py#L295-L324

https://github.com/crate/crate-python/blob/2ee91d52f6e07659c54337fe6bb08caf114e7469/src/crate/client/sqlalchemy/compat/core20.py#L392-L421