Обсуждение: the IN clause saga

Some of the threads on this are getting a bit bogged down, I thought I'd
summarize the viable options I've seen so far (well, from my point of view
anyway)

setObject() currently allows the user to bypass parameter escaping via
setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
plugged as it's a potential security hole.

However the same functionality lets you do the (nonstandard) trick of
providing an IN clause to a PreparedStatement like "SELECT * FROM table
WHERE pk IN ?". It'd be good to still allow this functionality somehow after
setObject is fixed. This is going to be a postgresql-specific extension
however we do it.

Here are the permutations I can remember:

Option 1: add a method to PGStatement that explicitly sets an IN clause,
  taking either a java.sql.Array, java.util.Collection + component type,
  array + component type, or a custom postgresql object

  + there's no confusion as to what it means
  + using a custom object allows access via setObject(..., Types.OTHER)
    consistently, as well as via the extension method.
  - java.sql.Array and java.util.Collection have problems as PGStatement is
    compiled for all JDKs and JDBC versions and those types may not be present
    (we could do a PGJDBC2Statement or something, but that's getting messy)
  - have to downcast to a PGStatement to use it

Option 2: make setArray() expand to an IN clause when the parameter follows " IN".

  + no new methods or types needed
  - setArray() behaves differently depending on query context
  - user has to wrap the underlying array in a java.sql.Array

Option 3: make setObject(n, Collection [, type]) expand to an IN clause.

  + no new methods or types needed
  - must assume that the contents of the collection use the default type mapping
    if a type is not provided
  - if a type is provided and we apply it to the *components* of the
    collection, this breaks the general getObject() interface of "bind this
    object interpreting it as this particular type".
  - not obvious what to do with objects that are both Collections and some
    other SQL-relevant type; solutions make setObject's behaviour complex
    and/or query-context-dependent

Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
   java.util.Collection

  + as 3, but the ambiguity of "object is both Collection and SQL type X"
    goes away.

Option 5: don't provide an extension at all i.e. do away with setting IN clauses
  in this way.

  + no issues with server-side prepare
  - obviously, you can't set IN clauses in one go any more.

1-4 all need to disable server-side prepare when used.

Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
partial implementation of 2 written but it's easy to adapt that to whatever
external interface.

setArray() needs fixing regardless of what happens here. I hope to have a
patch for that ready later today.

-O

Thanks for summarizing it Oliver.

I've asked Tom Lane about the backend behavior and he informed me that:

1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
$3) (i.e., our (?, ?, ?) syntax).

2) 7.4 backends have a PostgreSQL specific extension that allows you to
fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
pass a PostgreSQL array, like integer[] to fill the list.  Note that the
parenthesis is already in place, it is not generated by the ? expansion.

The feature 2 in 7.4 backends is of limited use as the planner does not
know about the list, so the generated plan will not be as good as if you
pass the list with fixed values since the beginning.  But an improvement
for this can be attempted for 7.5.

Regards,
Fernando




Oliver Jowett wrote:
> Some of the threads on this are getting a bit bogged down, I thought I'd
> summarize the viable options I've seen so far (well, from my point of view
> anyway)
>
> setObject() currently allows the user to bypass parameter escaping via
> setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
> plugged as it's a potential security hole.
>
> However the same functionality lets you do the (nonstandard) trick of
> providing an IN clause to a PreparedStatement like "SELECT * FROM table
> WHERE pk IN ?". It'd be good to still allow this functionality somehow after
> setObject is fixed. This is going to be a postgresql-specific extension
> however we do it.
>
> Here are the permutations I can remember:
>
> Option 1: add a method to PGStatement that explicitly sets an IN clause,
>   taking either a java.sql.Array, java.util.Collection + component type,
>   array + component type, or a custom postgresql object
>
>   + there's no confusion as to what it means
>   + using a custom object allows access via setObject(..., Types.OTHER)
>     consistently, as well as via the extension method.
>   - java.sql.Array and java.util.Collection have problems as PGStatement is
>     compiled for all JDKs and JDBC versions and those types may not be present
>     (we could do a PGJDBC2Statement or something, but that's getting messy)
>   - have to downcast to a PGStatement to use it
>
> Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>
>   + no new methods or types needed
>   - setArray() behaves differently depending on query context
>   - user has to wrap the underlying array in a java.sql.Array
>
> Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>
>   + no new methods or types needed
>   - must assume that the contents of the collection use the default type mapping
>     if a type is not provided
>   - if a type is provided and we apply it to the *components* of the
>     collection, this breaks the general getObject() interface of "bind this
>     object interpreting it as this particular type".
>   - not obvious what to do with objects that are both Collections and some
>     other SQL-relevant type; solutions make setObject's behaviour complex
>     and/or query-context-dependent
>
> Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>    java.util.Collection
>
>   + as 3, but the ambiguity of "object is both Collection and SQL type X"
>     goes away.
>
> Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>   in this way.
>
>   + no issues with server-side prepare
>   - obviously, you can't set IN clauses in one go any more.
>
> 1-4 all need to disable server-side prepare when used.
>
> Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
> partial implementation of 2 written but it's easy to adapt that to whatever
> external interface.
>
> setArray() needs fixing regardless of what happens here. I hope to have a
> patch for that ready later today.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

  I also prefer number one.
  Maybe we should do a poll? :-)

