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