Skip site navigation (1) Skip section navigation (2)

Re: BUG #1629: subquery IN returns incorrect results

From: Russell Smith <mr-russ(at)pws(dot)com(dot)au>
To: "mike g" <mike(at)thegodshalls(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #1629: subquery IN returns incorrect results
Date: 2005-04-27 15:01:01
Message-ID: 200504280101.01735.mr-russ@pws.com.au (view raw or flat)
Thread:
Lists: pgsql-bugs
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.

In response to

Responses

pgsql-bugs by date

Next:From: Ariel CarnaDate: 2005-04-27 15:06:13
Subject: Re: BUG #1630: Wrong conversion in to_date() function. See example.
Previous:From: Tom LaneDate: 2005-04-27 14:57:42
Subject: Re: BUG #1629: subquery IN returns incorrect results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group