Zenn
Open11

DuckDBのおもしろ仕様メモ

DuckDB
ktz_aliasktz_alias

ケツカンマ

https://duckdb.org/docs/sql/query_syntax/select#syntax

  • ドキュメントに明示的な記述はなく、syntaxで判断できる程度ではあるが、SELECTの最終要素の最後にカンマおいても怒られない。
  • 先頭カンマかケツカンマかで第三次世界大戦起きそうな勢いあるので、この仕様はQOL高い。
/* これOK */
select 1, 2, 3,
ktz_aliasktz_alias

スカラ問い合わせ(SELECT区でのサブクエリ)

以下のように記述した場合、普通（？）のRDBであれば、サブクエリが複数行返すためエラーにされる。

/* generate_seriesは引数で指定した範囲の数値を返す表関数 */
select (select * from generate_series(1, 10))

一方、DuckDBはサブクエリの先頭行を返してくるという困ったちゃん。

> select (select * from generate_series(1, 10));
┌────────────────────────────────────────┐
│ (SELECT * FROM generate_series(1, 10)) │
│                 int64                  │
├────────────────────────────────────────┤
│                                      1 │
└────────────────────────────────────────┘

本来必要だったWHEREを描き忘れてた場合にバグだと気付けなくなる困ったちゃん。
せめて、Nested-Arrayで返してくれれば、理解もできるのだけど・・・。

って思ってたんだけど、以下のようにすれば、Nested-Arrayで返してくれるのでQOLをなんとか維持できた。[1]

/* サブクエリのselect句で集約関数使いまとめる */
> select (select list(id) from generate_series(1, 10) t(id));
┌────────────────────────────────────────────────────────┐
│ (SELECT list(id) FROM generate_series(1, 10) AS t(id)) │
│                        int64[]                         │
├────────────────────────────────────────────────────────┤
│ [1, 2, 3, 4, 5, 6, 7, 8, 9, 10]                        │
└────────────────────────────────────────────────────────┘

ちなみにFROM句ではなくSELECT句でgenerate_seriesを使った場合はNested-Arrayを返すのでご心配なく。

> select (select generate_series(1, 10));
┌─────────────────────────────────┐
│ (SELECT generate_series(1, 10)) │
│             int64[]             │
├─────────────────────────────────┤
│ [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] │
└─────────────────────────────────┘
脚注

  1. バグの存在に気づけなくなってしまう問題からは目を背けつつ・・・ ↩︎

ktz_aliasktz_alias

INSERT文のBY NAMEオプション

https://duckdb.org/docs/sql/statements/insert#insert-into--by-name

普通（？）のRDBで以下のように書く場合、列の位置の型を合わせないとエラーになる。

insert into foo
select x, y, z from bar

なので、INSERTには列名を明示しましょうってなる。

insert into foo (x, y, z)
select x, y, z from bar

一方DuckDBではBY NAMEオプション使うことで、SELECT句で指定した名前で自動マッピングしてくれる。

くれるのだが・・・、エイリアス付けると、エイリアス付きの名前でのマッピングを行っちゃう困ったちゃん。[1]

/* FOOテーブルのb.x, b.y, b.zという列名でのマッピングを試みてしまう */
insert into foo by name
select b.x, b.y, b.z from bar b

以下のようにすれば通るが、エイリアスを多用する身としては、それならいっそINSERTで列名明示すればいいじゃんと思う今日この頃。

insert into foo by name
select b.x as x, b.y as y, b.z as z from bar b
脚注

  1. 擁護すると、構造型を列として定義できるため致し方ない ↩︎

ktz_aliasktz_alias

unnestをメソッドチェインで使用する際の注意点

unnest関数を使用することで、list, array, structのような構造化された列を行に展開してくれる。

https://duckdb.org/docs/sql/query_syntax/unnest

また、duckdbは、関数をメソッドチェインで記述できる。

https://duckdb.org/docs/sql/functions/overview#function-chaining-via-the-dot-operator

これを組み合わせて、以下のように記述すると、items列を展開できないとされてエラー扱いされてしまう。

select t.items.unnest() from t

一方上記を、以下のように書き換えると問題なく表示される。

select unnest(t.items) from t

メソッドチェインはapply関数の後のような、2番目の関数に対して使うのが心の平穏を保てそう。

ktz_aliasktz_alias

ﾂｲ....Xで流れてきた、PostgreSQLの基数変換

https://qiita.com/fujii_masao/items/f418e4d4072b965849f7

DuckDBでもチャレンジ (1.0.0時点)

10進数 -> 2進数

SELECT 33::bit(16);

結果はパースエラー

Parser Error: Type BIT does not support any modifiers!

DuckDBは幅指定のBIT型 (BITSTRING型)をサポートしていない模様

以下の記述なら、形だけでも2進数にはなる

