algeon-datafusion 0.1.2

use std::fs;

use tokio::runtime::Builder as TestRuntimeBuilder;

use super::*;

pub(super) const SQL_SUM_EXPRESSION_QUERY: &str = "
SELECT key, SUM(m00 + m01) AS sum_x00
FROM bench
WHERE payload >= 10
GROUP BY key
ORDER BY sum_x00 DESC, key ASC
LIMIT 2
";

pub(super) const SQL_MIN_MAX_COUNT_QUERY: &str = "
SELECT key, MIN(m00) AS min_m00, MAX(m01) AS max_m01, COUNT(payload) AS count_payload
FROM bench
GROUP BY key
ORDER BY key ASC
LIMIT 2
";

pub(super) const SQL_COUNT_STAR_QUERY: &str = "
SELECT key, SUM(m00 + m01) AS sum_x00, COUNT(*) AS count_rows
FROM bench
GROUP BY key
ORDER BY key ASC
LIMIT 2
";

pub(super) const SQL_COUNT_ONLY_QUERY: &str = "
SELECT COUNT(*) AS count_rows
FROM bench
";

pub(super) const SQL_LIST_GLOBAL_COUNT_QUERY: &str = "
SELECT COUNT(items) AS item_rows, COUNT(pairs) AS pair_rows, COUNT(*) AS all_rows
FROM bench
";

pub(super) const SQL_LIST_GLOBAL_COUNT_FILTERED_QUERY: &str = "
SELECT COUNT(items) AS item_rows, COUNT(pairs) AS pair_rows
FROM bench
WHERE key >= 3
";

// Runtime-filtered empty ROLLUP regression contract.
// The parquet is nonempty; the filter selects zero rows. CPU DataFusion still
// emits the grand-total row; native must match and stay on the native path.
pub(super) const SQL_FILTERED_EMPTY_ROLLUP_SUM_QUERY: &str = "
SELECT key, SUM(payload) AS s
FROM bench
WHERE key > 100000
GROUP BY ROLLUP(key)
ORDER BY key
";

pub(super) const SQL_AVG_AGGREGATE_QUERY: &str = "
SELECT key, AVG(m00) AS avg_m00
FROM bench
WHERE payload >= 10
GROUP BY key
ORDER BY avg_m00 DESC, key ASC
LIMIT 3
";

pub(super) const SQL_MEDIAN_AGGREGATE_QUERY: &str = "
SELECT key, MEDIAN(m00) AS median_m00
FROM bench
WHERE payload >= 10
GROUP BY key
ORDER BY median_m00 DESC, key ASC
LIMIT 3
";

pub(super) const SQL_FILTERED_AGGREGATE_QUERY: &str = "
SELECT key, SUM(m00) FILTER (WHERE payload >= 10) AS sum_m00
FROM bench
WHERE payload >= 10
GROUP BY key
ORDER BY sum_m00 DESC, key ASC
LIMIT 2
";

pub(super) const SQL_MIXED_FILTERED_AGGREGATES_QUERY: &str = "
SELECT
    key,
    SUM(m00) AS total_m00,
    SUM(m00) FILTER (WHERE payload >= 10) AS filtered_sum_m00,
    COUNT(payload) FILTER (WHERE payload >= 10) AS filtered_count_payload,
    COUNT(*) FILTER (WHERE payload >= 10) AS filtered_count_rows,
    AVG(m00) FILTER (WHERE payload >= 10) AS filtered_avg_m00
FROM bench
GROUP BY key
ORDER BY key ASC
LIMIT 3
";

pub(super) const SQL_SELECTIVE_READER_PUSHDOWN_QUERY: &str = "
SELECT key, SUM(m00 + m01) AS sum_x00
FROM bench
WHERE payload >= 900
GROUP BY key
ORDER BY key ASC
LIMIT 8
";

pub(super) const SQL_DECIMAL32_READER_PUSHDOWN_QUERY: &str = "
SELECT key, SUM(m00 + m01) AS sum_x00
FROM bench
WHERE payload >= 9.00
GROUP BY key
ORDER BY key ASC
LIMIT 8
";

