Red Hat Bugzilla – Bug 122933
PostgreSQL 7.4 yields erroneous results when using nested SELECT CASE
Last modified: 2013-07-02 23:01:06 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)
Steps to Reproduce:
1. CREATE DATABASE test;
2. \c test
3. \i pgsql_test.sql
4. SELECT * FROM view1;
5. SELECT * FROM view2;
Please see attachment "pgsql_test_results-bad.txt"
Please see attachment "pgsql_test_results-good.txt"
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) ?
Created attachment 100127 [details]
minimal test case example
Created attachment 100128 [details]
Correct output (PostgreSQL 7.3.4)
Created attachment 100129 [details]
Faulty output (PostgreSQL 7.4.2)
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.
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.
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).