On Fri, Feb 17, 2012 at 4:12 PM, Josh Berkus <j...@agliodbs.com> wrote: > On 2/17/12 12:04 PM, Robert Haas wrote: >> The argument isn't about whether the user made the right design >> choices; it's about whether he should be forced to insert an explicit >> type cast to get the query to do what it is unambiguously intended to >> do. > > I don't find INTEGER LIKE '1%' to be unambiguous. > > Prior to this discussion, if I had run across such a piece of code, I > couldn't have told you what it would do in MySQL without testing. > > What *does* it do in MySQL? >
IIRC it casts each INTEGER (without any left padding) to text and then does the comparison as per normal. Comparison of dissimilar types are a recipe for full table scans and unexpected results. A really good example is select * from employees where first_name=5; vs select * from employees where first_name='5'; Where first_name is string the queries above have very different behaviour in MySQL. The first does a full table scan and coerces first_name to an integer (so '5adfs' -> 5) while the second can use an index as it is normal string comparison. I have seen this sort of things cause significant production issues several times.* I have seen several companies use comparisons of dissimilar data types as part of their stump the prospective DBA test and they stump lots of folks. -- Rob Wultsch wult...@gmail.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