pub(super) const SQL_DECIMAL32_EQUALITY_READER_PUSHDOWN_QUERY: &str = "
SELECT key, SUM(m00 + m01) AS sum_x00
FROM bench
WHERE payload = 9.00
GROUP BY key
ORDER BY key ASC
LIMIT 8
";

pub(super) const SQL_BARE_BOOLEAN_FILTER_QUERY: &str = "
SELECT COUNT(*) AS count_rows
FROM bench
WHERE flag
";

pub(super) const SQL_AVG_BY_KEY_NO_FILTER_QUERY: &str = "
SELECT key, AVG(m00) AS avg_m00
FROM bench
GROUP BY key
ORDER BY key ASC
LIMIT 3
";

pub(super) const SQL_AVG_ORDER_BY_VALUE_QUERY: &str = "
SELECT key, AVG(m00) AS avg_m00
FROM bench
GROUP BY key
ORDER BY avg_m00 DESC, key ASC
LIMIT 3
";

pub(super) const SQL_UNSUPPORTED_APPROX_DISTINCT_QUERY: &str = "
SELECT APPROX_DISTINCT(payload) AS approx_payload
FROM bench
";

pub(super) const SQL_Q6_SHAPED_QUERY: &str = "
SELECT
    SUM(l_extendedprice * l_discount) AS revenue
FROM bench
WHERE l_shipdate >= DATE '1994-01-01'
  AND l_shipdate < DATE '1994-01-01' + INTERVAL '1' YEAR
  AND l_discount BETWEEN 0.05 AND 0.07
  AND l_quantity < 24
";

pub(super) const SQL_GLOBAL_DECIMAL_AVG_QUERY: &str = "
SELECT
    AVG(l_discount) AS avg_discount
FROM bench
WHERE l_shipdate >= DATE '1994-01-01'
  AND l_shipdate < DATE '1995-01-01'
";

pub(super) const SQL_SCALAR_EXPRESSIONS_QUERY: &str = "
SELECT status_prefix, ship_year, ship_month, status_label, SUM(order_doubled) AS sum_doubled
FROM (
    SELECT
        SUBSTRING(status FROM 1 FOR 1) AS status_prefix,
        EXTRACT(YEAR FROM shipdate) AS ship_year,
        shipdate + INTERVAL '1' MONTH AS ship_month,
        CASE WHEN status IN ('O', 'P') THEN 'open' ELSE 'other' END AS status_label,
        orderkey * 2 AS order_doubled
    FROM bench
    WHERE status LIKE 'O%'
      AND status IS NOT NULL
      AND orderkey IN (1, 3)
      AND orderkey * 2 >= 2
) scalar_expression
GROUP BY status_prefix, ship_year, ship_month, status_label
ORDER BY status_prefix ASC, ship_year ASC, ship_month ASC, status_label ASC
LIMIT 10
";

pub(super) const SQL_NULLABLE_SCALAR_EXPRESSIONS_QUERY: &str = "
SELECT
    status_prefix,
    ship_year,
    ship_month,
    status_label,
    not_in_label,
    like_match,
    not_like_match,
    status_is_null,
    status_is_not_null,
    SUM(order_doubled) AS sum_doubled
FROM (
    SELECT
        SUBSTRING(status FROM 1 FOR 1) AS status_prefix,
        EXTRACT(YEAR FROM shipdate) AS ship_year,
        shipdate + INTERVAL '1' MONTH AS ship_month,
        CASE
            WHEN status IS NULL THEN 'missing'
            WHEN status IN ('O', 'P') THEN 'listed'
            WHEN status NOT LIKE 'O%' THEN 'not_open'
            ELSE 'other'
        END AS status_label,
        CASE WHEN status NOT IN ('F') THEN 'not_f' ELSE 'f_or_null' END AS not_in_label,
        status LIKE 'O%' AS like_match,
        status NOT LIKE 'O%' AS not_like_match,
        status IS NULL AS status_is_null,
        status IS NOT NULL AS status_is_not_null,
        orderkey * 2 AS order_doubled
    FROM bench
) scalar_expression
GROUP BY
    status_prefix,
    ship_year,
    ship_month,
    status_label,
    not_in_label,
    like_match,
    not_like_match,
    status_is_null,
    status_is_not_null
