Repository: phoenix Updated Branches: refs/heads/4.13-HBase-1.1 3a7c4920d -> 2e174be96
PHOENIX-4508 Order-by not optimized in sort-merge-join on salted tables Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/2e174be9 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/2e174be9 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/2e174be9 Branch: refs/heads/4.13-HBase-1.1 Commit: 2e174be96a01c66b4e86bff5d9942a00b998e094 Parents: 3a7c492 Author: maryannxue <maryann....@gmail.com> Authored: Mon Jan 8 11:28:17 2018 -0800 Committer: maryannxue <maryann....@gmail.com> Committed: Mon Jan 8 11:28:17 2018 -0800 ---------------------------------------------------------------------- .../phoenix/end2end/SortMergeJoinMoreIT.java | 108 +++++++++++++++++++ .../compile/TupleProjectionCompiler.java | 7 +- 2 files changed, 111 insertions(+), 4 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e174be9/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java index e61332b..a132728 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/SortMergeJoinMoreIT.java @@ -634,4 +634,112 @@ public class SortMergeJoinMoreIT extends ParallelStatsDisabledIT { } } } + + @Test + public void testBug4508() throws Exception { + Properties props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + Connection conn = DriverManager.getConnection(getUrl(), props); + props = PropertiesUtil.deepCopy(TEST_PROPERTIES); + props.setProperty("TenantId", "010"); + Connection conn010 = DriverManager.getConnection(getUrl(), props); + try { + // Salted tables + String peopleTable = generateUniqueName(); + String myTable = generateUniqueName(); + conn.createStatement().execute("CREATE TABLE " + peopleTable + " (\n" + + "PERSON_ID VARCHAR NOT NULL,\n" + + "NAME VARCHAR\n" + + "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (PERSON_ID)) SALT_BUCKETS = 3"); + conn.createStatement().execute("CREATE TABLE " + myTable + " (\n" + + "LOCALID VARCHAR NOT NULL,\n" + + "DSID VARCHAR(255) NOT NULL, \n" + + "EID CHAR(40),\n" + + "HAS_CANDIDATES BOOLEAN\n" + + "CONSTRAINT PK_MYTABLE PRIMARY KEY (LOCALID, DSID)) SALT_BUCKETS = 3"); + verifyQueryPlanAndResultForBug4508(conn, peopleTable, myTable); + + // Salted multi-tenant tables + String peopleTable2 = generateUniqueName(); + String myTable2 = generateUniqueName(); + conn.createStatement().execute("CREATE TABLE " + peopleTable2 + " (\n" + + "TENANT_ID VARCHAR NOT NULL,\n" + + "PERSON_ID VARCHAR NOT NULL,\n" + + "NAME VARCHAR\n" + + "CONSTRAINT PK_TEST_PEOPLE PRIMARY KEY (TENANT_ID, PERSON_ID))\n" + + "SALT_BUCKETS = 3, MULTI_TENANT=true"); + conn.createStatement().execute("CREATE TABLE " + myTable2 + " (\n" + + "TENANT_ID VARCHAR NOT NULL,\n" + + "LOCALID VARCHAR NOT NULL,\n" + + "DSID VARCHAR(255) NOT NULL, \n" + + "EID CHAR(40),\n" + + "HAS_CANDIDATES BOOLEAN\n" + + "CONSTRAINT PK_MYTABLE PRIMARY KEY (TENANT_ID, LOCALID, DSID))\n" + + "SALT_BUCKETS = 3, MULTI_TENANT=true"); + verifyQueryPlanAndResultForBug4508(conn010, peopleTable2, myTable2); + } finally { + conn.close(); + conn010.close(); + } + } + + private static void verifyQueryPlanAndResultForBug4508( + Connection conn, String peopleTable, String myTable) throws Exception { + PreparedStatement peopleTableUpsertStmt = conn.prepareStatement( + "UPSERT INTO " + peopleTable + " VALUES(?, ?)"); + peopleTableUpsertStmt.setString(1, "X001"); + peopleTableUpsertStmt.setString(2, "Marcus"); + peopleTableUpsertStmt.execute(); + peopleTableUpsertStmt.setString(1, "X002"); + peopleTableUpsertStmt.setString(2, "Jenny"); + peopleTableUpsertStmt.execute(); + peopleTableUpsertStmt.setString(1, "X003"); + peopleTableUpsertStmt.setString(2, "Seymour"); + peopleTableUpsertStmt.execute(); + conn.commit(); + + PreparedStatement myTableUpsertStmt = conn.prepareStatement( + "UPSERT INTO " + myTable + " VALUES(?, ?, ?, ?)"); + myTableUpsertStmt.setString(1, "X001"); + myTableUpsertStmt.setString(2, "GROUP"); + myTableUpsertStmt.setString(3, null); + myTableUpsertStmt.setBoolean(4, false); + myTableUpsertStmt.execute(); + myTableUpsertStmt.setString(1, "X001"); + myTableUpsertStmt.setString(2, "PEOPLE"); + myTableUpsertStmt.setString(3, null); + myTableUpsertStmt.setBoolean(4, false); + myTableUpsertStmt.execute(); + myTableUpsertStmt.setString(1, "X003"); + myTableUpsertStmt.setString(2, "PEOPLE"); + myTableUpsertStmt.setString(3, null); + myTableUpsertStmt.setBoolean(4, false); + myTableUpsertStmt.execute(); + myTableUpsertStmt.setString(1, "X002"); + myTableUpsertStmt.setString(2, "PEOPLE"); + myTableUpsertStmt.setString(3, "Z990"); + myTableUpsertStmt.setBoolean(4, false); + myTableUpsertStmt.execute(); + conn.commit(); + + String query1 = "SELECT /*+ USE_SORT_MERGE_JOIN*/ COUNT(*)\n" + + "FROM " + peopleTable + " ds JOIN " + myTable + " l\n" + + "ON ds.PERSON_ID = l.LOCALID\n" + + "WHERE l.EID IS NULL AND l.DSID = 'PEOPLE' AND l.HAS_CANDIDATES = FALSE"; + String query2 = "SELECT /*+ USE_SORT_MERGE_JOIN */ COUNT(*)\n" + + "FROM (SELECT LOCALID FROM " + myTable + "\n" + + "WHERE EID IS NULL AND DSID = 'PEOPLE' AND HAS_CANDIDATES = FALSE) l\n" + + "JOIN " + peopleTable + " ds ON ds.PERSON_ID = l.LOCALID"; + + for (String q : new String[]{query1, query2}) { + ResultSet rs = conn.createStatement().executeQuery("explain " + q); + String plan = QueryUtil.getExplainPlan(rs); + assertFalse("Tables should not be sorted over their PKs:\n" + plan, + plan.contains("SERVER SORTED BY")); + + rs = conn.createStatement().executeQuery(q); + assertTrue(rs.next()); + assertEquals(2, rs.getInt(1)); + assertFalse(rs.next()); + } + } } http://git-wip-us.apache.org/repos/asf/phoenix/blob/2e174be9/phoenix-core/src/main/java/org/apache/phoenix/compile/TupleProjectionCompiler.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/compile/TupleProjectionCompiler.java b/phoenix-core/src/main/java/org/apache/phoenix/compile/TupleProjectionCompiler.java index 796dad0..9883de6 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/compile/TupleProjectionCompiler.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/compile/TupleProjectionCompiler.java @@ -161,10 +161,9 @@ public class TupleProjectionCompiler { public static PTable createProjectedTable(TableRef tableRef, List<ColumnRef> sourceColumnRefs, boolean retainPKColumns) throws SQLException { PTable table = tableRef.getTable(); - boolean hasSaltingColumn = retainPKColumns && table.getBucketNum() != null; List<PColumn> projectedColumns = new ArrayList<PColumn>(); - int position = hasSaltingColumn ? 1 : 0; - for (int i = position; i < sourceColumnRefs.size(); i++) { + int position = table.getBucketNum() != null ? 1 : 0; + for (int i = retainPKColumns ? position : 0; i < sourceColumnRefs.size(); i++) { ColumnRef sourceColumnRef = sourceColumnRefs.get(i); PColumn sourceColumn = sourceColumnRef.getColumn(); String colName = sourceColumn.getName().getString(); @@ -183,7 +182,7 @@ public class TupleProjectionCompiler { return PTableImpl.makePTable(table.getTenantId(), PROJECTED_TABLE_SCHEMA, table.getName(), PTableType.PROJECTED, null, table.getTimeStamp(), table.getSequenceNumber(), table.getPKName(), - retainPKColumns ? table.getBucketNum() : null, projectedColumns, null, null, + table.getBucketNum(), projectedColumns, null, null, Collections.<PTable> emptyList(), table.isImmutableRows(), Collections.<PName> emptyList(), null, null, table.isWALDisabled(), table.isMultiTenant(), table.getStoreNulls(), table.getViewType(), table.getViewIndexId(), null, table.rowKeyOrderOptimizable(), table.isTransactional(),
