X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (unknown [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 87AF5329D3C
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 05:10:21 +0100 (BST)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 29272-09
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Tue, 21 Sep 2004 04:10:14 +0000 (GMT)
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by svr1.postgresql.org (Postfix) with ESMTP id EB595329E15
	for <pgsql-sql@postgresql.org>; Tue, 21 Sep 2004 05:10:14 +0100 (BST)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id i8L4ADS2025395;
	Tue, 21 Sep 2004 00:10:13 -0400 (EDT)
To: "Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: JOIN performance 
In-reply-to: <5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net> 
References: <5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<414F02FF.8000603@numerixtechnology.de>
	<414F02FF.8000603@numerixtechnology.de>
	<5.1.0.14.2.20040920121606.00a89c68@imaps.mailpen.net>
	<5.1.0.14.2.20040920164348.00a99868@imaps.mailpen.net>
	<5.1.0.14.2.20040920182022.00adf5e8@imaps.mailpen.net>
Comments: In-reply-to "Dean Gibson (DB Administrator)"
	<postgresql3@ultimeth.com>
	message dated "Mon, 20 Sep 2004 18:41:33 -0700"
Date: Tue, 21 Sep 2004 00:10:13 -0400
Message-ID: <25393.1095739813@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=
X-Spam-Level: 
X-Archive-Number: 200409/179
X-Sequence-Number: 19025

"Dean Gibson (DB Administrator)" <postgresql3@ultimeth.com> writes:
> I could ask why a CASE statement is always non-nullable, but I don't think 
> the answer would help be solve my problem. <grin>  I suppose it's that even 
> though my particular CASE statement has WHEN/ELSE values that come from the 
> nullable side of the JOIN, in general that's not true ...

Right, the code just sees CASE and barfs.  A finer grain of analysis
could conclude that this CASE is actually safe, but I'm unconvinced that
it's worth doing.  (Feel free to pursue this on -hackers if you care.)

> Okay, now for my big question:  I searched high and low for a function that 
> would return the minimum of two dates, and found none.  Now you come up 
> with "date_smaller", which works fine (as does "date_larger"), but where 
> are those documented?

They aren't; they are actually only intended as support functions for
MIN (resp. MAX) on dates.  But they're there, and there's nothing to
stop you using 'em.

(You do of course realize that you could have implemented these
functions for yourself in a one-liner sql or plpgsql function.  Finding
the function in the system saves you a few minutes at most.)

> ps:  Who dreamt up the names "date_smaller" and "date_larger" ???

[ shrug... ]  Some now-forgotten grad student at Berkeley.  All the
support functions for MIN and MAX are named 'foosmaller' and
'foolarger'.  Try

select aggtransfn from pg_aggregate, pg_proc
where pg_proc.oid = aggfnoid and proname = 'min';

> pps:  I realize that "date_smaller" isn't exactly equivalent to my CASE 
> statement;  a NULL value for one of the CASE operands causes the result of 
> the ELSE clause to be returned, whereas "date_smaller" just returns NULL in 
> that case.  In my data, that's significant.

Well, in that case you may have a problem here.  The point of the test
in question is that the expression has to return NULL if *either* input
is null.  Now it's true that the LEFT JOIN only cares that it goes to
NULL when *both* inputs are NULL, but we don't have any way to declare
that particular property of a function.  To make the planner happy you
will have to declare the function as STRICT, which will force it to
behave in the first fashion.

> I suppose that COALESCE has the same problem as CASE ...

Yup, of course.

			regards, tom lane