On Tue, 22 Jul 2003 16:11:19 +1200
Oliver Jowett <oliver@opencloud.com> wrote:

> Some of the threads on this are getting a bit bogged down, I thought I'd
> summarize the viable options I've seen so far (well, from my point of view
> anyway)
>
> setObject() currently allows the user to bypass parameter escaping via
> setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
> plugged as it's a potential security hole.
>
> However the same functionality lets you do the (nonstandard) trick of
> providing an IN clause to a PreparedStatement like "SELECT * FROM table
> WHERE pk IN ?". It'd be good to still allow this functionality somehow after
> setObject is fixed. This is going to be a postgresql-specific extension
> however we do it.
>
> Here are the permutations I can remember:
>
> Option 1: add a method to PGStatement that explicitly sets an IN clause,
>   taking either a java.sql.Array, java.util.Collection + component type,
>   array + component type, or a custom postgresql object
>
>   + there's no confusion as to what it means
>   + using a custom object allows access via setObject(..., Types.OTHER)
>     consistently, as well as via the extension method.
>   - java.sql.Array and java.util.Collection have problems as PGStatement is
>     compiled for all JDKs and JDBC versions and those types may not be present
>     (we could do a PGJDBC2Statement or something, but that's getting messy)
>   - have to downcast to a PGStatement to use it
>
> Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>
>   + no new methods or types needed
>   - setArray() behaves differently depending on query context
>   - user has to wrap the underlying array in a java.sql.Array
>
> Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>
>   + no new methods or types needed
>   - must assume that the contents of the collection use the default type mapping
>     if a type is not provided
>   - if a type is provided and we apply it to the *components* of the
>     collection, this breaks the general getObject() interface of "bind this
>     object interpreting it as this particular type".
>   - not obvious what to do with objects that are both Collections and some
>     other SQL-relevant type; solutions make setObject's behaviour complex
>     and/or query-context-dependent
>
> Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>    java.util.Collection
>
>   + as 3, but the ambiguity of "object is both Collection and SQL type X"
>     goes away.
>
> Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>   in this way.
>
>   + no issues with server-side prepare
>   - obviously, you can't set IN clauses in one go any more.
>
> 1-4 all need to disable server-side prepare when used.
>
> Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
> partial implementation of 2 written but it's easy to adapt that to whatever
> external interface.
>
> setArray() needs fixing regardless of what happens here. I hope to have a
> patch for that ready later today.
>
> -O
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--

 /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
 \ / Ribbon Campaign  Analista de Sistemas
  X  Against HTML     Cel.: 51-91287530
 / \ Email!           Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341

>
>
>Here are the permutations I can remember:
>
>Option 1: add a method to PGStatement that explicitly sets an IN clause,
>  taking either a java.sql.Array, java.util.Collection + component type,
>  array + component type, or a custom postgresql object
>
>  + there's no confusion as to what it means
>  + using a custom object allows access via setObject(..., Types.OTHER)
>    consistently, as well as via the extension method.
>
it doesn't (at least, not in the current implementation) - Types.OTHER
ends up calling setString(), that makes it useless for IN parameters

>  - java.sql.Array and java.util.Collection have problems as PGStatement is
>    compiled for all JDKs and JDBC versions and those types may not be present
>    (we could do a PGJDBC2Statement or something, but that's getting messy)
>
you could declare it to take Object, I suppose (that would be the only
way anyway if you wanted to support arrays of primitive types anyway)

>  - have to downcast to a PGStatement to use it
>
>
>
>
>Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>
>  + no new methods or types needed
>  - setArray() behaves differently depending on query context
>  - user has to wrap the underlying array in a java.sql.Array
>
>Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>
>  + no new methods or types needed
>  - must assume that the contents of the collection use the default type mapping
>    if a type is not provided
>
You can require the type to be provided.

>  - if a type is provided and we apply it to the *components* of the
>    collection, this breaks the general getObject() interface of "bind this
>    object interpreting it as this particular type".
>  - not obvious what to do with objects that are both Collections and some
>    other SQL-relevant type; solutions make setObject's behaviour complex
>    and/or query-context-dependent
>
>

>Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>   java.util.Collection
>
>  + as 3, but the ambiguity of "object is both Collection and SQL type X"
>    goes away.
>
>Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>  in this way.
>
>  + no issues with server-side prepare
>  - obviously, you can't set IN clauses in one go any more.
>
>1-4 all need to disable server-side prepare when used.
>
>Did I miss anything? My personal order of preference is 1-2-4-5-3.
>
For what it's worth, mine is 3-4-1,2,5  (commas meaning that the last
three seem equally useless).

Dima

> I have a
>partial implementation of 2 written but it's easy to adapt that to whatever
>external interface.
>
>setArray() needs fixing regardless of what happens here. I hope to have a
>patch for that ready later today.
>
>-O
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Fernando Nasser wrote:

> Thanks for summarizing it Oliver.
>
> I've asked Tom Lane about the backend behavior and he informed me that:
>
> 1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
> $3) (i.e., our (?, ?, ?) syntax).
>
> 2) 7.4 backends have a PostgreSQL specific extension that allows you
> to fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has
> to pass a PostgreSQL array, like integer[] to fill the list.  Note
> that the parenthesis is already in place, it is not generated by the ?
> expansion.
>
> The feature 2 in 7.4 backends is of limited use as the planner does
> not know about the list, so the generated plan will not be as good as
> if you pass the list with fixed values since the beginning.

