Skip site navigation (1) Skip section navigation (2)

Nested CASE-WHEN scoping

From: Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Nested CASE-WHEN scoping
Date: 2011-05-25 11:57:44
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
While looking at fixing the multiple-evaluation issue in IN and BETWEEN 
discussed a while ago, I realized that the current assumption that only 
one CaseTestExpr placeholder needs to be valid at any given time is not 

Here's a bit contrived example:

CREATE FUNCTION evileq (timestamptz, int4) returns boolean AS $$
  SELECT case $2 WHEN length($1::text) THEN true ELSE false END;
$$ language sql;
CREATE OPERATOR = (procedure = evileq, leftarg = timestamptz, rightarg = 

postgres=#  SELECT now() = 29, CASE now() WHEN 29 THEN 'foo' ELSE 'bar' END;
  ?column? | case
  t        | bar
(1 row)

Direct call to the operator, "now () = 29" returns true, but when used 
in CASE-WHEN, which implicitly does the same comparison, the result is 
false. Admittedly that's pretty far-fetched, but nevertheless it's a bug.

As part of the BETWEEN/IN fix, I was going to refactor CaseTestExpr and 
CoerceToDomainValue placeholder node types into one generic placeholder 
node. BETWEEN needs three placeholder slots in the worst case [*], and 
now it seems that we need to handle an arbitrary number of simultaneous 
placeholders even for CASE-WHEN.

So I'm going to put the BETWEEN/IN fix aside for now, and refactor the 
placeholder infrastructure to handle several simultaneous placeholders, 
and replace CaseTestExpr and CoerceToDomainValue with it. Actually 
AggRef and WindowFunc nodes look a lot like CaseTestExpr and 
CoerceToDomainValue too, but I'm a bit scared of touching those.

PS. This is all 9.2 material, in case you're wondering. We're talking 
about pretty big patches.

[*] a BETWEEN SYMMETRIC b AND c is handled as "(a <= b AND a >= c) OR (a 
 >= b AND a <= c)", leading to multiple evaluation of all three operands 
if placeholders are not used

   Heikki Linnakangas


pgsql-hackers by date

Next:From: Heikki LinnakangasDate: 2011-05-25 12:21:33
Subject: Re: Nested CASE-WHEN scoping
Previous:From: Simon RiggsDate: 2011-05-25 11:34:59
Subject: Re: The way to know whether the standby has caught up with the master

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group