Spaarsh commented on issue #14303: URL: https://github.com/apache/datafusion/issues/14303#issuecomment-2640583140
I think I have found out the main problem here. I added a few debugging statements to print the DataTypes as the Optimizer code is running, here is what I found: ``` > with t as (select 1000000 as a) select try_cast(a as smallint) > 1 from t; Input value: Int64(1), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(1) to Int16 Checking if 1 is in range [-32768, 32767] Input value: Int16(1), Input type: Int16, Target type: Int64 Target type: Int64, Target min: -9223372036854775808, Target max: 9223372036854775807 Int16(1) to Int64 Checking if 1 is in range [-9223372036854775808, 9223372036854775807] +----------------+ | t.a > Int64(1) | +----------------+ | true | +----------------+ 1 row(s) fetched. Elapsed 0.002 seconds. ``` Instead of checking if the value held by ```a``` can be converted to ```smallint```, it checks if the ```1``` can converted to ```smallint``` which returns true! I further verified this by running a command where the right-hand side value is incompatible with the target datatype, and I got this: ``` > with t as (select 1000000 as a) select try_cast(a as smallint) > 10000000 from t; Input value: Int64(10000000), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(10000000) to Int16 Checking if 10000000 is in range [-32768, 32767] Input value: Int64(10000000), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(10000000) to Int16 Checking if 10000000 is in range [-32768, 32767] +-----------------------+ | t.a > Int64(10000000) | +-----------------------+ | NULL | +-----------------------+ 1 row(s) fetched. Elapsed 0.002 seconds. ``` Since ```10000000``` can't be converted to smallint, the expected output is seen now. Same for ```cast``` : ``` > with t as (select 1000000 as a) select cast(a as smallint) > 1 from t; Input value: Int64(1), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(1) to Int16 Checking if 1 is in range [-32768, 32767] Input value: Int16(1), Input type: Int16, Target type: Int64 Target type: Int64, Target min: -9223372036854775808, Target max: 9223372036854775807 Int16(1) to Int64 Checking if 1 is in range [-9223372036854775808, 9223372036854775807] +----------------+ | t.a > Int64(1) | +----------------+ | true | +----------------+ 1 row(s) fetched. Elapsed 0.002 seconds. ``` ``` > with t as (select 1000000 as a) select cast(a as smallint) > 10000000 from t; Input value: Int64(10000000), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(10000000) to Int16 Checking if 10000000 is in range [-32768, 32767] Input value: Int64(10000000), Input type: Int64, Target type: Int16 Target type: Int16, Target min: -32768, Target max: 32767 Int64(10000000) to Int16 Checking if 10000000 is in range [-32768, 32767] Arrow error: Cast error: Can't cast value 1000000 to type Int16 ``` -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
