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: [EMAIL PROTECTED]
> 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.
---------------------------(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
