Bug 122933 - PostgreSQL 7.4 yields erroneous results when using nested SELECT CASE
PostgreSQL 7.4 yields erroneous results when using nested SELECT CASE
Product: Fedora
Classification: Fedora
Component: postgresql (Show other bugs)
All Linux
medium Severity high
: ---
: ---
Assigned To: Tom Lane
David Lawrence
Depends On:
  Show dependency treegraph
Reported: 2004-05-10 11:49 EDT by Didier
Modified: 2013-07-02 23:01 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Last Closed: 2004-05-10 22:45:56 EDT
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---

Attachments (Terms of Use)
minimal test case example (1.47 KB, text/plain)
2004-05-10 11:50 EDT, Didier
no flags Details
Correct output (PostgreSQL 7.3.4) (1.88 KB, text/plain)
2004-05-10 11:50 EDT, Didier
no flags Details
Faulty output (PostgreSQL 7.4.2) (1.85 KB, text/plain)
2004-05-10 11:51 EDT, Didier
no flags Details

  None (edit)
Description Didier 2004-05-10 11:49:22 EDT
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:


Steps to Reproduce:
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 11:50:04 EDT
Created attachment 100127 [details]
minimal test case example
Comment 2 Didier 2004-05-10 11:50:55 EDT
Created attachment 100128 [details]
Correct output (PostgreSQL 7.3.4)
Comment 3 Didier 2004-05-10 11:51:26 EDT
Created attachment 100129 [details]
Faulty output (PostgreSQL 7.4.2)
Comment 4 Tom Lane 2004-05-10 20:38:02 EDT
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-10 22:45:56 EDT
Ah, found it.  The fix is committed in upstream CVS, and can be found
here if you want to apply a source patch:

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 13:40:55 EDT
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).

Note You need to log in before you can comment on or make changes to this bug.