This is the same problem, as it generally exists with x=? - the query
plan is generally not as good as x=1, because the planner doesn't know
the value to use with statistics.

Are you saying that #2 only works with integers? Or can you give it any
array?

Dima

On Tue, Jul 22, 2003 at 10:27:17AM -0400, Dmitry Tkach wrote:
> >
> >
> >Here are the permutations I can remember:
> >
> >Option 1: add a method to PGStatement that explicitly sets an IN clause,
> > taking either a java.sql.Array, java.util.Collection + component type,
> > array + component type, or a custom postgresql object
> >
> > + there's no confusion as to what it means
> > + using a custom object allows access via setObject(..., Types.OTHER)
> >   consistently, as well as via the extension method.
> >
> it doesn't (at least, not in the current implementation) - Types.OTHER
> ends up calling setString(), that makes it useless for IN parameters

Well, certainly, we'd need to change setObject to understand this new type.

> > - java.sql.Array and java.util.Collection have problems as PGStatement is
> >   compiled for all JDKs and JDBC versions and those types may not be
> >   present
> >   (we could do a PGJDBC2Statement or something, but that's getting messy)
> >
> you could declare it to take Object, I suppose (that would be the only
> way anyway if you wanted to support arrays of primitive types anyway)

Good point.

> >Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
> >
> > + no new methods or types needed
> > - must assume that the contents of the collection use the default type
> > mapping
> >   if a type is not provided
> >
> You can require the type to be provided.

Hmm, so what does setObject with no type do in that case? Also see the next
point.

> > - if a type is provided and we apply it to the *components* of the
> >   collection, this breaks the general getObject() interface of "bind this
> >   object interpreting it as this particular type".

-O

Fernando Nasser wrote:

> Thanks for summarizing it Oliver.
>
> I've asked Tom Lane about the backend behavior and he informed me that:
>
> 1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
> $3) (i.e., our (?, ?, ?) syntax).
>
> 2) 7.4 backends have a PostgreSQL specific extension that allows you
> to fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has
> to pass a PostgreSQL array, like integer[] to fill the list.  Note
> that the parenthesis is already in place, it is not generated by the ?
> expansion.

If I read this correctly, there is no need for any special handling
from  the driver side - just setArray() should work. Or am I missing
something?

Dima

Felipe Schnack wrote:
>   I also prefer number one.
>   Maybe we should do a poll? :-)
>

Oliver has to update his summary first.  There are some new info from
the backend side.

Note that option 2 now should read "when inside the parenthesis that
define an <in value list> of the IN <predicate>.  (I am using the SQL
standard clause names here).

I would go with number 2 because that is exactly what the backend does
in its PREPARE statement.

Cheers,
Fernando



> On Tue, 22 Jul 2003 16:11:19 +1200
> Oliver Jowett <oliver@opencloud.com> wrote:
>
>
>>Some of the threads on this are getting a bit bogged down, I thought I'd
>>summarize the viable options I've seen so far (well, from my point of view
>>anyway)
>>
>>setObject() currently allows the user to bypass parameter escaping via
>>setObject(n, string, Types.NUMERIC) (and variants on that). This needs to be
>>plugged as it's a potential security hole.
>>
>>However the same functionality lets you do the (nonstandard) trick of
>>providing an IN clause to a PreparedStatement like "SELECT * FROM table
>>WHERE pk IN ?". It'd be good to still allow this functionality somehow after
>>setObject is fixed. This is going to be a postgresql-specific extension
>>however we do it.
>>
>>Here are the permutations I can remember:
>>
>>Option 1: add a method to PGStatement that explicitly sets an IN clause,
>>  taking either a java.sql.Array, java.util.Collection + component type,
>>  array + component type, or a custom postgresql object
>>
>>  + there's no confusion as to what it means
>>  + using a custom object allows access via setObject(..., Types.OTHER)
>>    consistently, as well as via the extension method.
>>  - java.sql.Array and java.util.Collection have problems as PGStatement is
>>    compiled for all JDKs and JDBC versions and those types may not be present
>>    (we could do a PGJDBC2Statement or something, but that's getting messy)
>>  - have to downcast to a PGStatement to use it
>>
>>Option 2: make setArray() expand to an IN clause when the parameter follows " IN".
>>
>>  + no new methods or types needed
>>  - setArray() behaves differently depending on query context
>>  - user has to wrap the underlying array in a java.sql.Array
>>
>>Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>>
>>  + no new methods or types needed
>>  - must assume that the contents of the collection use the default type mapping
>>    if a type is not provided
>>  - if a type is provided and we apply it to the *components* of the
>>    collection, this breaks the general getObject() interface of "bind this
>>    object interpreting it as this particular type".
>>  - not obvious what to do with objects that are both Collections and some
>>    other SQL-relevant type; solutions make setObject's behaviour complex
>>    and/or query-context-dependent
>>
>>Option 4: as 3, but use java arrays (native arrays, not java.sql.Array) instead of
>>   java.util.Collection
>>
>>  + as 3, but the ambiguity of "object is both Collection and SQL type X"
>>    goes away.
>>
>>Option 5: don't provide an extension at all i.e. do away with setting IN clauses
>>  in this way.
>>
>>  + no issues with server-side prepare
>>  - obviously, you can't set IN clauses in one go any more.
>>
>>1-4 all need to disable server-side prepare when used.
>>
>>Did I miss anything? My personal order of preference is 1-2-4-5-3. I have a
>>partial implementation of 2 written but it's easy to adapt that to whatever
>>external interface.
>>
>>setArray() needs fixing regardless of what happens here. I hope to have a
>>patch for that ready later today.
>>
>>-O
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Oliver Jowett wrote:
> On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>
>>Thanks for summarizing it Oliver.
>>
>>I've asked Tom Lane about the backend behavior and he informed me that:
>>
>>1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
>>$3) (i.e., our (?, ?, ?) syntax).
>>
>>2) 7.4 backends have a PostgreSQL specific extension that allows you to
>>fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
>>pass a PostgreSQL array, like integer[] to fill the list.  Note that the
>>parenthesis is already in place, it is not generated by the ? expansion.
>
>
> I assume this is only when you're doing a PREPARE/EXECUTE?
>

