Psycopg2: RealDictRow.items() behave inconsistently on duplicate row names

[ProgVal]

Hi,

When a SELECT query has two columns with the same name, cur.fetchone().items() behaves inconsistently:

>>> import psycopg2, psycopg2.extras
>>> db = psycopg2.connect('service=swh-replica', cursor_factory=psycopg2.extras.RealDictCursor)
>>> cur = db.cursor()
>>> cur.execute('SELECT 1 AS a, 2 AS a;')
>>> row = cur.fetchone()
>>> list(row.items())
[('a', 2), ('a', 2)]
>>> row
{'a': 2}
>>> len(row)
1

row.items() having a length not equal to len(row) causes bugs in users of this dictionary that expect them to be equal.

eg. we noticed this bug in a script that queries psycopg2 with a duplicate column name (unintentionally), and the RealDictRow ends up being passed to msgpack.packb, which produces corrupted data when this happens.

This issue happens on psycopg2 2.8, but not 2.7.7.

[dvarrazzo]

The bug is in the query: if you are using a name-based mapping object and you are passing a query with duplicate names you are asking for troubles.

There is nothing we can reasonably fix here. Fix your query.

[ProgVal]

I understand this is caused by my query, but this issue took some time to track down: it only happened in a particular build because it doesn't happen versions of psycopg2 + it seemed to happen in an unrelated module (msgpack).

Could psycopg2 raise an error when instead of building such a dict?

[dvarrazzo]

I'll look into that

[dvarrazzo]

Issue fixed on master, but it slipped from release 2.8.1. Because of fixing #886, queries with repeated columns will fail in a different way. Call it "undefined result".

After 2.8.2 the result will be likely to leave only the last value in the record.

[ProgVal]

Thanks!

[erakli]

Moreover, in 2.8.1 this behaviour appears in conjunction with RealDictCursor (closely related to #886):

>>> postgres = psycopg2.connect(...)
>>> cur = postgres.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
>>> cur.execute("select 1 as a, 2 as a")
>>> cur.fetchall()
[RealDictRow([(<class 'psycopg2.extras.RealDictRow'>, ['a', 'a']), ('a', 2)])]

This key - instance of <class 'psycopg2.extras.RealDictRow'> - breaks kwargs expansion with error TypeError: __init__() keywords must be strings.