Skip site navigation (1) Skip section navigation (2)

Re: the IN clause saga

From: Joe Conway <mail(at)joeconway(dot)com>
To: Fernando Nasser <fnasser(at)redhat(dot)com>
Cc: Darin Ohashi <DOhashi(at)maplesoft(dot)com>,Felipe Schnack <felipes(at)ritterdosreis(dot)br>,Oliver Jowett <oliver(at)opencloud(dot)com>, pgsql-jdbc(at)postgresql(dot)org
Subject: Re: the IN clause saga
Date: 2003-07-22 21:46:16
Message-ID: 3F1DB0A8.2060809@joeconway.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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)

If everyone already knew this, just say so, and I'll go back to lurking...

HTH,

Joe


In response to

Responses

pgsql-jdbc by date

Next:From: Oliver JowettDate: 2003-07-23 00:11:06
Subject: Re: Patch applied for SQL Injection vulnerability for setObject(int,Object,int)
Previous:From: Jason BruceDate: 2003-07-22 21:13:20
Subject:

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group