ORDER BY
    status_is_null ASC,
    status_prefix ASC NULLS FIRST,
    ship_year ASC NULLS FIRST,
    ship_month ASC NULLS FIRST,
    status_label ASC,
    not_in_label ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_LIKE_QUERY: &str = "
SELECT row_id, like_match, not_like_match, COUNT(*) AS row_count
FROM (
    SELECT row_id, status LIKE 'O%' AS like_match, status NOT LIKE 'O%' AS not_like_match
    FROM bench
) null_semantics
GROUP BY row_id, like_match, not_like_match
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_ILIKE_QUERY: &str = "
SELECT row_id, ilike_match, not_ilike_match, COUNT(*) AS row_count
FROM (
    SELECT row_id, status ILIKE 'o%' AS ilike_match, status NOT ILIKE 'r%' AS not_ilike_match
    FROM bench
) null_semantics
GROUP BY row_id, ilike_match, not_ilike_match
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_SUBSTRING_QUERY: &str = "
SELECT row_id, status_prefix, COUNT(*) AS row_count
FROM (
    SELECT row_id, SUBSTRING(status FROM 1 FOR 1) AS status_prefix
    FROM bench
) null_semantics
GROUP BY row_id, status_prefix
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_STRING_CASE_QUERY: &str = "
SELECT row_id, status_lower, status_upper, COUNT(*) AS row_count
FROM (
    SELECT row_id, LOWER(status) AS status_lower, UPPER(status) AS status_upper
    FROM bench
) null_semantics
GROUP BY row_id, status_lower, status_upper
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_STRING_PREDICATES_QUERY: &str = "
SELECT row_id, contains_o, starts_o, ends_x, contains_empty, starts_empty, ends_empty, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        contains(status, 'O') AS contains_o,
        starts_with(status, 'O') AS starts_o,
        ends_with(status, 'X') AS ends_x,
        contains(CASE WHEN row_id = 6 THEN '' ELSE status END, '') AS contains_empty,
        starts_with(CASE WHEN row_id = 6 THEN '' ELSE status END, '') AS starts_empty,
        ends_with(CASE WHEN row_id = 6 THEN '' ELSE status END, '') AS ends_empty
    FROM bench
) null_semantics
GROUP BY row_id, contains_o, starts_o, ends_x, contains_empty, starts_empty, ends_empty
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_TRIM_FAMILY_QUERY: &str = "
SELECT row_id, trim_default, ltrim_default, rtrim_default, trim_tab_default, btrim_chars, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        TRIM(CONCAT('  ', status, '  ')) AS trim_default,
        LTRIM(CONCAT('  ', status, '  ')) AS ltrim_default,
        RTRIM(CONCAT('  ', status, '  ')) AS rtrim_default,
        TRIM(CONCAT('\t', status, '\t')) AS trim_tab_default,
        BTRIM(CONCAT('__', status, '__'), '_') AS btrim_chars
    FROM bench
) null_semantics
GROUP BY row_id, trim_default, ltrim_default, rtrim_default, trim_tab_default, btrim_chars
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_STRING_LENGTH_UNITS_QUERY: &str = "
SELECT row_id, character_length(name) AS name_chars, octet_length(name) AS name_bytes
FROM bench
ORDER BY row_id
";

pub(super) const SQL_STRING_LENGTH_FILTER_QUERY: &str = "
SELECT row_id, octet_length(name) AS name_bytes
FROM bench
WHERE octet_length(name) <> character_length(name)
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_TIME_FIELDS_SECONDS_QUERY: &str = "
SELECT
    row_id,
    EXTRACT(HOUR FROM ts_s) AS h,
    EXTRACT(MINUTE FROM ts_s) AS mi,
    EXTRACT(SECOND FROM ts_s) AS s,
    EXTRACT(MILLISECOND FROM ts_s) AS ms,
    EXTRACT(MICROSECOND FROM ts_s) AS us
