Changing column order causes baked tuple_ query to raise TypeError

[acowlikeobject]

Describe the bug
If I create a "regular" tuple_ query, it works as expected, even if I switch columns around in tuple_.

However, switching column order causes the baked variant of the same tuple_ query to raise a sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.. Seems to happen with Date and DateTime columns, but that may be because most of my other columns have interchangeable types.

May very well be user error. Would love to know what I'm doing wrong with the baked query.

Expected behavior
All queries return a count of 1.

To Reproduce

from sqlalchemy import Column, Date, Integer, create_engine, tuple_, bindparam
from sqlalchemy.ext.baked import bakery as bakery_maker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session

from datetime import date


Base = declarative_base()

class Foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, primary_key=True)
    first = Column(Integer)
    second = Column(Date)


# Setup.
engine = create_engine('sqlite:///:memory:')
Base.metadata.create_all(engine)
session = Session(bind=engine)

# Insert a row.
foo = Foo(first=1, second=date(2000, 1, 1))
session.add(foo)


def make_regular_query(columns, values):
    return session.query(Foo).filter(tuple_(*columns).in_(values))


def make_baked_query(columns):
    return bakery_maker()(lambda session: session.query(Foo)
        .filter(tuple_(*columns).in_(bindparam('values', expanding=True))))


def run():
    # (first, second) order.
    cols = Foo.first, Foo.second
    values = [(foo.first, foo.second)]

    print('(first, second) regular query',
          make_regular_query(cols, values).count())

    print('(first, second) baked query', 
          make_baked_query(cols)(session).params(values=values).count())


    # (second,) order.
    cols = (Foo.second,)
    values = [(foo.second,)]

    print('(second,) regular query',
          make_regular_query(cols, values).count())

    print('(second,) baked query',
          make_baked_query(cols)(session).params(values=values).count())


    # (second, first) order.
    cols = Foo.second, Foo.first
    values = [(foo.second, foo.first)]

    print('(second, first) regular query',
          make_regular_query(cols, values).count())

    print('(second, first) baked query',
          make_baked_query(cols)(session).params(values=values).count())
    # sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.

run()

Error

(first, second) regular query 1                                                                           
(first, second) baked query 1
(second,) regular query 1                                                                                
(second,) baked query 1                                                                                  
(second, first) regular query 1
Traceback (most recent call last):
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1211, in _execute_context
    context = constructor(dialect, self, conn, *args) 
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 836, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 837, in process
    "SQLite Date type only accepts Python "
TypeError: SQLite Date type only accepts Python date objects as input.

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

Traceback (most recent call last):
  File "test.py", line 73, in <module>
    run()
  File "test.py", line 69, in run
    make_baked_query(cols)(session).params(values=values).count())
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/ext/baked.py", line 461, in count
    return bq.for_session(self.session).params(self._params).scalar()
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/ext/baked.py", line 474, in scalar
    ret = self.one()
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/ext/baked.py", line 505, in one
    ret = self.one_or_none()
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/ext/baked.py", line 527, in one_or_none 
    ret = list(self)
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/ext/baked.py", line 445, in __iter__
    return q._execute_and_instances(context)
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/orm/query.py", line 3506, in _execute_and_instances
    result = conn.execute(querycontext.statement, self._params)
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1020, in execute
    return meth(self, multiparams, params)
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/sql/elements.py", line 298, in _execute_on_connection
    return connection._execute_clauseelement(self, multiparams, params)
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1139, in _execute_clauseelement
    distilled_params,
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1214, in _execute_context
    e, util.text_type(statement), parameters, None, None
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1518, in _handle_dbapi_exception
    sqlalchemy_exception, with_traceback=exc_info[2], from_=e
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/util/compat.py", line 178, in raise_
    raise exception
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/base.py", line 1211, in _execute_context
    context = constructor(dialect, self, conn, *args) 
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/engine/default.py", line 836, in _init_compiled
    param.append(processors[key](compiled_params[key]))
  File "/home/user/.local/lib/python3.7/site-packages/sqlalchemy/dialects/sqlite/base.py", line 837, in process
    "SQLite Date type only accepts Python "