yes.

>
>>The feature 2 in 7.4 backends is of limited use as the planner does not
>>know about the list, so the generated plan will not be as good as if you
>>pass the list with fixed values since the beginning.  But an improvement
>>for this can be attempted for 7.5.
>
>
> Hm, then it sounds like the right solution is to have setArray() expand as
> the guts of an IN clause when the backend is <7.4 or server prepares are
> off, and the parameter is in a query of the form "... IN (?)", and as a
> normal array otherwise.
>

That is _exactly_ what I am proposing (option 2 of your summary)



--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Dmitry Tkach wrote:
> Fernando Nasser wrote:
>
>> Thanks for summarizing it Oliver.
>>
>> I've asked Tom Lane about the backend behavior and he informed me that:
>>
>> 1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
>> $3) (i.e., our (?, ?, ?) syntax).
>>
>> 2) 7.4 backends have a PostgreSQL specific extension that allows you
>> to fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has
>> to pass a PostgreSQL array, like integer[] to fill the list.  Note
>> that the parenthesis is already in place, it is not generated by the ?
>> expansion.
>
>
> If I read this correctly, there is no need for any special handling
> from  the driver side - just setArray() should work. Or am I missing
> something?
>

With the new V3 protocol this is probably true (7.4 will support V3).

--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
> Thanks for summarizing it Oliver.
>
> I've asked Tom Lane about the backend behavior and he informed me that:
>
> 1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
> $3) (i.e., our (?, ?, ?) syntax).
>
> 2) 7.4 backends have a PostgreSQL specific extension that allows you to
> fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
> pass a PostgreSQL array, like integer[] to fill the list.  Note that the
> parenthesis is already in place, it is not generated by the ? expansion.

I assume this is only when you're doing a PREPARE/EXECUTE?

> The feature 2 in 7.4 backends is of limited use as the planner does not
> know about the list, so the generated plan will not be as good as if you
> pass the list with fixed values since the beginning.  But an improvement
> for this can be attempted for 7.5.

Hm, then it sounds like the right solution is to have setArray() expand as
the guts of an IN clause when the backend is <7.4 or server prepares are
off, and the parameter is in a query of the form "... IN (?)", and as a
normal array otherwise.

-O

>
>
>>>Option 3: make setObject(n, Collection [, type]) expand to an IN clause.
>>>
>>>+ no new methods or types needed
>>>- must assume that the contents of the collection use the default type
>>>mapping
>>>  if a type is not provided
>>>
>>>
>>>
>>You can require the type to be provided.
>>
>>
>
>Hmm, so what does setObject with no type do in that case? Also see the next
>point.
>
>
It will throw an exception - "Unrecognized parameter type: " +
Object.getClass().getName ()

>
>
>>>- if a type is provided and we apply it to the *components* of the
>>>  collection, this breaks the general getObject() interface of "bind this
>>>  object interpreting it as this particular type".
>>>
>>>
Well... this "general interface" is *by implication* only. It is not
defined this way in the spec, it is not documented to always work this way.
So, you just *assume*, that this is the general interface... It doesn't
have to be like that... Certainly not at the cost of valuable
functionality...

Dima.

P.S. Actually, in light of that previous message about 7.4 support for
arrays in the in clause, this whole discussion seems to be moot :-)
It seems to me that just setArray () should then work, without any
special handling by the driver... Isn't it the case?

> Oliver has to update his summary first.  There are some new info from
> the backend side.
>
> Note that option 2 now should read "when inside the parenthesis that
> define an <in value list> of the IN <predicate>.  (I am using the SQL
> standard clause names here).
>
> I would go with number 2 because that is exactly what the
> backend does
> in its PREPARE statement.

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.

Darin

Dmitry Tkach <dmitry@openratings.com> writes:
> Fernando Nasser wrote:
>> The feature 2 in 7.4 backends is of limited use as the planner does
>> not know about the list, so the generated plan will not be as good as
>> if you pass the list with fixed values since the beginning.

