[CALCITE-2071] Query with IN and OR in WHERE clause returns wrong result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.16.0
• Assignee: Julian Hyde
• Reporter: Vova Vysotskyi
• Created: 29/Nov/17 20:57
• Updated: 27/Feb/24 22:23
• Resolved: 14/Dec/17 19:17

Description

Query in this test returns the wrong result (it returns empty result):

  @Test
  @Ignore
  public void testWhereInOr() throws Exception {
    CalciteAssert.hr()
        .query("select \"empid\"\n"
            + "from \"hr\".\"emps\" t\n"
            + "    where (\"empid\" in (select \"empid\" from \"hr\".\"emps\") \n"
            + "        or \"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, "
            + "                     12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) )\n"
            + "      and \"empid\" in (100, 200, 150)")
        .returns("empid=100\n"
            + "empid=200\n"
            + "empid=150\n");
  }

Without condition in or operator

\"empid\" in (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25) 

it returns the right result.

Issue Links

• is duplicated by: CALCITE-2103 error logical plan when size of In-list connected with OR exceeds inSubQueryThreshold
• is related to: CALCITE-1483 Suboptimal plan for NOT IN query
• is related to: CALCITE-4887 Filter subQuery remove not fully optimized