Am basically trying to understand how cast conversion from char (10) to int//bigint works. Although the syntax provided in the documentation is helpful. But in reality, it behaves differently
For Instance, I have a TERADATA Query which is doing Char(10)/100K Computation .
select char(10)/1000000 from table using some Filter Condition
But while parsing on Starburst Enterprise (Trino SQL Syntax) fails. So in order to make it work, have changed it to:
select cast(char (10) as BIGINT from table - But this conversion did not work seems like number of bytes or either more or less
It errored out saying that Cannot convert char to BIg INT
So how to achieve the cast conversion for such a simple example
I thought this was going to be an easy question, but it wasn’t. First, thought we just needed to use try_cast() instead of cast(), but realized quickly, as you did, that this is rooted in the CHAR data type itself as it works find casting from VARCHAR.
I then thought I could cast the CHAR to VARCHAR and then take that and cast it to the BIGINT, but still got the same error. I did my testing on the Hive connector (I realize you are testing with TD) and since they both behave the same way it is surely something in the core Trino engine itself.
Someone brighter than me will be needed to explain exactly what the underlying logic is freaking out about. I’m pretty sure I could write a SQL routine that could help, but then all the data will likely have to come back to Trino to tackle the filter instead of letting TD resolve it internally.
Shy of that (or finding out from someone more knowledgeable of a better workaround) I guess you could always add a new column on the TD table to hold the answer of your division problem and just use a trigger to calculate it on insert/update of a row.