Re: BUG #1629: subquery IN returns incorrect results

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

"mike g" <mike(at)thegodshalls(dot)com> writes:
> Description: subquery IN returns incorrect results

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

On Wed, 27 Apr 2005 06:23 am, mike g wrote:
>
> 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'

Unless this is a copy/paste error, you have missed a , in your query.  Which effectively turns it into
('A', 'B''C', 'D' ...

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

> 'C',  --switch me
> 'D',  -- and switch me
> 'E',
> 'F',
[snip]

Regards

Russell Smith.

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:
> "mike g" <mike(at)thegodshalls(dot)com> writes:
> > Description: subquery IN returns incorrect results
>
> 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

[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
>
> Unless this is a copy/paste error, you have missed a , in your query.  Which effectively turns it into
> ('A', 'B''C', 'D' ...
>
> Which mean that switching those two will give incorrect results. One will be missing C, and it will be included with B,
> and the other D for the same reason.
>
> > 'C',  --switch me
> > 'D',  -- and switch me
> > 'E',
> > 'F',
> [snip]
>
> 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