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 ...
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? More importantly, where are other functions like them documented?
-- Dean
ps: Who dreamt up the names "date_smaller" and "date_larger" ??? Much more intuitive are "min_date" and "max_date".
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. I suppose that COALESCE has the same problem as CASE ...
Tom Lane wrote on 2004-09-20 17:54:
"Dean Gibson (DB Administrator)" <[EMAIL PROTECTED]> writes:
Question: Why do the last two column definitions in the second VIEW change the scan on _LicHD from indexed to sequential ??
It's the CASE that's getting you. The poor plan is basically because the sub-view isn't getting "flattened" into the upper query, and so it's not possible to choose a plan for it that's dependent on the upper query context. And the reason it's not getting flattened is that subselects that are on the nullable side of an outer join can't be flattened unless they have nullable targetlists --- otherwise the results might not go to NULL when they are supposed to. A CASE construct is always going to be treated as non-nullable.
Fixing this properly is a research project, and I haven't thought of any quick-and-dirty hacks that aren't too ugly to consider :-(
In the meantime, you could easily replace that CASE construct with a min() function that's declared strict. I think date_smaller would do nicely, assuming the columns are actually of type date.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