FROM bench
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_TIME_FIELDS_MILLISECONDS_QUERY: &str = "
SELECT
    row_id,
    EXTRACT(HOUR FROM ts_ms) AS h,
    EXTRACT(MINUTE FROM ts_ms) AS mi,
    EXTRACT(SECOND FROM ts_ms) AS s,
    EXTRACT(MILLISECOND FROM ts_ms) AS ms,
    EXTRACT(MICROSECOND FROM ts_ms) AS us
FROM bench
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_TIME_FIELDS_MICROSECONDS_QUERY: &str = "
SELECT
    row_id,
    EXTRACT(HOUR FROM ts_us) AS h,
    EXTRACT(MINUTE FROM ts_us) AS mi,
    EXTRACT(SECOND FROM ts_us) AS s,
    EXTRACT(MILLISECOND FROM ts_us) AS ms,
    EXTRACT(MICROSECOND FROM ts_us) AS us
FROM bench
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_TIME_FIELDS_NANOSECONDS_QUERY: &str = "
SELECT
    row_id,
    EXTRACT(HOUR FROM ts_ns) AS h,
    EXTRACT(MINUTE FROM ts_ns) AS mi,
    EXTRACT(SECOND FROM ts_ns) AS s,
    EXTRACT(MILLISECOND FROM ts_ns) AS ms,
    EXTRACT(MICROSECOND FROM ts_ns) AS us
FROM bench
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_MINUTE_FILTER_QUERY: &str = "
SELECT row_id, EXTRACT(SECOND FROM ts_us) AS s
FROM bench
WHERE EXTRACT(MINUTE FROM ts_us) = 59
ORDER BY row_id
";

pub(super) const SQL_EXTRACT_AGGREGATE_INPUT_QUERY: &str = "
SELECT SUM(EXTRACT(SECOND FROM ts_ms) + 1) AS second_sum, MAX(EXTRACT(MICROSECOND FROM ts_ns)) AS max_us
FROM bench
";

pub(super) const SQL_EXTRACT_MINUTE_FROM_INTEGER_CAST_QUERY: &str = "
SELECT row_id, EXTRACT(MINUTE FROM to_timestamp_seconds(epoch_s)) AS m
FROM bench
ORDER BY row_id
";

