DBError (psycopg2.ProgrammingError) operator does not exist: character varying = text[]

[melanie witt]

Opening a new bug based on a comment from another bug:

https://bugs.launchpad.net/nova/+bug/1518200/comments/6

There appears to be a problem with postgres queries using the in_ operator for example:

  query = query.filter(models.Migration.status.in_(status))

where status is an array of strings like ['accepted', 'done'].

The error:

 DBError: (psycopg2.ProgrammingError) operator does not exist: character varying = text[]
 LINE 3: ...HERE migrations.deleted = 0 AND migrations.status = ARRAY['a...
 HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

looks to be about the fact that the "status" column of the Migration table is of type varchar whereas the array for the IN operator is defaulting to being treated as an array of text types, and that an explicit cast is needed.

I didn't find any existing type casting and we do have a number of similar queries already of style "column.in_(array of strings)" so I wonder if this is a problem for all such queries, and not just this migration status example one.

[Sean Dague (sdague)]

Noting that the postgresql full stack testing did not expose this.