帰ってきた！ 標準はあるにはあるが癖の多いSQL #10、BOOLEAN型にも癖が出る（後編） Tweet
Previously on Mac De Oracle
前回は、BOOLEAN型に使える値の差異について、Oracle Database 23ai/PostgreSQL/MySQLで比較してみました。 なかなか癖がありますよね。それぞれw Oracle Database 23aiは、Db2やSnowflakeの仕様に類似していそうでしたよね。（未検証ですが）
ということで、5月もラストスパートwで、BOOLEANネタ後編で締めたいと思いますw
BOOLEAN/BOOL型どちらでもOKということなので、一応確認しておきます。
Oracle Database
SCOTT@freepdb1> create table sample3 (b1 boolean, b2 bool);
Table created.
SCOTT@freepdb1> desc sample3
Name Null? Type
----------------------------------------- -------- ----------------------------
B1 BOOLEAN
B2 BOOLEAN
SCOTT@freepdb1> drop table sample3 purge;Table dropped.
PostgreSQL
perftestdb=> create table sample3 (b1 boolean, b2 bool);
CREATE TABLE
perftestdb=> \d sample3
テーブル"public.sample3"
列 | タイプ | 照合順序 | Null 値を許容 | デフォルト
----+---------+----------+---------------+------------
b1 | boolean | | |
b2 | boolean | | |
perftestdb=> drop table sample3;
DROP TABLE
perftestdb=>
MySQL
mysql> create table sample3 (b1 boolean, b2 bool);
Query OK, 0 rows affected (0.27 sec)
mysql> desc sample3;
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| b1 | tinyint(1) | YES | | NULL | |
| b2 | tinyint(1) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
2 rows in set (0.04 sec)
mysql> drop table sample3;
Query OK, 0 rows affected (0.10 sec)
次は、データサイズ。
MySQL/PostgreSQLそれぞれマニュアルにて、1バイトと記載されていますが、Oracle Databaseでは発見できす（仕方ないので直接確認してみますw）
PostgreSQL - https://www.postgresql.jp/document/13/html/datatype-boolean.html MySQL BOOLEAN = TINYINT - 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/integer-types.html
Oracleも1バイトのようですね！
SCOTT@freepdb1> set null [null]
SCOTT@freepdb1> col dump(b1) for a30
SCOTT@freepdb1> col vsize(b1) for 9990
SCOTT@freepdb1> select dump(b1),vsize(b1) from example2
DUMP(B1) VSIZE(B1)
------------------------------ ---------
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1
Typ=252 Len=1: 1 1
...中略...
Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
Typ=252 Len=1: 0 1
NULL [null]
33 rows selected.
BOOLEAN型を文字、数値へキャストするとどうなるでしょう？
Oracle Database BOOLEAN型の数値へのキャストは、1 (true) / 0 (false)、文字列へのキャストは、'TRUE' / 'FALSE' になるようですね。CHAR型へのキャストの場合、空白埋めされるようですね（マニュアルより）。
SCOTT@freepdb1> select id,b1,to_number(b1),memo from example2 order by id;
ID B1 TO_NUMBER(B1) MEMO
---------- ----------- ------------- ----------
1 TRUE 1 TRUE
2 TRUE 1 true
3 TRUE 1 True
4 TRUE 1 ON
5 TRUE 1 on
6 TRUE 1 On
7 TRUE 1 YES
8 TRUE 1 yes
9 TRUE 1 Yes
10 TRUE 1 T
11 TRUE 1 t
12 TRUE 1 Y
13 TRUE 1 y
14 TRUE 1 1
15 TRUE 1 0.01
16 TRUE 1 -0.01
17 TRUE 1 10
18 TRUE 1 -12
19 FALSE 0 FALSE
20 FALSE 0 false
21 FALSE 0 False
22 FALSE 0 OFF
23 FALSE 0 off
24 FALSE 0 Off
25 FALSE 0 NO
26 FALSE 0 no
27 FALSE 0 No
28 FALSE 0 F
29 FALSE 0 f
30 FALSE 0 N
31 FALSE 0 n
32 FALSE 0 0
33 [null] [null] null
33 rows selected.
SCOTT@freepdb1> select id,b1,to_char(b1),memo from example2 order by id;
ID B1 TO_CHA MEMO
---------- ----------- ------ ----------
1 TRUE TRUE TRUE
2 TRUE TRUE true
3 TRUE TRUE True
4 TRUE TRUE ON
5 TRUE TRUE on
6 TRUE TRUE On
7 TRUE TRUE YES
8 TRUE TRUE yes
9 TRUE TRUE Yes
10 TRUE TRUE T
11 TRUE TRUE t
12 TRUE TRUE Y
13 TRUE TRUE y
14 TRUE TRUE 1
15 TRUE TRUE 0.01
16 TRUE TRUE -0.01
17 TRUE TRUE 10
18 TRUE TRUE -12
19 FALSE FALSE FALSE
20 FALSE FALSE false
21 FALSE FALSE False
22 FALSE FALSE OFF
23 FALSE FALSE off
24 FALSE FALSE Off
25 FALSE FALSE NO
26 FALSE FALSE no
27 FALSE FALSE No
28 FALSE FALSE F
29 FALSE FALSE f
30 FALSE FALSE N
31 FALSE FALSE n
32 FALSE FALSE 0
33 [null] [null] null
33 rows selected.
PostgreSQL PostgreSQLは想定の範囲内ですね。'true'/'false', 1/0 になるようですね
perftestdb=> \pset null [null]
Null表示は"[null]"です。
perftestdb=>
perftestdb=> select id,b1,b1::varchar,memo from example2 order by id;
id | b1 | b1 | memo
----+--------+--------+-------
1 | t | true | TRUE
2 | t | true | true
3 | t | true | True
4 | t | true | ON
5 | t | true | on
6 | t | true | On
7 | t | true | YES
8 | t | true | yes
9 | t | true | Yes
10 | t | true | T
11 | t | true | t
12 | t | true | Y
13 | t | true | y
14 | t | true | 1
17 | t | true | 10
19 | f | false | FALSE
20 | f | false | false
21 | f | false | False
22 | f | false | OFF
23 | f | false | off
24 | f | false | Off
25 | f | false | NO
26 | f | false | no
27 | f | false | No
28 | f | false | F
29 | f | false | f
30 | f | false | N
31 | f | false | n
32 | f | false | 0
33 | [null] | [null] | null
(30 行)
perftestdb=> select id,b1,b1::integer,memo from example2 order by id;
id | b1 | b1 | memo
----+--------+--------+-------
1 | t | 1 | TRUE
2 | t | 1 | true
3 | t | 1 | True
4 | t | 1 | ON
5 | t | 1 | on
6 | t | 1 | On
7 | t | 1 | YES
8 | t | 1 | yes
9 | t | 1 | Yes
10 | t | 1 | T
11 | t | 1 | t
12 | t | 1 | Y
13 | t | 1 | y
14 | t | 1 | 1
17 | t | 1 | 10
19 | f | 0 | FALSE
20 | f | 0 | false
21 | f | 0 | False
22 | f | 0 | OFF
23 | f | 0 | off
24 | f | 0 | Off
25 | f | 0 | NO
26 | f | 0 | no
27 | f | 0 | No
28 | f | 0 | F
29 | f | 0 | f
30 | f | 0 | N
31 | f | 0 | n
32 | f | 0 | 0
33 | [null] | [null] | null
(30 行)
MySQL TINYINTのまま文字列に変換されちゃいますね。これはこれで癖が強いといえば強い
mysql> select id,b1,cast(b1 as char),memo from example2 order by id;
+----+------+------------------+-------+
| id | b1 | cast(b1 as char) | memo |
+----+------+------------------+-------+
| 1 | 1 | 1 | TRUE |
| 2 | 1 | 1 | true |
| 3 | 1 | 1 | True |
| 14 | 1 | 1 | 1 |
| 15 | 0 | 0 | 0.01 |
| 16 | 0 | 0 | -0.01 |
| 19 | 0 | 0 | FALSE |
| 20 | 0 | 0 | false |
| 21 | 0 | 0 | False |
| 32 | 0 | 0 | 0 |
| 33 | NULL | NULL | null |
+----+------+------------------+-------+
Boolean型は索引にも利用できるので、念の為、Boolean型の検索でリテラルとして使える記述がどう評価されるか確認しておきましょうね。
暗黙のキャストやらなんやらあるのかないのか。。。索引に含めた場合、キャストされて索引として利用できないないケースなど把握しておくと便利ですよー。:)
Oracle Database 23ai Oracleの場合、簡易な方法として、SQL*Plusのオートトレースを利用し、 Predicate Information（述語情報）を見て filterの際に、関数が適用され内部的にキャストされてしまうかどうかで判断していくことにします。
ついでに、boolean列に非ユニーク索引（単一列索引）を作成しておき、索引が利用されることも見ておきます。
全てのケースは実施していませんが、大文字小文字は無視される前提で、小文字で検証しています。
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
以上が、OracleのBOOELAN型で利用できるリテラル値なので、
true, 'on', 'yes', 't', 'y', 1, 0以外の数値(10, 0.1, -2, -0.1)、全てにおいて、内部的にTRUEとしてハンドリングされていることがわかります。
（キャストされているわけではなく、TRUEとして評価されていることが、predicate information及び、index only scanになっているところでも判断できます）
ますます、 true/false, nullだけ使えばいいじゃんって感じがしますね。
以下、検証ログ
SCOTT@freepdb1> create index ix_bool_example2 on example2(b1);
Index created.
SCOTT@freepdb1> select b1 from example2 where b1 = true;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
7 recursive calls
4 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 't';
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 'y';
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 'yes';
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 'on';
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 1;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 10;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = 0.1;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
05:53:05 SCOTT@192.168.1.23:1521/freepdb1> select b1 from example2 where b1 = -2;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
SCOTT@freepdb1> select b1 from example2 where b1 = -0.1;
18 rows selected.
...中略...
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 16 | 16 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IX_BOOL_EXAMPLE2 | 16 | 16 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("B1"=TRUE)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
...中略...
18 rows processed
PostgreSQL
TRUEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、正の整数のみTRUEと解釈され、負の整数や少数はエラーとなる。 -1, 0.1, -1.5などはエラー) TRUE. 'ON', 'YES', 'T', 'Y', 1, 0以外の正の整数のみ（ただし、booleanへのキャストが必要）
FALSEとして解釈されるリテラル値 (文字列は大文字小文字は無視される。数値は、0のみFALSEと解釈される) FALSE, 'OFF', 'NO', 'F', 'N', 0
true, 'on', 'yes', 't', 'y', 1, 0以外の正の整数のみ（ただし、booleanへのキャスト必須）(10) 全てにおいて、内部的にTRUEとしてハンドリングされていることがわかります。
（キャストされているわけではなく、Index Cond:及び、、hintで矯正していますが、Index Scan uging..になっているところでも判断できます）
0以外の正の整数以外(0.1, -2, -0.1)は、エラーになっていることが確認できます。
perftestdb=> create index ix_bool_example2 on example2(b1);
CREATE INDEX
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = true;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.093..0.098 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 13.020 ms
Execution Time: 8.653 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'on';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.038..0.046 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.259 ms
Execution Time: 0.142 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'yes';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.021..0.026 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.168 ms
Execution Time: 0.087 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 't';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.022..0.030 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.173 ms
Execution Time: 0.117 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 'y';
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.017..0.021 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.119 ms
Execution Time: 0.066 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 1;
ERROR: operator does not exist: boolean = integer
行 1: ...example2 ix_bool_example2) */ b1 from example2 where b1 = 1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 1::boolean;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------
Index Scan using ix_bool_example2 on public.example2 (cost=0.14..8.40 rows=15 width=1) (actual time=0.010..0.015 rows=15 loops=1)
Output: b1
Index Cond: (example2.b1 = true)
Planning Time: 0.125 ms
Execution Time: 0.067 ms
(5 行)
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = 0.1::boolean;
ERROR: cannot cast type numeric to boolean
行 1: ..._bool_example2) */ b1 from example2 where b1 = 0.1::boolean;
^
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = -2::boolean;
ERROR: operator does not exist: - boolean
行 1: ... ix_bool_example2) */ b1 from example2 where b1 = -2::boolea...
^
HINT: No operator matches the given name and argument type. You might need to add an explicit type cast.
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where b1 = -0.1::boolean;
ERROR: cannot cast type numeric to boolean
行 1: ...bool_example2) */ b1 from example2 where b1 = -0.1::boolean;
暗黙型変換ではないですが、暗黙変換同様に関数が利用された場合は、索引が利用できなくなることが確認できますよね。
perftestdb=> explain (analyze,verbose) select /*+ IndexScan(example2 ix_bool_example2) */ b1 from example2 where (b1::integer - 1)::boolean = false;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Seq Scan on public.example2 (cost=10000000000.00..10000000001.52 rows=20 width=1) (actual time=0.027..0.041 rows=15 loops=1)
Output: b1
Filter: (NOT (((example2.b1)::integer - 1))::boolean)
Rows Removed by Filter: 15
Planning Time: 0.171 ms
Execution Time: 2.737 ms
(6 行)
MySQL
TRUEとして解釈されるリテラル値
TRUE, 1のみ。（エラーにはならないが、0以外の数字は数字として整数として登録さえるので要注意）
FALSEとして解釈されるリテラル値
FALSE, 0のみ
なので、true,1 は 内部的に true と扱われていることがわかります。
'yes'はやなりエラーですね。
mysql> create index ix_bool_example2 on example2(b1);
Query OK, 0 rows affected (1.28 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain analyze select b1 from example2 where b1 = true;
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=true) (cost=0.651 rows=4) (actual time=0.04..0.0441 rows=4 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
mysql> explain analyze select b1 from example2 where b1 = 1;
+--------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=1) (cost=0.651 rows=4) (actual time=0.0165..0.0203 rows=4 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
文字列だとエラーになるかと思いきや。ワーニング扱いですね。文字列がトランケートされた結果、述語が b1 = 0 に変更されてしまっています。これはダメですね。
mysql> explain analyze select b1 from example2 where b1 = 'yes';
+--------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=0) (cost=0.851 rows=6) (actual time=0.0179..0.0222 rows=6 loops=1)
|
+--------------------------------------------------------------------------------------------------------------------------------------+
1 row in set, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+-----------------------------------------+
| Level | Code | Message |
+---------+------+-----------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'yes' |
+---------+------+-----------------------------------------+
1 row in set (0.01 sec)
整数の正の値、負の値。予想通りそのまま、数値として比較されています。エラーにならないのでこれは避けた方が良さそう
mysql> explain analyze select b1 from example2 where b1 = 100;
+---------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+---------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=100) (cost=0.35 rows=1) (actual time=0.0214..0.0214 rows=0 loops=1)
|
+---------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> explain analyze select b1 from example2 where b1 = -100;
+------------------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+------------------------------------------------------------------------------------------------------------------------------------------+
| -> Covering index lookup on example2 using ix_bool_example2 (b1=-(100)) (cost=0.35 rows=1) (actual time=0.0147..0.0147 rows=0 loops=1)
|
+------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
少数の場合もエラーにはなりません。述語は (Impossible WHERE) なことになってますね。これらも避けた方が良さそう。
mysql> explain analyze select b1 from example2 where b1 = 0.1;
+--------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------+
| -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=281e-6..281e-6 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)
mysql> explain analyze select b1 from example2 where b1 = -0.1;
+--------------------------------------------------------------------------------------------------+
| EXPLAIN |
+--------------------------------------------------------------------------------------------------+
| -> Zero rows (Impossible WHERE) (cost=0..0 rows=0) (actual time=176e-6..176e-6 rows=0 loops=1)
|
+--------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
最後に、意地悪な検証を。
こんなことやらないと思いますけども、MySQL独特な挙動ですね。index only scanのまま、filterしてますね。TINYINTである影響なのでしょうね。面白いですが真似しないようにw。 この手の作り込んだら探すの大変そうだし。（データ積んでしっかり検証すると炙り出せるとは思うけどw）
mysql> explain analyze select b1 from example2 where (b1 - 1) = false;
+-------------------------------------------------------------------------------------------------------------------------------+
| EXPLAIN |
+-------------------------------------------------------------------------------------------------------------------------------+
| -> Filter: ((example2.b1 - 1) = false) (cost=1.35 rows=11) (actual time=1.78..1.8 rows=4 loops=1)
-> Covering index scan on example2 using ix_bool_example2 (cost=1.35 rows=11) (actual time=0.234..0.251 rows=11 loops=1)
|
+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.05 sec)
BOOLEAN型の癖、総まとめ
BOOLEAN/BOOLどちらでも定義可能 (Oracle/PostgreSQL/MySQL)
(MySQLは内部的には、TINYINT型なのでそれ由来の癖がある)
データサイズは、1バイト (Oracle/PostgreSQL/MySQL)
利用可能なリテラル値（INSERT/UPDATEでセットする場合） 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として扱われる値 TRUE
(Oracle/PostgreSQL/MySQL, No case sensitive)
'ON'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'ON' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
'YES'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'YES' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
'T'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'T' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い 。 sql_mode挙動が変わったりするのかは未確認)
'Y'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'Y' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
1
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)
0以外の数値
(Oracle 少数及び負の整数も含む)
(PostgreSQL 正の整数のみ)
(MySQL 内部的にTINYINTである影響だろうか、エラーとはならず、数値として扱われたり、切り捨てられたりする。避けた方が良い。 e.g. WHERE boolean列 = 100や、WHERE boolean列 = 0.1 など。 sql_mode挙動が変わったりするのかは未確認)
FALSEとして扱われる値 FALSE
(Oracle/PostgreSQL/MySQL, No case sensitive)
'OFF'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'OFF' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
'NO'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'NO' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
'F'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'F' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
'N'
(Oracle/PostgreSQL, No case sensitive)
(MySQL 利用すると値が切り捨てられ、WHERE boolean列 = 'N' が WHERE boolean列 = 0 に書き換えられてします。ワーニングなどで確認できるが、避けた方が良い。 sql_mode挙動が変わったりするのかは未確認)
0
(Oracle/MySQL)
(PostgreSQL booleanへのキャストが必要)
全体的に、true/false (null) を使うように規約で制限したり、CHECK制約で保護しておいた方が混乱を避けられるのではないだろうか。。
癖ありすぎ！ w 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型にも癖が出る
| 固定リンク | 0
コメント