Problem with PG ARRAY's ANY() and SA 1.4.36 (maybe related resolution of #6515 ?)

[Lele Gaifax]

Hi all,

I'm hitting what is either a regression or a "deprecated" usage of PG
ARRAY's any() method, trying to upgrade an app of mine from SA 1.4.35 to
SA 1.4.36.

The issue is within a quite complex query, with a filter expression like
the following:

query = query.filter(((CC.languages == None) | CC.languages.any(sa.bindparam('language'))))

where CC.languages is a sqlalchemy.dialects.postgresql.ARRAY(UnicodeText()) column,
and the language bindparam is the two-letter code of a language, say
"it" or "en".

That query works great since years in production using SA 1.3, and also
in my work-in-progress dev branch using 1.4.x, producing something like

...
AND (cc.languages IS NULL OR %(language)s = ANY (cc.languages))
...

Today I tried to upgrade the dev branch to SA 1.4.36 and several tests
failed, because that filter is rendered as

...
AND (cc.languages IS NULL OR %(language)s::TEXT[] = ANY (cc.languages))
...

In the note that follows
https://docs.sqlalchemy.org/en/14/core/type_basics.html#sqlalchemy.types.ARRAY.Comparator.any
I read that the any() method "is superceded by" the any_() function, so
maybe that's what I should do... but given that the above idiom has
worked well for me for so long, this may very well be an unintended
side effect of https://github.com/sqlalchemy/sqlalchemy/commit/63191fbef63ebfbf57e7b66bd6529305fc62c605.

Thank you for any hint,
ciao, lele.
--
nickname: Lele Gaifax | Dire che Emacs è "conveniente" è come
real: Emanuele Gaifas | etichettare l'ossigeno come "utile"
le...@etour.tn.it | -- Rens Troost

[Mike Bayer]

If you could post some code examples on a github discussion that I can run, that would be helpful.      the change basically fixed the any/all operations to correctly interpret incoming arguments so that proper casts could be provided.   

I dont use these operators myself so it could be that we did it wrong.  

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/87bkwjpzl0.fsf%40metapensiero.it.

[Mike Bayer]

it looks like the bindparam() when not given a type is being coerced to thinking it's an ARRAY, which might be a separate issue, but in any case, give it a type so that there is no ambiguity (do you really need to use bindparam() directly?)

    conn.execute(

        table.select().where(

            table.c.data.any(bindparam("data", type_=String))

        ),

        {"data": "some text"},

    )

On Fri, Apr 29, 2022, at 1:30 PM, Lele Gaifax wrote:

[Mike Bayer]

a fix for that is coming through at https://github.com/sqlalchemy/sqlalchemy/issues/7979

though it's best to give types to bindparam() objects if they don't have a value

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/79239f5b-3c7b-45d0-b283-cf983d07b88f%40www.fastmail.com.

[Lele Gaifax]

"Mike Bayer" <mik...@zzzcomputing.com> writes:

> a fix for that is coming through at https://github.com/sqlalchemy/sqlalchemy/issues/7979
>
> though it's best to give types to bindparam() objects if they don't have a value

Thank you Mike, and sorry for not providing a MWE of my issue in my first
post.

I confirm that explicitly typing the bind parameters fixed the issue.

W.r.t.

> (do you really need to use bindparam() directly?)

well, yes, it's very handy in this case, because as said this is a moderately
complex query, dinamically composed by several different smaller "chunks"
generated by common functions, used in several other places.

It would be very impractical to carry around the whole set of "parameters" and
use the implicit/automatic injection made by SA.

Thanks again,
ciao, lele.
--
nickname: Lele Gaifax | Quando vivrò di quello che ho pensato ieri
real: Emanuele Gaifas | comincerò ad aver paura di chi mi copia.
le...@metapensiero.it | -- Fortunato Depero, 1929.

[Mike Bayer]

the fix has merged so if you want to test the rel_1_4 branch that will confirm you have what you need.

-- 

SQLAlchemy - 

The Python SQL Toolkit and Object Relational Mapper

http://www.sqlalchemy.org/

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example.  See  http://stackoverflow.com/help/mcve for a full description.

--- 

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/878rrjgjaj.fsf%40metapensiero.it.