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) ?
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: 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.
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).