David,
> I have no idea how this mechanism works but ISTM that the planner could, for
> "anyelement", look at where the result of the function call is used and add
> a cast to the function input value to match the desired result type if the
> input type is "undefined".
Well, that's not how "anyelement" works, actually. And the input type
for min() is not "anyelement".
> I'm curious what you would consider to be a "more apropos error message" in
> this situation; regardless of how difficult it would be to implement.
"ERROR: unable to determine appropriate type for 'NULL'"
But again, don't hold your breath, per above.
> I am also curious if you can think of a better example of where this
> behavior is problematic. The query for this thread is not something that I
> would deem to be good SQL.
Yeah, but it gets generated a lot. And per your other example,
sometimes it *does* work, so developers/ORM authors start to rely on it.And then it breaks.
Mostly the problematic cases are involving function parameters, where
adding a new version of a function can suddently cause a call with an
unadorned NULL to break, when it used to work. For example, suppose I
have only one function "dingbat"
dingbat( timestamptz, text, text, float )
I can easily call it with:
SELECT dingbat( '2013-01-01', 'Josh', 'pgsql-hackers', NULL )
But if someone else adds a second function, possibly due to a typo with
the version control system:
dingbat(timestamptz, text, text, text)
... then the above SELECT call will automatically choose the second
function, because NULL defaults to TEXT if unadorned. Among other
things, that could make a fun exploit if people have been careless with
their SECURITY DEFINER functions.
A worse example is the CIText type. A couple versions ago, I attempted
to force default case-insensitive comparisons for:
'val'::CITEXT = 'val'::TEXT
... which is what the user would intuitively believe would happen,
instead of the case-sensitive comparison, which is what *does* happen.
After a long weekend of messy bug-hunting and breaking built-in
postgresql functions, I gave up.
The root cause of this is that we treat "default TEXT" the same as "real
TEXT" as a type. Changing that logic, though, would require a massive
refactoring and debugging of PostgreSQL.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
