Bug 122933

Summary: PostgreSQL 7.4 yields erroneous results when using nested SELECT CASE
Product: [Fedora] Fedora Reporter: Didier <d.bz-redhat>
Component: postgresqlAssignee: Tom Lane <tgl>
Status: CLOSED NEXTRELEASE QA Contact: David Lawrence <dkl>
Severity: high Docs Contact:
Priority: medium    
Version: 2CC: hhorak
Target Milestone: ---   
Target Release: ---   
Hardware: All   
OS: Linux   
Whiteboard:
Fixed In Version: Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of: Environment:
Last Closed: 2004-05-11 02:45:56 UTC Type: ---
Regression: --- Mount Type: ---
Documentation: --- CRM:
Verified Versions: Category: ---
oVirt Team: --- RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: --- Target Upstream Version:
Embargoed:
Attachments:
Description Flags
minimal test case example
none
Correct output (PostgreSQL 7.3.4)
none
Faulty output (PostgreSQL 7.4.2) none

Description Didier 2004-05-10 15:49:22 UTC 
Description of problem:

- When using nested SELECT CASE statements (which translate internal
table data to human readable strings), pgsql 7.4 seems to interpret
the SELECT CASE erroneously, and returns faulty strings.
- pgsql 7.3 works OK.


Version-Release number of selected component (if applicable):

postgresql-7.4.2-5, compiled on both RHEL3 and FC2t3
(also tested with postgresql-7.4-5 on FC2t1)


How reproducible:

Always


Steps to Reproduce:
1. CREATE DATABASE test;
2. \c test
3. \i pgsql_test.sql
4. SELECT * FROM view1;
5. SELECT * FROM view2;

 
Actual results:

Please see attachment "pgsql_test_results-bad.txt"


Expected results:

Please see attachment "pgsql_test_results-good.txt"


Additional info:

I attached a minimal case example (attachment "pgsql_test.sql"), in
which an 'OrderStatus' is derived from three fields :
- if ordercancelled is true, status = 'Cancelled' ;
- if approver_ref <> 1 :
   * status = 'Approved' if po_ref = 1
   * status = 'PO' if po_ref <> 1
  else status = '---'

I've included two views, which return :
- two identical and correct datasets with 7.3 ;
- two different and faulty datasets with 7.4 .


Unless I've implemented a seriously flawed SELECT CASE logic (which by
accident worked ok on 7.3), I assume this is a major regression
(severity = high) ?
Comment 1 Didier 2004-05-10 15:50:04 UTC 
Created attachment 100127 [details]
minimal test case example
Comment 2 Didier 2004-05-10 15:50:55 UTC 
Created attachment 100128 [details]
Correct output (PostgreSQL 7.3.4)
Comment 3 Didier 2004-05-10 15:51:26 UTC 
Created attachment 100129 [details]
Faulty output (PostgreSQL 7.4.2)
Comment 4 Tom Lane 2004-05-11 00:38:02 UTC 
I haven't quite tracked down the problem yet, but it seems to be
associated with nested sub-SELECTs not being recalculated when they
need to be.  A short-run workaround ... which would also make the
query a good bit faster, I suspect ... is to eliminate the unnecessary
sub-SELECTs.  Everyplace you have "SELECT CASE" could be just "CASE".

Anyway, I'll see that this gets fixed for 7.4.3.
Comment 5 Tom Lane 2004-05-11 02:45:56 UTC 
Ah, found it.  The fix is committed in upstream CVS, and can be found
here if you want to apply a source patch:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/optimizer/plan/planner.c.diff?r2=1.161.2.2&r1=1.161.2.1&f=c

It will appear in 7.4.3, which I don't know the schedule for yet, but
likely will be available in time for FC3.

The failure only occurs with doubly nested sub-SELECTs in which the
inner sub-SELECT refers to outer-query values not also used by the
outer sub-SELECT, and furthermore doesn't refer to any outer-query
values that are used by the outer sub-SELECT.  In testing with your
example, it seems to require triply nested sub-SELECTs (eliminating
any one of the three SELECT keywords makes the bug go away), though I
have not taken the time to figure out exactly why that happens; I
think there may be related cases that fail with only double nesting. 
I guess this is a fairly uncommon combination, else we'd have heard
about the bug before now.  Anyway, I have adapted your example case as
a regression test for Postgres, so hopefully we'll catch any similar
errors sooner in future.
Comment 6 Didier 2004-05-11 17:40:55 UTC 
I figure this is just another Bugzilla entry which qualifies as a
prominent example for my IT decision making colleagues, an example of
the sheer power of open source and the commitment of its participants.

Thank you for the very swift reply, Tom.
(I did not apply the patch, but removed the SELECTs).