MariaDB10.2でROW_NUMBER()が使えると聞いていたが…？

【結論】

"sql_mode=ONLY_FULL_GROUP_BY" があると ROW_NUMBER() は使えないので外した

 

【以下雑記】

MariaDB10.2.0から、ROW_NUMBER() や RANK() 等の分析関数（Window関数）を使えると聞いていた。

聞いていたし、実際使ってみた記憶があって、MariaDBサイコー！とか思っていたこともあった。（今もそれなりに思ってますが、瞬間的な喜びを表現しました）

 

最近、ROW_NUMBER() をバリバリ使いまくっている古いデータベースの処理（の一部）をMariaDBに移行するという無駄な作業を押しつけられ、ようやく重い腰を上げて取り掛かろうと思ったのだが…

 

こいつ（MariaDB10.2.11）…ROW_NUMBER() 動かねえぞ…？

 

という事件が起きた。

 

何が事件かと言うと、前述の移行の難易度を訊かれて、「余裕っすよｗｗｗSQLの文法ほとんど変わんないしｗｗ一番複雑な ROW_NUMBER() がそのままでいいんだからラクショーっすｗｗ」とかぶっこいてたのに、その ROW_NUMBER() が以下のようなエラーが出て使えないのである。

 

Error Code: 1055. '○○' isn't in GROUP BY

 

いやそんなバカな…確かにあのとき（どのときかは忘れた）は使えたはずなのに…デカい口叩いてできませんでしたはゴミカスすぎるぞおい…

ちょろっと調べても何も分からないし、みんな使えると言っている…なぜこんな不条理が…

 

悩んだ末に、ROW_NUMBER()を使わずに移行しようとかりかり書き始めたが、ありえないほどめんどくさいし難しい！

なにせwith句と併用でガリガリ書いているクエリだし、単に連番ふればよいというわけではなく、"PARTITION BY"も多用しているクエリだ。

 

例えば次のようなクエリを書き換えるのはかなりめんどくさい。

with tmp as (
  select
    hito,
    shohin,
    sirial_no,
    row_number(partition by hito, shohin order by kingaku desc, sirial_no asc) as rank
  from table_1
)
select
  hito,
  shohin,
  sirial_no
from tmp
where
  rank = 1
;

 

これをROW_NUMBER()を使わずに書くと次のようになる。（と思っている）

with tmp as (
  select
    hito,
    shohin,
    sirial_no,
    kingaku
  from table_1
)
select
  t1.hito,
  t1.shohin,
  t1.sirial_no
from tmp t1
inner join tmp t2
  on t1.hito = t2.hito
  and t1.shohin = t2.shohin
  and (
    t1.kingaku = t2.kingaku and t1.sirial_no >= t2.sirial_no
    or
    t1.kingaku < t2.kingaku
  )
group by
  t1.hito,
  t1.shohin,
  t1.sirial_no
having
  count(1) = 1
;

 

合ってるのかどうかも分からんし、こんな書き換えを全てのクエリでやってたら1年かかる。

しかもパフォーマンスが最悪になる可能性がある。「移行してパフォーマンス落ちました。」は死ぬより恥ずかしい。

 

そう思ってもう一回調べたところ、次のサイトがひっかかった。 

jira.mariadb.org

 

よく分かんないけど、my.cnfに確かに書いてある。誰だこれ作ったやつ！私だ！

 

とりあえずこれを外したら ROW_NUMBER() が動くようになった！！※

 

めでたしめでたし。

 

※ 本当に外してよいかは要確認。幸い私はテスト環境として使用していたので、本番環境構築時に気を付ければよいと思っている…