You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@phoenix.apache.org by "James Taylor (JIRA)" <ji...@apache.org> on 2014/03/12 02:59:42 UTC
[jira] [Commented] (PHOENIX-839) Joining on a dynamic column
doesn't work
[ https://issues.apache.org/jira/browse/PHOENIX-839?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13931269#comment-13931269 ]
James Taylor commented on PHOENIX-839:
--------------------------------------
[~maryannxue] - this allows the use of dynamic columns in join queries. Look ok?
> 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
> Attachments: dynColForJoin.patch
>
>
--
This message was sent by Atlassian JIRA
(v6.2#6252)