Skip to content

benchmark q6 producing incorrect result #4024

Closed

Description

@andygrove

Describe the bug

---- tests::verify_q6 stdout ----
Running benchmarks with the following options: DataFusionBenchmarkOpt { query: 6, debug: false, iterations: 1, partitions: 2, batch_size: 8192, path: "/home/andy/git/apache/arrow-datafusion/benchmarks/data", file_format: "tbl", mem_table: false, output_path: None, disable_statistics: false }
Query 6 iteration 0 took 2977.2 ms and returned 1 rows
Query 6 avg time: 2977.24 ms
thread 'tests::verify_q6' panicked at 'assertion failed: `(left == right)`
  left: `["123141078.23"]`,
 right: `["75207429.08"]`', benchmarks/src/bin/tpch.rs:1004:13

To Reproduce
Steps to reproduce the behavior:

Expected behavior
A clear and concise description of what you expected to happen.

Additional context
Add any other context about the problem here.

Activity

  1. added this to the 14.0.0 milestone on Oct 29, 2022
  2. andygrove commented on Oct 30, 2022

    @andygrove
    MemberAuthor

    This issue is not related to decimal support. I see the same behavior after reverting to use floats.

    Expected: 123141078.23; Actual: 75207768.18549997; Tolerance: 1
    
  3. andygrove commented on Oct 30, 2022

    @andygrove
    MemberAuthor

    If I change part of the query from this:

    and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    

    to this:

    and l_discount between 0.05 and 0.07
    

    then it works

  4. andygrove commented on Oct 30, 2022

    @andygrove
    MemberAuthor

    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.

    and l_discount between 0.049999999 and 0.069999999
    
  5. andygrove commented on Oct 31, 2022

    @andygrove
    MemberAuthor

    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)
    
  6. viirya commented on Nov 1, 2022

    @viirya
    Member

    If I change part of the query from this:

    and l_discount between 0.06 - 0.01 and 0.06 + 0.01
    

    to this:

    and l_discount between 0.05 and 0.07
    

    then it works

    Does it work? I just tried but it still doesn't work. The result 123140554.79.

  7. self-assigned this
    on Nov 1, 2022
  8. andygrove commented on Nov 1, 2022

    @andygrove
    MemberAuthor

    Does it work? I just tried but it still doesn't work. The result 123140554.79.

    You are correct, the result is still wrong, just closer:

      left: `["123141078.23"]`,
     right: `["123140554.79"]`', benchmarks/src/bin/tpch.rs:1003:13
    
  9. andygrove commented on Nov 1, 2022

    @andygrove
    MemberAuthor

    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

  10. viirya commented on Nov 2, 2022

    @viirya
    Member

    ❯ 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.

  11. modified the milestones: 14.0.0, 15.0.0 on Nov 2, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

Labels

bugSomething isn't working

Type

No type

Projects

No projects

    Milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions