Re: "IN" statement causing execution cancel?

I don't know why that problem is occurring in postgresql.

However, these two statements will return the same resultset:

SELECT field1 FROM table WHERE field2 IN (1,2,3,4,5,6,7);

SELECT field1 FROM table WHERE ',1,2,3,4,5,6,7,' like '%,'||field2||',%';

You may need to cast field2 to something to convert it to a string.

Cheers


----- Original Message -----
From: "Andrew Ayers" <aayers@eldocomp.com>
To: <pgsql-general@postgresql.org>
Sent: Thursday, May 22, 2003 9:16 AM
Subject: Re: [GENERAL] "IN" statement causing execution cancel?


> All,
>
> I am having (yet another) problem.
>
> I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver
> under Windows), a statement of the form:
>
> SELECT field1 FROM table WHERE field2 IN (list);
>
> Where "list" is a *very* long comma-delimited list of values (on the
> order of several hundred values).
>
> When I attempt to execute this query via the ODBC driver, a very long
> wait occurs (one minute or so), then I get back an error of "Execution
> Cancelled" - this error is reported both to my application, as well as
> in the error logs for the server PostgreSQL (7.3.2) is running on. I
> have also noticed the same error in the commlog you can set up to be
> created in the ODBC driver setup.
>
> The log also gives a strange error of an invalid character or something
> found at position 15 of my SQL statement - which makes no sense since
> that character is part of the field name, plus if I limit my search to
> only a few (say 10-20) values in the "IN" list, I don't get any error.
>
> What is strange, is that if I execute this same statement under psql
> (that is, the large list), it executes it fine, returning the expected
> results.
>
> I need to use ODBC for this application (it is a VB app). I have given
> thought to re-writing my SQL statement generation code to split it up
> into several SQL statements to be executed, and collate the results when
> they come back. However, this is a bandaid to the problem. BTW - running
> the same large query against my Access 97 DB gives back the results
> expected (ie, via JET - not ODBC).
>
> Is there some ODBC driver config option, or something on the server,
> that needs to be properly set for this to work? Is there a limit on the
> size of the IN value list?
>
> Thank you,
>
> Andrew L. Ayers
> Phoenix, Arizona
>
>
> -- CONFIDENTIALITY NOTICE --
>
> This message is intended for the sole use of the individual and entity to
who it is addressed, and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If you are not
the intended addressee, nor authorized to receive for the intended
addressee, you are hereby notified that you may not use, copy, disclose or
distribute to anyone the message or any information contained in the
message. If you have received this message in error, please immediately
advise the sender by reply email, and delete the message. Thank you.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>