標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ） Tweet
標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の10日目です。
なんとか10日目の窓をあけましたw
今回は、有名な非互換なので、まさか、これに引っかかる方はいないと思いますが、定番のお約束みたいな非互換ネタなので書かないといけないですよね！！
では、いつも通り Oracle から。
NULLと空文字(マニュアルでは長さゼロの文字列値と記載されています。有名な非互換)
https://docs.oracle.com/cd/E82638_01/sqlrf/Nulls.html#GUID-B0BA4751-9D88-426A-84AD-BCDBD5584071
CONCAT(char1, char2)
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CONCAT.html#GUID-D8723EA5-C93A-45C3-83FB-1F3D2A4CEAF2
空文字をNULLとして扱う点と、||による文字列連結(CONCATと同意）の挙動がOracle以外の世界と違うんですよね。これも有名です。
ORACLE> SELECT 'foo' || 'bar' FROM dual;
'FOO'|
------
foobar
ORACLE> SELECT 'foo' || '' FROM dual;
'FO
---
foo
ORACLE> SELECT 'foo' || NULL FROM dual;
'FO
---
foo
ORACLE> SELECT CONCAT('foo','bar') FROM dual;
CONCAT
------
foobar
ORACLE> SELECT CONCAT('foo','') FROM dual;
CON
---
foo
ORACLE> SELECT CONCAT('foo',null) FROM dual;
CON
---
foo
PostgreSQL
PostgreSQLでは、Oracleと異なり 文字列連結子でNULLを結合すると結果は、NULLになります。ここがOracleと異なる挙動ですね。
これを回避するにはconcat() or concat_ws()のいずれかを利用できます。
string || string
concat(str "any" [, str "any" [, ...] ])
concat_ws(sep text, str "any" [, str "any" [, ...] ])
https://www.postgresql.jp/document/12/html/functions-string.html
そして、これまた、悩ましいのは、 ||でNULLを結合した場合と、CONCAT_WS()でNULLを結合した挙動が異なるんですね。
||でNULLの場合はNULLですが、CONCATでNULLを結合するとOracleと同じ挙動になるんですよ。
postgres=> SELECT 'foo' || 'bar';
?column?
----------
foobar
(1 row)
postgres=> SELECT 'foo' || '';
?column?
----------
foo
(1 row)
postgres=> SELECT 'foo' || null;
?column?
----------
[NULL]
(1 row)
postgres=> SELECT CONCAT('foo', '');
concat
--------
foo
(1 row)
postgres=> SELECT CONCAT('foo', NULL);
concat
--------
foo
(1 row)
postgres=>
postgres=> SELECT CONCAT_WS('','foo','bar' );
concat_ws
-----------
foobar
(1 row)
postgres=> SELECT CONCAT_WS('','foo','' );
concat_ws
-----------
foo
(1 row)
postgres=> SELECT CONCAT_WS('','foo',NULL );
concat_ws
-----------
foo
(1 row)
postgres=>
MySQL
MySQLでは、|| は文字列連結子ではなく、なんと、論理演算子！！！！！！
え〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜~っ。
12.4.3 Logical Operators - OR, ||
https://dev.mysql.com/doc/refman/8.0/en/logical-operators.html
mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
| 0 |
+----------------+
1 row in set, 3 warnings (0.03 sec)
mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
| 0 |
+-------------+
1 row in set, 2 warnings (0.04 sec)
mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
| NULL |
+---------------+
1 row in set, 2 warnings (0.06 sec)
実は逃げ道があるようで、sql_mode='PIPES_AS_CONCAT' にすると文字列連結子に早変わり！w
ですが、挙動はPostgreSQL同様に、NULLと連結したり演算子すると結果はNULLになると言う一般的な動きをします。
Oracleは演算に関してはNULLが絡むとNULLになりますが、文字列連結だけはNULLが空文字のような扱いを受けると言う挙動を示します。理解しちゃえばあれですが、エラーにならないだけに混乱するタイプの非互換ですね。
PostgreSQLとは異なり、CONCAT_WS()だけがOracleと同じ挙動を示します。
CONCAT(str1,str2,...)
CONCAT_WS(separator,str1,str2,...)
https://dev.mysql.com/doc/refman/8.0/en/string-functions.html
mysql> set sql_mode='PIPES_AS_CONCAT';
Query OK, 0 rows affected (0.03 sec)
mysql> SELECT 'foo' || 'bar' FROM dual;
+----------------+
| 'foo' || 'bar' |
+----------------+
| foobar |
+----------------+
1 row in set (0.02 sec)
mysql> SELECT 'foo' || '' FROM dual;
+-------------+
| 'foo' || '' |
+-------------+
| foo |
+-------------+
1 row in set (0.04 sec)
mysql> SELECT 'foo' || NULL FROM dual;
+---------------+
| 'foo' || null |
+---------------+
| NULL |
+---------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT('foo','bar');
+---------------------+
| concat('foo','bar') |
+---------------------+
| foobar |
+---------------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT('foo','');
+------------------+
| concat('foo','') |
+------------------+
| foo |
+------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT('foo',NULL);
+--------------------+
| concat('foo',null) |
+--------------------+
| NULL |
+--------------------+
1 row in set (0.03 sec)
mysql> SELECT CONCAT_WS('foo',NULL);
+-----------------------+
| concat_ws('foo',null) |
+-----------------------+
| |
+-----------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo |
+--------------------------+
1 row in set (0.01 sec)
mysql> SELECT CONCAT_WS('','foo','bar');
+---------------------------+
| concat_ws('','foo','bar') |
+---------------------------+
| foobar |
+---------------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo','');
+------------------------+
| concat_ws('','foo','') |
+------------------------+
| foo |
+------------------------+
1 row in set (0.02 sec)
mysql> SELECT CONCAT_WS('','foo',NULL);
+--------------------------+
| concat_ws('','foo',null) |
+--------------------------+
| foo |
+--------------------------+
1 row in set (0.01 sec)
mysql>
ややこしやー、ややこしやー。
みなさん、ついてこれてますか？ この手の内容が25日まで続きますからね。（私が続けられるか次第だが。。。。頑張りマッス！
実行計画は、SQL文のレントゲン写真だ！ Oracle Database編 (全部俺） Advent Calendar 2019
・標準はあるにはあるが癖の多いSQL 全部俺 #1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺 #2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺 #3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺 #4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺 #5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺 #6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺 #7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺 #8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺 #9 部分文字列の扱いでも癖が出る＞＜
| 固定リンク | 0
コメント