> This is the same problem, as it generally exists with x=? - the query
> plan is generally not as good as x=1, because the planner doesn't know
> the value to use with statistics.

No, it's not the same thing --- the planner can generate an indexscan
plan when scalar params are involved, although it might choose not to.
The planner is simply not aware that any comparable optimization might
be possible when using the new array syntax.  Let me attach the example
I sent Fernando last night ...


Fernando Nasser <fnasser@redhat.com> writes:
> PREPARE tststmt (integer[]) AS SELECT * from testmetadata where id IN (?);
> PREPARE tststmt (integer, integer) AS SELECT * from testmetadata where id IN (?, ?);
> all give parsing errors.

The second case works fine from the command line:

regression=# prepare z(int,int) as select * from tenk1 where unique1 in ($1,$2);
PREPARE
regression=# execute z(42,66);
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even |
stringu1| stringu2 | string4  

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85 |
QBAAAA  | SEIAAA   | OOOOxx 
      66 |    6723 |   0 |    2 |   6 |      6 |      66 |       66 |          66 |        66 |       66 | 132 |  133 |
OCAAAA  | PYJAAA   | VVVVxx 
(2 rows)

Perhaps JDBC has some problem with it?

I would not expect the first case to work, since it violates the plain
meaning of IN.  But Joe Conway has implemented some non-SQL syntax to
handle that in 7.4:

regression=# prepare zz(int[]) as select * from tenk1 where unique1 = ANY ($1);
PREPARE
regression=# execute zz(ARRAY[42,66]);
 unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even |
stringu1| stringu2 | string4  

---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85 |
QBAAAA  | SEIAAA   | OOOOxx 
      66 |    6723 |   0 |    2 |   6 |      6 |      66 |       66 |          66 |        66 |       66 | 132 |  133 |
OCAAAA  | PYJAAA   | VVVVxx 
(2 rows)

I should warn you though that this is not yet executed efficiently; the
planner has no idea about reducing it to a set of indexscans.  Compare

regression=# explain analyze execute z(42,66);
                                                             QUERY PLAN
             

-------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using tenk1_unique1, tenk1_unique1 on tenk1  (cost=0.00..12.02 rows=2 width=244) (actual time=0.28..0.48
rows=2loops=1) 
   Index Cond: ((unique1 = $1) OR (unique1 = $2))
 Total runtime: 1.35 msec
(3 rows)

regression=# explain analyze execute zz(ARRAY[42,66]);
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Seq Scan on tenk1  (cost=0.00..708.00 rows=5000 width=244) (actual time=70.03..126.16 rows=2 loops=1)
   Filter: (unique1 = ANY ($1))
 Total runtime: 126.78 msec
(3 rows)


Perhaps we can make it work better in 7.5.

            regards, tom lane

