Archives

The List

« DATABASE() :: MySQL Function of the Day | Main | PHP array_merge is Slow »

MySQL Pop Quiz #27

By Carsten | April 29, 2008

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

This wonderful quiz from Vladimir Kolesnikov is one of those that should make you stop and think for a moment…

Given this table and data:

mysql> SELECT * FROM t;
+------+------+
| i1   | i2   |
+------+------+
|    1 |    2 |
|    2 |    1 |
|    4 |    3 |
|    3 |    4 |
+------+------+
4 rows in set (0.00 sec)

What is the result of the following three statements?

SELECT * FROM t ORDER BY 1
SELECT * FROM t ORDER BY 2
SELECT * FROM t ORDER BY 1+1

[answer]Answer: The first two statements will order the resulting rows by column 1 and 2, i.e. i1 and i2 respectively. This is known as ordering by column position and used to perfectly legal (and standard) SQL. “Used to be” since ordering by column position has been removed from later versions of the SQL standard*.

The third statement will appear to not do any sorting, but in fact it does: What happens in this instance is that MySQL does an ordering by expression. You’ve undoubtedly seen examples of ORDER BY RAND() at some point in your MySQL career and, although not clear when seen in the context of the other two examples, the same thing happens here. In this particular case, 1+1 is evaluated to 2 for every single row, and thus there’s really nothing to order by.

I lied a bit in that last paragraph: In fact, MySQL is smart enough to recognize that 1+1 comes out to a constant expression, and this bit is optimized away before actual evaluation. You can see this by running the query through EXPLAIN and compare it with one using e.g. ORDER BY RAND().

*Or so the manual claims (”Use of column positions is deprecated…”). But MySQL v. 5.0.45 doesn’t seem to complain or even emit a warning

[/answer]

[popps]

Topics: MySQL, Pop Quiz |

3 Responses to “MySQL Pop Quiz #27”

  1. Brian Papantonio Says:
    April 29th, 2008 at 4:23 pm

    It might be more fun if you didn’t post the answers to the quizes until later, like the next day.

    Just a thought.

  2. Carsten Says:
    April 29th, 2008 at 5:02 pm

    Brian, thanks for the comment. I’ll try that with the next couple of quizzes and see how it’s received.

  3. Trucs MySQL : copie et tri de tables Says:
    May 2nd, 2008 at 5:35 pm

    [...]  MySQL Pop Quiz #27 (0 visite) [...]

Comments