You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
These floats are being cast to decimal, which is not what we want:
CAST(lineitem.l_discount AS Decimal128(30, 15))lineitem.l_discount >= Decimal128(Some(49999999999999),30,15)
AND
CAST(lineitem.l_discount AS Decimal128(30, 15))lineitem.l_discount <= Decimal128(Some(69999999999999),30,15)
I am investigating more and there are potentially multiple issues here.
I have filed #4071 for the following issue that affects the filter:
I am comparing Spark and DataFusion behavior against a Parquet file:
Spark
scala> spark.read.parquet("/mnt/bigdata/tpch/sf1-parquet/lineitem").createTempView("lineitem")
scala> spark.sql("SELECT count(*) from lineitem where l_discount between 0.05 and 0.07").show
+--------+
|count(1)|
+--------+
|16361562|
+--------+
scala> spark.sql("SELECT count(*) from lineitem where l_discount between 0.06-0.01 and 0.06+0.01").show
+--------+
|count(1)|
+--------+
|16361562|
+--------+
DataFusion
❯ create external table lineitem stored as parquet location '/mnt/bigdata/tpch/sf1-parquet/lineitem';
0 rows in set. Query took 0.015 seconds.
❯ select count(*) from lineitem where l_discount between 0.05 and 0.07;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 16361562 |
+-----------------+
1 row in set. Query took 0.487 seconds.
❯ select count(*) from lineitem where l_discount between 0.06-0.01 and 0.06+0.01;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 10908630 |
+-----------------+
1 row in set. Query took 0.394 seconds.
So between 0.05 and 0.07 is consistent between Spark and DataFusion at least
❯ create external table lineitem stored as parquet location '/mnt/bigdata/tpch/sf1-parquet/lineitem';
0 rows in set. Query took 0.015 seconds.
❯ select count(*) from lineitem where l_discount between 0.05 and 0.07;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 16361562 |
+-----------------+
1 row in set. Query took 0.487 seconds.
❯ select count(*) from lineitem where l_discount between 0.06-0.01 and 0.06+0.01;
+-----------------+
| COUNT(UInt8(1)) |
+-----------------+
| 10908630 |
+-----------------+
1 row in set. Query took 0.394 seconds.
So between 0.05 and 0.07 is consistent between Spark and DataFusion, but between 0.06-0.01 and 0.06+0.01 is not.
This is understandable. This is because 0.06 is parsed as double type in DataFusion but as decimal type in Spark. 0.06 - 0.01 will result in 0.049999999 instead of 0.05 if they treated as double type literal (no matter DataFusion or Spark) due to precision of floating point value.
More interestingly, for the query select count(*) from lineitem where l_discount between 0.05 and 0.07;, I only got 1637557 while running the query on DataFusion with the data generated by ./tpch-gen.sh 1.0.
I assume the query execution is correct. Then I will suspect that if the data generated by tpch-gen.sh is the same as '/mnt/bigdata/tpch/sf1-parquet/lineitem'`?
I also suspect that 123140554.79 is correct answer for the query q6 on this data, instead of 123141078.23.
Activity
andygrove commentedon Oct 30, 2022
This issue is not related to decimal support. I see the same behavior after reverting to use floats.
andygrove commentedon Oct 30, 2022
If I change part of the query from this:
to this:
then it works
andygrove commentedon Oct 30, 2022
never mind, this is related to decimal so we'll have to wait until decimals are supported to fix this one
this is how the query is running in the physical plan and this is a different range of rows compares to between
0.05 and 0.07.andygrove commentedon Oct 31, 2022
These floats are being cast to decimal, which is not what we want:
viirya commentedon Nov 1, 2022
Does it work? I just tried but it still doesn't work. The result
123140554.79.andygrove commentedon Nov 1, 2022
You are correct, the result is still wrong, just closer:
andygrove commentedon Nov 1, 2022
I am investigating more and there are potentially multiple issues here.
I have filed #4071 for the following issue that affects the filter:
I am comparing Spark and DataFusion behavior against a Parquet file:
Spark
DataFusion
So
between 0.05 and 0.07is consistent between Spark and DataFusion at leastviirya commentedon Nov 2, 2022
This is understandable. This is because 0.06 is parsed as double type in DataFusion but as decimal type in Spark. 0.06 - 0.01 will result in 0.049999999 instead of 0.05 if they treated as double type literal (no matter DataFusion or Spark) due to precision of floating point value.
More interestingly, for the query
select count(*) from lineitem where l_discount between 0.05 and 0.07;, I only got1637557while running the query on DataFusion with the data generated by./tpch-gen.sh 1.0.I assume the query execution is correct. Then I will suspect that if the data generated by
tpch-gen.shis the same as'/mnt/bigdata/tpch/sf1-parquet/lineitem'`?I also suspect that
123140554.79is correct answer for the query q6 on this data, instead of123141078.23.Re-enable TPCH query 6 (apache#4024)