A Conversion Error Problem of CAST and CONCAT Function Combination

1

I found several interesting things about dremio.
Please try the following three queries.

select CAST(CONCAT('', DATE '2022-10-26') AS INTEGER)

You will get an error:

GandivaException: Failed to cast the string 2022-10-26 to int32_t.
1profile.zip (12.0 KB)

select CAST(CONCAT( DATE '2022-10-26') AS INTEGER),* from orders2

You will get an error:

GandivaException: Failed to cast the string 2022-10-26 to int32_t.
2profile.zip (15.7 KB)

The parquet file:
orders2.zip (403.7 KB)

select CAST(CONCAT('', DATE '2022-10-26') AS INTEGER),* from orders2

Execution succeeded!
3profile.zip (13.4 KB)

I have some questions:

What is the difference between “CONCAT (‘’, DATE ‘2022-10-26’)” and “CONCAT (DATE ‘2022-10-26’)”?

What is the difference between querying from a table and not querying from a table?

image

2

@bigfacewo

#1 Does below work?

SELECT CAST(TO_CHAR(DATE '2022-10-26','YYYYMMDD') AS INTEGER)

I see your SQL goes against MySQL and it is a push down whereas you have attached a PARQUET file.
For me even option #3 fails when querying PARQUET. The reason it succeeds for you when querying ORDERS is that it is a pushdown. Also the ’ ', is needed to convert to string first, instead I have used TO_CHAR