From: | Josh Berkus <josh(at)agliodbs(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: column "b" is of type X but expression is of type text |
Date: | 2013-07-12 17:48:56 |
Message-ID: | 51E04188.7060303@agliodbs.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Dunstan | 2013-07-12 17:49:46 | Re: Kudos for Reviewers -- wrapping it up |
Previous Message | Alvaro Herrera | 2013-07-12 17:28:38 | Re: Kudos for Reviewers -- wrapping it up |