Re: MySQL search query is not executing in Postgres DB

On 29/08/12 23:34, Robert Haas wrote:
> 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.
>
>
>
You realize of course, that '42' is the answer to Life, the Universe,
and Everything? :-)

Cheers,
Gavin