Re: IN with arrays - Mailing list pgsql-hackers

From Tom Lane
Subject Re: IN with arrays
Date
Msg-id 21275.1176737873@sss.pgh.pa.us
Whole thread Raw
In response to IN with arrays  (Peter Eisentraut <peter_e@gmx.net>)
List pgsql-hackers
Peter Eisentraut <peter_e@gmx.net> writes:
> That is, why can't you write
> SELECT 1 IN ( ARRAY[1, 2, 3] );
> when you can write
> SELECT 1 = ANY ( ARRAY[1, 2, 3] );
> ?

The two syntaxes are in fact *not* equivalent according to SQL92.
= ANY derives from
        <quantified comparison predicate> ::=             <row value constructor> <comp op> <quantifier> <table
subquery>
        <quantifier> ::= <all> | <some>
        <all> ::= ALL
        <some> ::= SOME | ANY

(notice the RHS *must* be a <table subquery>) whereas IN comes from
        <in predicate> ::=             <row value constructor>               [ NOT ] IN <in predicate value>
        <in predicate value> ::=               <table subquery>             | <left paren> <in value list> <right
paren>
        <in value list> ::=             <value expression> { <comma> <value expression> }...

The form "expr = ANY (non-query-expr)" is therefore a spec extension,
which we are free to define as we wish, and we defined it to be a
scalar-vs-array-elements comparison.  But I don't see any way that we
can interpret "expr IN (other-expr)" as anything except a variant
spelling for a simple equality test.
        regards, tom lane


pgsql-hackers by date:

Previous
From: Larry Rosenman
Date:
Subject: Re: CREATE DATABASE foo OWNER bar
Next
From: "Joshua D. Drake"
Date:
Subject: Re: What tools do people use to hack on PostgreSQL?