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

        Activity

        Hide
        github-import GitHub Import added a comment -

        [Date: Thu Apr 03 19:20:33 CEST 2014, Author: julianhyde]

        Interesting. I would have expected this to work, but since we didn't test it, of course it didn't. I'd have expected EnumerableJoinRule to kick in. Check that that rule is registered.

        Note that each JDBC data source has a different instance of JdbcConvention. This is as it should be. Maybe some rule is being confused because there are two similar but not identical instances of the same trait.

        Can you create a test based on something similar to JdbcTest.FOODMART_MODEL but with two JDBC schemas (both pointing to the same database). See if this issue reproduces there. That will tell us something.

        I'll look into this in a few days when I have some more time.

        Show
        github-import GitHub Import added a comment - [Date: Thu Apr 03 19:20:33 CEST 2014, Author: julianhyde ] Interesting. I would have expected this to work, but since we didn't test it, of course it didn't. I'd have expected EnumerableJoinRule to kick in. Check that that rule is registered. Note that each JDBC data source has a different instance of JdbcConvention. This is as it should be. Maybe some rule is being confused because there are two similar but not identical instances of the same trait. Can you create a test based on something similar to JdbcTest.FOODMART_MODEL but with two JDBC schemas (both pointing to the same database). See if this issue reproduces there. That will tell us something. I'll look into this in a few days when I have some more time.

          People

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

            Dates

            • Created:
              Updated:
              Resolved:

              Development