Re: the IN clause saga

On Tue, 22 Jul 2003, Joe Conway wrote:

> Fernando Nasser wrote:
> >> I'm not sure if this makes sense, but could you have a conflict
> >> between a set containing a single element that is the array and a
> >> set containing the elements of the array.
> >
> > You will need to have an Array of Arrays in that case.
>
> Sorry for jumping in but it doesn't seem that everyone understands the
> new functionality Tom mentioned, so I thought I might elaborate.
>
> The new syntax is:
> <scalar> <op> ANY | SOME | ALL (<array-expression>)
> or specific to this discussion
> <scalar> = ANY (<array-expression>)
> *not*
> <scalar> IN (<array-expression>)
> for exactly the reason above. If the latter were allowed, it would
> present a conflict, because
> <scalar> IN (<list-of-scalars>)
> is allowable. The former is not ambiguous because
> <scalar> = ANY (<list-of-scalars>)
> is not, and never has been allowed. E.g.:
>
> regression=# select 1 where 1 = any (array[1,2,3]);
> ?column?
> ----------
> 1
> (1 row)
>
> regression=# select 1 where 1 = any (1,2,3);
> ERROR: syntax error at or near "," at character 26
>
> regression=# select 1 where 1 in (1,2,3);
> ?column?
> ----------
> 1
> (1 row)
>

Perhaps these cases would also be illustrative as I believe that is what
Fernando was suggesting.

template1=# select 1 where 1 in (array[1,2,3]);
ERROR: Unable to identify an operator '=' for types 'integer' and 'integer[]'
You will have to retype this query using an explicit cast
IN: op_error (parse_oper.c:608)
ERROR: Unable to identify an operator '=' for types 'integer' and 'integer[]'
You will have to retype this query using an explicit cast

template1=# select 1 where 1 in ([1,2,3]);
ERROR: syntax error at or near "["
IN: yyerror (scan.l:596)
ERROR: syntax error at or near "[" at character 22

Kris Jurka