Precedence bug with date comparison to date plus interval #3408

Activity

1. andygrove

   added

   bugSomething isn't workingSomething isn't working

   on Sep 8, 2022
2. andygrove

   self-assigned this

   on Sep 8, 2022
3. Dandandan commented on Oct 6, 2022

   

   Dandandan

   on Oct 6, 2022

   Contributor

   More actions

   Seems this is an issue in sqlparser-rs, namely in the values in for + and > used in get_next_precedence.
4. sarahyurick commented on Oct 11, 2022

   

   sarahyurick

   on Oct 11, 2022

   Contributor

   More actions

   If no one has picked this up yet, I would be happy to work on this.
5. Dandandan commented on Oct 11, 2022

   

   Dandandan

   on Oct 11, 2022

   Last edited by Dandandan

   Contributor

   More actions

   If no one has picked this up yet, I would be happy to work on this.

   I don't believe so, so go ahead. As mentioned in the comment, I believe it is a bug in sqlparser (in precedence values).

   👍React with 👍1Reacted by Sarah Yurick
6. sarahyurick commented on Oct 20, 2022

   

   sarahyurick

   on Oct 20, 2022

   Contributor

   More actions

   I'm not really able to reproduce the error on the DataFusion side. For example:

   use datafusion::prelude::*;
   
   #[tokio::main]
   async fn main() -> datafusion::error::Result<()> {
     let ctx = SessionContext::new();
   
     let schema = datafusion::arrow::datatypes::Schema::new(vec![
       datafusion::arrow::datatypes::Field::new("i_item_desc", datafusion::arrow::datatypes::DataType::Utf8, true),
       datafusion::arrow::datatypes::Field::new("d3_date", datafusion::arrow::datatypes::DataType::Date64, true),
       datafusion::arrow::datatypes::Field::new("d1_date", datafusion::arrow::datatypes::DataType::Date64, true),
     ]);
   
     ctx.register_csv(
       "test", "data.csv", CsvReadOptions::default().schema(&schema)
     ).await?;
   
     let df = ctx.sql("select i_item_desc \
           from test \
           where d3_date > d1_date + INTERVAL '5 days'").await?;
   
     df.show().await?;
     Ok(())
   }
   

   where the contents of data.csv are:

   i_item_desc,d3_date,d1_date
   a,2022-12-12T7:7:7,2022-12-12T7:7:7
   b,2022-12-12T7:7:7,2022-12-11T7:7:7
   c,2022-12-12T7:7:7,2022-12-10T7:7:7
   d,2022-12-12T7:7:7,2022-12-9T7:7:7
   e,2022-12-12T7:7:7,2022-12-8T7:7:7
   f,2022-12-12T7:7:7,2022-12-7T7:7:7
   g,2022-12-12T7:7:7,2022-12-6T7:7:7
   h,2022-12-12T7:7:7,2022-12-5T7:7:7
   

   Produces the expected output without any errors. I'm able to get the correct results on the Dask-SQL side, too:

   import pandas as pd
   from dask_sql import Context
   from datetime import datetime
   
   c = Context()
   
   test = pd.DataFrame(
       {
           "i_item_desc": ["a", "b", "c", "d", "e", "f", "g", "h"],
           "d3_date": [
               datetime(2002, 6, 5),
               datetime(2002, 6, 6),
               datetime(2002, 6, 7),
               datetime(2002, 6, 8),
               datetime(2002, 6, 9),
               datetime(2002, 6, 10),
               datetime(2002, 6, 11),
               datetime(2002, 6, 12),
           ],
           "d1_date": [
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
               datetime(2002, 6, 5),
           ],
       }
   )
   c.create_table("test", test)
   
   c.sql("select i_item_desc \
           from test \
           where d3_date > d1_date + INTERVAL '5 days'").compute()
   

   Is there maybe another example I should try?
7. sarahyurick commented on Oct 20, 2022

   

   sarahyurick

   on Oct 20, 2022

   Last edited by sarahyurick

   Contributor

   More actions

   I get an error with: select i_item_desc from test where d3_date > d1_date + INTERVAL '5 days' and d2_date > d1_date + INTERVAL '3 days' that's due to incorrectly parsing the and operator, so I could open a separate issue for that.

   Error: NotImplemented("Unsupported interval argument. Expected string literal, got: BinaryOp { left: Value(SingleQuotedString(\"5 days\")), op: And, right: BinaryOp { left: Identifier(Ident { value: \"d2_date\", quote_style: None }), op: Gt, right: BinaryOp { left: Identifier(Ident { value: \"d1_date\", quote_style: None }), op: Plus, right: Interval { value: Value(SingleQuotedString(\"3 days\")), leading_field: None, leading_precision: None, last_field: None, fractional_seconds_precision: None } } } }")
8. andygrove commented on Oct 24, 2022

   

   andygrove

   on Oct 24, 2022

   MemberAuthor

   More actions

   Thanks @sarahyurick. Perhaps the original issue already got resolved in sqlparser. It makes sense to me to open a new issue for the issue you found.
9. alamb commented on Dec 12, 2022

   

   alamb

   on Dec 12, 2022

   Contributor

   More actions

   This is fixed -- I have a PR open to add some regression test coverage: #4569
10. alamb

    closed this as completedon Dec 12, 2022