sqlalchemy.exc.StatementError: (builtins.TypeError) SQLite Date type only accepts Python date objects as input.
[SQL: SELECT count(*) AS count_1 
FROM (SELECT foo.id AS foo_id, foo.first AS foo_first, foo.second AS foo_second 
FROM foo 
WHERE (foo.second, foo.first) IN ([EXPANDING_values])) AS anon_1]
[parameters: [{'values': [(datetime.date(2000, 1, 1), 1)]}]]

Additional context
If I (re)use a single bakery throughout (bakery = bakery_maker() in the setup), I get other unpredictable behavior from the baked queries (like no results if I switch column order), so I'm guessing running that each time I bake a query is the right way to go about it.

Versions.

• OS: Ubuntu 18.04
• Python: Python 3.7.4
• SQLAlchemy: 1.3.17
• Database: SQLite

Have a nice day!

[zzzeek]

hi there -

this is expected behavior because you are passing a single lambda: to the baked query that alters the structure of the SQL. the baked extension can't be used in this way, a particular lambda: must produce the identical SQL structure every time since this lambda is only called once.

Release 1.4 will deprecate the baked extension and introduce an all new caching system that eliminates these issues.

[acowlikeobject]

Thanks @zzzeek . I've been having trouble getting consistent results from baked queries, and based on your comment, it's probably because I don't understand the caching mechanism.

Anyway, I'll wait till 1.4! I see that it's in pre-release now. Very approximately, when do you think it'll be out?

[zzzeek]

Thanks @zzzeek . I've been having trouble getting consistent results from baked queries, and based on your comment, it's probably because I don't understand the caching mechanism.

yes, this is the terrible bargain of the baked caching system, that it does this thing that was not possible before but it requires this very weird way of thinking and it took me years to get my head around it also. 1.4's caching now works in the "traditional" way where the whole select() or query() you typed out can represent itself as a cache key. it's not yet as performant as the baked approach but it is much faster than no caching at all.

Anyway, I'll wait till 1.4! I see that it's in pre-release now. Very approximately, when do you think it'll be out?

with the caching feature now merged and seems to be working, that was the biggest single hump to get over, 1.4 is a really really huge release and there are still lots of API deprecations to occur and documentation to be written, it would be nice if I could start talking about betas in the fall but we'll have to see.

[acowlikeobject]

Much appreciated, @zzzeek . 1.4 sounds like a heavy lift... good luck with that!

Given that it's ~several months out, is there a simple-ish modification I can make to the baked queries above to get them to work?

Essentially, we need to do a cached tuple_().in_() where we specify the order of columns beforehand, and then provide a list of tuples to check "in". The order of tuple elements will always match the column order, of course, but there will be a varying number of different tuples to run the query against each time.

If it isn't straightforward, then we'll live with the slower queries till 1.4. I figured I'd ask :).

[zzzeek]

So i took a look and your example is not even using the cache, because it makes a new baked query each time. I was going to suggest adding the two column keys as part of the cache key, but before that, just running that last baked query all by itself with nothing else happening is producing the error, and it looks like the problem is actually in the expanding bound parameter which I note you aren't using in the non-baked version.

while I think your example still has a caching issue there's a more serious problem which is that the tuple_() construct with "expanding" parameters does not support heterogeneous types. I sort of screwed up this issue so I will add a new issue for this, as this is actually quite critically important for the 1.4 release also!

[zzzeek]

oh. OK, good news and bad news. the good news is, the bug is fixed! bad news is, I fixed it in 1.4 as part of #4645 in 60e7034:

The change also completes support for
expanding IN with tuples, where previously type-specific bind processors
weren't taking effect.

