Thread: BUG #1629: subquery IN returns incorrect results

BUG #1629: subquery IN returns incorrect results

From
"mike g"
Date:
The following bug has been logged online:

Bug reference:      1629
Logged by:          mike g
Email address:      mike@thegodshalls.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

Re: BUG #1629: subquery IN returns incorrect results

From
Tom Lane
Date:
"mike g" <mike@thegodshalls.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

Re: BUG #1629: subquery IN returns incorrect results

From
Russell Smith
Date:
On Wed, 27 Apr 2005 06:23 am, mike g wrote:
>=20
> The following bug has been logged online:
>=20
> Bug reference: =A0 =A0 =A01629
> Logged by: =A0 =A0 =A0 =A0 =A0mike g
> Email address: =A0 =A0 =A0mike@thegodshalls.com
> PostgreSQL version: 8.0
> Operating system: =A0 Windows 2000
> Description: =A0 =A0 =A0 =A0subquery IN returns incorrect results
> Details:=20
>=20
> If I run this query:
> SELECT distinct CAST(newprogram as varchar(60)) FROM
> =A0(SELECT t.propnbr,=20
> =A0 =A0 =A0CASE WHEN t.propname =3D =A0'A' THEN 'Am'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'B' THEN 'AMm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'C' THEN 'I might vanish'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'D' THEN 'Bem'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'E' THEN 'Cm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'F' THEN 'Clm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'G' THEN 'Com'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'H' THEN 'Dm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'I' THEN 'Er'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'J' THEN 'Err'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'K' THEN 'Em'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'L' THEN 'Fm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'M' THEN 'Fm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'N' THEN 'Gm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'O' THEN 'Hm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'P' THEN 'Dm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'Q' THEN 'Lm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'R' THEN 'Nm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'S' THEN 'Om'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'T' THEN 'Err'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'U' THEN 'Rm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'V' THEN 'Tm'
> =A0 =A0 =A0 =A0 =A0 WHEN t.propname =3D =A0'W' THEN 'Tm'
> =A0 =A0 =A0 =A0 =A0 ELSE t.propname
> =A0 =A0 =A0 END as newprogram
> =A0 =A0FROM example_data t
> =A0 =A0INNER JOIN example_data2 b ON t.propco =3D b.propco
> =A0WHERE
> =A0upper(b.serviced) =3D 'STATE1' and
> =A0t.propname =A0in ('A',
> 'B'

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.

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

Regards

Russell Smith.

Re: BUG #1629: subquery IN returns incorrect results

From
"Mike G."
Date:
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
attacheda pg_dump file. 

Mike

On Wed, Apr 27, 2005 at 10:57:42AM -0400, Tom Lane wrote:
> "mike g" <mike@thegodshalls.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

Re: BUG #1629: subquery IN returns incorrect results

From
"Mike G."
Date:
[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
agood 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