[PHOENIX-839] Joining on a dynamic column doesn't work

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 3.0.0, 4.0.0

Environment

 @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)

• Assignee: James R. Taylor
• Reporter: Samarth Jain
• Created: 11/Mar/14 23:55
• Updated: 21/Nov/15 02:16
• Resolved: 02/May/14 04:39