Ответы на запрос Q9 TPC-H s1 не соответствуют каноническим

[iddqdex]

Запрос:

$z1_12 = 1;
$p = (select p_partkey, p_name
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/part`
where p_name like '%green%');
$j1 = (select ps_partkey, ps_suppkey, ps_supplycost
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/partsupp` as ps
join $p as p
on ps.ps_partkey = p.p_partkey);
$j2 = (select l_suppkey, l_partkey, l_orderkey, l_extendedprice, l_discount, ps_supplycost, l_quantity
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/lineitem` as l
join $j1 as j
on l.l_suppkey = j.ps_suppkey AND l.l_partkey = j.ps_partkey);
$j3 = (select l_orderkey, s_nationkey, l_extendedprice, l_discount, ps_supplycost, l_quantity
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/supplier` as s
join $j2 as j
on j.l_suppkey = s.s_suppkey);
$j4 = (select o_orderdate, l_extendedprice, l_discount, ps_supplycost, l_quantity, s_nationkey
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/orders` as o
join $j3 as j
on o.o_orderkey = j.l_orderkey);
$j5 = (select n_name, o_orderdate, l_extendedprice, l_discount, ps_supplycost, l_quantity
from
    `/olap-testing-sas-daily/kikimr/olap/benchmark/dt64/column/tpch/s1/nation` as n
join $j4 as j
on j.s_nationkey = n.n_nationkey
);
$profit = (select
    n_name as nation,
    DateTime::GetYear(cast(o_orderdate as timestamp)) as o_year,
    l_extendedprice * ($z1_12 - l_discount) - ps_supplycost * l_quantity as amount
from $j5);
select
    nation,
    o_year,
    sum(amount) as sum_profit
from $profit
group by
    nation,
    o_year
order by
    nation,
    o_year desc;

Каноничный ответ:

| nation  | year | sum_profit        |
| ALGERIA | 1998 | 31342867.24+-0.01 |

Наш ответ:

| nation  | year | sum_profit    |
| ALGERIA | 1998 | 27136900.1803 |

Канонизация результатов

[Hor911]

Cloud CH returns the same result as YDB

https://clickhouse.com/docs/en/getting-started/example-datasets/tpch

[Hor911]

DuckDB agrees

https://duckdb.org/docs/extensions/tpch.html