Implementation Dependencies in the Commonly Used SQL

db tech showcase 2024 B13 11:30-12:00
https://www.db-tech-showcase.com/2024/schedule/

Transcript

1. 使い慣れた SQL に潜む実装依存 Noriyoshi Shinoda July 12, 2024 db tech showcase 2024 / B13
2. © 2024 Hewlett Packard Enterprise Development LP ✓篠田 典良（しのだ のりよし） ✓所属 ✓日本ヒューレット・パッカード合同会社 ✓現在の業務など ✓Oracle ACE Pro (2009～) ✓PostgreSQL 開発 （PostgreSQL 10～17 beta） ✓Oracle Databaseをはじめ PostgreSQL Microsoft SQL Server, Vertica 等 RDBMS 全般に関するシステムの設計、移行、チューニング、コンサルティング ✓関連する URL ✓Redgate 100 in 2022 (Most influential in the database community 2022) ✓https://www.red-gate.com/hub/redgate-100/ ✓「PostgreSQL 虎の巻」シリーズ ✓http://h30507.www3.hp.com/t5/user/viewprofilepage/user-id/838802 ✓Oracle ACE ってどんな人？ ✓http://www.oracle.com/technetwork/jp/database/articles/vivadeveloper/index-1838335-ja.html SPEAKER 2
3. RDBMS 間の SQL 文実行結果比較 はじめに © 2024 Hewlett Packard Enterprise Development LP 3 ✓異なるデータベース間の SQL 文実行結果を比較 ✓多くの RDBMS で使える基本的な構文、関数、演算子 ✓ソート順 ✓トランザクションの動作 ✓比較対象 ✓Oracle Database 23ai Free ✓PostgreSQL 16 (16.3) ✓Microsoft SQL Server 2022 ✓MySQL 9.0 ✓その他（Spanner, Vertica, SQLite など） ✓構成パラメーター等は基本的にデフォルト値を使用
4. 構文 © 2024 Hewlett Packard Enterprise Development LP 4
5. FROM 句の省略 構文 © 2024 Hewlett Packard Enterprise Development LP 5 ✓実行する SQL 文 SELECT CURRENT_DATE; ✓実行結果（Oracle Database 23ai） ✓他の RDBMS との差は無くなった ✓内部的には DUAL テーブルを使っているが、アプリケーションからは隠蔽される SQL> SELECT CURRENT_DATE; 実行計画 ----------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:01 | | 1 | FAST DUAL | | 1 | 2 (0)| 00:00:01 | -----------------------------------------------------------------
6. ORDER BY 構文 © 2024 Hewlett Packard Enterprise Development LP 6 ✓実行する SQL 文 SELECT column1 FROM table1 ORDER BY 1; RDBMS NULL の位置 NULLS FIRST 句 備考 Oracle Database 最大値 あり PostgreSQL 最大値 あり MySQL 最小値 - SQL Server 最小値 - Spanner 最小値 - Vertica データ型により異なる あり ✓実行結果 ✓文字列型のソート順はロケールや文字エンコードによって変化する
7. GROUP BY 構文 © 2024 Hewlett Packard Enterprise Development LP 7 ✓実行する SQL 文 SELECT column1 val1, SUM(column2) FROM table1 GROUP BY val1; SELECT column1 val1, SUM(column2) FROM table1 GROUP BY 1; RDBMS 実行可否#1 実行可否#2 備考 Oracle Database 〇 × Oracle Database 23ai～ パラメータ制御可能 PostgreSQL 〇 〇 MySQL 〇 〇 SQL Server × × Spanner 〇 〇 SQLite 〇 〇 ✓実行結果
8. BEGIN 構文 © 2024 Hewlett Packard Enterprise Development LP 8 ✓実行する SQL 文 BEGIN; INSERT INTO table1 VALUES (100, 'data1'); BEGIN; ROLLBACK; RDBMS 実行結果 備考 Oracle Database - BEGIN 文無し PostgreSQL ROLLBACK WARNING (there is already a transaction in progress) が発生 MySQL COMMIT SQL Server ROLLBACK BEGIN TRANSACTION 文を使用 SQLite ROLLBACK Runtime error: cannot start a transaction within a transaction が発生 ✓実行結果
9. TRANSACTION 中のエラー 構文 © 2024 Hewlett Packard Enterprise Development LP 9 ✓実行する SQL 文 BEGIN; INSERT INTO table1 VALUES (100, 'Normal'); INSERT INTO table1 VALUES (100, 'Duplicate'); -- 主キー制約違反 COMMIT; RDBMS 実行結果 備考 Oracle Database COMMIT PostgreSQL ROLLBACK トランザクション中にエラーが発生した場合、強制ロールバック MySQL COMMIT SQL Server COMMIT SQLite COMMIT ✓実行結果
10. INSERT 構文 © 2024 Hewlett Packard Enterprise Development LP 10 ✓実行する SQL 文 CREATE TABLE table1(column1 CHAR, column2 CHAR); INSERT INTO table1 VALUES ('A'); RDBMS 実行可否 備考 Oracle Database Error PostgreSQL Success column2 には NULL が格納される MySQL Error SQL Server Error Spanner Error 列名の省略不可 SQLite Error ✓実行結果
11. CASE 構文 © 2024 Hewlett Packard Enterprise Development LP 11 ✓実行する SQL 文 CREATE TABLE table1(column1 INTEGER, column2 VARCHAR(10)); SELECT CASE column1 WHEN 100 THEN 'Value#1' ELSE column1 END FROM table1; RDBMS 実行可否 備考 Oracle Database Error ORA-00932: incompatible with expected data type CHAR PostgreSQL Error ERROR: invalid input syntax for type integer MySQL Success SQL Server Error Conversion failed when converting the varchar value Spanner Error No matching signature for operator CASE for argument types SQLite Success ✓実行結果
12. オブジェクト名の規則 構文 © 2024 Hewlett Packard Enterprise Development LP 12 ✓予約語とオブジェクト名の変換 RDBMS 予約語数 エスケープ文字 大文字／小文字変換 Oracle Database 110 ダブルクォーテーション 大文字に変換 PostgreSQL 101 ダブルクォーテーション 小文字に変換 MySQL 264 バッククォート デフォルトはプラットフォームに依存 Linux では大文字／小文字は区別される SQL Server 185 ダブルクォーテーション 名前は大文字・小文字を維持、SQL では無視 mysql> CREATE TABLE condition(id INT PRIMARY KEY, category TEXT); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'condition(id INT PRIMARY KEY, category TEXT)' at line 1 ✓予約語を使ったテーブル作成
13. 関数 © 2024 Hewlett Packard Enterprise Development LP 13
14. GREATEST（最大値） / LEAST（最小値） 関数 © 2024 Hewlett Packard Enterprise Development LP 14 ✓実行する SQL 文 SELECT GREATEST(1, NULL, 3) val1, LEAST(4, NULL, 6) val2; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database NULL NULL PostgreSQL 3 4 MySQL NULL NULL SQL Server 3 4 SQL Server 2022～ Spanner NULL NULL ✓実行結果
15. MOD（剰余） 関数 © 2024 Hewlett Packard Enterprise Development LP 15 ✓実行する SQL 文 SELECT MOD(3, 0); RDBMS 実行結果 備考 Oracle Database 3 PostgreSQL orafce 拡張モジュールには互換 mod 関数がある PostgreSQL Div/0 Error MySQL NULL sql_mode システム変数に依存、デフォルトでは WARNING(1365) 出力 SQL Server Div/0 Error % 演算子で代替 Spanner Div/0 Error SQLite NULL ✓実行結果
16. SUBSTR（部分文字列） 関数 © 2024 Hewlett Packard Enterprise Development LP 16 ✓実行する SQL 文 RDBMS 実行結果 v1 実行結果 v2 実行結果 v3 備考 Oracle Database ABC ABC CD 開始番号 0 は 1 とみなす PostgreSQL AB Error '' MySQL '' BCD CD SQL Server AB ABC '' SUBSTRING 関数で代替 Spanner ABC Error CD SQLite AB ABC CD SELECT SUBSTR('ABCD', 0, 3) v1, SUBSTR('ABCD', 1.5, 3) v2, SUBSTR('ABCD', -2, 3) v3; ✓実行結果
17. LENGTH（文字列長） 関数 © 2024 Hewlett Packard Enterprise Development LP 17 ✓実行する SQL 文（UTF-8） RDBMS 実行結果 v1 実行結果 v2 備考 Oracle Database 1 1 PostgreSQL 1 1 MySQL 3 4 バイト数で取得、文字数は CHARACTER_LENGTH SQL Server 1 2 LEN 関数で代替、文字リテラルは「N'漢'」指定 Spanner 1 1 SQLite 1 1 SELECT LENGTH('漢') v1, LENGTH('𠮷') v2; ✓実行結果
18. CURRENT_TIMESTAMP（現在時刻） 関数 © 2024 Hewlett Packard Enterprise Development LP 18 ✓実行する SQL 文 RDBMS 実行結果 タイムゾーン 備考 Oracle Database クエリ開始時刻 含む PostgreSQL トランザクション開始時刻 含む MySQL クエリ開始時刻 含まない SET TIME 文で変更可能 SQL Server クエリ開始時刻 含まない Spanner クエリ開始時刻 含む SQLite クエリ開始時刻 含まない UTC、秒までの精度 SELECT CURRENT_TIMESTAMP; ✓実行結果
19. 曜日の番号 関数 © 2024 Hewlett Packard Enterprise Development LP 19 ✓曜日番号を取得する関数 RDBMS 関数名 備考 Oracle Database TO_CHAR PostgreSQL EXTRACT DOW, ISODOW の指定あり MySQL DAYOFWEEK SQL Server DATEPART SET DATEFIRST 文で定義変更可能 ０ から始まる １ から始まる 備考 日曜日から始まる PostgreSQL (DOW) SQLite (%w) Oracle Database, MySQL SQL Server, Spanner Firebird, Snowflake (default) 月曜日から始まる PostgreSQL (ISODOW), SQLite (%u) ✓実行結果
20. UPPER （大文字変換） / LOWER （小文字変換） 関数 © 2024 Hewlett Packard Enterprise Development LP 20 ✓実行する SQL 文 SELECT UPPER('aｄ') val1, LOWER('AＤ') val2; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database AＤ aｄ PostgreSQL Aｄ aＤ LOCALE=C の場合、全角文字は変換されない LOCALE=ja_JPの場合、全角文字も変換される MySQL AＤ aｄ SQL Server AＤ aｄ Spanner AＤ aｄ SQLite Aｄ aＤ 全角文字は変換されない ✓実行結果
21. データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 21
22. 四則演算 データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 22 ✓実行する SQL 文 SELECT 1/3 + 1/3 + 1/3 val1, 1/3*3 val2; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database 1 1 PostgreSQL 0 0 MySQL 1.0000 1.0000 SQL Server 0 0 Spanner 1 1 SQLite 0 0 ✓実行結果
23. 文字列結合 データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 23 ✓実行する SQL 文 SELECT 'A' || NULL || 'B' val1, CONCAT('A', NULL, 'B') val2; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database AB AB PostgreSQL NULL AB MySQL NULL NULL sql_mode=PIPES_AS_CONCAT が必要 SQL Server NULL AB 演算子は + を使用 Spanner NULL NULL Operands of || cannot be literal NULL SQLite NULL AB ✓実行結果
24. 暗黙の型変換 データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 24 ✓実行する SQL 文 SELECT '123' + '456'; SELECT '123' + '4YZ'; RDBMS '123' + '456’ '123' + '4YZ' 備考 Oracle Database 579 Error PostgreSQL Error Error MySQL 579 127 WARNING (1292) 発生 SQL Server '123456' '1234YZ' + は文字列結合演算子 Spanner Error Error SQLite 579 127 ✓実行結果
25. CHAR 型と VARCHAR 型 データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 25 CREATE TABLE table1(column1 CHAR(10), column2 VARCHAR(10)); INSERT INTO table1 VALUES ('ABC△', 'DEF△'); -- △はスペース SELECT LENGTH(column1) val1, LENGTH(column2) val2 FROM table1; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database 10 4 VARCHAR2 型を使用 PostgreSQL 3 4 MySQL 3 4 SQL Server 3 3 LEN 関数で代替 SQLite 4 4 ✓実行結果 ✓実行する SQL 文
26. 長さゼロの文字列 データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 26 ✓実行する SQL 文 SELECT LENGTH('') val1, '' IS NULL val2; RDBMS 実行結果 val1 実行結果 val2 備考 Oracle Database NULL TRUE PostgreSQL 0 FALSE MySQL 0 0 SQL Server 0 0 LEN, ISNULL 関数を使用 Spanner 0 FALSE SQLite 0 0 ✓実行結果
27. 文字エンコード データ型と演算子 © 2024 Hewlett Packard Enterprise Development LP 27 ✓異字体シーケンス（Ideographic Variation Sequence） ✓例は「東京都葛飾区」と「奈良県葛󠄀城市」の字体 ✓エンコードは UTF8 でも扱えるバイト数が異なる ✓標準の文字コードに異字体セレクター（Variation Selectors）を追加すると、UTF-8 が 7 バイト～ 8 バイトに RDBMS 対応状況 備考 Oracle Database UCA1210_JAPANESE_IVS ロケールで対応 Oracle Database 23ai ～ PostgreSQL 対応無し MySQL 対応無し SQL Server _VSS 指定の照合順序で区別可能 文字 UCS UTF-8 備考 葛 U+845B + U+E0100 E8 91 9B F3 A0 84 81 葛飾区 葛󠄀 U+845B + U+E0101 E8 91 9B F3 A0 84 80 葛󠄀城市
28. まとめ © 2024 Hewlett Packard Enterprise Development LP 28
29. © 2024 Hewlett Packard Enterprise Development LP ✓「SQL 文が動く」 ≠ 「SQL 文の結果が同じ」 ✓境界値（0, -1）や例外値（NULL）の取り扱いが異なる場合があります。 ✓単位（バイト、文字数）が異なる場合があります。 ✓みんなちがって、みんないい まとめ 29
30. THANK YOU Mail : noriyoshi.shinoda@hpe.com X(Twitter) : @nori_shinoda Qiita : @plusultra © 2024 Hewlett Packard Enterprise Development LP 30