Re: MySQL search query is not executing in Postgres DB - Mailing list pgsql-hackers

On Tue, Nov 27, 2012 at 2:26 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Uh, no, not really, and I think that assertion just goes to show that
> this area is more subtle than you think.  quote_literal() for instance
> presently works for any datatype that has an explicit cast to text.

That doesn't appear to be the behavior I'm seeing:

rhaas=# select quote_literal(17);quote_literal
---------------'17'
(1 row)

rhaas=# select * from pg_cast where castsource = 'int4'::regtype and
casttarget = 'text'::regtype;castsource | casttarget | castfunc | castcontext | castmethod
------------+------------+----------+-------------+------------
(0 rows)


> After making the change you propose above, it would only work for types
> for which the cast was assignment-grade or less.

...but that's everything, because there's a hardcoded exception in the
code that dictates that even if there is no entry in pg_cast, an
assignment cast to text exists for every data type.

> concat() is even
> looser: as now implemented, it works for *anything at all*, because it
> relies on datatype output functions not casts to text.  I'm dubious that
> that inconsistency is a good thing, actually, but that's how the
> committed code is written.

I argued at the time that CONCAT should take variadic text rather than
variadic any and was roundly ignored on the grounds that the implicit
casting to text behavior was what everyone wanted in that particular
case.  My feeling is that we need to adopt a solution to this problem
partly so that people don't keep inventing (even in core code!)
one-off, hackish solutions that make certain cases behave completely
differently from the general rules.

> Now, some of us might think that backing these conversions down to only
> allowing assignment-grade casts would be an improvement, in the sense
> that it would actually make the type system tighter not looser than it
> is today for these particular functions.  But I suspect you wouldn't see
> it as an improvement, given the position you're arguing from.

Actually, I think it wouldn't matter a bit, because of the exception
that says there's an assignment cast to text for everything.

> In fact, I'm afraid that making this change would result in requests to
> downgrade existing explicit casts to be assignment-only, so that people
> could be even lazier about not casting function arguments; and that is
> something up with which I will not put.

While I'm personally not excited about it, it is certainly imaginable
that someone might prefer something like text -> xml to be an
assignment casts rather than an explicit cast.  But we've got an easy
response to that, which is fine, change it for your database, but
we're not changing it in the upstream copy.  As a compatibility issue
with other databases, it's not really an issue; I can't remember a
single complaint about needing an explicit cast from text to xml or
integer to boolean or any of the other things that appear in pg_cast
with castcontext = 'e'.

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