Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-666

Anti-semi-joins against JDBC adapter give wrong results

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 1.2.0-incubating
    • Fix Version/s: 1.5.0
    • Component/s: None
    • Labels:
      None

      Description

      When using JdbcAdapter to execute a query with a WHERE clause wherein the predicate using the <> operator contains a subquery, e.g.:

      select * from table1 where c1 <> (select c1 from table2 where rnum =0);

      The query is split to two (outer - "select from *
      from table1" and inner -"select col2 from table2 where col1 =0") Enumerable objects and then combined with the org.apache.calcite.runtime.Enumerables semiJoin method.

      The returned resultset however, is equivalent to the result of executing "select * from table1" and the WHERE clause condition ignored.

      Test:
      package org.apache.calcite.test;

      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.PreparedStatement;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;
      import java.util.Properties;
      import org.apache.calcite.jdbc.Driver;

      public class TestSubqueryInPredicate {

      public static void main(String[] args) {
      try

      { //testScottdb(); testPredicateSubquery(); }

      catch (Exception e)

      { e.printStackTrace(); }

      }

      public static void testPredicateSubquery() throws SQLException {
      String h2dbMemUrl = "jdbc:hsqldb:mem:.";
      Connection baseConnection = DriverManager.getConnection(h2dbMemUrl);
      Statement statement = baseConnection.createStatement();

      statement.execute("CREATE TABLE if not exists table1 (\n"
      + "RNUM INTEGER,\n"
      + "C1 INTEGER,\n"
      + "C2 VARCHAR(2))");

      statement.execute("insert into table1 values ( 0, 10, 'BB')");
      statement.execute("insert into table1 values ( 1, 15, 'DD')");
      statement.execute("insert into table1 values ( 2, NULL, 'EE')");
      statement.execute("insert into table1 values ( 3, 10, 'FF')");

      statement.execute("CREATE TABLE if not exists table2 (\n"
      + "RNUM INTEGER,\n"
      + "C1 INTEGER,\n"
      + "C2 INTEGER)");

      statement.execute("insert into table2 values ( 0, 10, 15)");
      statement.execute("insert into table2 values ( 1, 20, 25)");
      statement.execute("insert into table2 values ( 2, NULL, 50)");

      statement.close();
      baseConnection.commit();

      Properties info = new Properties();
      info.put("model",
      "inline:"
      + "{\n"
      + " version: '1.0',\n"
      + " defaultSchema: 'BASEJDBC',\n"
      + " schemas: [\n"
      + "

      {\n" + " type: 'jdbc',\n" + " name: 'BASEJDBC',\n" + " jdbcDriver: '" + Driver.class.getName() + "',\n" + " jdbcUrl: '" + h2dbMemUrl + "',\n" + " jdbcCatalog: null,\n" + " jdbcSchema: null\n" + " }

      \n"
      + " ]\n"
      + "}");

      Connection calciteConnection = DriverManager.getConnection(
      "jdbc:calcite:", info);

      System.out.println ("select * from table1 where c1 <> (select c1 from table2 > where rnum =0)");
      PreparedStatement calcitePS = calciteConnection.prepareStatement(
      "select * from table1 where c1 <> (select c1 from table2 where rnum =0)");
      ResultSet rs = calcitePS.executeQuery();

      while (rs.next())

      { System.out.print ("\n" + "| RNUM:" + (Integer) rs.getObject("RNUM") + " | "); System.out.print ("C1:" + (Integer) rs.getObject("C1") + " | "); System.out.print ("C2:" + rs.getString("C2") + " | "); }

      rs.close();
      calciteConnection.close();
      }
      }

      Test Result:
      select * from table1 where c1 <> (select c1 from table2 > where rnum =0)

      RNUM:0 C1:10 C2:BB
      RNUM:1 C1:15 C2:DD
      RNUM:2 C1:null C2:EE
      RNUM:3 C1:10 C2:FF

      Generated Code:
      [SELECT *
      FROM "TABLE1"]
      [SELECT "C1"
      FROM "TABLE2"
      WHERE "RNUM" = 0]

      /* 1 */ org.apache.calcite.DataContext root;
      /* 2 */
      /* 3 */ public org.apache.calcite.linq4j.Enumerable bind(final org.apache.calcite.DataContext root0) {
      /* 4 */ root = root0;
      /* 5 */ return org.apache.calcite.runtime.Enumerables.semiJoin(org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema) root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(), "SELECT *\nFROM \"TABLE1\"", new org.apache.calcite.linq4j.function.Function1() {
      /* 6 */ public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) {
      /* 7 */ return new org.apache.calcite.linq4j.function.Function0() {
      /* 8 */ public Object apply() {
      /* 9 */ try {
      /* 10 */ final Object[] values = new Object[3];
      /* 11 */ values[0] = resultSet.getInt(1);
      /* 12 */ if (resultSet.wasNull())

      { /* 13 */ values[0] = null; /* 14 */ }

      /* 15 */ values[1] = resultSet.getInt(2);
      /* 16 */ if (resultSet.wasNull())

      { /* 17 */ values[1] = null; /* 18 */ }

      /* 19 */ values[2] = resultSet.getObject(3);
      /* 20 */ return values;
      /* 21 */ } catch (java.sql.SQLException e)

      { /* 22 */ throw new RuntimeException( /* 23 */ e); /* 24 */ }

      /* 25 */ }
      /* 26 */ }
      /* 27 */ ;
      /* 28 */ }
      /* 29 */ public Object apply(final Object resultSet)

      { /* 30 */ return apply( /* 31 */ (java.sql.ResultSet) resultSet); /* 32 */ }

      /* 33 */ }
      /* 34 */ ), org.apache.calcite.runtime.ResultSetEnumerable.of(((org.apache.calcite.adapter.jdbc.JdbcSchema) root.getRootSchema().getSubSchema("BASEJDBC").unwrap(org.apache.calcite.adapter.jdbc.JdbcSchema.class)).getDataSource(), "SELECT \"C1\"\nFROM \"TABLE2\"\nWHERE \"RNUM\" = 0", new org.apache.calcite.linq4j.function.Function1() {
      /* 35 */ public org.apache.calcite.linq4j.function.Function0 apply(final java.sql.ResultSet resultSet) {
      /* 36 */ return new org.apache.calcite.linq4j.function.Function0() {
      /* 37 */ public Object apply() {
      /* 38 */ try {
      /* 39 */ final Object value;
      /* 40 */ value = resultSet.getInt(1);
      /* 41 */ if (resultSet.wasNull())

      { /* 42 */ value = null; /* 43 */ }

      /* 44 */ return value;
      /* 45 */ } catch (java.sql.SQLException e)

      { /* 46 */ throw new RuntimeException( /* 47 */ e); /* 48 */ }

      /* 49 */ }
      /* 50 */ }
      /* 51 */ ;
      /* 52 */ }
      /* 53 */ public Object apply(final Object resultSet)

      { /* 54 */ return apply( /* 55 */ (java.sql.ResultSet) resultSet); /* 56 */ }

      /* 57 */ }
      /* 58 */ ), new org.apache.calcite.linq4j.function.Function1() {
      /* 59 */ public org.apache.calcite.runtime.FlatLists.ComparableEmptyList apply(Object[] v1)

      { /* 60 */ return org.apache.calcite.runtime.FlatLists.COMPARABLE_EMPTY_LIST; /* 61 */ }

      /* 62 */ public Object apply(Object v1)

      { /* 63 */ return apply( /* 64 */ (Object[]) v1); /* 65 */ }

      /* 66 */ }
      /* 67 */ , new org.apache.calcite.linq4j.function.Function1() {
      /* 68 */ public org.apache.calcite.runtime.FlatLists.ComparableEmptyList apply(Integer v1)

      { /* 69 */ return org.apache.calcite.runtime.FlatLists.COMPARABLE_EMPTY_LIST; /* 70 */ }

      /* 71 */ public Object apply(Object v1)

      { /* 72 */ return apply( /* 73 */ (Integer) v1); /* 74 */ }

      /* 75 */ }
      /* 76 */ );
      /* 77 */ }
      /* 78 */
      /* 79 */
      /* 80 */ public Class getElementType()

      { /* 81 */ return java.lang.Object[].class; /* 82 */ }

      /* 83 */
      /* 84 */

        Activity

        Hide
        yeongwei YeongWei added a comment -

        Hi Julian Hyde,

        I believe this issue has been resolved in some other fixes, I think it could be CALCITE-704.

        Currently the query presented above will run using the .join(...) method along with the SINGLE_VALUE function.

        I have added test cases, please refer to CALCITE-666-UnitTest-0.1.patch.

        Thanks!

        Show
        yeongwei YeongWei added a comment - Hi Julian Hyde , I believe this issue has been resolved in some other fixes, I think it could be CALCITE-704 . Currently the query presented above will run using the .join(...) method along with the SINGLE_VALUE function. I have added test cases, please refer to CALCITE-666 -UnitTest-0.1.patch. Thanks!
        Hide
        seanhychu Sean Hsuan-Yi Chu added a comment -

        I mis-clicked on the bottom. I already reassigned back to you. Sorry for the distraction.

        Show
        seanhychu Sean Hsuan-Yi Chu added a comment - I mis-clicked on the bottom. I already reassigned back to you. Sorry for the distraction.
        Hide
        julianhyde Julian Hyde added a comment -

        The issue was fixed a while ago. Committed the test case as http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/4f415b24. Thanks for the patch, YeongWei!

        The patch uncovered issue CALCITE-923, which I fixed in the next commit.

        Show
        julianhyde Julian Hyde added a comment - The issue was fixed a while ago. Committed the test case as http://git-wip-us.apache.org/repos/asf/incubator-calcite/commit/4f415b24 . Thanks for the patch, YeongWei ! The patch uncovered issue CALCITE-923 , which I fixed in the next commit.
        Hide
        jcamachorodriguez Jesus Camacho Rodriguez added a comment -

        Resolved in release 1.5.0 (2015-11-10)

        Show
        jcamachorodriguez Jesus Camacho Rodriguez added a comment - Resolved in release 1.5.0 (2015-11-10)

          People

          • Assignee:
            julianhyde Julian Hyde
            Reporter:
            YAY Yuri Au Yong
          • Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Development