pub(super) const SQL_NULL_SEMANTICS_IN_LIST_QUERY: &str = "
SELECT row_id, status_in_list, status_not_in_list, COUNT(*) AS row_count
FROM (
    SELECT row_id, status IN ('O', 'P') AS status_in_list, status NOT IN ('F') AS status_not_in_list
    FROM bench
) null_semantics
GROUP BY row_id, status_in_list, status_not_in_list
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_CASE_QUERY: &str = "
SELECT row_id, status_label, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        CASE
            WHEN status IS NULL THEN 'missing'
            WHEN status IN ('O', 'P') THEN 'listed'
            ELSE 'other'
        END AS status_label
    FROM bench
) null_semantics
GROUP BY row_id, status_label
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_CASE_NO_ELSE_QUERY: &str = "
SELECT row_id, open_code, COUNT(*) AS row_count
FROM (
    SELECT row_id, CASE WHEN status = 'O' THEN 1 END AS open_code
    FROM bench
) null_semantics
GROUP BY row_id, open_code
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_EXTRACT_QUERY: &str = "
SELECT row_id, ship_year, ship_month, ship_day, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        EXTRACT(YEAR FROM shipdate) AS ship_year,
        EXTRACT(MONTH FROM shipdate) AS ship_month,
        EXTRACT(DAY FROM shipdate) AS ship_day
    FROM bench
) null_semantics
GROUP BY row_id, ship_year, ship_month, ship_day
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_DATE_TRUNC_FIXED_GRAINS_QUERY: &str = "
SELECT
    row_id,
    trunc_day,
    trunc_hour,
    trunc_minute,
    trunc_second,
    trunc_millisecond,
    trunc_microsecond,
    COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        DATE_TRUNC('day', event_ts) AS trunc_day,
        DATE_TRUNC('hour', event_ts) AS trunc_hour,
        DATE_TRUNC('minute', event_ts) AS trunc_minute,
        DATE_TRUNC('second', event_ts) AS trunc_second,
        DATE_TRUNC('millisecond', event_ts) AS trunc_millisecond,
        DATE_TRUNC('microsecond', event_ts) AS trunc_microsecond
    FROM bench
) date_trunc_scalars
GROUP BY
    row_id,
    trunc_day,
    trunc_hour,
    trunc_minute,
    trunc_second,
    trunc_millisecond,
    trunc_microsecond
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_DATE_TRUNC_MONTH_UNSUPPORTED_QUERY: &str = "
SELECT row_id, trunc_month, COUNT(*) AS row_count
FROM (
    SELECT row_id, DATE_TRUNC('month', event_ts) AS trunc_month
    FROM bench
) date_trunc_month
GROUP BY row_id, trunc_month
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_DATE_TRUNC_YEAR_UNSUPPORTED_QUERY: &str = "
SELECT row_id, trunc_year, COUNT(*) AS row_count
FROM (
    SELECT row_id, DATE_TRUNC('year', event_ts) AS trunc_year
    FROM bench
) date_trunc_year
GROUP BY row_id, trunc_year
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_INTERVAL_QUERY: &str = "
SELECT row_id, shipdate_plus_month, shipdate_minus_day, event_ts_plus_second, event_ts_minus_second, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        shipdate + INTERVAL '1' MONTH AS shipdate_plus_month,
        shipdate - INTERVAL '1' DAY AS shipdate_minus_day,
        event_ts + INTERVAL '1' SECOND AS event_ts_plus_second,
        event_ts - INTERVAL '1' SECOND AS event_ts_minus_second
    FROM bench
) null_semantics
GROUP BY row_id, shipdate_plus_month, shipdate_minus_day, event_ts_plus_second, event_ts_minus_second
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_MODULO_QUERY: &str = "
SELECT row_id, order_mod, COUNT(*) AS row_count
FROM (
    SELECT row_id, orderkey % 2 AS order_mod
    FROM bench
) null_semantics
GROUP BY row_id, order_mod
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_BITWISE_SCALAR_QUERY: &str = "
SELECT row_id, flags_and, flags_or, flags_shift_left, flags_shift_right, flags_grouping, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        orderkey & 3 AS flags_and,
        orderkey | 8 AS flags_or,
        orderkey << 1 AS flags_shift_left,
        orderkey >> 1 AS flags_shift_right,
        CAST((orderkey & 2) >> 1 AS INT) AS flags_grouping
    FROM bench
) bitwise_scalars
GROUP BY row_id, flags_and, flags_or, flags_shift_left, flags_shift_right, flags_grouping
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_SEMANTICS_IS_NULL_QUERY: &str = "
SELECT row_id, status_is_null, status_is_not_null, shipdate_is_null, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        status IS NULL AS status_is_null,
        status IS NOT NULL AS status_is_not_null,
        shipdate IS NULL AS shipdate_is_null
    FROM bench
) null_semantics
GROUP BY row_id, status_is_null, status_is_not_null, shipdate_is_null
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_ROUND_SCALAR_QUERY: &str = "
SELECT row_id, rounded_orderkey, COUNT(*) AS row_count
FROM (
    SELECT row_id, ROUND(CAST(orderkey AS DOUBLE) + 0.5, 0) AS rounded_orderkey
    FROM bench
) round_scalars
GROUP BY row_id, rounded_orderkey
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_ABS_SCALAR_QUERY: &str = "
SELECT row_id, abs_order_delta, COUNT(*) AS row_count
FROM (
    SELECT row_id, ABS(CAST(orderkey AS DOUBLE) - 4.5) AS abs_order_delta
    FROM bench
) abs_scalars
GROUP BY row_id, abs_order_delta
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_SQRT_SCALAR_QUERY: &str = "
SELECT row_id, root_orderkey, COUNT(*) AS row_count
FROM (
    SELECT row_id, SQRT(CAST(orderkey AS DOUBLE)) AS root_orderkey
    FROM bench
) sqrt_scalars
GROUP BY row_id, root_orderkey
ORDER BY row_id ASC
LIMIT 20
";

