Is this a bug in python-sql?

Hi!
I noticed that my sql-query is not being executed correctly.
I have modeled a simple example:

    user = Table('res_user')
    group = user.create_uid
    total = Sum(Case((user.write_uid == 2, 3), else_=4))

    query = user.select(
        group.as_('group'),
        total.as_('total'),
        group_by=[user.create_uid],
        having=(total != 0),
        order_by=[Literal(1)],
    )

    logger.info('query = %s', str(query) % query.params)
    logger.info('query.params = %s', query.params)

And this is what I got:

query = SELECT "a"."create_uid" AS "group", 
SUM(CASE WHEN ("a"."write_uid" = 2) THEN 3 ELSE 4 END) AS "total" 
FROM "res_user" AS "a" 
GROUP BY "a"."create_uid" 
HAVING (SUM(CASE WHEN ("a"."write_uid" = 1) THEN 2 ELSE 3 END) != 4) 
ORDER BY 0

query.params = (2, 3, 4, 1, 2, 3, 4, 0)

But the result should be like this:

query.params = (2, 3, 4, 2, 3, 4, 0, 1)

If you replace order_by=[Literal(1)] with order_by=[group], the query will be executed correctly:

query.params = (2, 3, 4, 2, 3, 4, 0)

But if you then replace group = user.create_uid with group = Coalesce(user.create_uid, 1), the query will again be executed incorrectly:

query.params = (1, 2, 3, 4, 1, 2, 3, 4, 0)

P.S. The version of python-sql is 1.5.2

If you delete order_by completely, all queries will be processed correctly.

Yes it is a bug that should be fixed.

Here is Wrong parameter position on Select with order_by and having or windows (#94) · Issues · Tryton / python-sql · GitLab

2 Likes