[GitHub] [flink] godfreyhe commented on a change in pull request #17637: [FLINK-24708][planner] Fix wrong results of the IN operator

[GitBox <gi...@apache.org>]

godfreyhe commented on a change in pull request #17637:
URL: https://github.com/apache/flink/pull/17637#discussion_r744369780



##########
File path: flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/plan/rules/logical/ConvertToNotInOrInRuleTest.scala
##########
@@ -169,4 +169,32 @@ class ConvertToNotInOrInRuleTest extends TableTestBase {
       "SELECT * FROM MyTable WHERE b = 1 OR b = 2 OR (a <> 1 AND a <> 2 AND a <> 3 " +
       "AND a <> 4 AND c = 1) OR b = 3 OR b = 4 OR c = 1")
   }
+
+  @Test
+  def testConvertToSearch(): Unit = {
+    util.verifyRelPlan(
+      """
+        |SELECT * from MyTable where e in (
+        |'CTNBSmokeSensor',
+        |'H388N',
+        |'H389N     ',
+        |'GHL-IRD',
+        |'JY-BF-20YN',
+        |'HC809',
+        |'DH-9908N-AEP',
+        |'DH-9908N'
+        |)
+        |""".stripMargin
+    )
+  }
+
+  @Test
+  def testConvertToSearch2(): Unit = {
+    util.verifyRelPlan(
+      "SELECT * FROM MyTable WHERE " +
+        "e = 'a' or e = 'b' or e = 'c' or e = 'd' or e = 'e' or e = 'f' or e = NULL or e = " +
+        "'HELLO " +
+        "WORLD!'"
+    )

Review comment:
       please add some tests to cover other types (such as, int, bigint, double)

##########
File path: flink-table/flink-table-planner-blink/src/test/scala/org/apache/flink/table/planner/runtime/batch/sql/CalcITCase.scala
##########
@@ -1419,6 +1419,76 @@ class CalcITCase extends BatchTestBase {
     )
   }
 
+  @Test
+  def testSearch(): Unit = {
+    val myTableDataId = TestValuesTableFactory.registerData(
+      Seq(row("HC809"), row("H389N     "))
+    )
+    val ddl =
+      s"""
+         |CREATE TABLE SimpleTable (
+         |  content STRING
+         |) WITH (
+         |  'connector' = 'values',
+         |  'data-id' = '$myTableDataId',
+         |  'bounded' = 'true'
+         |)
+         |""".stripMargin
+    tEnv.executeSql(ddl)
+    val sql =
+      """
+        |SELECT UPPER(content) from SimpleTable where UPPER(content) in (
+        |'CTNBSmokeSensor',
+        |'H388N',
+        |'H389N     ',
+        |'GHL-IRD',
+        |'JY-BF-20YN',
+        |'HC809',
+        |'DH-9908N-AEP',
+        |'DH-9908N'
+        |)
+        |
+        |""".stripMargin
+    checkResult(
+      sql,
+      Seq(row("HC809"), row("H389N     "))
+    )
+  }
+
+  @Test
+  def testSearchWithNull(): Unit = {

Review comment:
       please add a test that the first element is null

##########
File path: flink-table/flink-table-planner-blink/src/main/scala/org/apache/flink/table/planner/plan/rules/logical/ConvertToNotInOrInRule.scala
##########
@@ -184,6 +188,52 @@ class ConvertToNotInOrInRule
       case _ => rexNodes.size >= THRESHOLD
     }
   }
+
+
+  /**
+   * Convert the conditions into the [[IN]] and fix the bug in [[RexBuilder#makeIn]].

Review comment:
       Is there any Calcite issue tracking this bug？




-- 
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: issues-unsubscribe@flink.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org