select printf('%b', 37);
┌──────────────────┐
│ printf('%b', 37) │
╞══════════════════╡
│ 100101           │
└──────────────────┘

2進数 → 10進数

SELECT '0000000000100001'::bit(16)::int;

幅指定のBIT型型はエラーになるが、以下のように幅指定を除けば10進数になる模様

SELECT '0000000000100001'::bit::int;
┌──────────────────────────────────────────────────┐
│ CAST(CAST('0000000000100001' AS BIT) AS INTEGER) │
╞══════════════════════════════════════════════════╡
│                                               33 │
└──────────────────────────────────────────────────┘

10進数 -> 16進数

SELECT to_hex(33);
┌────────────┐
│ to_hex(33) │
╞════════════╡
│ 21         │
└────────────┘

これは、DuckDBでも通る

16進数 → 10進数

SELECT 'x0021'::bit;
Conversion Error: Invalid character encountered in string -> bit conversion: 'x'

変換エラーになった。
x始まりのリテラルダメっぽい。

該当する方法も見つからず。むね〜ん。

追記

16進数 → 10進数について以下のコメントをいただきました。

https://twitter.com/KoizumiJp/status/1807007588963450882

SELECT '0x21'::int; 
┌─────────────────────────┐
│ CAST('0x21' AS INTEGER) │
╞═════════════════════════╡
│                      33 │
└─────────────────────────┘

期待通りの結果が得られました。

ktz_aliasktz_alias

JOINに関する論理プラン

全く聞いたことないことばかりのものが多かったのでChat GPT大先生にお伺いを立てた。

LOGICAL_JOIN

Joinに関する論理プランの親クラス
duckdb::LogicalJoin

LOGICAL_DELIM_JOIN

デリミティッド外部結合 (というものらしい)
左外部結合の最適化版
SQL構文木からLOGICAL_COMPARISON_JOINなプランが作成されたのち最適化フェーズでLOGICAL_DELIM_JOINに差し替えられてそう。
duckdb::LogicalComparisonJoin

  • 左側のテーブルの結合キーが少ない ( = カーディナリティが低い)場合に選択されるプラン
    • 左側のテーブルをグループ化してJoinの総ループ回数を減らす
    • 低カーディナリティゆえ、ハッシュ結合よりも効果的にハッシュ化可能
  • 左側のテーブルに比べて右側のテーブルのレコード数が少ない場合にも選択される
    • 右側のテーブルでハッシュテーブルを作ることで総ループ回数を減らす
  • スカラ問い合わせの際、サブクエリ側のレコード数が少ない場合に選択される事象を確認
    • ハッシュ化がいけると判断された模様

LOGICAL_COMPARISON_JOIN

比較演算子（<, <=, >, >=）を使用したJoinに選択されるプラン
duckdb::LogicalComparisonJoin

二重ループを総当たりで回す (Nested Loop Join)
たいていこれが選択されるっぽい

LOGICAL_ANY_JOIN

ORを使用したJoinに選択されるプラン
duckdb::LogicalAnyJoin

LOGICAL_CROSS_PRODUCT

直積 (cross join)を使用した際に選択されるプラン
duckdb::LogicalCrossProduct

LOGICAL_POSITIONAL_JOIN

positional joinを使用した際に選択されるプラン
duckdb::LogicalPositionalJoin

LOGICAL_ASOF_JOIN

asof joinを使用した際に選択されるプラン

LOGICAL_DEPENDENT_JOIN

相関サブクエリのように他のテーブルの値に依存してJoin使用した際に選択されるプラン。
duckdb::LogicalDependentJoin

二重ループを総当たりで回す (Nested Loop Join)

  • lateral joinで使用されやすい？

ただし以下の場合、LOGICAL_DELIM_JOINが選択されることもある。

  • 左右のレコード数に大きな開きがある
  • 左側のカーディナリティが低い

最適化フェーズで事前計算の結果、相関サブクエリが非相関サブクエリにできると判断された場合、再起的にフラットなJoinに変換される場合もあるっぽい。
ただしlateralが明示された場合は、極力duckdb::LogicalDependentJoinに振ってるっぽい。

Joinの論理プランの決定タイミング

TableRef (FORM)のステージで大まかに型が分類され、Joinのステージで詳細化される。

ktz_aliasktz_alias

LIMIT句

https://duckdb.org/docs/sql/query_syntax/limit

ドキュメントでは、LIMITに対して整数値を指定した例のみが表記されている。
実は整数値以外の指定も可能。
そんなアンドキュメントな機能のメモ（そのうち記載されるかも）。

Percentage LIMIT

以下のように、数値%として割合で指定できる。

PREAPRE q AS SELECT * FROM range(1, 20) LIMIT 20%;

プレースホルダを介して指定したい場合は以下のようにする。

/* %をプレースホルダの外に出し数値だけ渡す */
PREAPRE q AS SELECT * FROM range(1, 20) LIMIT $lim%;
EXECUTE q(lim := 20);

