Issue: Incorrect SQL for integer-returning `Case()` within `fn.SUM()`, with PostgreSQL
Author: a-p-
State: closed
Created: 2018-03-10 20:54:55

Peewee 3.1.2 appears to be generating incorrect SQL for a `Case()` call that evaluates to an integer within an `fn.SUM()` call, when using PostgreSQL (10.1).

Some simplified example code to reproduce the problem is at the bottom.

The query is like:

```python
query = Example.select(
    Example.name,
    fn.SUM(Case(None, [(('A' == Example.status), 1)], 0)).alias('status_a_count')
).group_by(Example.name)
```

Iterating over the results results in an exception like:

```
psycopg2.ProgrammingError: function sum(text) does not exist
LINE 1: SELECT "t1"."name", SUM(CASE WHEN ("t1"."status" = 'A') THEN...
                            ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
```

The Peewee logging emits this:

```
('SELECT "t1"."name", SUM(CASE WHEN ("t1"."status" = %s) THEN %s ELSE %s END) AS "status_a_count" FROM "example" AS "t1" GROUP BY "t1"."name"', ['A', '1', '0'])
```

And the PostgreSQL log includes:

```
ERROR:  function sum(text) does not exist at character 21
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
STATEMENT:  SELECT "t1"."name", SUM(CASE WHEN ("t1"."status" = 'A') THEN '1' ELSE '0' END) AS "status_a_count" FROM "example" AS "t1" GROUP BY "t1"."name"
```

Looks like the `1` and `0` integer literals within the Python code end up as string literals `'1'` and `'0'`, instead of integer literals, in the generated SQL query.

Example code:

```python
#!/usr/bin/env python3.6

import logging
import random

from peewee import *
import playhouse.postgres_ext


database = playhouse.postgres_ext.PostgresqlExtDatabase(database='test')


# Sample table to query against.
class Example(Model):
    class Meta:
        database = database
    id = AutoField()
    name = TextField()
    status = TextField()


database.connect()

with database.atomic() as transaction:
    database.create_tables([Example])

    # Populate the sample table with some sample data.
    for i in range(0, 100):
        example = Example(
            name=random.choice(['Name-0', 'Name-1', 'Name-2', 'Name-3']),
            status=random.choice(['A', 'B', 'C', 'D'])
        )
        example.save()

    # Enable logging.
    logger = logging.getLogger('peewee')
    logger.setLevel(logging.DEBUG)
    logger.addHandler(logging.StreamHandler())

    # Problematic query...
    query = Example.select(
        Example.name,
        fn.SUM(Case(None, [(('A' == Example.status), 1)], 0)).alias('status_a_count')
    ).group_by(Example.name)

    for row in query:
        print(row.status_a_count)

    transaction.rollback()

database.close()
```


Thank you for the excellent report. I was able to replicate this and found a bug related to updating mutable scope-specific settings in-place instead of copying. Should be fixed. I'll push a new bugfix release shortly.

I'm getting the same error and wondering when this can be fixed? Thanks.

The example above is working correctly for me, and as the comment I made on Mar 10, 2018 indicates - this fix was merged quite a long time ago.

If you can provide an example illustrating the error you're receiving I will look into it.

Here is the [example](https://stackoverflow.com/questions/63627858/get-error-when-using-sum-and-case-in-sqlalchemy/63631184#63631184). Then I realized this looks like a bug related to `SQLAlchemy` or `asyncpg` instead. Please take a look and would like to know your expertise thought on this. Thanks.

I'm confused. You're reporting an issue on the peewee issue tracker, but saying the bug is with sqlalchemy and asyncpg.