From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Peter Eisentraut <peter_e(at)gmx(dot)net> |
Cc: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: IN with arrays |
Date: | 2007-04-16 15:37:53 |
Message-ID: | 21275.1176737873@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Peter Eisentraut <peter_e(at)gmx(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Joshua D. Drake | 2007-04-16 15:41:31 | Re: What tools do people use to hack on PostgreSQL? |
Previous Message | Larry Rosenman | 2007-04-16 15:31:11 | Re: CREATE DATABASE foo OWNER bar |