0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@mori-dev@github

【第4回】`IS DISTINCT FROM` で NULL 安全に比較する

0
Posted at

目次

この記事で扱うこと

この記事では、次を扱います。

  • SQL の NULL 比較が直感とずれる理由
  • != が差分判定で失敗する例
  • IS DISTINCT FROM の意味
  • IS NOT DISTINCT FROM の使い方
  • JSONB の差分判定での実用例
  • COALESCE との使い分け
  • ORM で使うときの注意

なぜ != では足りないのか

例として、編集内容を保存する correction_overrides テーブルを考えます。

attr_overrides:
  現在の編集内容

last_published_attrs:
  最後に公開した内容

「現在の編集内容と最後に公開した内容が違う行」を取得したいとします。

SELECT *
FROM correction_overrides
WHERE attr_overrides != last_published_attrs;

このクエリは、両方に値が入っているときは期待どおりに動きます。

SELECT '{"alt":"a"}'::jsonb != '{"alt":"b"}'::jsonb;
-- true

SELECT '{"alt":"a"}'::jsonb != '{"alt":"a"}'::jsonb;
-- false

ここでの論点は、jsonb のキー順ではありません。jsonb 同士の比較は構造として行われます。この記事で扱うのは、NULL が混ざったときの SQL の三値論理です。

しかし、NULL が絡むと結果が変わります。

SELECT '{"alt":"a"}'::jsonb != NULL;
-- NULL

SELECT NULL != '{"alt":"a"}'::jsonb;
-- NULL

SELECT NULL != NULL;
-- NULL

true でも false でもなく、NULL です。

SQL の WHERE 句では、条件が true になった行だけが残ります。

そのため、条件の結果が NULL の行は除外されます。

なお、<> を使っても NULL に対する挙動は同じです。NULL を含む差分判定には、!= でも <> でもなく IS DISTINCT FROM を使います。

差分判定で何が起きるか

次の表で見ると分かりやすいです。

attr_overrides last_published_attrs != の結果 期待
{"alt":"a"} {"alt":"b"} true 変更あり
{"alt":"a"} {"alt":"a"} false 変更なし
{"alt":"a"} NULL NULL 本当は変更あり
NULL {"alt":"a"} NULL 本当は変更あり
NULL NULL NULL 変更なし

問題は、片方だけ NULL のケースです。

たとえば、last_published_attrs が NULL なら、「まだ公開していない」という意味かもしれません。

その状態で attr_overrides に値があるなら、公開待ちの変更として扱いたいはずです。

しかし、!= では結果が NULL になり、WHERE 句の結果から除外されます。

つまり、公開待ちの行を取り逃がします。

IS DISTINCT FROM を使う

このようなときに使うのが IS DISTINCT FROM です。

SELECT *
FROM correction_overrides
WHERE attr_overrides IS DISTINCT FROM last_published_attrs;

IS DISTINCT FROM は、NULL を通常の値のように扱って比較します。

SELECT NULL IS DISTINCT FROM NULL;
-- false

SELECT NULL IS DISTINCT FROM 'x';
-- true

SELECT 'x' IS DISTINCT FROM NULL;
-- true

SELECT '{"a":1}'::jsonb IS DISTINCT FROM '{"a":1}'::jsonb;
-- false

SELECT '{"a":1}'::jsonb IS DISTINCT FROM NULL;
-- true

先ほどの表は、期待どおりになります。

attr_overrides last_published_attrs IS DISTINCT FROM の結果 期待
{"alt":"a"} {"alt":"b"} true 変更あり
{"alt":"a"} {"alt":"a"} false 変更なし
{"alt":"a"} NULL true 変更あり
NULL {"alt":"a"} true 変更あり
NULL NULL false 変更なし

これで、「片方だけ NULL なら違う」「両方 NULL なら同じ」という直感的な比較になります。

IS NOT DISTINCT FROM もある

逆に、「同じかどうか」を NULL 安全に判定したい場合は、IS NOT DISTINCT FROM を使います。

SELECT NULL IS NOT DISTINCT FROM NULL;
-- true

SELECT NULL IS NOT DISTINCT FROM 'x';
-- false

たとえば、「公開済みの値と現在の値が同じ行」を取りたいなら、次のように書けます。

SELECT *
FROM correction_overrides
WHERE attr_overrides IS NOT DISTINCT FROM last_published_attrs;

通常の = では、両方 NULL の行が取れません。

SELECT NULL = NULL;
-- NULL

IS NOT DISTINCT FROM なら、両方 NULL を「同じ」として扱えます。

JSONB の差分件数を集計する

実務でよく使うのは、ページごとの未公開件数を数えるようなクエリです。

