SQLの間違い集2 MySQLのユーザ変数は評価順序が未定義

OracleやMySQLで
よく見かけるSQLの間違いをまとめていきます。

#MySQLで分析関数のRow_Number関数と同じ結果を取得
話を簡単にするために、ソートキーは重複値なしとします。

#####テーブル定義とデータ準備
```sql
create table IDTable(
ID int unsigned,
primary key(ID));

insert into IDTable values
(1),(3),(5),(10),(20),(30),(40);
```

#####分析関数を使うSQL(MySQL5.6では動作しません)
```sql
select ID,Row_Number() over(order by ID) as rn
  from IDTable
order by ID;
```

#間違ったSQL
ユーザ変数を使って、OrderBy句でのソート順に
カウントアップしていくというもの

```sql
select ID,@rn := @rn+1 as Rn
  from IDTable,(select @rn := 0) as Init
order by ID;

+----+------+
| ID | Rn   |
+----+------+
|  1 |    1 |
|  3 |    2 |
|  5 |    3 |
| 10 |    4 |
| 20 |    5 |
| 30 |    6 |
| 40 |    7 |
+----+------+
```

#間違いの理由
<pre>
MySQLのマニュアルに、ユーザー変数の評価順序が未定義と書いてあるから。
上記のSelect文だと
@rn := 0 が1番目に評価されて、
その後で、order by IDの行の順番で、@rn := @rn+1が評価されることが保証されない。
</pre>

マニュアル(日本語)より引用
http://download.nust.na/pub6/mysql/doc/refman/5.1/ja/user-variables.html
>MySQL 5.1 リファレンスマニュアル :: 8.4 ユーザによって定義された変数
>ユーザ変数の評価順序は定義されておらず、与えられたクエリ内の要素に基づいて変更されることがあります。
>SELECT @a, @a := @a+1 ...では、MySQLは@aを先に評価し次に割り当てが実行されるように見えますが、
>クエリの変更（例えばGROUP BY、HAVINGまたはORDER BY節による変更）は評価順序を変更する可能性があります。
>
>基本的なルールは、
>ステートメントの一部でユーザ変数値を割り当てないことおよび
>同一ステートメント内の他部分で同じ変数を使用しないことです。
>期待通りの結果を得られるかもしれませんが、
>これは確約されていません。

英語の原文
MySQL 5.1 Reference Manual :: 8.4 User-Defined Variables
>The order of evaluation for user variables is undefined and
>may change based on the elements contained within a given query.
>
>In SELECT @a, @a := @a+1 ...,
>you might think that MySQL will evaluate @a first and then do an assignment second,
>but changing the query (for example, by adding a GROUP BY, HAVING, or ORDER BY clause)
>may change the order of evaluation.
>
>The general rule is never to assign a value to a user variable in one part of a statement
>and use the same variable in some other part of the same statement.
>You might get the results you expect, but this is not guaranteed.

なお、上記のマニュアルのステートメントという表現は、
Select文、Update文、Insert文、Delete文において、その文全体を指して、1つのステートメントという意味です。
(Now関数の結果が一致するため)

>Now() は、ステートメントが実行を開始する時刻を示す定数時間を返します。
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_now

>NOW関数は デフォルトでは 「そのステートメントの開始時刻」を返します。
https://yoku0825.blogspot.com/2016/12/mysqlnow.html

```sql
select ID,@rn := @rn+1 as Rn,Now(),SLEEP(1)
  from IDTable,(select @rn := 0) as Init
order by ID;
+----+------+---------------------+----------+
| ID | Rn   | Now()               | SLEEP(1) |
+----+------+---------------------+----------+
|  1 |    1 | 2019-02-10 09:33:05 |        0 |
|  3 |    2 | 2019-02-10 09:33:05 |        0 |
|  5 |    3 | 2019-02-10 09:33:05 |        0 |
| 10 |    4 | 2019-02-10 09:33:05 |        0 |
| 20 |    5 | 2019-02-10 09:33:05 |        0 |
| 30 |    6 | 2019-02-10 09:33:05 |        0 |
| 40 |    7 | 2019-02-10 09:33:05 |        0 |
+----+------+---------------------+----------+
```

##MySQL5.7以降で削除予定の機能になりました
さらには、MySQL5.7以降で、Set文以外(例えばSelect文)
でのユーザ変数への値の代入は、削除予定の機能になりました。

https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

>It is also possible to assign a value to a user variable in statements other than SET.
>(This functionality is deprecated in MySQL 8.0 and subject to removal in a subsequent release.)

##MySQLが賢くなったら、インラインビューでのユーザ変数の初期化を、スキップ
さらに、MySQL開発者のGuilhem Bichotさんのブログによると
将来、MySQLが賢くなったら
インラインビューでのユーザ変数の初期化を、スキップするそうです。
https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

>SELECT @rownum:=(@rownum+1) AS num, name, birthdate
>FROM (SELECT @rownum:=0) AS initialization, people
>ORDER BY birthdate;

>It works. It relies on the fact that MySQL evaluates the content of the derived table named initialization.
>But in the future, MySQL may become smarter and say that 
>"this derived table isn't used by the statement so I can just skip its evaluation".
>And it would thus skip the initialization.

#正しいSQL
```sql
select ID,
(select count(*)+1
   from IDTable b
  where b.ID < a.ID) as Rn
from IDTable a
order by ID;
```

#参考にしたサイト
MySQL開発者のGuilhem Bichotさんのブログ
https://mysqlserverteam.com/row-numbering-ranking-how-to-use-less-user-variables-in-mysql-queries/

MySQLユーザ会のメーリングリスト
http://www.mysql.gr.jp/mysqlml/mysql/msg/16487