標準はあるにはあるが癖の多いSQL 全部俺 #19 帰ってきた、部分文字列の扱いでも癖w Tweet

標準はあるにはあるが癖の多いSQL 全部俺w Advent Calendar 2020の19日目です。

標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
で、すっかり忘れてた。非互換ではその手のが多いw

部分文字列の扱いの癖、盲点というかなんというか、小数の扱いの違いを忘れてましたw

Oracle

Positionが大きい場合の挙動では　Oracle以外は空文字を返します。これも非互換は非互換ですが。

注目してもらいたいのは、整数じゃないとき。Oracleは、小数点以下切り捨てで動きます。

ORACLE> set tab off
ORACLE> set null [NULL]
ORACLE> col str for a30

ORACLE> SELECT SUBSTR('1234567890', 10, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890', 11, 1) AS str FROM dual;

STR
------------------------------
[NULL]

ORACLE> SELECT SUBSTR('1234567890',10.4, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890',10.5, 1) AS str FROM dual;

STR
------------------------------
0

ORACLE> SELECT SUBSTR('1234567890',10.9, 1) AS str FROM dual;

STR
------------------------------
0

MySQL (8.0)

Positionが範囲外であれば空文字を返すのは冒頭で説明した通りですが、Oracleと明らかに違うのは、小数点以下は四捨五入
整数以外も受け付けてくれますが、デフォルトの挙動で、切り捨てか、四捨五入という違いにより取り出される結果に差異が出ます。ハマりますよね。これw

mysql> SELECT SUBSTRING('1234567890', 10, 1) AS str;
+-----+
| str |
+-----+
| 0   |
+-----+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING('1234567890', 11, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
+-----+
| str |
+-----+
| 0   |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.06 sec)

mysql> SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
+-----+
| str |
+-----+
|     |
+-----+
1 row in set (0.02 sec)

mysql> SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
+-----+
| str |
+-----+
| 1   |
+-----+
1 row in set (0.01 sec)

mysql> SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
+-----+
| str |
+-----+
| 2   |
+-----+
1 row in set (0.02 sec)

mysql> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
+-----+
| str |
+-----+
| ''  |
+-----+
1 row in set (0.02 sec)

PostgreSQL

こちらPostgreSQLは単純、Positionに指定できるのは整数のみです。ある意味わかりやすいですw　文字列の位置が　1.9とかなかなかですからね。

postgres=> SELECT SUBSTRING('1234567890', 10, 1) AS str;
 str
-----
 0
(1 row)

postgres=> SELECT SUBSTRING('1234567890', 11, 1) AS str;
 str
-----

(1 row)

postgres=> SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 11, 1)),'''') AS str;
 str
-----
 ''
(1 row)

postgres=> SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
ERROR:  function pg_catalog.substring(unknown, numeric, integer) does not exist
LINE 1: SELECT SUBSTRING('1234567890', 10.4, 1) AS str;

おまけ

Redshift

PostgreSQLの血筋のはずですが、少数はエラーにもならず、そんなのねーよ。的な空文字が帰ってきます。なかなか男前です。素直に考えれば、1.9のところって文字の途中な訳で。。。

これもなかなか気づかいないです。エラーにはならないタイプなので、結果をみて？？？？　としばらく悩むタイプですね。非互換としては事前に判断が難しいタイプ。リテラルで少数指定されていれば気づきやすいですが、バインド変数だったりすると気づくのは、かなり辛いです。

redshift=# SELECT SUBSTRING('1234567890', 10, 1) AS str;
 str
-----
 0
(1 row)

redshift=# SELECT SUBSTRING('1234567890', 11, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.4, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.5, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 10.9, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 1.4, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT SUBSTRING('1234567890', 1.5, 1) AS str;
 str
-----

(1 row)

redshift=# SELECT CONCAT(CONCAT('''',SUBSTRING('1234567890', 10.9, 1)),'''') AS str;
 str
-----
 ''
(1 row)

hr>
さあ、カウントダウンだw　(^^;;;;;

---

---

・標準はあるにはあるが癖の多いSQL 全部俺　#1 Pagination
・標準はあるにはあるが癖の多いSQL 全部俺　#2 関数名は同じでも引数が逆の罠！
・標準はあるにはあるが癖の多いSQL 全部俺　#3 データ型確認したい時あるんです
・標準はあるにはあるが癖の多いSQL 全部俺　#4 リテラル値での除算の内部精度も違うのよ!
・標準はあるにはあるが癖の多いSQL 全部俺　#5 和暦変換機能ある方が少数派
・標準はあるにはあるが癖の多いSQL 全部俺　#6 時間厳守！
・標準はあるにはあるが癖の多いSQL 全部俺　#7 期間リテラル！
・標準はあるにはあるが癖の多いSQL 全部俺　#8 翌月末日って何日？
・標準はあるにはあるが癖の多いSQL 全部俺　#9 部分文字列の扱いでも癖が出る＞＜
・標準はあるにはあるが癖の多いSQL 全部俺　#10 文字列連結の罠（有名なやつ）
・標準はあるにはあるが癖の多いSQL 全部俺　#11 デュエル、じゃなくて、デュアル
・標準はあるにはあるが癖の多いSQL 全部俺　#12 文字[列]探すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#13 あると便利ですが意外となかったり
・標準はあるにはあるが癖の多いSQL 全部俺　#14 連番の集合を返すにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#15 SQL command line client
・標準はあるにはあるが癖の多いSQL 全部俺　#16 SQLのレントゲンを撮る方法
・標準はあるにはあるが癖の多いSQL 全部俺　#17 その空白は許されないのか？
・標準はあるにはあるが癖の多いSQL 全部俺　#18 (+)の外部結合は方言