SELECT
  page_url,
  COUNT(*) AS total,
  COUNT(*) FILTER (
    WHERE attr_overrides IS DISTINCT FROM last_published_attrs
  ) AS changed_count
FROM correction_overrides
WHERE tenant_id = $1
  AND domain_id = $2
GROUP BY page_url;

FILTER を使うと、条件を満たす行だけを集計できます。

この例では、ページごとに次の情報が返ります。

total:
  対象行の総数

changed_count:
  公開済みの値と現在の値が違う行の数

この例では、公開済みの値と現在の値が違う行を「未公開の変更」として扱っています。

UI では、次のような表示に使えます。

このページには 12 件の補正があり、そのうち 3 件が公開済みの内容から変更されています。

IS DISTINCT FROM を使うことで、NULL を含む差分判定も期待どおりに扱えます。

NULL と空オブジェクトを同じ扱いにしたい場合

要件によっては、NULL と {} を同じ意味として扱いたいことがあります。

たとえば、次の 2 つをどちらも「補正なし」と見なしたい場合です。

NULL
{}::jsonb

この場合は、COALESCE で正規化してから比較できます。

SELECT *
FROM correction_overrides
WHERE COALESCE(attr_overrides, '{}'::jsonb)
      IS DISTINCT FROM
      COALESCE(last_published_attrs, '{}'::jsonb);

これにより、次のように扱えます。

NULL と NULL:
  同じ

NULL と {}:
  同じ

{} と {}:
  同じ

{"alt":"a"} と NULL:
  違う

ただし、これは要件次第です。

NULL と {} を区別したい場合は、COALESCE しないほうがよいです。

NULL:
  未設定

{}:
  明示的に空の設定

この 2 つに意味の違いがあるなら、その違いを残します。

NULL を扱うときによく使う書き方

IS DISTINCT FROM と一緒に、次の関数や演算子もよく使います。

書き方 用途
IS DISTINCT FROM NULL 安全な不一致判定
IS NOT DISTINCT FROM NULL 安全な一致判定
IS NULL NULL かどうかを見る
IS NOT NULL NULL でないかを見る
COALESCE(a, b) a が NULL なら b を使う
NULLIF(a, b) a = b なら NULL を返す

特に、値同士を比較したいときは IS DISTINCT FROM、NULL そのものを条件にしたいときは IS NULL、既定値に寄せたいときは COALESCE、と分けると整理しやすくなります。

ORM で使うときの注意

ORM やクエリビルダーによっては、IS DISTINCT FROM を標準 API で扱いにくいことがあります。

その場合は、生 SQL のフラグメントを使う判断も必要です。

たとえば Drizzle なら、次のような形です。

import { sql } from 'drizzle-orm';

const dirtyRows = await db
  .select()
  .from(correctionOverrides)
  .where(
    sql`${correctionOverrides.attrOverrides}
        IS DISTINCT FROM
        ${correctionOverrides.lastPublishedAttrs}`,
  );

ORM の not equal API だけで書こうとして != 相当になってしまうと、NULL を含む行を取り逃がします。

JSONB の差分判定では、必要に応じて生 SQL を使うほうが安全です。

よくある判断

実務では、次のように考えると分かりやすいです。

値が違うかを NULL 安全に見たい:
  IS DISTINCT FROM

値が同じかを NULL 安全に見たい:
  IS NOT DISTINCT FROM

NULL そのものを検出したい:
  IS NULL / IS NOT NULL

NULL を既定値に寄せてから比較したい:
  COALESCE

!= や = は、NULL が入らないことが分かっている場合には使えます。

ただし、NULL が入りうる列の差分判定では、まず IS DISTINCT FROM を検討するのが安全です。

まとめ

SQL の NULL は、通常の値とは違います。

NULL != 'x' は true ではなく NULL です。

そのため、次のような差分判定は期待どおりに動かないことがあります。

WHERE attr_overrides != last_published_attrs

NULL を含む差分判定では、IS DISTINCT FROM を使います。

WHERE attr_overrides IS DISTINCT FROM last_published_attrs

これにより、次のように直感的に扱えます。

両方同じ値:
  false

片方だけ NULL:
  true

両方 NULL:
  false

逆に、一致を見たい場合は IS NOT DISTINCT FROM を使います。

JSONB の差分判定、公開待ちの変更、編集前後の差分、NULL を含む任意の列比較では、IS DISTINCT FROM を知っていると SQL を安全に書きやすくなります。

次回は、シリーズ最終回として、監査ログの HMAC チェーンを扱います。

第 3 回の SELECT FOR UPDATE で安全に払い出した seq を使って、過去の監査ログが改ざんされていないことを検出する仕組みを作ります。

参考資料

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?