Oops :-(

I see... that is a world of a differnce :-(

Dima

Tom Lane wrote:

>Dmitry Tkach <dmitry@openratings.com> writes:
>
>
>>Fernando Nasser wrote:
>>
>>
>>>The feature 2 in 7.4 backends is of limited use as the planner does
>>>not know about the list, so the generated plan will not be as good as
>>>if you pass the list with fixed values since the beginning.
>>>
>>>
>
>
>
>>This is the same problem, as it generally exists with x=? - the query
>>plan is generally not as good as x=1, because the planner doesn't know
>>the value to use with statistics.
>>
>>
>
>No, it's not the same thing --- the planner can generate an indexscan
>plan when scalar params are involved, although it might choose not to.
>The planner is simply not aware that any comparable optimization might
>be possible when using the new array syntax.  Let me attach the example
>I sent Fernando last night ...
>
>
>Fernando Nasser <fnasser@redhat.com> writes:
>
>
>>PREPARE tststmt (integer[]) AS SELECT * from testmetadata where id IN (?);
>>PREPARE tststmt (integer, integer) AS SELECT * from testmetadata where id IN (?, ?);
>>all give parsing errors.
>>
>>
>
>The second case works fine from the command line:
>
>regression=# prepare z(int,int) as select * from tenk1 where unique1 in ($1,$2);
>PREPARE
>regression=# execute z(42,66);
> unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even
|stringu1 | stringu2 | string4  

>---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
>      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85
|QBAAAA   | SEIAAA   | OOOOxx 
>      66 |    6723 |   0 |    2 |   6 |      6 |      66 |       66 |          66 |        66 |       66 | 132 |  133
|OCAAAA   | PYJAAA   | VVVVxx 
>(2 rows)
>
>Perhaps JDBC has some problem with it?
>
>I would not expect the first case to work, since it violates the plain
>meaning of IN.  But Joe Conway has implemented some non-SQL syntax to
>handle that in 7.4:
>
>regression=# prepare zz(int[]) as select * from tenk1 where unique1 = ANY ($1);
>PREPARE
>regression=# execute zz(ARRAY[42,66]);
> unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even
|stringu1 | stringu2 | string4  

>---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
>      42 |    5530 |   0 |    2 |   2 |      2 |      42 |       42 |          42 |        42 |       42 |  84 |   85
|QBAAAA   | SEIAAA   | OOOOxx 
>      66 |    6723 |   0 |    2 |   6 |      6 |      66 |       66 |          66 |        66 |       66 | 132 |  133
|OCAAAA   | PYJAAA   | VVVVxx 
>(2 rows)
>
>I should warn you though that this is not yet executed efficiently; the
>planner has no idea about reducing it to a set of indexscans.  Compare
>
>regression=# explain analyze execute z(42,66);
>                                                             QUERY PLAN
              

>-------------------------------------------------------------------------------------------------------------------------------------
> Index Scan using tenk1_unique1, tenk1_unique1 on tenk1  (cost=0.00..12.02 rows=2 width=244) (actual time=0.28..0.48
rows=2loops=1) 
>   Index Cond: ((unique1 = $1) OR (unique1 = $2))
> Total runtime: 1.35 msec
>(3 rows)
>
>regression=# explain analyze execute zz(ARRAY[42,66]);
>                                              QUERY PLAN
>-------------------------------------------------------------------------------------------------------
> Seq Scan on tenk1  (cost=0.00..708.00 rows=5000 width=244) (actual time=70.03..126.16 rows=2 loops=1)
>   Filter: (unique1 = ANY ($1))
> Total runtime: 126.78 msec
>(3 rows)
>
>
>Perhaps we can make it work better in 7.5.
>
>            regards, tom lane
>
>

Darin Ohashi wrote:
>>Oliver has to update his summary first.  There are some new info from
>>the backend side.
>>
>>Note that option 2 now should read "when inside the parenthesis that
>>define an <in value list> of the IN <predicate>.  (I am using the SQL
>>standard clause names here).
>>
>>I would go with number 2 because that is exactly what the
>>backend does
>>in its PREPARE statement.
>
>
> 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.


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

On Tue, Jul 22, 2003 at 10:51:51AM -0400, Tom Lane wrote:

> Fernando Nasser <fnasser@redhat.com> writes:
> > PREPARE tststmt (integer[]) AS SELECT * from testmetadata where id IN (?);
> > PREPARE tststmt (integer, integer) AS SELECT * from testmetadata where id IN (?, ?);
> > all give parsing errors.

> I would not expect the first case to work, since it violates the plain
> meaning of IN.  But Joe Conway has implemented some non-SQL syntax to
> handle that in 7.4:
>
> regression=# prepare zz(int[]) as select * from tenk1 where unique1 = ANY ($1);
> PREPARE
> regression=# execute zz(ARRAY[42,66]);

Ouch. That syntax is going to be messy to transform into an IN clause for
<7.4 backends.

-O

Am I the only the only one who doesn't like the idea of the driver parsing SQL statements (to check if there is a IN
clause)

On Tue, 22 Jul 2003 10:41:22 -0400
Fernando Nasser <fnasser@redhat.com> wrote:

> Oliver Jowett wrote:
> > On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
> >
> >>Thanks for summarizing it Oliver.
> >>
> >>I've asked Tom Lane about the backend behavior and he informed me that:
> >>
> >>1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
> >>$3) (i.e., our (?, ?, ?) syntax).
> >>
> >>2) 7.4 backends have a PostgreSQL specific extension that allows you to
> >>fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
> >>pass a PostgreSQL array, like integer[] to fill the list.  Note that the
> >>parenthesis is already in place, it is not generated by the ? expansion.
> >
> >
> > I assume this is only when you're doing a PREPARE/EXECUTE?
> >
>
> yes.
>
> >
> >>The feature 2 in 7.4 backends is of limited use as the planner does not
> >>know about the list, so the generated plan will not be as good as if you
> >>pass the list with fixed values since the beginning.  But an improvement
> >>for this can be attempted for 7.5.
> >
> >
> > Hm, then it sounds like the right solution is to have setArray() expand as
> > the guts of an IN clause when the backend is <7.4 or server prepares are
> > off, and the parameter is in a query of the form "... IN (?)", and as a
> > normal array otherwise.
> >
>
> That is _exactly_ what I am proposing (option 2 of your summary)
>
>
>
> --
> Fernando Nasser
> Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
> 2323 Yonge Street, Suite #300
> Toronto, Ontario   M4P 2C9
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)


--

 /~\ The ASCII        Felipe Schnack (felipes@ritterdosreis.br)
 \ / Ribbon Campaign  Analista de Sistemas
  X  Against HTML     Cel.: 51-91287530
 / \ Email!           Linux Counter #281893

Centro Universitário Ritter dos Reis
http://www.ritterdosreis.br
ritter@ritterdosreis.br
Fone: 51-32303341

Oliver Jowett wrote:
> On Tue, Jul 22, 2003 at 10:51:51AM -0400, Tom Lane wrote:
>
>
>>Fernando Nasser <fnasser@redhat.com> writes:
>>
>>>PREPARE tststmt (integer[]) AS SELECT * from testmetadata where id IN (?);
>>>PREPARE tststmt (integer, integer) AS SELECT * from testmetadata where id IN (?, ?);
>>>all give parsing errors.
>>
>
>>I would not expect the first case to work, since it violates the plain
>>meaning of IN.  But Joe Conway has implemented some non-SQL syntax to
>>handle that in 7.4:
>>
>>regression=# prepare zz(int[]) as select * from tenk1 where unique1 = ANY ($1);
>>PREPARE
>>regression=# execute zz(ARRAY[42,66]);
>
>
> Ouch. That syntax is going to be messy to transform into an IN clause for
> <7.4 backends.
>

