id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
28880	The QuerySet.query sql representation of an ORM call on a PostgreSQL ArrayField is missing a key bit of syntax.	Alexander Kavanaugh		"Representations of Arrays in PostgreSQL operations are preceeded by the word ARRAY (e.g. `ARRAY['3', '4', '5']`; see also [https://www.postgresql.org/docs/current/static/functions-array.html array operation docs]. The output of `QuerySet.query` is missing ""ARRAY"" -- it currently just injects a python list representation into the rhs of the operation sql string.

It is worth noting that actual operations on ArrayFields use the correct syntax; only the `QuerySet.query` representation is incorrect. The key ramification of this issue is that copying and pasting the `QuerySet.query` output into a psql shell and running it results in a SyntaxError (`ERROR:  syntax error at or near ""[""`). This can be quite confusing for people unfamiliar with PostgreSQL array syntax (like me, before I dug into this) attempting to troubleshoot their code.

I'll happily work on a fix and submit a PR if I can get some guidance. I'm assuming the fix would need to be somewhere along the [https://github.com/django/django/blob/master/django/contrib/postgres/lookups.py#L7 postgres Lookup as_sql] code path, but I'm not sure what an elegant solution would be. Is overriding `process_rhs` the right move?


Relevant bit of the Django Model:
{{{#!python
class Message(Model):
    network_lookup_ids = ArrayField(base_field=CharField(max_length=160))
}}}

Django ORM Code:
{{{#!python
Message.objects.filter(network_lookup_ids__overlap=[""3"", ""4"", ""5""]).values(""id"", ""network_lookup_ids"")
}}}

Django QuerySet.query representation:
{{{#!sql
SELECT ""production_message"".""id"", ""production_message"".""network_lookup_ids"" FROM ""production_message"" WHERE ""production_message"".""network_lookup_ids"" && ['3', '4', '5']::varchar(160)[]
}}}

Actual call made to the database (from the `pg_stat_activity` table):
{{{#!sql
SELECT ""production_message"".""id"", ""production_message"".""network_lookup_ids"" FROM ""production_message"" WHERE ""production_message"".""network_lookup_ids"" && ARRAY['3', '4', '5']::varchar(160)[] LIMIT 21
}}}
"	Bug	closed	contrib.postgres	1.11	Normal	invalid	postgres,arrayfield,syntaxerror,.query,as_sql,ARRAY		Unreviewed	0	0	0	0	1	0