OracleとPHPのソートで NULLの位置が違うから困ったという話

  • 0
    いいね
  • 0
    コメント

    動的SQL禁止令が敷かれている職場では、

    SQLで全件取得 (静的で書ける範囲の絞り込み条件は書かれている)
    → PHPで絞り込み (職場の自作フレームワークの関数。これのせいでソートが崩れる)
    → PHPでソートし直す

    こんな(無駄とバグの潜む)フローが主流になっている。

    今回、これで困ったのが、

    OracleとPHPのソートでは、nullの位置が違う

    Oracle SQLで昇順にした場合、ソート順の一番最後に NULL 値が来るが、
    PHPの昇順では最初になってしまう。

    NULL 値の取り扱い方が違うので仕方ない…

    OracleでのNULLの扱い

    NULL≒無限大

    NULL のソート ORDER BY NULLS ~
    Oracle 8.1.6 以上であれば、NULL の並べ替えのために NVL,MIN 関数を使う必要がなくなっている。
    NULL はデフォルトの昇順ソートでは最後、降順ソートでは最初に並び替えられる。(≒無限大)

    NULL≒ブラックホール

    オラクルにおける NULL とは、C言語などの NULL STRING '\0' CHAR(0) とは別の存在である。
    また、数学の空集合(a set null) と比較すると宇宙の特異点のようなブラックホールとも思える強力なパワーで特別な振る舞いをする。
    それを知らないでおくと、ブラックホールに飲み込まれて大切なデータを失う時限式の不具合を抱えることになりかねない。

    PHPでのNULLの扱い

    ややこしいのでこちら参照
    PHP: PHP 型の比較表 - Manual

    逆にOracleの昇順でNULLを最初にしたいときは

    ORDER BY に並び替えのオプションを指定ができる。

    ORDER BY ORDER_NO ASC NULLS FIRST
    

    NULLS FIRST
     NULL 値を順序の最初にするソートを行なう
    NULLS LAST
     NULL 値を順序の最後にするソートを行なう

    ちなみに…MySQLはPHPと同じソート順

    MySQLもPHPと同じく昇順ではNULLが最初になるが、
    下記のように書くと最後にすることができるらしい。

    SELECT * FROM table ORDER BY column IS NULL ASC, column ASC;
    

    本題

    多次元配列(DBから取得したレコードデータ)なので、array_multisortを使ってソートを行っている。
    でも、改修範囲は最低限にしたい( = SQLを触らずに済ませたい)。

    そこで [0, 1, ~, 99999, null ] という配列を作成して回避を図る。

    $ary = range(0,99999);
    $ary[] = "null";
    echo $ary[100000];
    -> null
    

    これをソート部分に組み込む。

    $ary1 = range(0,99999);
    $ary1[] = "null";
    $ary2 = array_column($dbData, 'NAME');
    array_multisort($ary1, SORT_ASC, $ary2, SORT_ASC, $dbData);
    

    でとりあえず事なきを得た。
    こんな方法でいいのかはよく分からない。

    そもそも動的SQL使えたら、こんな手間ないんだけど。

    参考サイト