BUG #6242: ERROR: unexpected CASE WHEN clause: 333

On 07.10.2011 10:29, Sergey wrote:

The following bug has been logged online:

Bug reference: 6242
Logged by: Sergey
Email address: sergey-1987@yandex.ru
PostgreSQL version: 8.4.4
Operating system: FreeBSD 8.1-RELEASE
Description: ERROR: unexpected CASE WHEN clause: 333
Details:

create view test_view as
select
case n when null then 1 when 1 then 2 when 2 then 3 end
from (
values (null), (1), (2)
) as t(n)

pg_dump then fail with error: "ERROR: unexpected CASE WHEN clause: 333" but
select * from test_view does not.

This error occurs with the configuration "transform_null_equals".

Hmm, this seems to be similar to the case of deconstructing SQL function
inlining in CASE-WHEN constructs we fixed a while ago
(http://archives.postgresql.org/pgsql-hackers/2011-05/msg01319.php, last
paragraph). On PostgreSQL 8.4.9, you don't get that error, but this:

CREATE VIEW test_view AS
SELECT CASE t.n WHEN (CASE_TEST_EXPR IS NULL) THEN 1 WHEN 1 THEN 2
WHEN 2 THEN 3 ELSE NULL::integer END AS "case" FROM (VALUES
(NULL::integer), (1), (2)) t(n);

That's not much better than the error, though, because that will fail on
restore.

I think the real bug here is that transform_null_equals affects the
evaluation of the "n WHEN null" condition at all. The documentation of
transform_null_equals says:

Note that this option only affects the exact form = NULL, not other comparison operators or other expressions that are computationally equivalent to some expression involving the equals operator (such as IN). Thus, this option is not a general fix for bad programming.

In that CASE construct there was no "exact form = NULL", so
transform_null_equals should have had no effect on it.

I'm inclined to do a quick fix in transformAExprOp() function to not
apply the transformation if either side of the = operation is a
CaseTestExpr, per attached patch. We could potentially have the same
problem with any "a = NULL" expression created internally in the parser,
but I don't see any more instances of that, aside from this construction
of WHEN expressions.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On 07.10.2011 11:26, Heikki Linnakangas wrote:

I'm inclined to do a quick fix in transformAExprOp() function to not
apply the transformation if either side of the = operation is a
CaseTestExpr, per attached patch. We could potentially have the same
problem with any "a = NULL" expression created internally in the parser,
but I don't see any more instances of that, aside from this construction
of WHEN expressions.

Committed that patch, thanks for the report!

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com