[
https://issues.apache.org/jira/browse/PHOENIX-839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Samarth Jain updated PHOENIX-839:
---------------------------------
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 column pkA from TABLEA dynamically into 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); //check that we got back all the rows.
} 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}
was:
{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}
> 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 column pkA from TABLEA dynamically into 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); //check that we got back all the rows.
> } 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)
