I'm wondering why a IN b isn't equivalent to a = ANY b for arrays, as it
is for subqueries.

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] );

?

I'm guessing that there is a semantic inconsistency between these
expressions, as the first one considers what is in parentheses as a
list, the second one as a single expression. That would be very bad.

Search Discussions

  • Tom Lane at Apr 16, 2007 at 3:38 pm

    Peter Eisentraut 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedApr 16, '07 at 6:49a
activeApr 16, '07 at 3:38p
posts2
users2
websitepostgresql.org...
irc#postgresql

2 users in discussion

Peter Eisentraut: 1 post Tom Lane: 1 post

People

Translate

site design / logo © 2021 Grokbase