it was a huge change and i can't really backport it :(

[zzzeek]

Okey doke so if I do a hacky thing to let the tuple work in this case, which is, apparently the SQLite driver these days appears to accept the Python date() object directly so we can make a new type that doesnt have a processor, I can make a test case that first shows the caching bug, e.g. the two statements are the same but the parameters get reversed:

 SELECT foo.id AS foo_id, foo.first AS foo_first, foo.second AS foo_second 
FROM foo 
WHERE (foo.second, foo.first) IN (VALUES (?, ?))
2020-06-11 00:52:40,412 INFO sqlalchemy.engine.base.Engine (datetime.date(2000, 1, 1), 1)
2020-06-11 00:52:40,413 DEBUG sqlalchemy.engine.base.Engine Col ('foo_id', 'foo_first', 'foo_second')
2020-06-11 00:52:40,413 DEBUG sqlalchemy.engine.base.Engine Row (1, 1, '2000-01-01')
2020-06-11 00:52:40,413 INFO sqlalchemy.engine.base.Engine SELECT foo.id AS foo_id, foo.first AS foo_first, foo.second AS foo_second 
FROM foo 
WHERE (foo.second, foo.first) IN (VALUES (?, ?))
2020-06-11 00:52:40,413 INFO sqlalchemy.engine.base.Engine (1, datetime.date(2000, 1, 1))
2020-06-11 00:52:40,413 DEBUG sqlalchemy.engine.base.Engine Col ('foo_id', 'foo_first', 'foo_second')

we can add an additional bit of information to the cache key that resolves this, which is below.

but also, I just saw something else, which is apparently the sqlite dialect has native date support if you turn it on w/ a flag. I have absolutely zero recollection of working on this or reviewing someone's feature, but if we turn that on, the Date type no longer does any processing thus bypassing the bug.

so that's all below!

from sqlalchemy import Column, Date, Integer, create_engine, tuple_, bindparam
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session
from sqlalchemy.ext.baked import bakery as bakery_maker

from datetime import date


Base = declarative_base()


class Foo(Base):
    __tablename__ = "foo"
    id = Column(Integer, primary_key=True)
    first = Column(Integer)
    second = Column(Date)


# Setup.
engine = create_engine(
    "sqlite:///:memory:", echo="debug", native_datetime=True
)
Base.metadata.create_all(engine)
session = Session(bind=engine)

bakery = bakery_maker()

# Insert a row.
foo = Foo(first=1, second=date(2000, 1, 1))
session.add(foo)


def make_query(cols):
    return bakery(
        lambda session: session.query(Foo).filter(
            tuple_(*cols).in_(bindparam("values", expanding=True))
        ),
        tuple(c.key for c in cols),
    )


cols = Foo.second, Foo.first
values = [(foo.second, foo.first)]

b = make_query(cols)
b(session).params(values=values).all()

cols = Foo.first, Foo.second
values = [(foo.first, foo.second)]

b = make_query(cols)

b(session).params(values=values).all()

[zzzeek]

OK that's actually a very old feature. for it to work in both directions you have to do this:

import sqlite3

engine = create_engine(
    "sqlite:///:memory:",
    echo="debug",
    native_datetime=True,
    connect_args={
        "detect_types": sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES
    },
)

[zzzeek]

anyway. 1.4 is going to be so great! :)

[acowlikeobject]

Right, when I tried re-using the cache, I saw a host of other issues. It smelled like it had to do with heterogeneous types... glad the fix is already in 1.4.

And, thanks a ton for the extremely helpful walk-through for 1.3! Looking at the docs, seems like this won't work with DATETIMEs though, which we do need.

And in any case, we're prototyping with SQLite but will use MySQL in production. So, all in all, think just waiting for 1.4 is the right call.

anyway. 1.4 is going to be so great! :)

Yeah, I mean, now I can't wait :).

Again, thank you very much for the quick help.