// `orderkey` is [1, 2, NULL, 4, 5, NULL], so the shifted inputs cover a
// negative argument (LN -> NaN), zero (LN -> -inf), null, and EXP overflow to
// +inf in both Float64 and Float32.
pub(super) const SQL_LN_EXP_SCALAR_QUERY: &str = "
SELECT
    row_id,
    LN(CAST(orderkey AS DOUBLE) - 2.0) AS ln_f64,
    LN(CAST(orderkey AS REAL) - CAST(2 AS REAL)) AS ln_f32,
    EXP(CAST(orderkey AS DOUBLE) * 300.0 - 600.0) AS exp_f64,
    EXP(CAST(orderkey AS REAL) * CAST(30 AS REAL) - CAST(60 AS REAL)) AS exp_f32
FROM bench
ORDER BY row_id ASC
";

pub(super) const SQL_STRING_CONCAT_OPERATOR_QUERY: &str = "
SELECT row_id, status_label, COUNT(*) AS row_count
FROM (
    SELECT row_id, status || '-x' AS status_label
    FROM bench
) concat_operator
GROUP BY row_id, status_label
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULL_AWARE_COMPARISON_QUERY: &str = "
SELECT row_id, status_distinct, status_not_distinct, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        status IS DISTINCT FROM 'O' AS status_distinct,
        status IS NOT DISTINCT FROM 'O' AS status_not_distinct
    FROM bench
) null_aware_comparison
GROUP BY row_id, status_distinct, status_not_distinct
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_NULLIF_SCALAR_QUERY: &str = "
SELECT row_id, nullif_status, nullif_orderkey, nullif_nullable_rhs, COUNT(*) AS row_count
FROM (
    SELECT
        row_id,
        NULLIF(status, 'O') AS nullif_status,
        NULLIF(orderkey, 3) AS nullif_orderkey,
        NULLIF(
            orderkey,
            CASE WHEN row_id IN (1, 4) THEN orderkey ELSE CAST(NULL AS BIGINT) END
        ) AS nullif_nullable_rhs
    FROM bench
) nullif_scalars
GROUP BY row_id, nullif_status, nullif_orderkey, nullif_nullable_rhs
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_INTEGER_TO_TIMESTAMP_CAST_QUERY: &str = "
SELECT row_id, order_ts, COUNT(*) AS row_count
FROM (
    SELECT row_id, CAST(orderkey AS TIMESTAMP) AS order_ts
    FROM bench
) temporal_cast
GROUP BY row_id, order_ts
ORDER BY row_id ASC
LIMIT 20
";

pub(super) const SQL_INVALID_SUBSTRING_ZERO_START_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM 0 FOR 1) AS status_prefix
    FROM bench
) invalid_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_INVALID_SUBSTRING_DYNAMIC_START_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM orderkey FOR 1) AS status_prefix
    FROM bench
) invalid_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_INVALID_SUBSTRING_DYNAMIC_LENGTH_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM 1 FOR orderkey) AS status_prefix
    FROM bench
) invalid_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_INVALID_SUBSTRING_NEGATIVE_LENGTH_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM 1 FOR -1) AS status_prefix
    FROM bench
) invalid_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_SUBSTRING_MAX_START_LENGTH_ONE_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM 2147483647 FOR 1) AS status_prefix
    FROM bench
) boundary_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_INVALID_SUBSTRING_STOP_OVERFLOW_QUERY: &str = "
SELECT status_prefix, COUNT(*) AS count_rows
FROM (
    SELECT SUBSTRING(status FROM 2147483647 FOR 2) AS status_prefix
    FROM bench
) invalid_substring
GROUP BY status_prefix
ORDER BY status_prefix ASC
LIMIT 10
";

pub(super) const SQL_SORT_NULLS_ASC_FIRST_QUERY: &str = "
SELECT group_key, tie_key, AVG(m00) AS avg_m00
FROM bench
GROUP BY group_key, tie_key
ORDER BY avg_m00 ASC NULLS FIRST, tie_key DESC
LIMIT 8
";

