[jira] [Created] (CALCITE-597) JdbcJoin: Wrong generated column list when join table with same column name

["Ng Jiunn Jye (JIRA)" <ji...@apache.org>]

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)