帰ってきた！ 標準はあるにはあるが癖の多いSQL #12 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（後編）ー 列エイリアスの扱いにも癖がある！ Tweet
Previously on Mac De Oracle
前回は、帰ってきた！ 標準はあるにはあるが癖の多いSQL #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）でした、
引用符にも多少の方言が存在すること、NULLのソート方法にも同様に方言が存在することを確認しました。
今回はその後編です。
引用符で囲んだ識別子（列名、表名、列エイリアス、表エイリアス）を英語だと、Quoted Identifier と記載されシノニムはあまり見かけません。（SQL-1992などでは、delimited identifier と記されている程度ですかね）日本語のマニュアル等では翻訳影響だと思いますが、多少揺れていたりします。
Oraclerのみなさんだと、引用識別子 のほうが馴染み深い日本語訳だと思いますが、他のRDBMSの日本語マニュアルでは、引用符付き識別子 と記載されていたりします。とうのは前回にも書いてますが、Oracle Database/PostgreSQL/MySQLの間でも多少表現は違ったりします。
参考）
・Database Oracle / Release 19 / SQL言語リファレンス / データベース・オブジェクト名および修飾子 / データベース・オブジェクトのネーミング規則
・MySQL 8.0 リファレンスマニュアル / 言語構造 / スキーマオブジェクト名
・PostgreSQL 13.1文書 / 第4章 SQLの構文 / 4.1. 字句の構造
という前置きはこれぐらいで。本日のお題。列エイリアスの扱いの癖！
今日のお題は、列エイリアスの扱いの癖 を見てみます（気づかないと意外にハマりますよーーw）
今回も前回同様、お馴染みの、emp表を、Oracle Database 23ai/PostgreSQL 13.14/MySQL 8.0.36 それぞれに作成してあります。（以下はOracle Database）
SCOTT@localhost:1521/freepdb1> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
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.
では、前回のおさらいから、MySQLだけ癖が強い結果となりましたが、問い合わせたい結果はどれも正しいですよね！（標準はあるにはあるが、癖の多いSQLらしい。素晴らしい結果ですよねw）
Oracle Database 23ai
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 13.14
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 8.0.36
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)
今日は、上記のクエリー列エイリアスにちょっと意地の悪い変更を行なって、その挙動の違いを見てみたいと思います。（おもしろいよ、それぞれの個性が出てて）
オリジナルでは、mgr AS "Boss's emp no." としていた列エイリアスですが、まずは、 mgr AS empno と、非識別引用子にして、かつ、emp表の列名である empno と同じ名称にしてあります。。。 AS 列エイリアス にはなっているので文法的には正しいです。。。よね。ちょっと嫌な予感はしますがw
(MySQLの場合、ORDER BY句の構文が異なります。以下、Oracle Database 23ai/PostgreSQL向け)
Before
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;
After
SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno NULLS FIRST;
では、早速、MySQLから実験してみましょう！
mysql> desc emp;
+----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| empno | smallint | NO | PRI | NULL | |
| ename | varchar(10) | YES | | NULL | |
| job | varchar(10) | YES | | NULL | |
| mgr | smallint | YES | | NULL | |
| hiredate | date | YES | | NULL | |
| sal | decimal(7,2) | YES | | NULL | |
| comm | decimal(7,2) | YES | | NULL | |
| deptno | smallint | YES | | NULL | |
+----------+--------------+------+-----+---------+-------+
8 rows in set (0.05 sec)
mysql> \! cat quoted_identification2.sql
SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno IS NULL DESC
,empno ASC
;
おおおおおおおおおーーーーーーー。想定外の結果がががががー（いや、予想してましたよw）。
mysql> \. quoted_identification2.sql
+-------+-------------+
| empno | head_counts |
+-------+-------------+
| NULL | 1 |
| 7566 | 1 |
| 7566 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7902 | 1 |
+-------+-------------+
14 rows in set, 1 warning (0.02 sec)
ワーニングがでてますね。覗いてみると、どうやら、empno が曖昧だけど、実行しておいたから。。。。と。emp表のempnoと mgr列に対する列エイリアス、どちらか曖昧だけど、とりあえず、emp表のempno列の方でやっといたでーーー。ということみたいですね。まじか。。。 AS 列エイリアス とSQLで書いてるから列エイリアスとしてみてくれんの？？？（ちょっとわざとらしいセリフを入れてみましたw）
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1052 | Column 'empno' in group statement is ambiguous |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
そうか、列エイリアスと表の列名が被ってて、曖昧だと。 では、 引用符を使って、これは、列エイリアスだーーーーーーとわかるように書けば良いのでは？？
ということで試してみる。
MySQLのデフォルトの引用符(`)バッククォートで囲った結果。。。だめだ。。。。引用識別子にしても、表の列側としてハンドリングされている。。。。。
mysql> \! cat quoted_identification2.sql
SELECT
mgr AS `empno`
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
`empno`
ORDER BY
`empno` IS NULL DESC
,`empno` ASC
;
mysql> \. quoted_identification2.sql
+-------+-------------+
| empno | head_counts |
+-------+-------------+
| NULL | 1 |
| 7566 | 1 |
| 7566 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7698 | 1 |
| 7782 | 1 |
| 7788 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7839 | 1 |
| 7902 | 1 |
+-------+-------------+
14 rows in set, 1 warning (0.00 sec)
mysql> show warnings;
+---------+------+------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------+
| Warning | 1052 | Column 'empno' in group statement is ambiguous |
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)
mysql>
気を取り直して、PostgreSQL ではどうなのでしょう？
perftestdb=> \d+ emp
テーブル"public.emp"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト | ストレージ | 統計目標 | 説明
----------+-----------------------+----------+---------------+------------+------------+----------+------
empno | numeric(4,0) | | not null | | main | |
ename | character varying(10) | | | | extended | |
job | character varying(9) | | | | extended | |
mgr | numeric(4,0) | | | | main | |
hiredate | date | | | | plain | |
sal | numeric(7,2) | | | | main | |
comm | numeric(7,2) | | | | main | |
deptno | numeric(2,0) | | | | main | |
インデックス:
"pk_emp" PRIMARY KEY, btree (empno)
"ix_deptno" btree (deptno)
外部キー制約:
"fk_deptno" FOREIGN KEY (deptno) REFERENCES dept(deptno)
アクセスメソッド: heap
perftestdb=> \! cat quoted_identification.sql
SELECT
mgr AS empno
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
empno
ORDER BY
empno NULLS FIRST
;
ギョギョ！ サカナくんみたいな声がでますね！w MySQLと同じ挙動です！！！！ なーーーーーーーんーーーーーーだってーーーーーーーっ。 MySQLとは異なりワーニングもなく、結果が想定結果を違うんだーー。というところから列エイリアスが列エイリアスと認識されていないことに気づいてあげないとならないですね。。。これだと。 難易度が上がったw
MySQL、意外と親切かも。ワーニングで教えてくれるなんて。。。
perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \i quoted_identification.sql
empno | head_counts
--------+-------------
[null] | 1
7566 | 1
7566 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7782 | 1
7788 | 1
7839 | 1
7839 | 1
7839 | 1
7902 | 1
(14 行)
では、引用識別子に書き換えてみましょう。。。。。あ、まじで、PostgreSQLもMySQLと同じ挙動ですね。 列エイリアスだよーーーーーと明示しても、表の列としてのハンドリングを優先しています。。。むむむ。
perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=> \! cat quoted_identification2.sql
SELECT
mgr AS "empno"
, COUNT(empno) AS head_counts
FROM
emp
GROUP BY
"empno"
ORDER BY
"empno" NULLS FIRST
;
perftestdb=> \i quoted_identification2.sql
empno | head_counts
--------+-------------
[null] | 1
7566 | 1
7566 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7698 | 1
7782 | 1
7788 | 1
7839 | 1
7839 | 1
7839 | 1
7902 | 1
(14 行)
perftestdb=>
では、ここまでくると、わかりますよね。 前回のエントリーで、MySQL の立ち位置にいるのは、Oracle Database 23ai でーーーす。
なお、23aiからGROUP BYに列エイリアスが書けるようになってます。
結果を見てみましょうw
おおおおおーーーーーー。まじでーーーー。
MySQL/PostgreSQLと異なる挙動。列エイリアス（非引用識別子）と表の列名が被ってる影響で列エイリアスがエイリアスとして認識されていないためエラーとして扱っていますね。興味深い。いや、実はミスに気づきやすいのかも。。。
SCOTT@localhost:1521/freepdb1> set null [null]
SCOTT@localhost:1521/freepdb1> edit quoted_identification.sql
SCOTT@localhost:1521/freepdb1> @quoted_identification.sql
1 SELECT
2 mgr AS empno
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 empno
8 ORDER BY
9* empno NULLS FIRST
mgr AS empno
*
ERROR at line 2:
ORA-00979: "MGR": must appear in the GROUP BY clause or be used in an aggregate function
Help: https://docs.oracle.com/error-help/db/ora-00979/
非識別引用子のままだと、表の列名である、empno と区別できない（AS empno と記載しても）ので、引用識別子にしてみます。PostgreSQL/Oracle Databaseの引用符は(")ダブルクォートですよね。
実行してみると。。。。。。あーーーーら不思議、MySQL/PostgreSQLとは異なり、引用識別子にしてあげることで、emp表のempno列とはことなる、列エイリアスと認識して、正しく処理しています。。。なんと。。。というか、この実装のほうが引用識別子の意味としては理解しやすくないですかね。。どうなんだろう。
SCOTT@localhost:1521/freepdb1> !cp quoted_identification.sql quoted_identification2.sql
SCOTT@localhost:1521/freepdb1> edit quoted_identification2.sql
SCOTT@localhost:1521/freepdb1> @quoted_identification2.sql
1 SELECT
2 mgr AS "empno"
3 , COUNT(empno) AS head_counts
4 FROM
5 emp
6 GROUP BY
7 "empno"
8 ORDER BY
9* "empno" NULLS FIRST
empno HEAD_COUNTS
---------- -----------
[null] 1
7566 2
7698 5
7782 1
7788 1
7839 3
7902 1
7 rows selected.
Elapsed: 00:00:00.01
SCOTT@localhost:1521/freepdb1> set linesize 80
SCOTT@localhost:1521/freepdb1> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SCOTT@localhost:1521/freepdb1>
引用識別子/非引用識別子が既存の列名と同じ場合の評価の仕方は皆バラバラですねw。思いっきり実装依存の部分なので、列、表名に対するエイリアスを指定する場合には注意しましょう。
（個人的な感覚でしかないですがw
Oracle Databaseの実装のほうが直感的には理解しやすいきがします）
MySQLの場合、PostgreSQL同様に実行されますが、面白い特徴は、ワーニングという形で、"Warning 1052 | Column 'empno' in group statement is ambiguous" を示してくれることですね。MySQLでは warningのカウントに注意!!! 忘れないようにしたいですね。重要なコメントが書かれてたりしますw
この3者の中で、もっとも問題に気づきにくいのは、PostgreSQLでしょうか。。。
挙動がMySQL側に似ているので、MySQLのようにちょっと曖昧だけど、実行だけしておいたよ！ 的なワーニングでも返してくれると、
MySQLのように気づきやすくなるかもしれませんね。＞ 各位
これだから、癖のあるSQLとの付き合いはやめられないwww
Enjoy SQL！ そして、癖！ も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 (+)の外部結合は方言
・標準はあるにはあるが癖の多い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 #11 - 引用符にも癖がでるし、NULLのソート構文にも癖がある！（前編）
最近のコメント