bind_expression, column_expression need to work per dialect impl, work for TypeDecorator, Variant

[Martin Webb]

When using with_variant() to specify a custom column (decorated type) for the dialect variant, implementations of bind_expression and col_expression are never called.

Attached file test.py contains a minimal example showing this in action.

Expected behaviour is that the INSERT query fails as it uses functions unavailable in SQLite, however bind_expression and col_expression are never called to make the SQL-level changes to apply these functions.

Obviously I'm not expecting the INSERT queries to actually work in SQLite because those functions don't exist, but I've written the example this way to demonstrate that the SQL-level overrides are never getting called.

The real world use case for this is that I'm trying to have a variant type apply for MySQL that uses COMPRESS() and UNCOMPRESS() while for local testing with SQLite the data is stored unchanged.

SQLAlchemy version: 1.1.9

Reproduced in: - Python 2.7.10 on Mac OS X 10.11.6 - Python 2.7.3 on Debian Wheezy

[Mike Bayer]

unfortunately the compiler does not consult the "impl" of a type in order to acquire bind_expression() and column_expression(). This will require major changes that I can only target at 1.2.

[Martin Webb]

So, am I right in thinking that it's specifically the use of Variant here that's the problem? I got as far as finding that _has_col_expression and _has_bind_expression were returning False when using Variant, which I assumed was why the overrides weren't getting called?

In the mean time, can you recommend another way of accomplishing this?

Regards.

[Mike Bayer]

not sure which route to take:

method one

the dialect should be passed to bind_expression() and column_expression(). In order to deal with backwards incompatibility, compiler will invoke type._compat_bind_expression and type._compat_column_expression, each of which do an inspect() on the real method and correct for the "dialect" argument missing, emit a warning, and cache the result on the type itself. TypeDecorator, or at the very least Variant, will start implementing bind_expression() and column_expression() and begin routing these methods to the "impl" in the absense of the user-defined

method two

compiler will begin consulting "type" and "impl" directly in _label_select_column and visit_bindparam(). The current logic that tests the type only will need to be expanded to accommodate for both type and dialect-specific impl - it will invoke first for dialect-specific impl and if an expression is returned, that is then wrapped in the one returned by dialect-agnostic impl.

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 6da0647..5161d2f 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -1213,10 +1213,19 @@ class SQLCompiler(Compiled):
                         literal_binds=False,
                         skip_bind_expression=False,
                         **kwargs):
