42000!TODO: column names of level >= 3

[aris-koning]

The following script fails spectacularly

from sqlalchemy import create_engine, schema, Column, Integer
from sqlalchemy.schema import MetaData, Table
from sqlalchemy.sql import select, insert

engine = create_engine('monetdb://monetdb:monetdb@localhost:50000/devdb', echo=True)
# engine = create_engine('postgresql+psycopg2://aris:[email protected]/aris', echo=True)

connection = engine.connect() 
with connection.begin() as trans:
    connection.execute(schema.CreateSchema("foo"))
    foo = MetaData(schema="foo")
    bar = Table(
        "bar",
        foo,
        Column('c1', Integer),
        Column('c2', Integer)
    )
    foo.create_all(connection)

    connection.execute(insert(bar).values(c1=10, c2=10))

    result = connection.execute(select([bar]))
    for row in result:
        print(row)

    trans.rollback()

on the line containing result = connection.execute(select([bar])) with inner error message:

sqlalchemy.exc.OperationalError: (pymonetdb.exceptions.OperationalError) 42000!TODO: column names of level >= 3
[SQL: SELECT foo.bar.c1, foo.bar.c2 
FROM foo.bar]

The underlying issue is actually due to MonetDB itself not being able to handle fully qualified column names like foo.bar.c1. There is a corresponding MonetDB github issue for it: SQL: level 3 naming #2526 . And I have compared the query generation for the Postgres backend psycopg2 of sqlalchemy. This generates the same select query. So this gives the impression that sqlalchemy expects its SQL backends to be able handle 3 level names.

So essentially this is not a sqlalchemy-monetdb problem. However I think it is a good idea to keep this issue open for as long as the underlying MonetDB issue isn't solved. And it would be nice to have this example added to the test suite eventually. Either as reminder to have this fixed on the MonetDB side or hopefully to have it as a test ready for a fix on a future release of MonetDB. The underlying issue effects the Jan2022 release and older versons of MonetDB.

Python library versions used for testing this:

Name: sqlalchemy-monetdb
Version: 1.0.0

Name: SQLAlchemy
Version: 1.3.24