[ https://issues.apache.org/jira/browse/SPARK-43491?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
KuijianLiu updated SPARK-43491: ------------------------------- Description: The query results of Spark SQL 3.1.1 and Hive SQL 3.1.0 are inconsistent with same sql. Spark SQL calculates `{{{}0 in ('00')`{}}} as false, which act different from `{{{}=`{}}} keyword, but Hive calculates true. Hive is compatible with the `{{{}in`{}}} keyword in 3.1.0, but SparkSQL does not. It's better when dataTypes of elements in `{{{}In`{}}} expression are the same, it should behaviour as same as BinaryComparison like ` {{{}EqualTo`{}}}. Test SQL: {code:java} scala> spark.sql("select 1 as test where 0 = '00'").show +----+ |test| +----+ | 1| +----+ scala> spark.sql("select 1 as test where 0 in ('00')").show +----+ |test| +----+ +----+{code} !image-2023-05-13-13-14-55-853.png! was: The query results of Spark SQL 3.1.1 and Hive SQL 3.1.0 are inconsistent with same sql. Spark SQL calculates `{{{}0 in ('00')`{}}} as false, which act different from `{{{}=`{}}} keyword, but Hive calculates true. Hive is compatible with the `{{{}in`{}}} keyword in 3.1.0, but SparkSQL does not. It's better when dataTypes of elements in `{{{}In`{}}} expression are the same, it should behaviour as same as BinaryComparison like ` {{{}EqualTo`{}}}. Test SQL: {code:java} scala> spark.sql("select 1 as test where 0 = '00'").show +----+ |test| +----+ | 1| +----+ scala> spark.sql("select 1 as test where 0 in ('00')").show +----+ |test| +----+ +----+{code} !image-2023-05-13-13-15-50-685.png! !image-2023-05-13-13-14-55-853.png! > In expression not compatible with EqualTo Expression > ---------------------------------------------------- > > Key: SPARK-43491 > URL: https://issues.apache.org/jira/browse/SPARK-43491 > Project: Spark > Issue Type: Bug > Components: SQL > Affects Versions: 3.3.1 > Reporter: KuijianLiu > Priority: Major > Attachments: image-2023-05-13-13-14-55-853.png, > image-2023-05-13-13-15-50-685.png > > > The query results of Spark SQL 3.1.1 and Hive SQL 3.1.0 are inconsistent > with same sql. Spark SQL calculates `{{{}0 in ('00')`{}}} as false, which act > different from `{{{}=`{}}} keyword, but Hive calculates true. Hive is > compatible with the `{{{}in`{}}} keyword in 3.1.0, but SparkSQL does not. > It's better when dataTypes of elements in `{{{}In`{}}} expression are the > same, it should behaviour as same as BinaryComparison like ` {{{}EqualTo`{}}}. > Test SQL: > {code:java} > scala> spark.sql("select 1 as test where 0 = '00'").show > +----+ > |test| > +----+ > | 1| > +----+ > scala> spark.sql("select 1 as test where 0 in ('00')").show > +----+ > |test| > +----+ > +----+{code} > > !image-2023-05-13-13-14-55-853.png! -- This message was sent by Atlassian Jira (v8.20.10#820010) --------------------------------------------------------------------- To unsubscribe, e-mail: issues-unsubscr...@spark.apache.org For additional commands, e-mail: issues-h...@spark.apache.org