メジャーなRDBMSでREPLACE関数が空文字を返すかnullを返すか調べてみた

青森に帰ってきてから仕事ではOracleを使うようになった。 今までは予算の都合から使える事はなかったのだが......。

そんな中、2017年にもなってOracleを使う人ならまず引っかからないであろう奇妙な挙動に引っかかる。

SELECT 'TEST'
FROM dual 
WHERE
  REPLACE (REPLACE (' テスト　', ' ', ''), '　', '') = 'テスト' 
  AND REPLACE ('//', '//', '') = ''

上記SQL、実際に実行していただければわかると思うが 結果は何も出ないのだ。

原因は5行目、ここを空文字比較しているのが問題。 Oralceでは上記のようにREPLACEを行って空文字になるとnullと解釈する。

なので上記のSQLは

SELECT 'TEST'
FROM dual 
WHERE
  REPLACE (REPLACE (' テスト　', ' ', ''), '　', '') = 'テスト' 
  AND REPLACE ('//', '//', '') IS NULL

と IS NULL を使えばちゃんとtrueになって値が取得できるというわけだ。

......いやまて、なぜ0byte文字列とnullが同値扱いになる？ ※実際には空文字比較でfalseなので空文字はnullへの不可逆変換なわけだが。

これに関しては

と教えていただいてそこにはさらに

By the time that the SQL standard came around and agreed that NULL and the empty string were distinct entities, there were already Oracle users that had code that assumed the two were equivalent. So Oracle was basically left with the options of breaking existing code, violating the SQL standard, or introducing some sort of initialization parameter that would change the functionality of potentially large number of queries. Violating the SQL standard (IMHO) was the least disruptive of these three options.

とあるので標準化が進む中で、既にもう既存コードが置き換えするのが難しいくらいになってきているのであえて規則に違反する道を選んだのではないかと言われている。 確かにこれはうなずける部分もある。上記のSQLの挙動が変われば影響あるシステムはいくつもあるはずだ。

しかしそれはあくまでnullとempty Stringの問題であってREPLACE関数まで同様にするのは正直どうかと思う。 REPLACEで置き換えてempty Stringになった時点でもうnullと比較しないといけないということじゃないか。

SELECT 'TEST'
FROM dual 
WHERE
  REPLACE (REPLACE (' テスト　', ' ', ''), '　', '') = 'テスト' 
  AND REPLACE ('//', '//', ' ') = ' '

あるいはまあこう書くのも一つの手なのかもしれないが、なんだかこれもバッドノウハウだと思う。 空白で置き換えるというのがややこしい。

ついでなのでメジャーなRDBMSでREPLACE関数で空文字になった場合にどのような挙動をするか調べてみた。

• SQL Server
• Oracle
• MySQL
• PostgreSQL
• DB2
• SQLite3
• SQL Anywhere

一番最後のは趣味だが......。 結果は案の定だったけれど一応検証用クエリを載せる。 テーブルを作成する必要がないからDBをインストールした状態であればすぐに追従はできるはずだ。

SQL Server

SELECT NULLIF(REPLACE ('//', '//', ''), '') ⇒ null
SELECT NULLIF(REPLACE ('//', '//', ''), null) ⇒ 空文字

※REPLACE結果は空文字扱い

Oracle

上記のとおり空文字はnull扱い これを流せば「(･ω･`U)null」となるはずだ。

SELECT
  '//'
  , NVL2(REPLACE ('//', '//', ''), '(･ω･U)null以外', '(･ω･`U)null') 
FROM dual 
WHERE
  REPLACE (REPLACE (' テスト　', ' ', ''), '　', '') = 'テスト' 
  AND REPLACE ('//', '//', '') is null

MySQL

mysql> SELECT ISNULL(REPLACE ('//', '//', '')) as a;
+---+
| a |
+---+
| 0 |
+---+

※REPLACE結果は空文字扱い

PostgreSQL

SELECT NULLIF(REPLACE ('//', '//', ''), '') ⇒ null

※REPLACE結果は空文字扱い

DB2

SELECT COALESCE(REPLACE ('//', '//', ''),'0') FROM SYSIBM.DUAL ⇒ 空文字

※REPLACE結果は空文字扱い

SQLite3

SELECT ifnull(REPLACE ('//', '//', ''),'0')  ⇒ 空文字

※REPLACE結果は空文字扱い

SQL Anywhere

SELECT ifnull(REPLACE ('//', '//', ''),'0', '1') ⇒ 1

※REPLACE結果は空文字扱い

まとめるまでもないがまとめると

RDBMS	null == empty String
SQL Server	false
Oracle	true
MySQL	false
PostgreSQL	false
DB2	false
SQLite3	false
SQL Anywhere	false

とまあ、Oracleだけがぽっかりと奇妙な挙動をしてくれている。 他のDBも概ねこうなのだろうな......。

ちなみに

https://docs.oracle.com/cd/E16338_01/server.112/b56299/sql_elements005.htm

Oracle Databaseは現在、長さが0(ゼロ)の文字値をNULLとして処理します。ただし、この処理はOracleの今後のバージョンでも継続されるとはかぎらないため、空の文字列をNULLとして処理しないことをお薦めします。

とあるから上記非推奨なんじゃないかとは思う。 だったら空文字でもtrueにするようにすればよかったと思うのだが......

2017年になっても上記の挙動は変わってないのがよくわかる事案だった。