phoenix-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "James Taylor (JIRA)" <j...@apache.org>
Subject [jira] [Resolved] (PHOENIX-839) Joining on a dynamic column doesn't work
Date Fri, 02 May 2014 04:40:15 GMT

     [ https://issues.apache.org/jira/browse/PHOENIX-839?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

James Taylor resolved PHOENIX-839.
----------------------------------

       Resolution: Fixed
    Fix Version/s: 5.0.0
                   4.0.0
                   3.0.0

> 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
>             Fix For: 3.0.0, 4.0.0, 5.0.0
>
>         Attachments: dynColForJoin.patch, dynColForJoin2.patch
>
>




--
This message was sent by Atlassian JIRA
(v6.2#6252)

Mime
View raw message