Thread: BUG #2847: Bug with IN statement

The following bug has been logged online:

Bug reference:      2847
Logged by:          Sandip
Email address:      sandip@singapore.com
PostgreSQL version: 8.1
Operating system:   Windows XP
Description:        Bug with IN statement
Details:

this is my function:

CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
varying, character varying)
  RETURNS ret_dv_sp_get_phase AS
$BODY$
SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
                      AddInfo1, AddInfo2
FROM            T_PHASE
WHERE           (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
$BODY$
  LANGUAGE 'sql' VOLATILE;


When I run
select * from sp_get_phase ('sandip', 'oms', '4')   returns 1
record.....this works fine....

select * from sp_get_phase ('sandip', 'oms', '1')  returns 1 record.....this
also works fine... BUT

select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
record.

I tried to execute the SQL statement from the function

SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
UPDATE_DATE,
                      AddInfo1, AddInfo2
FROM            T_PHASE
WHERE           (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany') AND
BOOK_NO IN (1,4)

----- This Works fine... returns 2 records.   What may be the problem?

Thanks in advance.
Regards,
Sandip.

On 12/21/06, Sandip <sandip@singapore.com> wrote:
>
> CREATE OR REPLACE FUNCTION sp_get_phase(character varying, character
> varying, character varying)
>   RETURNS ret_dv_sp_get_phase AS
> $BODY$
> SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
>                       AddInfo1, AddInfo2
> FROM            T_PHASE
> WHERE           (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3)
> $BODY$
>   LANGUAGE 'sql' VOLATILE;
>
>
> select * from sp_get_phase ('sandip', 'oms', '1,4') this return a Blank
> record.


For this function call, the SELECT inside the function gets translated into



SELECT          ...
FROM            T_PHASE
WHERE           (USER_ID = 'sandip') AND (COMPANY_ID = 'oms') AND BOOK_NO IN
('1,4')

Notice the quotes around the list of values for $3. So you can see that the
IN list contains just one value and not two as you might have expected.
You'll need to do some trickery. Try the EXECUTE, perhaps that might help!!!


I tried to execute the SQL statement from the function
>
> SELECT          BOOK_NO, USER_ID, COMPANY_ID, PHASE, UPDATE_USER_ID,
> UPDATE_DATE,
>                       AddInfo1, AddInfo2
> FROM            T_PHASE
> WHERE           (USER_ID = 'testUser') AND (COMPANY_ID = 'testCompany')
> AND
> BOOK_NO IN (1,4)
>
> ----- This Works fine... returns 2 records.   What may be the problem?
>
>
Best regards,


--
gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com