Remember we will already have to know that we are handling the <in
values list> clause (i.e. it is a " IN (?)'), so we can very well
special case the expansion of the array.

But it will be much better on 7.4 and V3, I agree.

--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Felipe Schnack wrote:
>   Am I the only the only one who doesn't like the idea of the driver parsing SQL statements (to check if there is a
INclause) 
>

Mind that this is only necessary for backward compatibility.  With 7.4
and the V3 protocol you just send an array and the backend sorts it out.





> On Tue, 22 Jul 2003 10:41:22 -0400
> Fernando Nasser <fnasser@redhat.com> wrote:
>
>
>>Oliver Jowett wrote:
>>
>>>On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>
>>>
>>>>Thanks for summarizing it Oliver.
>>>>
>>>>I've asked Tom Lane about the backend behavior and he informed me that:
>>>>
>>>>1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
>>>>$3) (i.e., our (?, ?, ?) syntax).
>>>>
>>>>2) 7.4 backends have a PostgreSQL specific extension that allows you to
>>>>fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
>>>>pass a PostgreSQL array, like integer[] to fill the list.  Note that the
>>>>parenthesis is already in place, it is not generated by the ? expansion.
>>>
>>>
>>>I assume this is only when you're doing a PREPARE/EXECUTE?
>>>
>>
>>yes.
>>
>>
>>>>The feature 2 in 7.4 backends is of limited use as the planner does not
>>>>know about the list, so the generated plan will not be as good as if you
>>>>pass the list with fixed values since the beginning.  But an improvement
>>>>for this can be attempted for 7.5.
>>>
>>>
>>>Hm, then it sounds like the right solution is to have setArray() expand as
>>>the guts of an IN clause when the backend is <7.4 or server prepares are
>>>off, and the parameter is in a query of the form "... IN (?)", and as a
>>>normal array otherwise.
>>>
>>
>>That is _exactly_ what I am proposing (option 2 of your summary)
>>
>>
>>
>>--
>>Fernando Nasser
>>Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
>>2323 Yonge Street, Suite #300
>>Toronto, Ontario   M4P 2C9
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

No you are not.

--Barry

Felipe Schnack wrote:
>   Am I the only the only one who doesn't like the idea of the driver parsing SQL statements (to check if there is a
INclause) 
>
> On Tue, 22 Jul 2003 10:41:22 -0400
> Fernando Nasser <fnasser@redhat.com> wrote:
>
>
>>Oliver Jowett wrote:
>>
>>>On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>
>>>
>>>>Thanks for summarizing it Oliver.
>>>>
>>>>I've asked Tom Lane about the backend behavior and he informed me that:
>>>>
>>>>1) 7.4 backends do support parameters in the IN predicate, as ($1, $2,
>>>>$3) (i.e., our (?, ?, ?) syntax).
>>>>
>>>>2) 7.4 backends have a PostgreSQL specific extension that allows you to
>>>>fill the IN predicate with a list: ($1)  (i.e., our (?) ).  One has to
>>>>pass a PostgreSQL array, like integer[] to fill the list.  Note that the
>>>>parenthesis is already in place, it is not generated by the ? expansion.
>>>
>>>
>>>I assume this is only when you're doing a PREPARE/EXECUTE?
>>>
>>
>>yes.
>>
>>
>>>>The feature 2 in 7.4 backends is of limited use as the planner does not
>>>>know about the list, so the generated plan will not be as good as if you
>>>>pass the list with fixed values since the beginning.  But an improvement
>>>>for this can be attempted for 7.5.
>>>
>>>
>>>Hm, then it sounds like the right solution is to have setArray() expand as
>>>the guts of an IN clause when the backend is <7.4 or server prepares are
>>>off, and the parameter is in a query of the form "... IN (?)", and as a
>>>normal array otherwise.
>>>
>>
>>That is _exactly_ what I am proposing (option 2 of your summary)
>>
>>
>>
>>--
>>Fernando Nasser
>>Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
>>2323 Yonge Street, Suite #300
>>Toronto, Ontario   M4P 2C9
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>
>

> > 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.
>

Will this requirement violate the SQL standard for PREPARE/EXECUTE?

Darin

In case you missed my last comment:

Mind that this is only necessary for backward compatibility.  With 7.4
and the V3 protocol you just send an array and the backend sorts it out.

Of course, one can opt in not providing this feature for pre 7.4
backends but I see no reason for that.  The parsing required is confined
to the surrounding sql fragments (we already split the command) and very
simple.

Fernando

