Repository: phoenix Updated Branches: refs/heads/3.0 05eb9a668 -> 4b511a0c4
PHOENIX-1182 UPSERT SELECT not working with joins; Add a test in DerivedTableIT Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/4b511a0c Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/4b511a0c Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/4b511a0c Branch: refs/heads/3.0 Commit: 4b511a0c4a20cd8b2c95ecc73b34f6d970035983 Parents: 05eb9a6 Author: maryannxue <maryann...@apache.org> Authored: Tue Aug 19 16:15:30 2014 -0400 Committer: maryannxue <maryann...@apache.org> Committed: Tue Aug 19 16:15:30 2014 -0400 ---------------------------------------------------------------------- .../apache/phoenix/end2end/DerivedTableIT.java | 13 ++++++ .../org/apache/phoenix/end2end/HashJoinIT.java | 49 ++++++++++++++++++++ .../apache/phoenix/compile/QueryCompiler.java | 13 +++--- 3 files changed, 69 insertions(+), 6 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/4b511a0c/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java index 2e45313..8a80764 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/DerivedTableIT.java @@ -395,6 +395,19 @@ public class DerivedTableIT extends BaseClientManagedTimeIT { } catch (SQLFeatureNotSupportedException e) { assertEquals(MSG, e.getMessage()); } + + // (groupby orderby) limit + query = "SELECT a, s FROM (SELECT a_string a, sum(a_byte) s FROM aTable GROUP BY a_string ORDER BY sum(a_byte)) LIMIT 2"; + statement = conn.prepareStatement(query); + rs = statement.executeQuery(); + assertTrue (rs.next()); + assertEquals(C_VALUE,rs.getString(1)); + assertEquals(9,rs.getInt(2)); + assertTrue (rs.next()); + assertEquals(A_VALUE,rs.getString(1)); + assertEquals(10,rs.getInt(2)); + + assertFalse(rs.next()); } finally { conn.close(); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/4b511a0c/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java index 02caa9d..d5f1297 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/HashJoinIT.java @@ -2530,6 +2530,55 @@ public class HashJoinIT extends BaseHBaseManagedTimeIT { assertNull(rs.getDate(5)); assertFalse(rs.next()); + + //Bug: PHOENIX-1182 + String sourceTable = "SOURCE_TABLE"; + String joinTable = "JOIN_TABLE"; + conn.createStatement().execute("CREATE TABLE " + sourceTable + + " (TID CHAR(3) NOT NULL, " + + " A UNSIGNED_INT NOT NULL, " + + " B UNSIGNED_INT NOT NULL " + + " CONSTRAINT pk PRIMARY KEY (TID, A, B))"); + conn.createStatement().execute("CREATE TABLE " + joinTable + + " (TID CHAR(3) NOT NULL, " + + " A UNSIGNED_INT NOT NULL, " + + " B UNSIGNED_INT NOT NULL, " + + " COUNT UNSIGNED_INT " + + " CONSTRAINT pk PRIMARY KEY (TID, A, B))"); + + PreparedStatement upsertStmt = conn.prepareStatement( + "upsert into " + sourceTable + "(TID, A, B) " + "values (?, ?, ?)"); + upsertStmt.setString(1, "1"); + upsertStmt.setInt(2, 1); + upsertStmt.setInt(3, 1); + upsertStmt.execute(); + upsertStmt.setString(1, "1"); + upsertStmt.setInt(2, 1); + upsertStmt.setInt(3, 2); + upsertStmt.execute(); + upsertStmt.setString(1, "1"); + upsertStmt.setInt(2, 1); + upsertStmt.setInt(3, 3); + upsertStmt.execute(); + upsertStmt.setString(1, "1"); + upsertStmt.setInt(2, 2); + upsertStmt.setInt(3, 1); + upsertStmt.execute(); + upsertStmt.setString(1, "1"); + upsertStmt.setInt(2, 2); + upsertStmt.setInt(3, 2); + upsertStmt.execute(); + conn.commit(); + + upsertStmt = conn.prepareStatement( + "upsert into " + joinTable + "(TID, A, B, COUNT) " + + "SELECT t1.TID, t1.A, t2.A, COUNT(*) " + + "FROM " + sourceTable + " t1 " + + "INNER JOIN " + sourceTable + " t2 ON t1.B = t2.B " + + "WHERE t1.A != t2.A AND t1.TID = '1' AND t2.TID = '1' " + + "GROUP BY t1.TID, t1.A, t2.A"); + upsertStmt.execute(); + conn.commit(); } finally { conn.close(); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/4b511a0c/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java index 20c0acd..1cf5514 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java @@ -129,7 +129,7 @@ public class QueryCompiler { JoinTable joinTable = JoinCompiler.compile(statement, select, context.getResolver()); return compileJoinQuery(context, binds, joinTable, false); } else { - return compileSingleQuery(context, select, binds, parallelIteratorFactory, true); + return compileSingleQuery(context, select, binds, false, true); } } @@ -146,7 +146,7 @@ public class QueryCompiler { context.setCurrentTable(table.getTableRef()); context.setResolver(projectedTable.createColumnResolver()); table.projectColumns(context.getScan()); - return compileSingleQuery(context, subquery, binds, null, true); + return compileSingleQuery(context, subquery, binds, asSubquery, true); } QueryPlan plan = compileSubquery(subquery); ProjectedPTableWrapper projectedTable = table.createProjectedTable(plan.getProjector()); @@ -221,7 +221,7 @@ public class QueryCompiler { } context.setCurrentTable(tableRef); context.setResolver(needsProject ? projectedTable.createColumnResolver() : joinTable.getOriginalResolver()); - BasicQueryPlan plan = compileSingleQuery(context, query, binds, parallelIteratorFactory, joinTable.isAllLeftJoin()); + BasicQueryPlan plan = compileSingleQuery(context, query, binds, asSubquery, joinTable.isAllLeftJoin()); Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context); Integer limit = null; if (query.getLimit() != null && !query.isAggregate() && !query.isDistinct() && query.getOrderBy().isEmpty()) { @@ -276,7 +276,7 @@ public class QueryCompiler { TupleProjector.serializeProjectorIntoScan(context.getScan(), rhsProjTable.createTupleProjector()); context.setCurrentTable(rhsTableRef); context.setResolver(projectedTable.createColumnResolver()); - BasicQueryPlan rhsPlan = compileSingleQuery(context, rhs, binds, parallelIteratorFactory, type == JoinType.Right); + BasicQueryPlan rhsPlan = compileSingleQuery(context, rhs, binds, asSubquery, type == JoinType.Right); Expression postJoinFilterExpression = joinTable.compilePostFilterExpression(context); Integer limit = null; if (rhs.getLimit() != null && !rhs.isAggregate() && !rhs.isDistinct() && rhs.getOrderBy().isEmpty()) { @@ -297,7 +297,7 @@ public class QueryCompiler { return statement.getConnection().getQueryServices().getOptimizer().optimize(statement, plan); } - protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, ParallelIteratorFactory parallelIteratorFactory, boolean allowPageFilter) throws SQLException{ + protected BasicQueryPlan compileSingleQuery(StatementContext context, SelectStatement select, List<Object> binds, boolean asSubquery, boolean allowPageFilter) throws SQLException{ PhoenixConnection connection = statement.getConnection(); ColumnResolver resolver = context.getResolver(); TableRef tableRef = context.getCurrentTable(); @@ -324,7 +324,7 @@ public class QueryCompiler { WhereCompiler.compile(context, select, viewWhere); context.setResolver(resolver); // recover resolver OrderBy orderBy = OrderByCompiler.compile(context, select, groupBy, limit); - RowProjector projector = ProjectionCompiler.compile(context, select, groupBy, targetColumns); + RowProjector projector = ProjectionCompiler.compile(context, select, groupBy, asSubquery ? Collections.<PDatum>emptyList() : targetColumns); // Final step is to build the query plan int maxRows = statement.getMaxRows(); @@ -335,6 +335,7 @@ public class QueryCompiler { limit = maxRows; } } + ParallelIteratorFactory parallelIteratorFactory = asSubquery ? null : this.parallelIteratorFactory; if (select.isAggregate() || select.isDistinct()) { return new AggregatePlan(context, select, tableRef, projector, limit, orderBy, parallelIteratorFactory, groupBy, having); } else {