[Sqlalchemy-tickets] Issue #3952: with_variant with dialect type and parameters doesn't work when query. (zzzeek/sqlalchemy)

[Isaku Y. <iss...@bi...>]

New issue 3952: with_variant with dialect type and parameters doesn't work when query.
https://bitbucket.org/zzzeek/sqlalchemy/issues/3952/with_variant-with-dialect-type-and

Isaku Yamahata:

The following code snipet doesn't work as expected from sqlalchemy 1.1.5
The cultprit is the change set of df9b6492e5ca47e26d539d2283fa816a2d5c8ad6
change-id of I7b7b45d604a4ae8d1dc236a5a1248695aab5232e
https://bitbucket.org/zzzeek/sqlalchemy/commits/df9b6492e5ca


import sqlalchemy as sa
from sqlalchemy.dialects import sqlite

class Data(Base):
   created_at = sa.Column(
        sa.DateTime().with_variant(
            sqlite.DATETIME(truncate_microseconds=True), 'sqlite'),
        server_default=sa.func.now())

# insert row and query based on created_at doesn't work as expected.
row = Data()
session.add(row)
session.flush()

rows = sessio.query(data.created_at).all()
row = rows[0]

# query by created_at
found = sessoin.squery(Data).filer(Data.created_at == row.created_at).all()

found should include added row. but after the change set pointed above, found is empty list.


by setting engine.echo=True, the following was gotten.
SELECT Data.created_at AS Data_created_at
WHERE Data.created_at = ?
2017-03-31 17:42:51,951 INFO sqlalchemy.engine.base.Engine.sqlite@vgluzzatoz ('2017-04-01 00:42:45.000000',)
('2017-04-01 00:42:45.000000',)

please notice the it includes microseconds as ".000000" even with truncate_microseconds=True

sqlite.DATETIME(truncate_micorseconds=True) should be used when bind_processor() is called, but plain DateTime is used. This is because of type_api.Variant.coerce_compared_value ignores dialects.