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

Errors joining between jdbc schema & enumerable

    XMLWordPrintableJSON

    Details

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

      Description

      This is a sort-of follow up on (#229 | FLINK-229). This time, I'm trying to join between a jdbc and an enumerable (reflective schema). I'm using today's master.

      The testcode below shows:

      • first a join which works
      • then the exact same query, but with the join order reversed, fails with a CannotPlanException
      • adding a where clause to the query makes it plannable again, but the query result is wrong because the join condition is reduced to simply "true": EnumerableJoinRel(condition=[true], joinType=[inner])

      These two problems are probably because of quite different reasons, if you rather have two tickets for this, just let me know.

      Here's my test code:

      ```java
      package net.hydromatic.optiq.test;

      import com.google.common.collect.Sets;
      import net.hydromatic.optiq.SchemaPlus;
      import net.hydromatic.optiq.impl.java.ReflectiveSchema;
      import net.hydromatic.optiq.impl.jdbc.JdbcSchema;
      import net.hydromatic.optiq.jdbc.OptiqConnection;
      import org.junit.Before;
      import org.junit.Test;

      import java.sql.Connection;
      import java.sql.DriverManager;
      import java.sql.ResultSet;
      import java.sql.SQLException;
      import java.sql.Statement;
      import java.util.Set;

      import static org.junit.Assert.assertEquals;

      /**

      • Tests joining between tables of a jdbc schema and some enumerable,
      • here a reflective schema.
        */
        public class JdbcEnumerableJoinTest {
        private static boolean dbCreated;
        private Connection optiqConn;

      @Before
      public void setup() throws SQLException {
      // Create a jdbc database & table
      String hsqlUrl = "jdbc:hsqldb:mem:" + getClass().getSimpleName();
      if (!dbCreated)

      { Connection c1 = DriverManager.getConnection(hsqlUrl, "", ""); Statement stmt1 = c1.createStatement(); // This is a table we can join with the emps from the hr schema stmt1.execute("create table table1(id integer not null primary key, " + "field1 varchar(10))"); stmt1.execute("insert into table1 values(100, 'foo')"); stmt1.execute("insert into table1 values(200, 'bar')"); c1.close(); dbCreated = true; }

      // Make an optiq schema with both a jdbc schema and a non-jdbc schema
      optiqConn = DriverManager.getConnection("jdbc:optiq:");
      OptiqConnection optiqConnection =
      optiqConn.unwrap(OptiqConnection.class);
      SchemaPlus rootSchema = optiqConnection.getRootSchema();
      rootSchema.add("DB", JdbcSchema.create(rootSchema, "DB",
      JdbcSchema.dataSource(hsqlUrl, "org.hsqldb.jdbcDriver", "", ""),
      null, null));
      rootSchema.add("hr", new ReflectiveSchema("hr", new JdbcTest.HrSchema()));
      }

      @Test
      public void testJdbcWithEnumerableJoin() throws SQLException

      { // This query works correctly String query1 = "select t.id, t.field1 " + "from db.table1 t join \"hr\".\"emps\" e on e.\"empid\" = t.id"; runQuery(optiqConn, query1, Sets.newHashSet(100, 200)); }

      @Test
      public void testEnumerableWithJdbcJoin() throws SQLException

      { // * compared to testJdbcWithEnumerableJoin, the join order is reversed // * the query fails with a CannotPlanException String query2 = "select t.id, t.field1 " + "from \"hr\".\"emps\" e join db.table1 t on e.\"empid\" = t.id"; runQuery(optiqConn, query2, Sets.newHashSet(100, 200)); }

      @Test
      public void testEnumerableWithJdbcJoinWithWhereClause() throws SQLException

      { // Same query as above but with a where condition added: // * the good: this query does not give a CannotPlanException // * the bad: the result is wrong: there is only one emp called Bill. // The query plan shows the join condition is always true, // afaics, the join condition is pushed down to the non-jdbc // table. It might have something to do with the cast that // is introduced in the join condition. String query3 = "select t.id, t.field1 " + "from \"hr\".\"emps\" e join db.table1 t on e.\"empid\" = t.id" + " where e.\"name\" = 'Bill'"; runQuery(optiqConn, query3, Sets.newHashSet(100)); }

      private void runQuery(Connection optiqConn, String query,
      Set<Integer> expectedResult) throws SQLException {
      System.out.println("Query = " + query);

      // Print out the plan
      Statement stmt = optiqConn.createStatement();
      try {
      ResultSet rs = stmt.executeQuery("explain plan for " + query);
      rs.next();
      System.out.println(rs.getString(1));

      // Run the actual query
      rs = stmt.executeQuery(query);
      Set<Integer> ids = Sets.newHashSet();
      while (rs.next())

      { ids.add(rs.getInt(1)); }

      assertEquals(expectedResult, ids);
      } finally

      { stmt.close(); }

      }
      }
      ```

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/234
      Created by: bdumon
      Labels:
      Created at: Sun Apr 06 16:53:40 CEST 2014
      State: closed

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              github-import GitHub Import
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: