« VirtualBox TestBuild 7.0.97r163425 (2024-06-05T13:13:46Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 | トップページ | VirtualBox TestBuild 7.0.97r163606 (2024-06-21T11:55:16Z) for macOS/ARM64における現時点でのOracle Database 21cの起動、停止時間の記録 »

2024年6月22日 (土) / Author : Hiroshi Sekiguchi.

帰ってきた! 標準はあるにはあるが癖の多い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〜)

| |

コメント

コメントを書く