The following bug has been logged online:

Bug reference: 1629
Logged by: mike g
Email address: mike (AT) thegodshalls (DOT) com
PostgreSQL version: 8.0
Operating system: Windows 2000
Description: subquery IN returns incorrect results
Details:

If I run this query:
SELECT distinct CAST(newprogram as varchar(60)) FROM
(SELECT t.propnbr,
CASE WHEN t.propname = 'A' THEN 'Am'
WHEN t.propname = 'B' THEN 'AMm'
WHEN t.propname = 'C' THEN 'I might vanish'
WHEN t.propname = 'D' THEN 'Bem'
WHEN t.propname = 'E' THEN 'Cm'
WHEN t.propname = 'F' THEN 'Clm'
WHEN t.propname = 'G' THEN 'Com'
WHEN t.propname = 'H' THEN 'Dm'
WHEN t.propname = 'I' THEN 'Er'
WHEN t.propname = 'J' THEN 'Err'
WHEN t.propname = 'K' THEN 'Em'
WHEN t.propname = 'L' THEN 'Fm'
WHEN t.propname = 'M' THEN 'Fm'
WHEN t.propname = 'N' THEN 'Gm'
WHEN t.propname = 'O' THEN 'Hm'
WHEN t.propname = 'P' THEN 'Dm'
WHEN t.propname = 'Q' THEN 'Lm'
WHEN t.propname = 'R' THEN 'Nm'
WHEN t.propname = 'S' THEN 'Om'
WHEN t.propname = 'T' THEN 'Err'
WHEN t.propname = 'U' THEN 'Rm'
WHEN t.propname = 'V' THEN 'Tm'
WHEN t.propname = 'W' THEN 'Tm'
ELSE t.propname
END as newprogram
FROM example_data t
INNER JOIN example_data2 b ON t.propco = b.propco
WHERE
upper(b.serviced) = 'STATE1' and
t.propname in ('A',
'B'
'C', --switch me
'D', -- and switch me
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X')) as my_data

My results are:
newprogram
Am
Bem
Clm
Cm
Com
Dm
Em
Er
Err
Fm
Gm
Hm
Lm
Nm
Om
Rm
Tm


If I just change the order of the data for the IN subquery portion

SELECT distinct CAST(newprogram as varchar(60)) FROM
(SELECT t.propnbr,
CASE WHEN t.propname = 'A' THEN 'Am'
WHEN t.propname = 'B' THEN 'AMm'
WHEN t.propname = 'C' THEN 'I might vanish'
WHEN t.propname = 'D' THEN 'Bem'
WHEN t.propname = 'E' THEN 'Cm'
WHEN t.propname = 'F' THEN 'Clm'
WHEN t.propname = 'G' THEN 'Com'
WHEN t.propname = 'H' THEN 'Dm'
WHEN t.propname = 'I' THEN 'Er'
WHEN t.propname = 'J' THEN 'Err'
WHEN t.propname = 'K' THEN 'Em'
WHEN t.propname = 'L' THEN 'Fm'
WHEN t.propname = 'M' THEN 'Fm'
WHEN t.propname = 'N' THEN 'Gm'
WHEN t.propname = 'O' THEN 'Hm'
WHEN t.propname = 'P' THEN 'Dm'
WHEN t.propname = 'Q' THEN 'Lm'
WHEN t.propname = 'R' THEN 'Nm'
WHEN t.propname = 'S' THEN 'Om'
WHEN t.propname = 'T' THEN 'Err'
WHEN t.propname = 'U' THEN 'Rm'
WHEN t.propname = 'V' THEN 'Tm'
WHEN t.propname = 'W' THEN 'Tm'
ELSE t.propname
END as newprogram
FROM example_data t
INNER JOIN example_data2 b ON t.propco = b.propco
WHERE
upper(b.serviced) = 'STATE1' and
t.propname in ('A',
'B'
'D', -- and switch me
'C', --switch me
'E',
'F',
'G',
'H',
'I',
'J',
'K',
'L',
'M',
'N',
'O',
'P',
'Q',
'R',
'S',
'T',
'U',
'V',
'W',
'X')) as my_data

Gives this for a result
Am
Clm
Cm
Com
Dm
Em
Er
Err
Fm
Gm
Hm
I might vanish
Lm
Nm
Om
Rm
Tm

I will email a pg_dump of the two tables on request.
The results of either version are incorrect.

If you remove the IN subquery and replace each case with t.propname = 'A'
or... then correct results returned:

AMm
Am
Bem
Clm
Cm
Com
Dm
Em
Er
Err
Fm
Gm
Hm
I might vanish
Lm
Nm
Om
Rm
Tm

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"mike g" <mike (AT) thegodshalls (DOT) com> writes:
It's impossible to investigate this with the amount of information you
have provided. Please show a *self contained* example, including any
table declarations and test data needed.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Wed, 27 Apr 2005 06:23 am, mike g wrote:
Unless this is a copy/paste error, you have missed a , in your query. =A0Wh=
ich effectively turns it into
('A', 'B''C', 'D' ...

Which mean that switching those two will give incorrect results. One will b=
e missing C, and it will be included with B,
and the other D for the same reason.


Regards

Russell Smith.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Sorry,

I used the online bug reporting form and it doesn't have an option to attach a file to it. If it had I would have attached a pg_dump file.

Mike

On Wed, Apr 27, 2005 at 10:57:42AM -0400, Tom Lane wrote:
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[snip]

You are correct about the comma missing between the B and C in the query. It is turning it into B'C. I can't think of a good way for postgres to try and generate a warning in case a typo like this is made.

Thank you.

Mike
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo (AT) postgresql (DOT) org so that your
message can get through to the mailing list cleanly