pub(super) const SQL_SORT_NULLS_ASC_LAST_QUERY: &str = "
SELECT group_key, tie_key, AVG(m00) AS avg_m00
FROM bench
GROUP BY group_key, tie_key
ORDER BY avg_m00 ASC NULLS LAST, tie_key DESC
LIMIT 8
";

pub(super) const SQL_SORT_NULLS_DESC_FIRST_QUERY: &str = "
SELECT group_key, tie_key, AVG(m00) AS avg_m00
FROM bench
GROUP BY group_key, tie_key
ORDER BY avg_m00 DESC NULLS FIRST, tie_key DESC
LIMIT 8
";

pub(super) const SQL_SORT_NULLS_DESC_LAST_QUERY: &str = "
SELECT group_key, tie_key, AVG(m00) AS avg_m00
FROM bench
GROUP BY group_key, tie_key
ORDER BY avg_m00 DESC NULLS LAST, tie_key DESC
LIMIT 8
";

pub(super) const SQL_SORT_NULLS_TOPK_QUERY: &str = "
SELECT group_key, tie_key, AVG(m00) AS avg_m00
FROM bench
GROUP BY group_key, tie_key
ORDER BY avg_m00 ASC NULLS LAST, tie_key DESC
LIMIT 4
";

pub(super) const SQL_SINGLE_DISTINCT_GROUPED_QUERY: &str = "
SELECT key, COUNT(DISTINCT x) AS distinct_x, SUM(y) AS sum_y, COUNT(y) AS count_y, COUNT(*) AS row_count,
       MIN(y) AS min_y, MAX(y) AS max_y, AVG(y) AS avg_y, AVG(d) AS avg_d
FROM bench
GROUP BY key
ORDER BY key ASC
";

pub(super) const SQL_SINGLE_DISTINCT_GLOBAL_QUERY: &str = "
SELECT COUNT(DISTINCT x) AS distinct_x, SUM(y) AS sum_y, COUNT(y) AS count_y, COUNT(*) AS row_count,
       MIN(y) AS min_y, MAX(y) AS max_y, AVG(y) AS avg_y, AVG(d) AS avg_d
FROM bench
";

pub(super) const SQL_SINGLE_DISTINCT_EMPTY_GLOBAL_QUERY: &str = "
SELECT COUNT(DISTINCT x) AS distinct_x, SUM(y) AS sum_y, COUNT(y) AS count_y, COUNT(*) AS row_count,
       MIN(y) AS min_y, MAX(y) AS max_y, AVG(y) AS avg_y, AVG(d) AS avg_d
FROM bench
WHERE key > 100
";

pub(super) const SQL_EMPTY_JOIN_GLOBAL_COUNT_QUERY: &str = "
SELECT COUNT(l_extendedprice) AS priced, COUNT(l_quantity) AS quantities, SUM(l_extendedprice) AS sum_price
FROM lineitem
JOIN orders ON l_orderkey = o_orderkey
WHERE o_orderpriority = 'NO SUCH PRIORITY'
";

async fn native_runtime_default_sql_list_global_count_matches_cpu() {
    for (name, sql) in [
        ("list-global-count", SQL_LIST_GLOBAL_COUNT_QUERY),
        ("list-global-count-filtered", SQL_LIST_GLOBAL_COUNT_FILTERED_QUERY),
    ] {
        let dir = unique_temp_dir(format!("algeon-datafusion-native-runtime-default-{name}").as_str());
        let path = dir.join("input.parquet");
        write_list_count_input_parquet(&path);

        assert_default_sql_native_parity(&path, sql, None).await;

        fs::remove_dir_all(dir).unwrap();
    }
}

#[test]
fn native_runtime_sql_cases_1() {
    testing::run_named_cases(&[("native_runtime_default_sql_list_global_count_matches_cpu", || {
        TestRuntimeBuilder::new_current_thread()
            .enable_all()
            .build()
            .expect("case runtime")
            .block_on(native_runtime_default_sql_list_global_count_matches_cpu())
    })]);
}