"""卷五原生对照，使用 Python 3 和 duckdb==1.4.4。参数为可写的工作目录。"""

from pathlib import Path
import sys

import duckdb


work = Path(sys.argv[1])
work.mkdir(parents=True, exist_ok=True)
con = duckdb.connect()
con.execute("SET threads = 1")
con.execute("""
    CREATE TABLE clustered AS
    SELECT i::BIGINT AS id, (i // 100000)::BIGINT AS k
    FROM range(1000000) AS r(i)
""")
con.execute("""
    CREATE TABLE mixed AS
    SELECT i::BIGINT AS id, (i % 10)::BIGINT AS k
    FROM range(1000000) AS r(i)
""")

for table in ("clustered", "mixed"):
    path = str(work / (table + ".parquet"))
    con.execute(f"COPY {table} TO ? (FORMAT PARQUET, ROW_GROUP_SIZE 100000)", [path])
    groups, candidates = con.execute("""
        SELECT count(*), count(*) FILTER (
            WHERE try_cast(stats_min_value AS BIGINT) <= 4
              AND try_cast(stats_max_value AS BIGINT) >= 4
        )
        FROM parquet_metadata(?)
        WHERE path_in_schema = 'k'
    """, [path]).fetchone()
    matches = con.execute("""
        SELECT count(*) FROM read_parquet(?) WHERE k = 4
    """, [path]).fetchone()[0]
    assert matches == 100000
    assert candidates < groups if table == "clustered" else candidates == groups
    print(table, "row_groups", groups, "minmax_candidates", candidates, "matching_rows", matches)

equality, null_safe = con.execute("""
    WITH a(k) AS (VALUES (1), (1), (NULL)),
         b(k) AS (VALUES (1), (1), (NULL))
    SELECT
        (SELECT count(*) FROM a JOIN b ON a.k = b.k),
        (SELECT count(*) FROM a JOIN b ON a.k IS NOT DISTINCT FROM b.k)
""").fetchone()
assert (equality, null_safe) == (4, 5)
print("join rows", equality, "null-safe join rows", null_safe)
