Re: MySQL search query is not executing in Postgres DB

On Fri, Feb 17, 2012 at 4:12 PM, Josh Berkus <josh(at)agliodbs(dot)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
wultsch(at)gmail(dot)com