Skip to content

SQL statement (UNION + EXCEPT) causes panic #4837

Closed

Description

@DDtKey

Describe the bug
datafusion 15.0.0 panics for specific SQL-query:

(   
    SELECT * FROM table_1
    EXCEPT
    SELECT * FROM table_2
)  
UNION ALL
(   
    SELECT * FROM table_2
    EXCEPT
    SELECT * FROM table_1
) 

To Reproduce
Steps to reproduce the behavior:

    let table_1_path = "...";
    let table_2_path = "...";

    let ctx = SessionContext::new();

    ctx.register_csv("table_1", table_1_path, CsvReadOptions::default())
        .await?;
    ctx.register_csv("table_2", table_2_path, CsvReadOptions::default())
        .await?;

    let data_frame = ctx.sql("(SELECT * FROM table_1 EXCEPT SELECT * FROM table_2) UNION ALL (SELECT * FROM table_2 EXCEPT SELECT * FROM table_1)").await?;

    data_frame.show().await?;

It will panic:
thread 'main' panicked at 'index out of bounds: the len is 2 but the index is 2' (len & index depends on number of columns in file)

Expected behavior
It should works correctly or return an error, but not to panic.

Activity

  1. alamb commented on Jan 7, 2023

    @alamb
    Contributor

    Thank you for the report @DDtKey

  2. Jefffrey commented on Jan 7, 2023

    @Jefffrey
    Contributor

    @DDtKey are you able to provide the files you used to cause the bug, or at least give an example of their structure/data? I don't seem to be able to reproduce the panic myself

  3. DDtKey commented on Jan 7, 2023

    @DDtKey
    ContributorAuthor

    @DDtKey are you able to provide the files you used to cause the bug, or at least give an example of their structure/data? I don't seem to be able to reproduce the panic myself

    It seems to be reproducible for any file structure 🤔

    But for example, very simple ones:

    table_1:

    name
    Alex
    Bob
    Alice

    and

    table_2:

    name
    Alex
    Bob
    John

    And at least for datafusion 15 I wasn't able to run such query without panic.

  4. Jefffrey commented on Jan 7, 2023

    @Jefffrey
    Contributor

    Hmm, still can't reproduce, on latest master 83c1026:

    jeffrey:~/Code/arrow-datafusion/datafusion-cli$ cat data1.csv
    name
    Alex
    Bob
    Alice
    jeffrey:~/Code/arrow-datafusion/datafusion-cli$ cat data2.csv
    name
    Alex
    Bob
    John
    jeffrey:~/Code/arrow-datafusion/datafusion-cli$ cargo run
        Finished dev [unoptimized + debuginfo] target(s) in 0.10s
         Running `/media/jeffrey/1tb_860evo_ssd/.cargo_target_cache/debug/datafusion-cli`
    DataFusion CLI v15.0.0
    ❯ CREATE EXTERNAL TABLE foo1 STORED AS CSV WITH HEADER ROW LOCATION 'data1.csv';
    0 rows in set. Query took 0.013 seconds.
    ❯ CREATE EXTERNAL TABLE foo2 STORED AS CSV WITH HEADER ROW LOCATION 'data2.csv';
    0 rows in set. Query took 0.002 seconds.
    ❯ (select * from foo1 except select * from foo2) union all (select * from foo2 except select * from foo1);
    +-------+
    | name  |
    +-------+
    | Alice |
    | John  |
    +-------+
    2 rows in set. Query took 0.012 seconds.
    ❯ \q
    jeffrey:~/Code/arrow-datafusion/datafusion-cli$ git rev-parse --short HEAD
    83c10269

    Or should I try for different file formats too?

  5. DDtKey commented on Jan 7, 2023

    @DDtKey
    ContributorAuthor

    @Jefffrey oh, looks like it isn't reproducible against main branchq(but for last stable release) for me as well. Thank you.

    But results isn't correct for me 🤔

    data_frame.show() output:

    +-------+
    | name  |
    +-------+
    | John  |
    | Alice |
    +-------+
    +-------+
    | name  |
    +-------+
    | Alice |
    | John  |
    +-------+
    +-------+
    | name  |
    +-------+
    | Alice |
    | John  |
    +-------+
    

    But if I would use

    data_frame.write_csv("...").await?;

    It will lead to an error:
    { code: 17, kind: AlreadyExists, message: "File exists" }

    Any thoughts?

  6. Jefffrey commented on Jan 7, 2023

    @Jefffrey
    Contributor

    The output seems correct to me from my understanding, unless you're referring to the inconsistent output row ordering?

    The error seems straightforward enough, where it doesn't seem to override the existing output file?

  7. DDtKey commented on Jan 7, 2023

    @DDtKey
    ContributorAuthor

    The output seems correct to me from my understanding, unless you're referring to the inconsistent output row ordering?

    The error seems straightforward enough, where it doesn't seem to override the existing output file?

    My bad, sorry. Accidentally I've ran 3 parallel processes, that's the reason of 3 outputs & file already exists error.

  8. DDtKey commented on Jan 7, 2023

    @DDtKey
    ContributorAuthor

    So, with bisect I was able to figure out that it was fixed by #4666 (ac2e5d1).

    Thanks for help @Jefffrey!
    Shouldn't such case be a part of tests?

  9. DDtKey commented on Jan 7, 2023

    @DDtKey
    ContributorAuthor

    After some tests against current master branch I was able to discover a new bug(broken behavior), see new issue: #4844

  10. Jefffrey commented on Jan 7, 2023

    @Jefffrey
    Contributor

    Shouldn't such case be a part of tests?

    That would be a good idea to have more test coverage, though I confess I'm not entirely certain where such a test would be located, maybe in the sqllogictests?

    Relevant epic #4460

  11. alamb commented on Jan 8, 2023

    @alamb
    Contributor

    Thanks @DDtKey -- indeed if you have a test that fails before #4666 it would be great to add it to the test suite

  12. alamb commented on Jan 8, 2023

    @alamb
    Contributor
  13. ygf11 commented on Jan 9, 2023

    @ygf11
    Contributor

    I find the bug is in union type coercion(#3513), and the bug still exists.
    We can reproduce it in the master branch:

    ❯ create table table_2(name text, id INT) as  values('Alex',1);
    0 rows in set. Query took 0.002 seconds.
    ❯ create table table_1(name text, id TINYINT) as  values('Alex',1);
    0 rows in set. Query took 0.002 seconds.
    ❯ (
        SELECT * FROM table_1
        EXCEPT
        SELECT * FROM table_2
    )
    UNION ALL
    (
        SELECT * FROM table_2
        EXCEPT
        SELECT * FROM table_1
    );
    SchemaError(FieldNotFound { field: Column { relation: Some("table_2"), name: "id" }, valid_fields: Some([Column { relation: Some("table_1"), name: "name" }, Column { relation: Some("table_1"), name: "id" }]) })

    For union operation, we need ensure each data type of left and right should be same.
    It is done in:
    https://github.com/apache/arrow-datafusion/blob/71b9baecd0a3c881f96e9994d922f3c1b3d61854/datafusion/expr/src/expr_rewriter.rs#L523-L527

    But it uses plan.expressions() to get the type coercion units of the input, which I think is not correct, because it maybe return other expressions, like join will return its predicates not the fields of the schema.

    To fix this issue, I think we can abandon plan.expressions(), and use the input schema to enumerate the fields, finally create the new plan with Projection::try_new_with_schema.

  14. liukun4515 commented on Jan 10, 2023

    @liukun4515
    Contributor

    I find the bug is in union type coercion(#3513), and the bug still exists. We can reproduce it in the master branch:

    ❯ create table table_2(name text, id INT) as  values('Alex',1);
    0 rows in set. Query took 0.002 seconds.
    ❯ create table table_1(name text, id TINYINT) as  values('Alex',1);
    0 rows in set. Query took 0.002 seconds.
    ❯ (
        SELECT * FROM table_1
        EXCEPT
        SELECT * FROM table_2
    )
    UNION ALL
    (
        SELECT * FROM table_2
        EXCEPT
        SELECT * FROM table_1
    );
    SchemaError(FieldNotFound { field: Column { relation: Some("table_2"), name: "id" }, valid_fields: Some([Column { relation: Some("table_1"), name: "name" }, Column { relation: Some("table_1"), name: "id" }]) })

    For union operation, we need ensure each data type of left and right should be same. It is done in:

    https://github.com/apache/arrow-datafusion/blob/71b9baecd0a3c881f96e9994d922f3c1b3d61854/datafusion/expr/src/expr_rewriter.rs#L523-L527

    But it uses plan.expressions() to get the type coercion units of the input, which I think is not correct, because it maybe return other expressions, like join will return its predicates not the fields of the schema.

    To fix this issue, I think we can abandon plan.expressions(), and use the input schema to enumerate the fields, finally create the new plan with Projection::try_new_with_schema.

    😭, I think this is the only solution to resolve union plan for now.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't workinggood first issueGood for newcomers

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions