帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）

Oracle ACE Program的に新年度に切り替わり。今期もOracle ACE Proに認定されました。:)

前置きはそれぐらいにして、今日の本題。

column expressionのaliasや、 table, view, subqueryなどのaliasを指定する際に利用することがある引用符、通常は (")ダブルクォートで囲むわけですが、そんな引用符にも癖があるというお話。
SQL-1992のドラフトではありますが以下のドキュメントを delimited identifier で検索すると見つけることができます。
( (Second Informal Review Draft) ISO/IEC 9075:1992, Database Language SQL- July 30, 1992 )

ついでに、世間ではいろいろ言われて肩身の狭い？想いをしているかもしれない NULL. そのNULLのソートが必要になってしまった場合にも、ソートの構文に癖がある！！

ほんと、みんな、癖多すぎますよね！（w
いい感じに差し支えない単語にすると、個性　があるというか... これだからSQLは楽しいって話もありますけども。人によるかなw

Oracle Database 23ai / PostgreSQL 13.14 / MySQL 8.0.36 のそれぞれで、どうなるか見てみましょう。

それぞれのデータベースに以下のような emp表を事前に作成しておきます。Oraclerにはお馴染みの表です:)

SCOTT@localhost:1521/freepdb1> select * from emp order by empno;

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 80-12-17        800                    20
      7499 ALLEN      SALESMAN        7698 81-02-20       1600        300         30
      7521 WARD       SALESMAN        7698 81-02-22       1250        500         30
      7566 JONES      MANAGER         7839 81-04-02       2975                    20
      7654 MARTIN     SALESMAN        7698 81-09-28       1250       1400         30
      7698 BLAKE      MANAGER         7839 81-05-01       2850                    30
      7782 CLARK      MANAGER         7839 81-06-09       2450                    10
      7788 SCOTT      ANALYST         7566 87-04-19       3000                    20
      7839 KING       PRESIDENT            81-11-17       5000                    10
      7844 TURNER     SALESMAN        7698 81-09-08       1500          0         30
      7876 ADAMS      CLERK           7788 87-05-23       1100                    20
      7900 JAMES      CLERK           7698 81-12-03        950                    30
      7902 FORD       ANALYST         7566 81-12-03       3000                    20
      7934 MILLER     CLERK           7782 82-01-23       1300                    10

14 rows selected.

まず、Oracle Database 23ai
なぜ最新にしたかと言うと、GROUP BYで　alias が利用可能になった最初のリリースだからなのですw　（例で利用するクエリで利用する必要があるので）
（ GROUP BY列の別名または位置の指定が可能に！ / 23ai〜 / SQL / FAQ ）

SCOTT@localhost:1521/freepdb1> select banner_full from v$version;

BANNER_FULL
-------------------------------------------------------------------------------
Oracle Database 23ai Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.4.0.24.05

この例では、別名に空白などを含めているため引用符が必要になります。
Oracle Databaseの場合は昔から (") ダブルクォートですね。
一般的には引用符を必要としない記述にすることが多いのではないでしょうか。プログラムで扱うには面倒ですからね。（印字するだけの目的なら別ですが）
とはいえ、引用符の利用が必須のケースや、コーディング規約次第というところはあります。
SQL言語リファレンス/ データベース・オブジェクト名および修飾子/ データベース・オブジェクトのネーミング規則

NULLの位置が最初に来るようにソートするには、NULLS FIRSTですよね。みなさんもご存知のはず。

Oraclerのみなさんには GROUP BY でいきなりaliasを使う構文で目新しいですよね。すっきり書けるようになって嬉しい:)

SCOTT@localhost:1521/freepdb1> set null [null]
SCOTT@localhost:1521/freepdb1> @quoted_identification.sql
  1  SELECT
  2    mgr AS "Boss's emp no."
  3  , COUNT(empno) AS head_counts
  4  FROM
  5    emp
  6  GROUP BY
  7    "Boss's emp no."
  8  ORDER BY
  9*   "Boss's emp no." NULLS FIRST

Boss's emp no. HEAD_COUNTS
-------------- -----------
[null]                   1
          7566           2
          7698           5
          7782           1
          7788           1
          7839           3
          7902           1

7 rows selected.

次は、PostgreSQL　
Oracle Database同様、引用符が必要な別名は、ダブルクォートを利用します。( PostgreSQL 16.0 / 4.1.1. 識別子とキーワード )

NULLS FIRSTでNULLをいい感じにソートする方法も同じですね。

perftestdb=> select version();
                                                 version                                                  
----------------------------------------------------------------------------------------------------------
 PostgreSQL 13.14 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-20), 64-bit
(1 行)

perftestdb=> 
perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \! cat quoted_identification.sql
SELECT
  mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
  emp
GROUP BY
  "Boss's emp no."
ORDER BY 
  "Boss's emp no." NULLS FIRST;
;
perftestdb=> \i quoted_identification.sql
 Boss's emp no. | head_counts 
----------------+-------------
         [null] |           1
           7566 |           2
           7698 |           5
           7782 |           1
           7788 |           1
           7839 |           3
           7902 |           1
(7 行)

さて、最後は、MySQLです。

気付いたかと思いますが、本日の癖の主役ですw

