{"id":1330,"date":"2009-09-25T04:02:08","date_gmt":"2009-09-25T02:02:08","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1330"},"modified":"2009-09-25T15:20:52","modified_gmt":"2009-09-25T13:20:52","slug":"sql-ranking-without-self-join-revisited","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/sql-ranking-without-self-join-revisited","title":{"rendered":"SQL: ranking without self join, revisited"},"content":{"rendered":"

This post follows SQL: Ranking without self join<\/a> and On user variables evaluation order<\/a>. I wish to share some insights with regard to user variables evaluation, as well as provide yet another<\/em> ranking solution, which attempts to overcome the uncertainty factor with user variables.<\/p>\n

There will be hand waving in this post (albeit empirical hand waving). Stop here if you don’t like hand waving. Continue if you feel curious or wish to contradict my assumptions.<\/p>\n

Recap<\/h4>\n

The order of evaluation of user variables is undefined. The documentation has some contradicting example (bug 47514<\/a>), but states that variables should not be read and assigned in different parts of the same statement (just what is<\/em> a different part? Bug 47516<\/a>).<\/p>\n

Looking for a solution<\/h4>\n

There doesn’t seem to be a problem with reading and assigning variables in the very same part of the statement. For example, SELECT @a := @a+1<\/strong>… is exactly such a case.<\/p>\n

So I was looking for means to setup all the variables in one single “part” of the statement, which is to say, a single column. I’ve found several solutions, here’s one:<\/p>\n

COALESCE()<\/h4>\n

The COALESCE()<\/strong> function accepts any number of parameters (>=1), and returns the first non-NULL parameter. Tests show that it iterates the parameters and stops iterating<\/em> at the first non-NULL parameter. So, parameter #3 is not even evaluated if parameter #2 is proven to be non-NULL.<\/p>\n

This makes for a very interesting property: there is a known order of evaluation of parameters in this function.<\/p>\n

So, for example:<\/p>\n

\n
SET @a := 0, @b := 0;\r\nSELECT COALESCE(@a := @a+1, @b := @a+3);\r\n+----------------------------------+\r\n| COALESCE(@a := @a+1, @b := @a+3) |\r\n+----------------------------------+\r\n|                                1 |\r\n+----------------------------------+\r\n\r\nSELECT @a, @b;\r\n+------+------+\r\n| @a   | @b   |\r\n+------+------+\r\n| 1    | 0    |\r\n+------+------+<\/pre>\n<\/blockquote>\n
We know that @a+1<\/strong> is first evaluated, and only if it turns to be NULL, @a+3<\/strong> is evaluated (which isn’t the case here).<\/p>\n
PS: CASE<\/strong> statements also work this way, but COALESCE()<\/strong> is more compact, so I’ll use it instead.<\/em><\/p>\n
We still can’t be sure that the assignment<\/em> takes place at that time. For example, is it possible that @a+1<\/strong> is first evaluated, then (assuming it’s NULL) @a+3<\/strong> is evaluated, and only then @a<\/strong> and @b<\/strong> get the assignment?<\/p>\n
I find this extremely unlikely. That would mean, code-wise, creating a temporary map which caches every internal, intermediate result, until column value is finally evaluated, only then to be iterated and mapped to the variables. To give an extreme example, look at:<\/p>\n
\n
SELECT @a := (@b := 3 + (@c := @c + 2*(@d := SIN(id)))) FROM world.City<\/pre>\n<\/blockquote>\n
Would SIN(id)<\/strong>, 2*SIN(id)<\/strong>, @c + 2*SIN(id)<\/strong>, 3 + (@c + 2*SIN(id))<\/strong> all be cached even while the result is being evaluated? Not only is this unlikely, user variables common usage indicates the opposite. I think it’s safe to say that @d<\/strong> is first assigned, then @c<\/strong>, then @b<\/strong>, and @a<\/strong> being the last.<\/p>\n
ORDER BY<\/h4>\n
Yet another issue is: if q query has an ORDER BY<\/strong>, how can I be certain that user variables are iterated after<\/em> ordering takes place, and not during<\/em> sort merge passes?<\/p>\n
Apart from:<\/p>\n