Re: BUG #1629: subquery IN returns incorrect results

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.