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

Join between different JDBC schemas not implementable

    Details

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

      Description

      Trying to join between two tables in two jdbc schemas fails with a RelOptPlanner$CannotPlanException.

      I've created a small test to illustrate the problem:

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

      import net.hydromatic.optiq.SchemaPlus;
      import net.hydromatic.optiq.impl.jdbc.JdbcSchema;
      import net.hydromatic.optiq.jdbc.OptiqConnection;
      import org.junit.Test;

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

      public class MultiJdbcSchemaJoinTest {
      @Test
      public void test() throws SQLException, ClassNotFoundException {
      // Create two databases
      // It's two times hsqldb, but imagine they are different rdbms's
      Connection c1 = DriverManager.getConnection("jdbc:hsqldb:mem:db1", "", "");
      Statement stmt1 = c1.createStatement();
      stmt1.execute("create table table1(id varchar(10) not null primary key, "
      + "field1 varchar(10))");
      stmt1.execute("insert into table1 values('a', 'aaaa')");
      c1.close();

      Connection c2 = DriverManager.getConnection("jdbc:hsqldb:mem:db2", "", "");
      Statement stmt2 = c2.createStatement();
      stmt2.execute("create table table2(id varchar(10) not null primary key, "
      + "field1 varchar(10))");
      stmt2.execute("insert into table2 values('a', 'aaaa')");
      c2.close();

      // Connect via optiq to these databases
      Connection connection = DriverManager.getConnection("jdbc:optiq:");
      OptiqConnection optiqConnection =
      connection.unwrap(OptiqConnection.class);
      SchemaPlus rootSchema = optiqConnection.getRootSchema();
      rootSchema.add("DB1", JdbcSchema.create(rootSchema, "DB1",
      JdbcSchema.dataSource("jdbc:hsqldb:mem:db1",
      "org.hsqldb.jdbcDriver", "", ""), null, null));
      rootSchema.add("DB2", JdbcSchema.create(rootSchema, "DB2",
      JdbcSchema.dataSource("jdbc:hsqldb:mem:db2",
      "org.hsqldb.jdbcDriver", "", ""), null, null));

      Statement stmt3 = connection.createStatement();
      ResultSet rs = stmt3.executeQuery("select table1.id, table1.field1 "
      + "from db1.table1 join db2.table2 on table1.id = table2.id");
      while (rs.next())

      { System.out.println(rs.getString("id")); }

      }
      }
      ```

      Its execution fails with:

      ```
      java.sql.SQLException: while executing SQL: select table1.id, table1.field1 from db1.table1 join db2.table2 on table1.id = table2.id
      at net.hydromatic.avatica.Helper.createException(Helper.java:40)
      at net.hydromatic.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:80)
      at net.hydromatic.optiq.test.MultiJdbcSchemaJoinTest.test(MultiJdbcSchemaJoinTest.java:47)
      [snipped]
      Caused by: org.eigenbase.relopt.RelOptPlanner$CannotPlanException: Node [rel(#18 | FLINK-18):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]] could not be implemented; planner state:

      Root: rel(#18 | FLINK-18):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]
      Original rel:
      AbstractConverter(subset=[rel(#18 | FLINK-18):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).ENUMERABLE.[]], convention=[ENUMERABLE], sort=[[]]): rowcount = 1.7976931348623157E308, cumulative cost =

      {inf}

      , id = 19
      ProjectRel(subset=[rel(#14 | FLINK-14):Subset([#4|https://github.com/JulianHyde/optiq/issues/4] | FLINK-4).NONE.[]], ID=[$0], FIELD1=[$1]): rowcount = 1.7976931348623157E308, cumulative cost =

      {1.7976931348623157E308 rows, Infinity cpu, 0.0 io}

      , id = 13
      JoinRel(subset=[rel(#12 | FLINK-12):Subset([#3|https://github.com/JulianHyde/optiq/issues/3] | FLINK-3).NONE.[]], condition=[=($0, $2)], joinType=[inner]): rowcount = 1.7976931348623157E308, cumulative cost =

      {1.7976931348623157E308 rows, 0.0 cpu, 0.0 io}

      , id = 11
      JdbcTableScan(subset=[rel(#7 | FLINK-7):Subset([#0|https://github.com/JulianHyde/optiq/issues/0] | FLINK-0).JDBC.DB1.[]], table=[[DB1, TABLE1]]): rowcount = 100.0, cumulative cost =

      {100.0 rows, 101.0 cpu, 0.0 io}, id = 0
      ProjectRel(subset=[rel(#10 | FLINK-10):Subset([#2|https://github.com/JulianHyde/optiq/issues/2] | FLINK-2).NONE.[]], ID=[$0]): rowcount = 100.0, cumulative cost = {100.0 rows, 100.0 cpu, 0.0 io}, id = 9
      JdbcTableScan(subset=[rel(#8 | FLINK-8):Subset([#1|https://github.com/JulianHyde/optiq/issues/1] | FLINK-1).JDBC.DB2.[]], table=[[DB2, TABLE2]]): rowcount = 100.0, cumulative cost = {100.0 rows, 101.0 cpu, 0.0 io}

      , id = 1

      Sets:
      [snipped]
      ```

      ---------------- Imported from GitHub ----------------
      Url: https://github.com/julianhyde/optiq/issues/229
      Created by: bdumon
      Labels:
      Created at: Thu Apr 03 16:01:59 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: