You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Is your feature request related to a problem or challenge? Please describe what you are trying to do.
While porting some tests to sqllogictest I want t ensure the same coverage for specific types.
-- casts `x` to `bigint` which maps to DataType::Int64select x::bigintfrom foo;
-- casts `x` to timestamp (DatType::Timestamp)select x::timestampfrom foo;
However, it is not possible to use SQL to name certain arrow types (e.g TimestampMicrosecondasTIMESTAMP` maps to TimestampNanos
I would like a way to convert an expression to an arbitrary arrow type.
Describe the solution you'd like
I would love to do something like
arrow_cast(source, target_type)
Where target_type is a string that describes an arrow tyoe
For example:
-- casts x to a Int8 (which I don't think is possible in sql)
select arrow_cast(x, 'Int8') from foo;
-- casts x to a LargeBinary
select arrow_cast(x, 'LargeBinary') from foo;
I would like the values accepted as 'target_datatype' to t same as returned by the arrow_typeof() function that goes the other direction (expression to string that represents Arrow type)
It would be nice to have some way to specify Arrow types in SQL datatype syntax. Perhaps like CAST X as CUSTOM TYPE 'Int64'. This would allow using functions such astry_cast, as well as creating tables with specific column types.
However, I am not really sure how to do this in the parser
I think this is apretty good first issue as it is well specified and you can follow the model of arrow_typeof to add a new function and there is an arrow cast kernel that does all the data type conversions
@alamb in parser we operate with expressions which works with arrow types.
I understand the necessity of having arrow_typeof which seems to be a synonym for PG pg_typeof function.
But for the potential arrow_cast function, we will return Expression as well with Arrow datatype inside, how it differs from usual cast? Perhaps you have the analogue in PG? I went through https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/ but didn't find anything similar
But for the potential arrow_cast function, we will return Expression as well with Arrow datatype inside, how it differs from usual cast?
It differs because normal casting CAST(x as target_type) or x::target_type can only cast to SQL types (not Arrow types). DataFusion has a mapping from SQL Type --> Arrow type but there are Arrow types without any corresponding SQL type.
I don't think there is any analog in postgres; The difference is that you can refer to all types in postgres directly using SQL. Since posgres types are sql types there is no analog to this ticket's proposal in SQL
@comphead I wonder if you plan to work on this issue soon? It would be very helpful for writing sqllogictests for dictionary arrays (e.g. #5151). I may take a shot at writing it up this weekend if you haven't had a chance
Activity
alamb commentedon Jan 21, 2023
I think this is apretty good first issue as it is well specified and you can follow the model of arrow_typeof to add a new function and there is an arrow cast kernel that does all the data type conversions
comphead commentedon Jan 23, 2023
@alamb I'd like taking this
alamb commentedon Jan 23, 2023
Thanks @comphead !
melgenek commentedon Jan 24, 2023
It would be also interesting to support "custom" types in
CREATE TABLEstatements. This would help to migrate tests like this one https://github.com/apache/arrow-datafusion/blob/master/datafusion/core/tests/sql/aggregates.rs#L913-L976 to sqllogictest.alamb commentedon Jan 24, 2023
I was thinking we could use
CREATE TABLE ... AS SELECT ...which creates columns based on the types of the SELECT querySo I think we could make a int8 column in a table like
comphead commentedon Jan 31, 2023
@alamb in parser we operate with expressions which works with arrow types.
I understand the necessity of having arrow_typeof which seems to be a synonym for PG
pg_typeoffunction.But for the potential
arrow_castfunction, we will return Expression as well with Arrow datatype inside, how it differs from usual cast? Perhaps you have the analogue in PG? I went through https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-cast/ but didn't find anything similaralamb commentedon Jan 31, 2023
It differs because normal casting
CAST(x as target_type)orx::target_typecan only cast to SQL types (not Arrow types). DataFusion has a mapping from SQL Type --> Arrow type but there are Arrow types without any corresponding SQL type.I don't think there is any analog in postgres; The difference is that you can refer to all types in postgres directly using SQL. Since posgres types are sql types there is no analog to this ticket's proposal in SQL
comphead commentedon Jan 31, 2023
@alamb please correct me if I'm wrong so
will have the same output as
alamb commentedon Jan 31, 2023
@comphead you are correct. Here is an example I don't think you can write with SQL:
because
defaults to TimestampNanosecond
alamb commentedon Feb 2, 2023
@comphead I wonder if you plan to work on this issue soon? It would be very helpful for writing sqllogictests for dictionary arrays (e.g. #5151). I may take a shot at writing it up this weekend if you haven't had a chance
comphead commentedon Feb 2, 2023
@alamb sorry, haven't started it yet. made couple of drafts, but thats it. If this is a priority thing, please feel free to take this on
alamb commentedon Feb 2, 2023
Thanks @comphead -- I will post on this ticket if/when I start work
alamb commentedon Feb 2, 2023
I am starting to hack on this
alamb commentedon Mar 4, 2023
#5166 is now ready for review (I really want this function so I can more easily write / port tests and reproduce bugs)