« DATABASE() :: MySQL Function of the Day | Main | PHP array_merge is Slow »
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
Show 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
Do you have your own favorite MySQL question? Is there something in MySQL that has you scratching your head? Send your ideas for new pop quiz questions to carsten (at) bitbybit (dot) dk. Send it in, even if you don’t know the answer — that only makes it more interesting.
More Pop Quiz entries:
WWW or
RSS.
Topics: MySQL, Pop Quiz |
Comments
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.
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.
May 2nd, 2008 at 5:35 pm
[...] MySQL Pop Quiz #27 (0 visite) [...]