Re: column "b" is of type X but expression is of type text

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