-        if not skip_bind_expression and bindparam.type._has_bind_expression:
-            bind_expression = bindparam.type.bind_expression(bindparam)
-            return self.process(bind_expression,
-                                skip_bind_expression=True)
+        if not skip_bind_expression:
+            impl = bindparam.type.dialect_impl(self.dialect)
+
+            if bindparam.type._has_bind_expression:
+                bind_expression = bindparam.type.bind_expression(
+                    impl.bind_expression(bindparam)
+                    if impl._has_bind_expression else
+                    bindparam
+                )
+                return self.process(bind_expression, skip_bind_expression=True)
+            elif impl._has_bind_expression:
+                bind_expression = impl.bind_expression(bindparam)
+                return self.process(bind_expression, skip_bind_expression=True)

         if literal_binds or \
             (within_columns_clause and

at the moment option 2 seems better because it allows for dialect-specific impls to provide bind / column expressions too.

[Mike Bayer]

workaround for your case is to use custom compilation constructs:

#!/usr/bin/env python
import sqlalchemy
import sqlalchemy.ext.declarative
import sqlalchemy.orm
import sqlalchemy.types

from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement


class Compress(ColumnElement):
    def __init__(self, element, is_compress, type_):
        self.element = element
        self.is_compress = is_compress
        self.type_ = type_


@compiles(Compress)
def _default_compress(element, compiler, **kw):
    return compiler.process(element.element, **kw)


@compiles(Compress, "sqlite")
def _sqlite_compress(element, compiler, **kw):

    return compiler.process(
        sqlalchemy.func.compress(element.element, type_=element.type_)
        if element.is_compress else
        sqlalchemy.func.uncompress(element.element, type_=element.type_),
        **kw
    )


class CompressedLargeBinary(sqlalchemy.types.TypeDecorator):
    impl = sqlalchemy.types.LargeBinary

    def bind_expression(self, bindvalue):
        return Compress(bindvalue, True, self.impl)

    def column_expression(self, col):
        return Compress(col, False, self.impl)


Base = sqlalchemy.ext.declarative.declarative_base()


class Foo(Base):
    __tablename__ = "foo"

    id = sqlalchemy.Column(sqlalchemy.Integer, nullable=False, primary_key=True)
    bar = sqlalchemy.Column("baz", CompressedLargeBinary, nullable=False)

    def __init__(self, bar):
        self.id = None
        self.bar = bar

from sqlalchemy.dialects import sqlite
from sqlalchemy import select

stmt = select([Foo.__table__]).where(Foo.bar == 'asdf')
print stmt.compile(dialect=sqlite.dialect())

stmt = Foo.__table__.insert().values(baz='xyz')
print stmt.compile(dialect=sqlite.dialect())

[Mike Bayer]

Variant makes use of TypeDecorator and "impl" to switch between datatypes, so yes, Variant can only take effect for those places where the Core takes the time to look at the "impl". Historically this has not been within Compiler and the bind_expression() / col_expression() features, added much later, did not anticipate this.

[Martin Webb]

Your workaround works perfectly, thanks!

One other thing (though happy to raise a separate issue for this if required and I've already seen a few similar-looking issues): Having put the workaround into place in the real application, when I retrieve the uncompressed value from SQLite it's coming back as a buffer instance instead of str. I tried overriding process_result_value in CompressedLargeBinary to convert to str however now that never gets called. Did I miss something obvious?

[Martin Webb]

Just managed to resolve the above (secondary) issue with buffer and str. After double-checking the documentation at http://docs.sqlalchemy.org/en/latest/core/compiler.html, specifically the section that reads "ColumnElement classes want to have a type member which is expression’s return type.", I changed this (from your workaround), however I had to keep type_ as otherwise this fails when using MySQL:

class Compress(sqlalchemy.sql.ColumnElement):
    def __init__(self, element, is_compress, type_):
        self.element = element
        self.is_compress = is_compress
        self.type_ = type_

To this:

class Compress(sqlalchemy.sql.ColumnElement):
    def __init__(self, element, is_compress, type_):
        self.element = element
        self.is_compress = is_compress
        self.type = type_  # Needed to work with SQLite so that str is returned
        self.type_ = type_  # Needed to work with MySQL

Is it valid to have type and type_ together?

EDIT:

Actually, I see where type is referenced now, so I've updated the code I'm using to look like the following:

The two changes are:

• Using type instead type_
• Passing sqlalchemy.func.compress and sqlalchemy.func.uncompress in instead of using a bool to select which to use

import sqlalchemy.sql
import sqlalchemy.types
import sqlalchemy.ext.compiler


class Compress(sqlalchemy.sql.ColumnElement):
    def __init__(self, element, compress_func, type_):
        self.element = element
        self.compress_func = compress_func
        self.type = type_


@sqlalchemy.ext.compiler.compiles(Compress)
def _default_compress(element, compiler, **kw):
    return compiler.process(element.element, **kw)


@sqlalchemy.ext.compiler.compiles(Compress, "mysql")
def _sqlite_compress(element, compiler, **kw):
    return compiler.process(element.compress_func(element.element, type_=element.type), **kw)


class CompressedLargeBinary(sqlalchemy.types.TypeDecorator):
    impl = sqlalchemy.types.LargeBinary

    def bind_expression(self, bindvalue):
        return Compress(bindvalue, sqlalchemy.func.compress, self.impl)

    def column_expression(self, col):
        return Compress(col, sqlalchemy.func.uncompress, self.impl)

[Mike Bayer]

yeah that is all great, sorry about the type / type_ thing.

[Mike Bayer]

please implement using method two and allow for dialect-level bind_expression as well as nesting. See #4036.