帰ってきた！ 標準はあるにはあるが癖の多いSQL #9、BOOLEAN型にも癖が出る Tweet
PostgreSQL/MySQLには実装済みだった Boolean型がやっとOracle Database 23aiで追加されました。長ーい間実装されなかったデータ型なので、number型を使ったりして代替されていたわけですが、23ai以降は普通に使えるってことですね。
ただ、タイトルの通り、標準はあるものの、それぞれの実装には癖があります！！！！w
どのような癖があるのか、知っておきましょう。 （兼、自分用メモ）
Oracle/PostgreSQL/MySQLで違いを見てみます。
Oracle Database 23ai
SCOTT@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
PostgreSQL
例によってw こちらの都合により、13.14を使っています。
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 行)
MySQL
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.36 |
+-----------+
1 row in set (0.02 sec)
データ登録時、どの値がBOOLEANとして扱われるのかマニュアルから拾った値を元に確認しておきたいと思います。キャストが必要だったりする場合には、暗黙型変換とか気にしておいた方が良いですからね。索引にも使えるデータ型ですし。。。
Oracle Database 23ai
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0以外の数値は全て、TRUEと解釈される。e.g. -1,10,0.1,-1.5 など)
TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の数値
FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される)
FALSE, 'OFF', 'NO', 'F', 'N', 0
この仕様、Db2やSnowflakeのBOOLEAN型と同じように見えますね。（試してないですけどw）
マニュアルに記載されている通りの挙動。。。数値を指定した時の解釈もマニュアル通りなのですが、こんな使い方したくないですよねw 挙動確認なので試してはいますが。。。。
SCOTT@freepdb1> @boolean_literals.sql
Table dropped.
Table created.
1 row created.
1 row created.
...中略...
1 row created.
1 row created.
Commit complete.
ID B1 MEMO
---------- ----------- ----------
1 TRUE TRUE
2 TRUE true
3 TRUE True
4 TRUE ON
5 TRUE on
6 TRUE On
7 TRUE YES
8 TRUE yes
9 TRUE Yes
10 TRUE T
11 TRUE t
12 TRUE Y
13 TRUE y
14 TRUE 1
15 TRUE 0.01
16 TRUE -0.01
17 TRUE 10
18 TRUE -12
19 FALSE FALSE
20 FALSE false
21 FALSE False
22 FALSE OFF
23 FALSE off
24 FALSE Off
25 FALSE NO
26 FALSE no
27 FALSE No
28 FALSE F
29 FALSE f
30 FALSE N
31 FALSE n
32 FALSE 0
33 [null] null
33 rows selected.
PostgreSQL 13.14
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、正の整数のみTRUEと解釈され、負の整数や少数はエラーとなる。 -1, 0.1, -1.5などはエラー)
TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の正の整数のみ（ただし、booleanへのキャストが必要）
FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される)
FALSE, 'OFF', 'NO', 'F', 'N', 0
Oracleに類似していますが、数値の扱いが微妙に違いますね。
perftestdb=> \i boolean_literals.sql
DROP TABLE
CREATE TABLE
INSERT 0 1
INSERT 0 1
...中略...
INSERT 0 1
INSERT 0 1
INSERT 0 1
psql:boolean_literals.sql:19: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(14, 1, '1');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
psql:boolean_literals.sql:21: ERROR: column "b1" is of type boolean but expression is of type numeric
行 1: insert into example2 values(15, 0.01, '0.01');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:22: ERROR: cannot cast type numeric to boolean
行 1: insert into example2 values(15, 0.01::boolean, '0.01');v ^
psql:boolean_literals.sql:23: ERROR: column "b1" is of type boolean but expression is of type numeric
行 1: insert into example2 values(16, -0.1, '-0.01');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:24: ERROR: cannot cast type numeric to boolean
行 1: insert into example2 values(16, -0.1::boolean, '-0.01');
^
psql:boolean_literals.sql:25: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(17, 10, '10');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
psql:boolean_literals.sql:27: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(18, -12, '-12');
^
HINT: You will need to rewrite or cast the expression.
psql:boolean_literals.sql:28: ERROR: operator does not exist: - boolean
行 1: insert into example2 values(18, -12::boolean, '-12');
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
INSERT 0 1
INSERT 0 1
...中略...
INSERT 0 1
INSERT 0 1
psql:boolean_literals.sql:44: ERROR: column "b1" is of type boolean but expression is of type integer
行 1: insert into example2 values(32, 0, '0');
^
HINT: You will need to rewrite or cast the expression.
INSERT 0 1
INSERT 0 1
Null表示は"[null]"です。
id | b1 | memo
----+--------+-------
1 | t | TRUE
2 | t | true
3 | t | True
4 | t | ON
5 | t | on
6 | t | On
7 | t | YES
8 | t | yes
9 | t | Yes
10 | t | T
11 | t | t
12 | t | Y
13 | t | y
14 | t | 1
17 | t | 10
19 | f | FALSE
20 | f | false
21 | f | False
22 | f | OFF
23 | f | off
24 | f | Off
25 | f | NO
26 | f | no
27 | f | No
28 | f | F
29 | f | f
30 | f | N
31 | f | n
32 | f | 0
33 | [null] | null
(30 行)
perftestdb=>
MySQL 8.0.32
実データ型がTINYINTであるためですが、
数値の場合0,1以外ではエラーは出ないようで注意が必要ですよね。CHECK制約で保護するとかだろうか。。。
TRUEとして解釈されるリテラル値
TRUE, 1のみ。（エラーにはならないが、0以外の数字は数字として整数として登録さえるので要注意）
FALSEとして解釈されるリテラル値
FALSE, 0のみ
Oracle/PostgreSQLとも違い、最低限に絞ってるって感じですが、数値を指定した時の挙動には要注意ですね。内部の型はTINYINTなので。。。文字列を全て受け付けないのは、これもTINYINTである影響ですかね?
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.01 sec)
mysql> \. boolean_literals.sql
Query OK, 0 rows affected (0.05 sec)
...中略...
Query OK, 1 row affected (0.01 sec)
ERROR 1366 (HY000): Incorrect integer value: 'ON' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'on' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'On' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'YES' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'yes' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Yes' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'T' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 't' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Y' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'y' for column 'b1' at row 1
Query OK, 1 row affected (0.01 sec)
...中略...
Query OK, 1 row affected (0.01 sec)
ERROR 1366 (HY000): Incorrect integer value: 'OFF' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'off' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'Off' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'NO' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'no' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'No' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'F' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'f' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'N' for column 'b1' at row 1
ERROR 1366 (HY000): Incorrect integer value: 'n' for column 'b1' at row 1
Query OK, 1 row affected (0.01 sec)
Query OK, 1 row affected (0.01 sec)
+----+------+-------+
| id | b1 | memo |
+----+------+-------+
| 1 | 1 | TRUE |
| 2 | 1 | true |
| 3 | 1 | True |
| 14 | 1 | 1 |
| 15 | 0 | 0.01 |
| 16 | 0 | -0.01 |
| 17 | 10 | 10 |
| 18 | -12 | -12 |
| 19 | 0 | FALSE |
| 20 | 0 | false |
| 21 | 0 | False |
| 32 | 0 | 0 |
| 33 | NULL | null |
+----+------+-------+
13 rows in set (0.00 sec)
長くなりそうなので、次回へ続く！w
今回のまとめ
Oracle > PostgreSQL > MySQLのような感じで使える値の種類はサブセットになってる感じですね。 なので、true/false , nullだけ使ってればどこに行っても問題はなさそう。。ではあります。
Oracle Database 23ai/MySQL/PostgreSQL共通
BOOLEAN/BOOL型として定義可能。
（ただし、MySQLでは実態はTINYINTなので、TINYINT由来の癖があるので注意
TRUEとして扱われる値
TRUE
(Oracle/PostgreSQL/MySQL, No case sensitive)
'ON'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'YES'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'T'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'Y'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
1
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)
0以外の数値
(Oracle 少数及び負の整数も含む)
(PostgreSQL 正の整数のみ)
(MySQL 少数以下を切り捨てた整数として扱われるのでCHECK制約などで保護した方が安全なのではないだろうか。内部的にTINYINTである影響だろう)
FALSEとして扱われる値
FALSE
(Oracle/PostgreSQL/MySQL, No case sensitive)
'OFF'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'NO'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'F'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
'N'
(Oracle/PostgreSQL, No case sensitive)
(MySQLでは使えない)
0
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)
全体的に、true/false (null) を使うように規約等、場合によってはCHECK制約で保護しておいた方が、何かと混乱を避けられるのではないだろうか。。
補足）
この検証に利用したスクリプト
Oracle Database 23ai
SCOTT/freepdb1> !cat boolean_literals.sql
drop table if exists example2;
create table example2 (id number primary key, b1 boolean, memo varchar2(10));
-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(18, -12, '-12');
-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');
-- NULL
insert into example2 values(33, null, 'null');
commit;
-- check
set NULL [null]
select * from example2 order by id;
PostgreSQL 13.14
erftestdb=> \! cat boolean_literals.sql
drop table if exists example2;
create table example2 (id integer primary key, b1 boolean, memo varchar(10));
-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(14, 1::boolean, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(15, 0.01::boolean, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(16, -0.1::boolean, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(17, 10::boolean, '10');
insert into example2 values(18, -12, '-12');
insert into example2 values(18, -12::boolean, '-12');
-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');
insert into example2 values(32, 0::boolean, '0');
-- NULL
insert into example2 values(33, null, 'null');
-- check
\pset null [null]
select * from example2 order by id;
MySQL 8.0.32
mysql> \! cat boolean_literals.sql
drop table if exists example2;
create table example2 (id integer primary key, b1 boolean, memo varchar(10));
-- TRUE
insert into example2 values(1, TRUE, 'TRUE');
insert into example2 values(2, true, 'true');
insert into example2 values(3, True, 'True');
insert into example2 values(4, 'ON', 'ON');
insert into example2 values(5, 'on', 'on');
insert into example2 values(6, 'On', 'On');
insert into example2 values(7, 'YES', 'YES');
insert into example2 values(8, 'yes', 'yes');
insert into example2 values(9, 'Yes', 'Yes');
insert into example2 values(10, 'T', 'T');
insert into example2 values(11, 't', 't');
insert into example2 values(12, 'Y', 'Y');
insert into example2 values(13, 'y', 'y');
insert into example2 values(14, 1, '1');
insert into example2 values(15, 0.01, '0.01');
insert into example2 values(16, -0.1, '-0.01');
insert into example2 values(17, 10, '10');
insert into example2 values(18, -12, '-12');
-- FALSE
insert into example2 values(19, FALSE, 'FALSE');
insert into example2 values(20, false, 'false');
insert into example2 values(21, False, 'False');
insert into example2 values(22, 'OFF', 'OFF');
insert into example2 values(23, 'off', 'off');
insert into example2 values(24, 'Off', 'Off');
insert into example2 values(25, 'NO', 'NO');
insert into example2 values(26, 'no', 'no');
insert into example2 values(27, 'No', 'No');
insert into example2 values(28, 'F', 'F');
insert into example2 values(29, 'f', 'f');
insert into example2 values(30, 'N', 'N');
insert into example2 values(31, 'n', 'n');
insert into example2 values(32, 0, '0');
-- NULL
insert into example2 values(33, null, 'null');
-- check
select * from example2 order by id;
ここまで見た感じ、true/false, 必要があれば null を使う側のルールとしておくと、みんな幸せになれる気がするよね。BOOLEAN型って。
参考資料）
Oracle Database 23 / 開発者ガイド / 外部データ型
"https://docs.oracle.com/cd/F82042_01/lnoci/data-types.html#GUID-D69455D9-CE01-44CC-B5A9-E541C7774805
Oracle Database 23 / SQL言語リファレンス / ブールデータ型
https://docs.oracle.com/cd/F82042_01/sqlrf/Data-Types.html#GUID-285FFCA8-390D-4FA9-9A51-47B84EF5F83A
Oracle Database 23 / SQL言語リファレンス / Oracleの組込みデータ型
https://docs.oracle.com/cd/F82042_01/sqlrf/Data-Types.html#GUID-7B72E154-677A-4342-A1EA-C74C1EA928E6
PostgreSQL / データ型 / 論理値データ型
https://www.postgresql.jp/document/13/html/datatype-boolean.html
マニュアルによると、PostgreSQLのBOOLEAN型もサイズは１バイト
PostgreSQL 13 / 8.6. 論理値データ型
https://www.postgresql.jp/document/13/html/datatype-boolean.html
MySQL その他のデータベースエンジンのデータ型の使用 - BOOLEAN/BOOL
https://dev.mysql.com/doc/refman/8.0/ja/other-vendor-data-types.html
MySQL 数値型の記憶域要件 - TINYINT
https://dev.mysql.com/doc/refman/8.0/ja/storage-requirements.html#data-types-storage-reqs-numeric
MySQLのBOOLEANはTINYINT型で1バイトということですね。Boolean型のサイズはMySQL/Oracle/PostgreSQLどれも1バイト。
MySQL 8.0 / 11.1.6 Boolean Literals
https://dev.mysql.com/doc/refman/8.0/en/boolean-literals.html
長くなりそうなので、次回へ続くw
こういうことで、この手の SQLの癖w なかなか厳しいなw Enjoy SQL!
関連エントリー
・標準はあるにはあるが癖の多い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させるにも癖が出る
| 固定リンク | 0
コメント