Samarth Jain created PHOENIX-839:
------------------------------------
Summary: Joining on a dynamic column doesn't work
Key: PHOENIX-839
URL: https://issues.apache.org/jira/browse/PHOENIX-839
Project: Phoenix
Issue Type: Bug
Environment: {code}
@Test
public void testJoinOnDynamicColumns() throws Exception {
String tableA = "tableA";
String tableB = "tableB";
Properties props = new Properties(TEST_PROPERTIES);
Connection conn = null;
PreparedStatement stmt = null;
try {
conn = DriverManager.getConnection(PHOENIX_JDBC_URL, props);
String ddlA = "CREATE TABLE " + tableA
+ " (pkA INTEGER NOT NULL, "
+ " colA1 INTEGER, "
+ " colA2 VARCHAR "
+ "CONSTRAINT PK PRIMARY KEY"
+ "(pkA)"
+ ")";
String ddlB = "CREATE TABLE " + tableB
+ " (pkB INTEGER NOT NULL PRIMARY KEY, "
+ " colB INTEGER)";
stmt = conn.prepareStatement(ddlA);
stmt.execute();
stmt.close();
stmt = conn.prepareStatement(ddlB);
stmt.execute();
stmt.close();
String upsertA = "UPSERT INTO TABLEA (pkA, colA1, colA2)
VALUES(?, ?, ?)";
stmt = conn.prepareStatement(upsertA);
int i = 0;
for (i = 0; i < 5; i++) {
stmt.setInt(1, i);
stmt.setInt(2, i + 10);
stmt.setString(3, "00" + i);
stmt.executeUpdate();
}
conn.commit();
stmt.close();
//upsert select dynamic columns in tableB
conn.createStatement().execute("CREATE SEQUENCE SEQB");
String upsertBSelectA = "UPSERT INTO TABLEB (pkB, pkA INTEGER)"
+ "SELECT NEXT VALUE
FOR SEQB, pkA FROM TABLEA";
stmt = conn.prepareStatement(upsertBSelectA);
stmt.executeUpdate();
stmt.close();
conn.commit();
conn.createStatement().execute("DROP SEQUENCE SEQB");
//perform a join between tableB and tableA by joining on the
dynamic column that we upserted in
//tableB. This join should return all the rows from table A.
String joinSql = "SELECT A.pkA, A.COLA1, A.colA2 FROM TABLEB
B(pkA INTEGER) JOIN TABLEA A ON a.pkA = b.pkA";
stmt = conn.prepareStatement(joinSql);
ResultSet rs = stmt.executeQuery();
i = 0;
while(rs.next()) {
//check that we get back all the rows that we upserted
for tableA above.
assertEquals(rs.getInt(1), i);
assertEquals(rs.getInt(2), i + 10);
assertEquals(rs.getInt(3), "00" + i);
i++;
}
assertEquals(5, 4);
} finally {
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
}
Exception stacktrace:
org.apache.phoenix.schema.ColumnNotFoundException: ERROR 504 (42703): Undefined
column. columnName=PKA
at org.apache.phoenix.schema.PTableImpl.getColumn(PTableImpl.java:508)
at
org.apache.phoenix.compile.FromCompiler$SingleTableColumnResolver.resolveColumn(FromCompiler.java:249)
at
org.apache.phoenix.compile.ExpressionCompiler.resolveColumn(ExpressionCompiler.java:311)
at
org.apache.phoenix.compile.ProjectionCompiler$SelectClauseVisitor.resolveColumn(ProjectionCompiler.java:501)
at
org.apache.phoenix.compile.ExpressionCompiler.visit(ExpressionCompiler.java:329)
at
org.apache.phoenix.compile.ExpressionCompiler.visit(ExpressionCompiler.java:1)
at
org.apache.phoenix.parse.ColumnParseNode.accept(ColumnParseNode.java:50)
at
org.apache.phoenix.compile.ProjectionCompiler.compile(ProjectionCompiler.java:302)
at
org.apache.phoenix.compile.QueryCompiler.compileSingleQuery(QueryCompiler.java:264)
at
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:128)
at
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:75)
at
org.apache.phoenix.optimize.QueryOptimizer.optimize(QueryOptimizer.java:70)
at
org.apache.phoenix.compile.JoinCompiler.optimize(JoinCompiler.java:1062)
at
org.apache.phoenix.compile.QueryCompiler.compile(QueryCompiler.java:120)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:264)
at
org.apache.phoenix.jdbc.PhoenixStatement$ExecutableSelectStatement.compilePlan(PhoenixStatement.java:1)
at
org.apache.phoenix.jdbc.PhoenixStatement.executeQuery(PhoenixStatement.java:199)
at
org.apache.phoenix.jdbc.PhoenixPreparedStatement.executeQuery(PhoenixPreparedStatement.java:157)
at
org.apache.phoenix.end2end.HashJoinTest.testJoinOnDynamicColumns(HashJoinTest.java:2309)
{code}
Reporter: Samarth Jain
Assignee: James Taylor
--
This message was sent by Atlassian JIRA
(v6.2#6252)
