読者です 読者をやめる 読者になる 読者になる

HHeLiBeXの日記 正道編

日々の記憶の記録とメモ‥

文字列の連結

SQLにおける文字列連結は「縦棒2本(||)」という自分の中の常識が打ち破られたらしい。
ということで、次のテーブルを前提として調査してみた。

CREATE TABLE hoge(
      v1 VARCHAR(4) NOT NULL
    , v2 VARCHAR(4) NOT NULL
    , v3 VARCHAR(4) NOT NULL
    , v4 VARCHAR(4) NOT NULL
    , v5 VARCHAR(4) NOT NULL
    , v6 VARCHAR(4) NOT NULL
)

なぜにこんなに列数が多いかというと、数値として解釈できる文字列とできない文字列について試したいため。
挿入するデータは次のような感じ。(文字列はJavaにおける表現)

v1 v2 v3 v4 v5 v6
"hoge" "-" "foo" "-1" "-2" "-3"
"0" "-" "0" "0" "0" "0"
"0" "-" "1" "0" "1" "2"
"1" "-" "2" "1" "2" "3"

で、次のパターンを調べてみた。

  • (01) v1 || v2 || v3
  • (02) v4 || v5 || v6
  • (03) v1 + v2 + v3
  • (04) v4 + v5 + v6
  • (05) CONCAT(v1, v2, v3)
  • (06) CONCAT(v4, v5, v6)
  • (07) CONCAT(CONCAT(v1, v2), v3)
  • (08) CONCAT(CONCAT(v4, v5), v6)

わざとらしいパターンがいくつかあるが、それは結果を見たら分かると思う。
一応、念のため(謎)、DatabaseMetaDataのgetDatabaseProductName()とgetDatabaseProductVersion()で取得したものも書いておく。ただし、全部を書くと長くなるので、無駄なものは省くようにしている。

DBMS getDatabaseProductName()+getDatabaseProductVersion()
DB2 9.1 DB2/NT SQL09012
DB2 9.5 DB2/NT SQL09052
DB2 9.7 DB2/NT SQL09072
Oracle 10g XE Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production
PostgreSQL 8.3 PostgreSQL 8.3.7
PostgreSQL 8.4 PostgreSQL 8.4.2
MySQL 5.1 MySQL 5.1.32-community
Firebird 2.1 WI-V2.1.0.17798 Firebird 2.1.WI-V2.1.0.17798 Firebird 2.1/tcp (proteus)/P10
Firebird 2.5 WI-V2.5.0.26074 Firebird 2.5.WI-V2.5.0.26074 Firebird 2.5/tcp (proteus)/P10
Derby 10.05.03.00 Apache Derby 10.5.3.0 - (802917)
SQLite 03.06.14.02 SQLite 3.6.3
SQLServer 2005 Microsoft SQL Server 9.00.4053
SQLServer 2008 Microsoft SQL Server 10.00.2531
H2 1.2.126 H2 1.2.126 (2009-12-18)
HSQLDB 1.8.1 HSQL Database Engine 1.8.1

で、次が、各DBMSで実行したときの結果を整理したもの。

DBMS (01) (02) (03) (04) (05) (06) (07) (08)
DB2 9.1 O O X X (*1) (*1) O O
DB2 9.5 O O X X (*1) (*1) O O
DB2 9.7 O O X (*2) (*1) (*1) O O
Oracle 10g XE O O X (*2) (*1) (*1) O O
PostgreSQL 8.3 O O X X X X X X
PostgreSQL 8.4 O O X X X X X X
MySQL 5.1 (*3) (*3) (*4) (*4) O O O O
Firebird 2.1 O O X X X X X X
Firebird 2.5 O O X X X X X X
Derby 10.05.03.00 O O X X X X X X
SQLite 03.06.14.02 O O (*5) (*2) X X X X
SQLServer 2005 X X O O X X X X
SQLServer 2008 X X O O X X X X
H2 1.2.126 O O X X O O O O
HSQLDB 1.8.1 O O O O (*1) (*1) O O
  • (*1) 関数CONCAT自体はあるが、引数の数が2個しか許されていないためにエラー。
  • (*2) 算術演算の結果が数値型の値(java.math.BigDecimal)として返される。
  • (*3) 無理矢理な論理演算の結果が数値型の値(java.lang.Long)として返される。
  • (*4) 無理矢理な算術演算の結果が数値型の値(java.lang.Double)として返される。
  • (*5) 無理矢理な算術演算の結果が数値型の値(java.lang.Doubleまたはjava.lang.Integer)として返される。

結果を見て分かるように、MySQLSQLServerは、文字列連結演算子として「||」をサポートしていない。
さらに悪いことに、MySQLでは、演算子「||」が無理矢理論理演算子(何)として扱われ、一見すると意味不明な結果が返ってくる。
なお、MySQLの「(04)」を「無理矢理な〜」としているが、これは「(03)」で結果が返ってきていることから、たまたま数値文字列なので正常に見えるだけ、という意味で「(03)」と同じ分類にしてある。
DB2がv9.7から「(04)」で算術演算の結果を返すようになったのは、Oracleとの互換性を持たせるためなのでしょうね。

ちなみに、使用したプログラム。