Issue: ARRAY_AGG not returning expected or consistent results
Author: RickMeasham
State: closed
Created: 2021-03-03 23:43:05


[peeweetest.txt](https://github.com/coleifer/peewee/files/6079849/peeweetest.txt)
Aggregating rows in postgresql using Peewee gives unexpected results, depending on the field type.

_The attached file was used to generate these cases._

## The results

### UUID Fields
`fn.ARRAY_AGG(AggregateTest.uuid_field)` **returns a string** from Peewee:
```
print(repr(record.uuid_field))
# '{314b1cc1-d074-432c-82ba-40864a581806,9e4b271f-d37a-4b59-90ae-12f58abd5636}'
```

### Char Fields
`fn.ARRAY_AGG(AggregateTest.char_field)` **returns a string** from Peewee:
```
print(repr(record.char_test))
# "['CharField', 'CharField2']"
```

Note that this isn't the native postgres format. Postgres returns arrays in curly braces per the UUID field above.

### Integer Fields
`fn.ARRAY_AGG(AggregateTest.integer_field)` **returns a list** from Peewee:
```
print(repr(record.integer_test))
# [3, 6]
```

### Decimal Fields
`fn.ARRAY_AGG(AggregateTest.decimal_field)` **raises an exception** from Peewee:
```
   ...
[peeweetest.txt](https://github.com/coleifer/peewee/files/6079845/peeweetest.txt)


  File "/usr/local/lib/python3.8/dist-packages/peewee.py", line 4636, in python_value
    return decimal.Decimal(text_type(value))
decimal.InvalidOperation: [<class 'decimal.ConversionSyntax'>]
```

## Is it psycopg2?

I executed the query generated by peewee directly in psycopg2. It returned the (almost) correct results:

```
'{4350795c-3e1c-4e40-86fa-5a1253a34027,7447bb30-7440-4fe9-9d43-e47ec46d3fc5}'
['CharField', 'CharField2']
['TextField', 'TextField2']
[3, 6]
```

The only unexpected result here was for the UUID field. I'll come back to that later.

Even if I add the Decimal field to the psycopg2 query, I get back the correct result:
```
[Decimal('3.14100'), Decimal('6.28200')]
```

## Conclusion

Somewhere after calling psycopg2 to get the values from the database, Peewee is doing something odd.

* For integer fields, it's correctly returning a list.
* For char fields, it returns a string representation of a python list.
* For UUID fields it's return a string representation of a postgresql list.
* For Decimal fields it's throwing an exception.

Looking at the source, Peewee is expecting the underlying field type to be returned, not a list. Two examples:

* For integer fields peewee [attempts to `int(val)` and on exception just returns `val`](https://github.com/coleifer/peewee/blob/666a7091d5d0893e6f86fced9fb74c308687d66e/peewee.py#L4577) .. thus we get the raw list that came from psycopg2
* For string fields peewee [ultimately calls `str()`](https://github.com/coleifer/peewee/blob/666a7091d5d0893e6f86fced9fb74c308687d66e/peewee.py#L4678) (aliased as text_type) and thus we get back a stringified python list
* For decimal fields, peewee attempts to create a Decimal object using the stringified version of the result.

So it seems that maybe there needs to be either an understanding that `fn.ARRAY_AGG` returns a list, or if psycopg2 returns a list, Peewee needs to call the handler on each value rather than on the list itself.

## UUID Extra info

If I register UUID before making a connection in psycopg2, then I get the correct result from psycopg2:
```
psycopg2.extras.register_uuid()
connection = psycopg2.connect(...)
# [UUID('bd403fbc-baaa-4b34-b30a-59616b16e0ef'), UUID('9ff379a3-969a-4b87-b71c-b2a57b9b46f2')]
```

But if I register before opening the connection in peewee, the world ends (Or an exception is raised. One or the other.) because psycopg2 is now returning a list of UUID objects and peewee is attempting to create a UUID from the list:
```
  ...
  File "/usr/local/lib/python3.8/dist-packages/peewee.py", line 4833, in python_value
    return uuid.UUID(value) if value is not None else None
  File "/usr/lib/python3.8/uuid.py", line 166, in __init__
    hex = hex.replace('urn:', '').replace('uuid:', '')
AttributeError: 'list' object has no attribute 'replace'
```




Peewee uses a heuristic on Function objects called with a single field as the argument, where the idea is to use that field's "python value" converter to convert the db value into an appropriate python type. As you've found, this is not always what you want.

To resolve this for now, you can explicitly tell Peewee not to convert the value by adding `.coerce(False)` after your function invocation:

```python

query = AggregateTest.select(
    fn.ARRAY_AGG(AggregateTest.uuid_field).coerce(False).alias('uuid_test'),
    fn.ARRAY_AGG(AggregateTest.char_field).coerce(False).alias('char_test'),
    fn.ARRAY_AGG(AggregateTest.text_field).coerce(False).alias('text_test'),
    fn.ARRAY_AGG(AggregateTest.decimal_field).coerce(False).alias('decimal_test'),
    fn.ARRAY_AGG(AggregateTest.integer_field).coerce(False).alias('integer_test'),
)
```

This is somewhat covered here:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Function
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Function.coerce

To get the UUID working you need to register the UUID adapter, which is in psycopg2.extras:

```python

# Add at top of file.
from psycopg2.extras import register_uuid
register_uuid()
```

I've made a small change in Peewee to:

* blacklist array_agg from using coerce=True by default. This means that Peewee will return the value directly from the cursor without attempting to alter it.
* register the psycopg2 UUID type automatically when peewee is imported

Those changes should resolve this going forward.