2

Recently I've read Quantified Comparison Predicates – Some of SQL’s Rarest Species:

In fact, the SQL standard defines the IN predicate as being just syntax sugar for the = ANY() quantified comparison predicate.

8.4 <in predicate>

Let RVC be the <row value predicand> and 
let IPV be the <in predicate value>.

The expression  RVC IN IPV
is equivalent to  RVC = ANY IPV

Fair enough, based on other answers like: What is exactly “SOME / ANY” and “IN” or Oracle: '= ANY()' vs. 'IN ()' I've assumed that I could use them interchangely.

Now here is my example:

select 'match'
where 1 = any( string_to_array('1,2,3', ',')::int[])
-- match

select 'match'
where 1 IN ( string_to_array('1,2,3', ',')::int[])
-- ERROR:  operator does not exist: integer = integer[]
-- HINT:  No operator matches the given name and argument type(s).
-- You might need to add explicit type casts.

DB Fiddle

The question is why the first query is working and the second returns error?

Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275
  • Arrays are not part of standard SQL. Postgres interprets that second as saying the "1" is equal to the entire array, not a particular component of it. – Gordon Linoff Jan 20 '18 at 13:54
  • @GordonLinoff Yes, I know that array is PostgreSQL extension. Could you provide more detailed answer with links to documentation? Maybe with how operator overloading works in presented case. – Lukasz Szozda Jan 20 '18 at 13:57
  • `in (scalar list)` and `= any (array)` are valid. `in(array)` is not documented to work. right?.. – Vao Tsun Jan 20 '18 at 14:15
  • docs sate that `in(subquery)` is equal to `= any(subquery)` https://www.postgresql.org/docs/current/static/functions-subquery.html#FUNCTIONS-SUBQUERY-IN but `= any(array)` is not equal to `in(array)`, even despite the fact that if `in(scalar list)` has more then one item, planner rewrites it as `=any(array)`, it does not mean `in(array)` would work... – Vao Tsun Jan 20 '18 at 14:24

1 Answers1

1

That's because IN (unlike ANY) does not accept an array as input. Only a set (from a subquery) or a list of values. Detailed explanation:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228