割合の対象は元のクエリのレコードに対して。
OFFSETでカーソルを動かしたとしても。

LIMIT NULL

NULLを指定するとリミット指定なしとして扱われる。
プレースホルダを用意し、NULLを渡した場合も同様

PREAPRE q AS SELECT * FROM range(1, 20) LIMIT $lim;
EXECUTE q(lim := NULL);

LIMIT $lim%とした上で、NULLを渡した場合も同様に全件取得。

LIMIT NULLはPostgreSQLでサポートされているため、その関係で入っているのかも（なんとなく）

https://www.postgresql.jp/document/16/html/queries-limit.html

LIMIT ALL

ALLを指定するとリミット指定なしとして扱われる。

SELECT * FROM range(1, 20) LIMIT ALL;

ただしプレースホルダを介してALLを渡すと実行時エラーとなる。
代わりにNULLを渡すよろし

LIMIT ALLもまた同様にPostgreSQLでサポートされている機能。

ktz_aliasktz_alias

Merk Join

Mark JoinはDuckDBのクエリプラン内で使用される結合方式であり、SQL文で直接指定することはできない。
主にエンジン内部で相関サブクエリを効率化するために利用される。

通常、相関サブクエリをそのまま個別に評価すると非効率
Mark JoinはEXISTSやNOT EXISTSのようにブール値を返す相関サブクエリの部分的な評価結果をキャッシュし、親リレーションに対する再評価の際にそのキャッシュを使用することで効率を向上させる。

  • まず、相関サブクエリの評価結果は依存する列以外で処理され、結果がマーク（ブール値）として保存される。
  • その後、マークされた結果と依存する列を組み合わせて再評価を行う。

ただし、単純なEXISTSクエリであれば、SEMI JOIN、NOT EXISTSの場合はANTI JOINが結合種として選ばれることが一般的。
Mark Joinが採用されるのは、例えば、EXISTS句内でさらに別のEXISTSを使用したり、Lateral Joinの内部でEXISTSを使用したりする複雑なケース。

手元の環境では、CTE（共通テーブル式）の結果をLateral Join内のEXISTS句で使用することでMark Joinが現れた。

Merk Join

ktz_aliasktz_alias

LISTのunnestの怪しい挙動

Case 1: 異なるLISTを一度にunnestする

以下の構造のJSONがあるとして

[
    {
        "data": {
            "k": 42,
            "v": {
                "v1": "42", "v2": "xyz"
            }
        },
        "id": 99, "code": "99"
    },
    {
        "data":{
            "k": 0, "v": null
        },
        "id": 101, "code": null
    }
]

以下のクエリを流すと

select k, v1, unnest(v1), v2, v3, unnest(v3)
from read_json( '_dataset-examples/list_sample.json') t(k, v1, v2, v3)

以下の結果となる。

k
varchar		 v1
int64[]		 unnest(v1)
int64		 v2
int64[]		 v3
int64[]		 unnest(v3)
int64
odd [1, 3, 5, 7] 1 [2, 4, 6] [10, NULL, 30] 10
odd [1, 3, 5, 7] 3 [2, 4, 6] [10, NULL, 30]
odd [1, 3, 5, 7] 5 [2, 4, 6] [10, NULL, 30] 30
odd [1, 3, 5, 7] 7 [2, 4, 6] [10, NULL, 30]
even [2, 8] 2 [20] 20
even [2, 8] 8 [20]

case 2: NULLのunnestとの組み合わせ

以下のクエリを流すと

select unnest(j.data_1), unnest(j.data_2.code)
from read_json( '_dataset-examples/struct_sample_2.json') t(j)
k
int64		 v
struct(v1 varchar, v2 varchar)		 unnest(j.data_2.code)
varchar
42 {'v1': 42, 'v2': xyz} 99

となる。
二行目のdata_2.codeがNULLのため、たとえunnest(j.data_1)から結果が取り出せてても、行ごと亡くされる。

data_2.codeをNULLではなく空配列にしてもNULLとして扱われてしまう。

https://duckdb.org/docs/sql/query_syntax/unnest.html

ktz_aliasktz_alias

VALUES句

duckdb_cliで

VALUES (1, 2, 3), (4, 5,6);

と入力すると、普通にSELECTとして結果が表示される。

これは、duckdbのパーザが

SELECT * FROM (VALUES (1, 2, 3), (4, 5,6)) as valueslist

に相当する構文木に作り替えているから。

ただし、

(VALUES (1, 2, 3)) CROSS JOIN (VALUES (4, 5,6))

までは面倒見てくれず、

Parser Error: syntax error at or near "cross"
LINE 1: (values (1, 'a')) cross join values (2, 'b');

となるのでご注意あれ。

SELECT * FROM (VALUES (1, 2, 3)) CROSS JOIN (VALUES (4, 5,6))

とすれば問題なく通る。