Hello! I am performing the following SQL-Query on a Postgres database.
select
l_returnflag,
l_linestatus,
sum(l_quantity) as sum_qty,
sum(l_extendedprice) as sum_base_price,
sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
avg(l_quantity) as avg_qty,
avg(l_extendedprice) as avg_price,
avg(l_discount) as avg_disc,
count(*) as count_order
from
lineitem
where
l_shipdate <= date ‘1998-12-01’ - interval ‘90’ day
group by
l_returnflag,
l_linestatus
order by
l_returnflag,
l_linestatus;
Everything works as expected. But when I look into the postgres server query store, I see that Dremio is forming my initial SQL-Query into a slightly different one. This is the SQL Query that Dremio performs on my postgres server.
SELECT
“l_returnflag”,
“l_linestatus”,
CAST(
CASE WHEN COUNT(“l_quantity”) = $ 1 THEN $ 2 ELSE CAST(SUM(“l_quantity”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum_qty”,
CAST(
CASE WHEN COUNT(“l_extendedprice”) = $ 3 THEN $ 4 ELSE CAST(SUM(“l_extendedprice”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum_base_price”,
CAST(
CASE WHEN COUNT(“$f4”) = $ 5 THEN $ 6 ELSE CAST(SUM(“$f4”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum_disc_price”,
CAST(
CASE WHEN COUNT(“$f5”) = $ 7 THEN $ 8 ELSE CAST(SUM(“$f5”) AS NUMERIC(38, 6)) END AS NUMERIC(38, 6)
) AS “sum_charge”,
(
CAST(
CASE WHEN COUNT(“l_quantity”) = $ 9 THEN $ 10 ELSE CAST(SUM(“l_quantity”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l_quantity”)
) AS “avg_qty”,
(
CAST(
CASE WHEN COUNT(“l_extendedprice”) = $ 11 THEN $ 12 ELSE CAST(SUM(“l_extendedprice”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l_extendedprice”)
) AS “avg_price”,
(
CAST(
CASE WHEN COUNT(“l_discount”) = $ 13 THEN $ 14 ELSE CAST(SUM(“l_discount”) AS NUMERIC(38, 6)) END AS DOUBLE PRECISION
) / COUNT(“l_discount”)
) AS “avg_disc”,
COUNT(*) AS “count_order”
FROM
(
SELECT
“l_returnflag”,
“l_linestatus”,
“l_quantity”,
“l_extendedprice”,
(“l_extendedprice” * ((1 - “l_discount”))) AS “$f4”,
(
(“l_extendedprice” * ((1 - “l_discount”))) * ((1 + “l_tax”))
) AS “$f5”,
“l_discount”
FROM
(
SELECT
“lineitem”.“l_quantity”,
“lineitem”.“l_extendedprice”,
“lineitem”.“l_discount”,
“lineitem”.“l_tax”,
“lineitem”.“l_returnflag”,
“lineitem”.“l_linestatus”,
“lineitem”.“l_shipdate”
FROM
“public”.“lineitem”
) AS “lineitem”
WHERE
“l_shipdate” <= DATE ‘1998-12-01’ - interval ‘90’ day
) AS “lineitem”
GROUP BY
“l_returnflag”,
“l_linestatus”
ORDER BY
“l_returnflag”,
“l_linestatus”
Is there any documentation on why Dremio does that? And especially why forming the initial statement to this particular one?
Thanks for any help!
