Ng Jiunn Jye created CALCITE-597:
------------------------------------
Summary: JdbcJoin: Wrong generated column list when join table
with same column name
Key: CALCITE-597
URL: https://issues.apache.org/jira/browse/CALCITE-597
Project: Calcite
Issue Type: Bug
Affects Versions: 1.0.0-incubating
Environment: Remote H2 Database.
Reporter: Ng Jiunn Jye
Assignee: Julian Hyde
Problem
Generated SQL Select contain wrong column names when joining 2 table which
contain same column name.
Executed Query:
select tint.rnum, tint2.rnum from tint, tint2 where tint.cint >= tint2.cint
Generated Query:
SELECT "RNUM", "RNUM0"
FROM "TINT"
INNER JOIN "TINT2"
WHERE "TINT"."CINT" >= "TINT2"."CINT"
Column RNUM is ambiguous and Column RNUM0 is not valid.
** this problem only only happened when JDBCJoin rule kick in.
Error Message:
java.sql.SQLException: exception while executing query: while executing SQL
[SELECT "RNUM", "RNUM0"
FROM "TINT"
INNER JOIN "TINT2"
WHERE "TINT"."CINT" >= "TINT2"."CINT"]
at org.apache.calcite.avatica.Helper.createException(Helper.java:39)
at
org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:430)
at
org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:89)
at
org.apache.calcite.jdbc.TestJdbcAdaptorAmbiguousColumn.main(TestJdbcAdaptorAmbiguousColumn.java:74)
Caused by: java.lang.RuntimeException: while executing SQL [SELECT "RNUM",
"RNUM0"
FROM "TINT"
INNER JOIN "TINT2"
WHERE "TINT"."CINT" >= "TINT2"."CINT"]
at
org.apache.calcite.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:149)
at
org.apache.calcite.linq4j.AbstractEnumerable.iterator(AbstractEnumerable.java:33)
at org.apache.calcite.avatica.MetaImpl.createCursor(MetaImpl.java:74)
at
org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:62)
at
org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:1)
at
org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:428)
... 2 more
Caused by: org.h2.jdbc.JdbcSQLException: Ambiguous column name "RNUM"; SQL
statement:
SELECT "RNUM", "RNUM0"
FROM "TINT"
INNER JOIN "TINT2"
WHERE "TINT"."CINT" >= "TINT2"."CINT" [90059-175]
at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
at org.h2.message.DbException.get(DbException.java:172)
Test Code:
package org.apache.calcite.jdbc;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Properties;
import org.h2.Driver;
//import org.hsqldb.jdbcDriver;
public class TestJdbcAdaptorAmbiguousColumn {
public static void main(String[] args) {
try {
String hsqldbMemUrl = "jdbc:h2:mem:test";
Connection baseConnection =
DriverManager.getConnection(hsqldbMemUrl);
Statement baseStmt =
baseConnection.createStatement();
baseStmt.execute("CREATE TABLE TINT (RNUM INTEGER,
CINT INTEGER)");
baseStmt.execute("INSERT INTO TINT VALUES (0, 1)");
baseStmt.execute("CREATE TABLE TINT2 (RNUM INTEGER,
CINT INTEGER)");
baseStmt.execute("INSERT INTO TINT2 VALUES (0, 1)");
baseStmt.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: '" + hsqldbMemUrl +
"',\n"
+ " jdbcCatalog: null,\n"
+ " jdbcSchema: null\n"
+ " }\n"
+ " ]\n"
+ "}");
Connection calciteConnection =
DriverManager.getConnection("jdbc:calcite:", info);
ResultSet rs =
calciteConnection.prepareStatement("select tint.rnum, tint2.rnum from tint,
tint2 where tint.cint >= tint2.cint").executeQuery();
while (rs.next()){
System.out.println ("tint.rnum:" + (Integer)
rs.getObject(1));
System.out.println ("tdbl.rnum:" + (Integer)
rs.getObject(2));
}
rs.close();
calciteConnection.close();
}catch (Exception e){
e.printStackTrace();
}
}
}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
