positional_names doesn't get passed to whereclause in visit_select
Hi,
when compiling a query using a dialect that expects positional arguments, when there are CTEs in the expression the positional arguments can end up in the wrong order.
This is because when visit_select (in sql/compiler.py) is called via visit_cte there are various points where the positional_names don't get passed when building the cte clauses, such as the where clause:
def visit_select(self, select, asfrom=False, parens=True,
iswrapper=False, fromhints=None,
compound_index=0,
positional_names=None,
force_result_map=False,
**kwargs):
....
....
if select._whereclause is not None:
t = select._whereclause._compiler_dispatch(self, **kwargs)
if t:
text += " \nWHERE " + t
This could be fixed by not listing positional_names as an explicit kwarg to visit_select and instead picking it out of the kwargs. That way it will be passed everywhere kwargs is passed without having to check every case, ie:
def visit_select(self, select, asfrom=False, parens=True,
iswrapper=False, fromhints=None,
compound_index=0,
force_result_map=False,
**kwargs):
positional_names = kwargs.get("positional_names")
To reproduce this problem try a query like this slightly contrived example on a database that uses positional parameters, eg sqlite (although you need a recent version that supports CTEs, I've been testing with 3.8.6).
WITH cte_0 AS (
SELECT coalesce(table_a.x, '?') AS x, table_a.y as y
FROM x WHERE x.z == '?'
),
cte_1 AS (
SELECT coalesce(table_a.x, '?') AS x, table_a.y as y
FROM x WHERE x.z == '?'
),
SELECT cte_0.x, cte_1.x
FROM cte_0
JOIN cte_1 ON cte_0.y = cte_1.y
WHERE cte_0.x IN (?) AND cte_1.x IN (?)
You'll find that the params for the cte where clauses get added to the main positiontup list instead of the cte_positional list and so the final parameter order is wrong.
thanks! Tony
Comments (3)
-
repo owner -
reporter Hi,
ah, yes, sorry I didn't notice it had already been fixed. That commit removes the positional_name kwarg so it gets passed around in the kwargs instead, which will fix the problem I was having. I could have saved myself some time :)
thanks! Tony
-
reporter - changed status to closed
already fixed in da46b015f075
- Log in to comment
what code are we talking about? Here's visit_select: https://bitbucket.org/zzzeek/sqlalchemy/src/42837f4bca6a0b2fad05faade7837719f872c35d/lib/sqlalchemy/sql/compiler.py?at=master#cl-1467
It seems like you're looking for https://bitbucket.org/zzzeek/sqlalchemy/commits/da46b015f075 which was implemented as the fix for
#3090.