Re: MySQL search query is not executing in Postgres DB

On Tue, Aug 28, 2012 at 9:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Tue, Aug 28, 2012 at 2:55 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> >> Oh, I'd forgotten that worked that way. Frankly, that makes me quite a
> >> bit more concerned about this proposal than I was before. I do *not*
> >> want to re-introduce silent cross-category casts to text, not even if
> >> there's no other way to match the function/operator. I think that hack
> >> was/is tolerable for actual assignment to a table column, because there
> >> is very little chance that the semantics of such an assignment will come
> >> out differently than the user expected.
>
> > Well, I think that when there is only one LPAD function, there is also
> > very little chance that the results will come out differently than the
> > user expected.
>
> [ shrug... ] I'm having a hard time resisting the temptation to point
> out that there are two. The real point here though is that the proposed
> behavior change will affect all functions, not only the cases where you
> think there is only one sane behavior. And features such as search paths
> and default parameters frequently mean that there are more potential
> matches than the user thought of while writing the query.
>
> In the end, SQL is a fairly strongly typed language, especially in our
> manifestation of it. I don't think we should give that up, especially
> not for benefits as dubious as not having to write a cast to make it
> clear that yes you really do want a timestamp to be treated as text.
> IMO, saving people from the errors that inevitably arise from that sort
> of sloppy thinking is a benefit, not a cost, of having a typed language.
>
> regards, tom lane
>
+a very big number

I remember the pain we had when we upgraded from 8.1 to 8.4, but I also
distinctly remember that after the upgrade I was a little bit more
confident that our SQL code does the right thing. But we are a OCaml shop
if there is one thing we believe in with ferocity it is that a STRICT type
checker is a good thing (TM). You pay a little verbosity tax but in return
all the "stupid" little obvious bugs get caught and maybe even more
importantly when you later change your types the system are forced to
reconsider all cases where you used the value of (now different) type (and
that is A VERY GOOD THING in a big code base). Admittedly we are not there
yet in Postgres as functions are only (re)checked upon execution.

My 2cents,

Bene