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