MySQLのデフォルトの引用符は、なんと、(`) バッククォートです。手癖で、ダブルクォートをタイプして、え！と一瞬固まる、Oraclerが。。＞　俺だよw
( MySQL 8.0 リファレンスマニュアル / 言語構造 / スキーマオブジェクト名 )

また、NULLのソートも可能ですが、見たこともない構文でソートします。私まだよくわかってないですが、これで良いらしい。この癖に慣れる必要もありそう。。

+-----------+
| version() |
+-----------+
| 8.0.36    |
+-----------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
  mgr AS `Boss's emp no.`
, COUNT(empno) AS head_counts
FROM
  emp
GROUP BY
  `Boss's emp no.`
ORDER BY 
  `Boss's emp no.` IS NULL DESC
 ,`Boss's emp no.` ASC
;
mysql> 
mysql> \. quoted_identification.sql
+----------------+-------------+
| Boss's emp no. | head_counts |
+----------------+-------------+
|           NULL |           1 |
|           7566 |           2 |
|           7698 |           5 |
|           7782 |           1 |
|           7788 |           1 |
|           7839 |           3 |
|           7902 |           1 |
+----------------+-------------+
7 rows in set (0.01 sec)

ところで、MySQLでも、(")ダブルクォートを引用符にすることができます。
( MySQL 8.0 リファレンスマニュアル / 5.1.11 サーバー SQL モード / ANSI_QUOTES 参照のこと )

sql_modeに ANSI_QUOTESを設定することで使えるようになります。。。。あ〜っ、スッキリ。NULLのソート構文以外はw

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set session sql_mode = concat(@@sql_mode,',ANSI_QUOTES');
Query OK, 0 rows affected (0.01 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
  mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
  emp
GROUP BY
  "Boss's emp no."
ORDER BY 
  "Boss's emp no." IS NULL DESC
 ,"Boss's emp no." ASC
;
mysql> \. quoted_identification.sql
+----------------+-------------+
| Boss's emp no. | head_counts |
+----------------+-------------+
|           NULL |           1 |
|           7566 |           2 |
|           7698 |           5 |
|           7782 |           1 |
|           7788 |           1 |
|           7839 |           3 |
|           7902 |           1 |
+----------------+-------------+
7 rows in set (0.00 sec)

mysql> 

ANSI_QUOTESを無効にすると、GROUP BY で指定した alias 無効となり、デフォルトで有効化されているONLY_FULL_GROUP_BYのため、ERROR 1055　となります。なんとなく理解した！

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                        |
+-----------------------------------------------------------------------------------------------------------------------------------+
| ANSI_QUOTES,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> set session sql_mode = replace(@@sql_mode,'ANSI_QUOTES,','');
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> \! cat quoted_identification.sql
SELECT
  mgr AS "Boss's emp no."
, COUNT(empno) AS head_counts
FROM
  emp
GROUP BY
  "Boss's emp no."
ORDER BY 
  "Boss's emp no." IS NULL DESC
 ,"Boss's emp no." ASC
;
mysql> \. quoted_identification.sql
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'perftestdb.emp.mgr' which is not functionally dependent on columns in GROUP BY clause; this is incompatible
 with sql_mode=only_full_group_by

そういえば、
英語だと、大抵は、quoted identifier と書かれていますが、日本語表記だと 引用符付き識別子とか、引用識別子、 各社のドキュメントで微妙に違いがあったりして難しいなぁ。と思ったり。
quoted identifier　ってカタカタにしたら長くてタイプするの面倒、結局、Oraclerなので、引用識別子/非引用識別子　で通りしゃったりしますけど。

ではでは。
次回へつづく。

---

---

関連エントリー
・標準はあるにはあるが癖の多い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 (+)の外部結合は方言
・標準はあるにはあるが癖の多いSQL 全部俺　#19 帰ってきた、部分文字列の扱いでも癖w
・標準はあるにはあるが癖の多いSQL 全部俺　#20 結果セットを単一列に連結するにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#21 演算結果にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#22 集合演算にも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#23 複数行INSERTにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#24 乱数作るにも癖がある
・標準はあるにはあるが癖の多いSQL 全部俺　#25 SQL de Fractalsにも癖がある:)
・標準はあるにはあるが癖の多いSQL 全部俺　おまけ SQL de 湯婆婆やるにも癖がでるw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #1 SQL de ROT13 やるにも癖が出るw
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #2 Actual Plan取得中のキャンセルでも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #3 オプティマイザの結合順評価テーブル数上限にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #4 Optimizer Traceの取得でも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #5 - Optimizer Hint でも癖が多い
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #6 - Hash Joinの結合ツリーにも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #7 - Hash Joinの実行計画にも癖がでる
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #8 - Hash Joinさせるにも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）
・帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編）の　おまけ　- SQL*PlusのautotraceでSQL Analysis Reportが出力される！ (23ai〜)

標準はあるにはあるが癖の多いSQL 全部俺 #10 文字列連結の罠（有名なやつ）

標準はあるにはあるが癖の多い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

連結演算子
https://docs.oracle.com/cd/F19136_01/sqlrf/Concatenation-Operator.html#GUID-08C10738-706B-4290-B7CD-C279EBC90F7E

空文字を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 部分文字列の扱いでも癖が出る＞＜