You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Ng Jiunn Jye (JIRA)" <ji...@apache.org> on 2015/03/24 08:56:52 UTC
[jira] [Commented] (CALCITE-597) JdbcJoin: Wrong generated column
list when join table with same column name
[ https://issues.apache.org/jira/browse/CALCITE-597?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14377455#comment-14377455 ]
Ng Jiunn Jye commented on CALCITE-597:
--------------------------------------
Hi Julian,
This issue is fixed in CALCITE-631.
Thank you.
> 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)