[Sqlalchemy-tickets] Issue #3213: positional_names doesn't get passed to whereclause in visit_select (zzzeek/sqlalchemy)

[Tony R. <iss...@bi...>]

New issue 3213: positional_names doesn't get passed to whereclause in visit_select
https://bitbucket.org/zzzeek/sqlalchemy/issue/3213/positional_names-doesnt-get-passed-to

Tony Roberts:

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:

```
#!python
    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:

```
#!python

    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).

```
#!sql
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