Re: MySQL search query is not executing in Postgres DB

On Wed, Aug 29, 2012 at 12:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> To put some concreteness into what so far has been a pretty hand-wavy
> discussion, I experimented with the attached patch. I'm not sure that
> it exactly corresponds to what you proposed, but I think this is the
> only place the consideration could be injected without a substantial
> amount of code rearrangement.

Yeah, this is what I was thinking of.

> This results in half a dozen regression
> test failures (see second attachment), which mostly consist of
> "function/operator does not exist" errors changing to "function/operator
> is not unique". I've not looked into exactly why each one happens ---
> possibly the code is now finding multiple string-category matches where
> before it found none. But it definitely illustrates my point that this
> would not be without surprises.

Well, the good news is that nothing fails that would have succeeded
before, or for that matter visca versa. But after playing around with
it a little, I agree that there's danger lurking. The fact that
length(42) fails due to the ambiguity between length(text) and
length(bpchar) is mostly happy coincidence. It's hard to get excited
about the possibility of that managing to return "2". The situation
with || is even worse. If I remove textanycat and anytextcat on the
theory that textcat itself ought to be enough under the new rules,
then a whole bunch of regression test failures occur because we end up
bogusly matching the array concatenation operator somehow, and fail to
interpret an unknown literal as an array (ouch!).

The upshot here appears to be that we're kind of schizophrenic about
what we want. With things like text || anyelement, anyelement ||
text, and concat(variadic "any") we are basically asserting that we
want to treat anything that we don't recognize as a string. But then
we have other functions (like max and length) where we don't want that
behavior. I suppose that more than anything this is based on a
perception that || won't be ambiguous (though whether that perception
is entirely correct is debatable, given the array-related meanings of
that operator) but there might be more than one possible sense for
length() or max(). Is there any principled way of distinguishing
these cases, or even a rule for what we ought to do by hand in future
cases of this type, or is it totally arbitrary?

> regression=# select lpad(42,8);
> ERROR: failed to find conversion function from integer to text
>
> so this doesn't actually solve the problem you want to solve.
> I'm not sure why that's happening, either, but evidently some
> additional coercion laxity would required.

This, however, is a trivial problem; make_fn_arguments just didn't get
the memo that it might now need to look for assignment casts. See
attached.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company