

Things in MySQL that won't work as expected - rnicholson
http://explainextended.com/2010/11/03/10-things-in-mysql-that-wont-work-as-expected/

======
prodigal_erik
Misleading title. While #4 and #3 look like good ways to avoid relying on a
horrible MySQL-only misfeature (silently picking arbitrary values from rows
you've grouped over), almost all the other behavior (maybe other than LIMIT in
#5) is correct according to the ANSI standard and you'll find other databases
do the same.

------
ceejayoz
What's with the massive photos that have nothing to do with the article?

~~~
jacobbijani
Social Media 101 says no one will read your posts unless there are completely
unrelated pictures next to it.

~~~
pan69
That's funny. I read the first one, then all I did was look at the photos. I
understand that I'm just me but this article was more set up as a number of
photos with some unrelated SQL tips around it. I think the poster over did it.
I think the Social Media 101 is more referring to how Joel used to add a bit
of flair to his articles.

------
compay
Most of the things listed there are not MySQL specific. Here's one that I'm
pretty sure is:

SELECT * FROM example WHERE value = 'á';

where "á" uses UTF-8 encoding.

By default this will return rows that match both "á" AND "a", which may or may
not be what you want.

If you want to make MySQL distinguish between an "a" with an accent and one
without, then you need to specify a collation either in the table
creation/alter statement, or in your query directly:

SELECT * example WHERE value = 'á' COLLATE utf8_bin;

~~~
quassnoi
All major engines have collations and collation-specific behavior (which can
be confusing). However, this one I saved for the next post (with another set
of completely unrelated pictures).

------
d_r
Some of these are just silly. E.g.

    
    
      WHERE column IN ('1, 2, 3')
    

How can this ever be expected to work, unless, of course your column is
literally '1, 2, 3'? Seems strange to blame MySQL for it. ("MySQL is buggy
because it cannot infer meaning from my arbitrary string literals!")

~~~
quassnoi
This is very often "expected to work". Just search StackOverflow for
FIND_IN_SET. Here are just three random hits:

[http://stackoverflow.com/questions/4037145/mysql-how-to-
sele...](http://stackoverflow.com/questions/4037145/mysql-how-to-select-rows-
where-value-is-in-array)

[http://stackoverflow.com/questions/3946831/mysql-where-
probl...](http://stackoverflow.com/questions/3946831/mysql-where-problem)

[http://stackoverflow.com/questions/3734161/mysql-select-
wher...](http://stackoverflow.com/questions/3734161/mysql-select-where-a-in-
field)

None actually "blames" MySQL for that. The article just describes what comes
to the beginning developer's mind first, and tells the correct way to do that.

------
tzs
A lot of these come down to NULL makes things not work the way you might
expect. Maybe C. J. Date is rightbwhen he says there should be no NULL in the
relational model.

~~~
jerf
Or possibly the NULL should behave more like a modern NULL and have a
different truth table for it. I don't think it is coincidence that no modern
NULL value in a programming language acts like the SQL NULL.

People will usually reply to my criticisms about SQL NULL by pointing out that
it is logically consistent, which is true, but it's not the _only_ logically
consistent NULL we can define. I know the theory behind the SQL NULL quite
well, well enough to observe just how rarely it matches SQL practice.

~~~
saurik
(I personally find the SQL null semantics incredibly clear and understandable,
and find the weird hacks many languages take to have null mean something
really confusing.)

------
axiak
haven't we learned that order by rand() is a bad idea?

~~~
compay
That depends on how and when you're going to use it. It's fine with small data
sets, or if you're going to run the query and cache its output periodically.
With MySQL you can also speed it up quite a bit by selecting only an indexed
column rather than `SELECT *`.

But yeah, if it's in a query that you're going to run on every single hit to
the index page of a busy site, then you're going to have problems.

------
jpcx01
Do most of these examples work in Postgres?

~~~
quassnoi
All "wrong" queries are wrong in PostgreSQL as well (and #3 won't even parse).
The workarounds for #4, #3 and #2 are different for PostgreSQL, the others
will work.