Barry Lind wrote:
> No you are not.
>
> --Barry
>
> Felipe Schnack wrote:
>
>>   Am I the only the only one who doesn't like the idea of the driver
>> parsing SQL statements (to check if there is a IN clause)
>>
>> On Tue, 22 Jul 2003 10:41:22 -0400
>> Fernando Nasser <fnasser@redhat.com> wrote:
>>
>>
>>> Oliver Jowett wrote:
>>>
>>>> On Tue, Jul 22, 2003 at 09:05:45AM -0400, Fernando Nasser wrote:
>>>>
>>>>
>>>>> Thanks for summarizing it Oliver.
>>>>>
>>>>> I've asked Tom Lane about the backend behavior and he informed me
>>>>> that:
>>>>>
>>>>> 1) 7.4 backends do support parameters in the IN predicate, as ($1,
>>>>> $2, $3) (i.e., our (?, ?, ?) syntax).
>>>>>
>>>>> 2) 7.4 backends have a PostgreSQL specific extension that allows
>>>>> you to fill the IN predicate with a list: ($1)  (i.e., our (?) ).
>>>>> One has to pass a PostgreSQL array, like integer[] to fill the
>>>>> list.  Note that the parenthesis is already in place, it is not
>>>>> generated by the ? expansion.
>>>>
>>>>
>>>>
>>>> I assume this is only when you're doing a PREPARE/EXECUTE?
>>>>
>>>
>>> yes.
>>>
>>>
>>>>> The feature 2 in 7.4 backends is of limited use as the planner does
>>>>> not know about the list, so the generated plan will not be as good
>>>>> as if you pass the list with fixed values since the beginning.  But
>>>>> an improvement for this can be attempted for 7.5.
>>>>
>>>>
>>>>
>>>> Hm, then it sounds like the right solution is to have setArray()
>>>> expand as
>>>> the guts of an IN clause when the backend is <7.4 or server prepares
>>>> are
>>>> off, and the parameter is in a query of the form "... IN (?)", and as a
>>>> normal array otherwise.
>>>>
>>>
>>> That is _exactly_ what I am proposing (option 2 of your summary)
>>>
>>>
>>>
>>> --
>>> Fernando Nasser
>>> Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
>>> 2323 Yonge Street, Suite #300
>>> Toronto, Ontario   M4P 2C9
>>>
>>>
>>> ---------------------------(end of broadcast)---------------------------
>>> TIP 2: you can get off all lists at once with the unregister command
>>>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>>
>>
>>
>
>
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Darin Ohashi 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.
>>
>
>
> Will this requirement violate the SQL standard for PREPARE/EXECUTE?
>

PREPARE is not a Core SQL statement, it is only defined for embedded SQL
(like for the C language).  The PostgreSQL prepare statement is an
extension as it can be used interactively and has it is own syntax that
is _way_ better than what is used on DB2 for instance (where types have
to be guessed based on a brain damaged long set of rules).



--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

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

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

Kris Jurka wrote:
> 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

Right, as I said, this syntax is trying to compare the scalar value to
the entire array value, not its elements.

> 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

And this syntax was never valid and still isn't.

Joe

On Tuesday, July 22, 2003, at 11:12  AM, Felipe Schnack wrote:
>   Am I the only the only one who doesn't like the idea of the driver
> parsing SQL statements (to check if there is a IN clause)

not at all. i say the people that need that write their own layer on
top of JDBC.
-pete

OK, I got the message that the parameter can only be used in the set
predicates like ANY but not in the IN predicate.

But I never wanted to generate a 'in (array[1,2,3])'.

I thought specifying IN (?) and doing a prepare with integer[] (where
integer[] is ARRAY[1,2,3]) could produce the equivalent of 'in (1, 2,
3)'.  It is just a PREPARE+EXECUTE syntax, not the SELECT command's IN
predicate itself.

You've mentioned a possible ambiguity.   Can anyone provide me with an
example so I can understand it better?  Please mind that I am only
talking about PREPARE+EXECUTE syntax, not the SQL command itself.

Thanks for the clarifications.

Regards,
Fernando




Joe Conway wrote:
> Kris Jurka wrote:
>
>> 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
>
>
> Right, as I said, this syntax is trying to compare the scalar value to
> the entire array value, not its elements.
>
>> 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
>
>
> And this syntax was never valid and still isn't.
>
> Joe
>
>


--
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fernando Nasser wrote:
> You've mentioned a possible ambiguity.   Can anyone provide me with an
> example so I can understand it better?  Please mind that I am only
> talking about PREPARE+EXECUTE syntax, not the SQL command itself.

None of what I mentioned had anything specific to do with
PREPARE+EXECUTE. Sorry if I caused any confusion.

Joe

On Wed, 23 Jul 2003, Fernando Nasser wrote:

> OK, I got the message that the parameter can only be used in the set
> predicates like ANY but not in the IN predicate.
>
> But I never wanted to generate a 'in (array[1,2,3])'.
>
> I thought specifying IN (?) and doing a prepare with integer[] (where
> integer[] is ARRAY[1,2,3]) could produce the equivalent of 'in (1, 2,
> 3)'.  It is just a PREPARE+EXECUTE syntax, not the SELECT command's IN
> predicate itself.

The prepare+execute is no different than the select.  It's not going to
perform any magic for you.

template1=# PREPARE stmt (integer[]) AS SELECT 1 WHERE 1 IN ($1);
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=# PREPARE stmt (integer) AS SELECT 1 WHERE 1 IN ($1);
PREPARE
template1=# EXECUTE stmt(array[1,2]);
ERROR:  Parameter $1 of type integer[] cannot be coerced into the expected
type integer
        You will need to rewrite or cast the expression
IN:  transformExecuteStmt (analyze.c:2553)
ERROR:  Parameter $1 of type integer[] cannot be coerced into the expected
type integer
        You will need to rewrite or cast the expression


I hope this helps.

Kris Jurka