dqkqd commented on issue #17774:
URL: https://github.com/apache/datafusion/issues/17774#issuecomment-3392608088
I cannot reproduce with the data from `tpchgen-cli -s 10 --format=parquet`.
```
DataFusion CLI v50.1.0
> CREATE EXTERNAL TABLE customer
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/customer.parquet';
CREATE EXTERNAL TABLE nation
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/nation.parquet';
CREATE EXTERNAL TABLE part
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/part.parquet';
CREATE EXTERNAL TABLE region
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/region.parquet';
CREATE EXTERNAL TABLE lineitem
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/lineitem.parquet';
CREATE EXTERNAL TABLE orders
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/orders.parquet';
CREATE EXTERNAL TABLE partsupp
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/partsupp.parquet';
CREATE EXTERNAL TABLE supplier
STORED AS PARQUET
LOCATION '../tpchgen-cli/data/supplier.parquet';
WITH MonthlySales AS (
SELECT
DATE_TRUNC('month', o_orderdate) AS sales_month,
SUM(l_extendedprice) AS total_sales
FROM orders AS o
JOIN lineitem AS l
ON o.o_orderkey = l.l_orderkey
GROUP BY sales_month
),
TopRegions AS (
SELECT
n.n_name AS region_name,
SUM(ps.ps_supplycost) AS region_supply_cost
FROM partsupp AS ps
JOIN supplier AS s
ON ps.ps_suppkey = s.s_suppkey
JOIN nation AS n
ON s.s_nationkey = n.n_nationkey
GROUP BY region_name
ORDER BY region_supply_cost DESC
LIMIT 5
)
SELECT
ms.sales_month,
ms.total_sales,
tr.region_name,
tr.region_supply_cost
FROM MonthlySales AS ms
LEFT JOIN TopRegions AS tr
ON tr.region_supply_cost > (
SELECT AVG(region_supply_cost) FROM TopRegions
);
0 row(s) fetched.
Elapsed 0.010 seconds.
0 row(s) fetched.
Elapsed 0.002 seconds.
0 row(s) fetched.
Elapsed 0.005 seconds.
0 row(s) fetched.
Elapsed 0.002 seconds.
0 row(s) fetched.
Elapsed 0.180 seconds.
0 row(s) fetched.
Elapsed 0.031 seconds.
0 row(s) fetched.
Elapsed 0.016 seconds.
0 row(s) fetched.
Elapsed 0.003 seconds.
+---------------------+----------------+-------------+--------------------+
| sales_month | total_sales | region_name | region_supply_cost |
+---------------------+----------------+-------------+--------------------+
| 1992-07-01T00:00:00 | 29556421886.02 | IRAQ | 163998124.11 |
| 1992-07-01T00:00:00 | 29556421886.02 | INDIA | 162997794.70 |
| 1997-07-01T00:00:00 | 29528362332.21 | IRAQ | 163998124.11 |
| 1997-07-01T00:00:00 | 29528362332.21 | INDIA | 162997794.70 |
| 1995-08-01T00:00:00 | 29515099557.59 | IRAQ | 163998124.11 |
| 1995-08-01T00:00:00 | 29515099557.59 | INDIA | 162997794.70 |
| 1994-02-01T00:00:00 | 26655098612.82 | IRAQ | 163998124.11 |
| 1994-02-01T00:00:00 | 26655098612.82 | INDIA | 162997794.70 |
| 1997-11-01T00:00:00 | 28495888628.25 | IRAQ | 163998124.11 |
| 1997-11-01T00:00:00 | 28495888628.25 | INDIA | 162997794.70 |
| 1997-05-01T00:00:00 | 29564586413.25 | IRAQ | 163998124.11 |
| 1997-05-01T00:00:00 | 29564586413.25 | INDIA | 162997794.70 |
| 1992-03-01T00:00:00 | 29363218066.35 | IRAQ | 163998124.11 |
| 1992-03-01T00:00:00 | 29363218066.35 | INDIA | 162997794.70 |
| 1995-04-01T00:00:00 | 28599411468.16 | IRAQ | 163998124.11 |
| 1995-04-01T00:00:00 | 28599411468.16 | INDIA | 162997794.70 |
| 1993-09-01T00:00:00 | 28601870137.56 | IRAQ | 163998124.11 |
| 1993-09-01T00:00:00 | 28601870137.56 | INDIA | 162997794.70 |
| 1994-07-01T00:00:00 | 29570003529.64 | IRAQ | 163998124.11 |
| 1994-07-01T00:00:00 | 29570003529.64 | INDIA | 162997794.70 |
| 1997-08-01T00:00:00 | 29560366075.58 | IRAQ | 163998124.11 |
| 1997-08-01T00:00:00 | 29560366075.58 | INDIA | 162997794.70 |
| 1995-03-01T00:00:00 | 29646497772.60 | IRAQ | 163998124.11 |
| 1995-03-01T00:00:00 | 29646497772.60 | INDIA | 162997794.70 |
| 1995-07-01T00:00:00 | 29595521746.01 | IRAQ | 163998124.11 |
| 1995-07-01T00:00:00 | 29595521746.01 | INDIA | 162997794.70 |
| 1993-02-01T00:00:00 | 26791875298.38 | IRAQ | 163998124.11 |
| 1993-02-01T00:00:00 | 26791875298.38 | INDIA | 162997794.70 |
| 1994-04-01T00:00:00 | 28697148003.03 | IRAQ | 163998124.11 |
| 1994-04-01T00:00:00 | 28697148003.03 | INDIA | 162997794.70 |
| 1997-06-01T00:00:00 | 28596894169.02 | IRAQ | 163998124.11 |
| 1997-06-01T00:00:00 | 28596894169.02 | INDIA | 162997794.70 |
| 1994-05-01T00:00:00 | 29530870665.28 | IRAQ | 163998124.11 |
| 1994-05-01T00:00:00 | 29530870665.28 | INDIA | 162997794.70 |
| 1994-12-01T00:00:00 | 29598647284.29 | IRAQ | 163998124.11 |
| 1994-12-01T00:00:00 | 29598647284.29 | INDIA | 162997794.70 |
| 1995-12-01T00:00:00 | 29497736932.04 | IRAQ | 163998124.11 |
| 1995-12-01T00:00:00 | 29497736932.04 | INDIA | 162997794.70 |
| 1993-10-01T00:00:00 | 29547358655.90 | IRAQ | 163998124.11 |
| 1993-10-01T00:00:00 | 29547358655.90 | INDIA | 162997794.70 |
| . |
| . |
| . |
+---------------------+----------------+-------------+--------------------+
160 row(s) fetched. (First 40 displayed. Use --maxrows to adjust)
Elapsed 21.717 seconds.
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
