[CALCITE-229] Join between different JDBC schemas not implementable

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Labels: github-import
• Assignee: Unassigned
• Reporter: GitHub Import
• Created: 18/Jun/14 08:42
• Updated: 18/Jun/14 08:42
• Resolved: 18/Jun/14 08:42

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