Queries with date filters in the format 'yyyymmdd' give an error

[osawyerr]

Describe the bug

Hi there. When running queries with date filters that are in the format 'yyyymmdd' , DataFusion throws an error. This is valid Postgres syntax. However dates in the format 'yyyy-mm-dd' succeed. Both are valid postgres syntax.

For both queries below, both are valid postgres syntax however:

--this query fails
select * from orders where o_orderdate = '19961006'

--but this succeeds
select * from orders where o_orderdate = '1996-10-06'

To Reproduce

For a date column run a query with the date in string format 'yyyymmdd' as shown below

select * from orders where o_orderdate = '19961006'

Error thrown is

Arrow error: Cast error: Cannot cast string '19961006' to value of Date32 type

Expected behavior

This should return the correct result and not fail

Additional context

No response

[tustvold]

Without delimiters how can a parser distinguish between dd-mm-yyyy or yyyy-mm-dd? I'm very surprised postgres supports this and would be somewhat inclined not to copy this... Is there some particular use case for why you want this functionality?

[osawyerr]

Is there some particular use case for why you want this functionality?

Not specifically. I was querying a date column and realised it didn't work the same as postgres did. I assumed DataFusion was intended to support postgres-esque queries thats why I raised this as an issue.

[jackwener]

When there isn't delimiters, '20200101' will be treated as format yyyymmdd.

Same above, '200101' will be treated as format yymmdd.

Both MySQL and PG support it. Just in my opinion, it quite is a common usage and these usage also is part of ISO standard.

Without delimiters how can a parser distinguish between dd-mm-yyyy or yyyy-mm-dd?

ISO standard does not permit MMDDYYYY, MMYYYYDD or any other unapproved combination to eliminate any confusion.

[alamb]

Another option here might be to implement the two argument for of to_timestamp / to_date / to_time where the second argument is a format string. I think this is tracked with #5398

[Tangruilin]

@alamb . Maybe I can try to fix it.

[alamb]

Thank you @Tangruilin

[alamb]

Arrow has this function: https://docs.rs/arrow/latest/arrow/compute/kernels/cast_utils/fn.string_to_datetime.html

You could look at how it uses Chrono to handle the parsing the different timestamp formats, perhaps

[tustvold]

It is actually https://docs.rs/arrow-cast/47.0.0/src/arrow_cast/parse.rs.html#571 that would need modification. It should be a relatively straightforward case of adding an additional match case

[Tangruilin]

@alamb Maybe you can assign this issue to me.

[Tangruilin]

Otherwise I can't find this issue well

@alamb Maybe you can assign this issue to me.

[jackwener]

I have assigned this issue to you, thanks @Tangruilin

[Tangruilin]

I have already fix this in arrow，this issue can be closed