variant() does not transfer expression behavior using the comparator correctly

[Leonardo Rossi]

Hi,

I found a different behaviour of the JSON column working with on MySQL and on PostgreSQL.

Somebody can say me if I did something wrong or it's a bug?

I run the query session.query(table).filter(table.js['hello'] == 'world').first(), and:

1. with sa.Column(sa.JSON().with_variant(postgresql.JSONB(), 'postgresql'),) works on PostgreSQL but not with MySQL.

2. with sa.Column(sa.JSON()) doesn't work with PostgreSQL but works with MySQL.

p.s. I attached a fully example ready-to-run.

[Mike Bayer]

the Postgresql JSON needs a cast to compare JSON to string. Note that in JSON, foo['bar'] returns a JSON value, not string. not sure if there's any solution to that coming anytime soon.

[Mike Bayer]

here's how to compare JSON elements in a backend-agnostic way for now:

expr = sa.cast(table.js['hello'], sa.String) == sa.type_coerce("world", sa.JSON)

result = session.query(table).filter(expr).first()

adding this to the docs

[Mike Bayer]

Ensure Variant passes along impl right-hand type

Fixed issue in :class:.Variant where the "right hand coercion" logic, inherited from :class:.TypeDecorator, would coerce the right-hand side into the :class:.Variant itself, rather than what the default type for the :class:.Variant would do. In the case of :class:.Variant, we want the type to act mostly like the base type so the default logic of :class:.TypeDecorator is now overridden to fall back to the underlying wrapped type's logic. Is mostly relevant for JSON at the moment.

This patch additionally adds documentation and basic tests to allow for backend-agnostic comparison of JSON index elements to other objects. A future version should attempt to improve upon this by providing "astext", "asint" types of operators.

Change-Id: I7b7b45d604a4ae8d1dc236a5a1248695aab5232e Fixes: #3859

→ <<cset df9b6492e5ca>>