Repository: phoenix Updated Branches: refs/heads/4.8-HBase-0.98 e3e368bdc -> 29632f9ef
PHOENIX-3342 ORDER BY and LIMIT+OFFSET doesnt work on second column from compound key(Ankit Singhal/Sergey Soldatov) Project: http://git-wip-us.apache.org/repos/asf/phoenix/repo Commit: http://git-wip-us.apache.org/repos/asf/phoenix/commit/29632f9e Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/29632f9e Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/29632f9e Branch: refs/heads/4.8-HBase-0.98 Commit: 29632f9efc477a23eee4fa529d40bf6d310e2512 Parents: e3e368b Author: Ankit Singhal <ankitsingha...@gmail.com> Authored: Fri Oct 21 20:22:36 2016 +0530 Committer: Ankit Singhal <ankitsingha...@gmail.com> Committed: Fri Oct 21 20:22:36 2016 +0530 ---------------------------------------------------------------------- .../java/org/apache/phoenix/end2end/QueryWithOffsetIT.java | 8 ++++++++ .../src/it/java/org/apache/phoenix/end2end/UnionAllIT.java | 8 +++++--- .../src/main/java/org/apache/phoenix/execute/ScanPlan.java | 6 ++++-- .../src/main/java/org/apache/phoenix/execute/UnionPlan.java | 2 +- .../org/apache/phoenix/iterate/BaseResultIterators.java | 3 ++- .../apache/phoenix/iterate/MergeSortTopNResultIterator.java | 3 +++ .../phoenix/compile/StatementHintsCompilationTest.java | 2 +- .../test/java/org/apache/phoenix/query/QueryPlanTest.java | 9 ++++++--- 8 files changed, 30 insertions(+), 11 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java index 4452176..7b1aabd 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java @@ -19,6 +19,7 @@ package org.apache.phoenix.end2end; import static org.apache.phoenix.util.TestUtil.TEST_PROPERTIES; import static org.junit.Assert.assertEquals; +import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; import java.sql.Connection; @@ -109,6 +110,13 @@ public class QueryWithOffsetIT extends BaseOwnClusterHBaseManagedTimeIT { assertTrue(rs.next()); assertEquals(strings[i - 1], rs.getString(1)); } + limit =1; + offset=1; + rs = conn.createStatement() + .executeQuery("SELECT k2 from " + tableName + " order by k2 desc limit " + limit + " offset " + offset); + assertTrue(rs.next()); + assertEquals(25, rs.getInt(1)); + assertFalse(rs.next()); conn.close(); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java index 57ba858..b8ee6d3 100644 --- a/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java +++ b/phoenix-core/src/it/java/org/apache/phoenix/end2end/UnionAllIT.java @@ -612,11 +612,13 @@ public class UnionAllIT extends BaseOwnClusterHBaseManagedTimeIT { "UNION ALL OVER 2 QUERIES\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER TEST_TABLE\n" + " SERVER TOP 1 ROW SORTED BY [COL1]\n" + - " CLIENT MERGE SORT\n" + + " CLIENT MERGE SORT\n" + + " CLIENT LIMIT 1\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER B_TABLE\n" + " SERVER TOP 1 ROW SORTED BY [COL1]\n" + - " CLIENT MERGE SORT\n" + - "CLIENT MERGE SORT", QueryUtil.getExplainPlan(rs)); + " CLIENT MERGE SORT\n" + + " CLIENT LIMIT 1\n" + + "CLIENT MERGE SORT\nCLIENT LIMIT 1", QueryUtil.getExplainPlan(rs)); String limitPlan = "UNION ALL OVER 2 QUERIES\n" + http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java b/phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java index 7f735b7..ebe4441 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java @@ -96,7 +96,9 @@ public class ScanPlan extends BaseQueryPlan { if (isOrdered) { // TopN int thresholdBytes = context.getConnection().getQueryServices().getProps().getInt( QueryServices.SPOOL_THRESHOLD_BYTES_ATTRIB, QueryServicesOptions.DEFAULT_SPOOL_THRESHOLD_BYTES); - ScanRegionObserver.serializeIntoScan(context.getScan(), thresholdBytes, limit == null ? -1 : limit, orderBy.getOrderByExpressions(), projector.getEstimatedRowByteSize()); + ScanRegionObserver.serializeIntoScan(context.getScan(), thresholdBytes, + limit == null ? -1 : QueryUtil.getOffsetLimit(limit, offset), orderBy.getOrderByExpressions(), + projector.getEstimatedRowByteSize()); } Integer perScanLimit = !allowPageFilter || isOrdered ? null : limit; perScanLimit = QueryUtil.getOffsetLimit(perScanLimit, offset); @@ -199,7 +201,7 @@ public class ScanPlan extends BaseQueryPlan { * limit is provided, run query serially. */ boolean isOrdered = !orderBy.getOrderByExpressions().isEmpty(); - Integer perScanLimit = QueryUtil.getOffsetLimit(!allowPageFilter || isOrdered ? null : limit, offset); + Integer perScanLimit = !allowPageFilter || isOrdered ? null : QueryUtil.getOffsetLimit(limit, offset); boolean isOffsetOnServer = isOffsetPossibleOnServer(context, orderBy, offset, isSalted, table.getIndexType()); /* * For queries that are doing a row key order by and are not possibly querying more than a http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java b/phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java index cf95b5b..1814dee 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java @@ -175,7 +175,7 @@ public class UnionPlan implements QueryPlan { ResultIterator iterator = iterator(); iterator.explain(steps); // Indent plans steps nested under union, except last client-side merge/concat step (if there is one) - int offset = !orderBy.getOrderByExpressions().isEmpty() || limit != null ? 1 : 0; + int offset = !orderBy.getOrderByExpressions().isEmpty() && limit != null ? 2 : limit != null ? 1 : 0; for (int i = 1 ; i < steps.size()-offset; i++) { steps.set(i, " " + steps.get(i)); } http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java b/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java index 2685b93..581e0cd 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java @@ -93,6 +93,7 @@ import org.apache.phoenix.util.Closeables; import org.apache.phoenix.util.LogUtil; import org.apache.phoenix.util.PrefixByteCodec; import org.apache.phoenix.util.PrefixByteDecoder; +import org.apache.phoenix.util.QueryUtil; import org.apache.phoenix.util.SQLCloseables; import org.apache.phoenix.util.ScanUtil; import org.apache.phoenix.util.SchemaUtil; @@ -348,7 +349,7 @@ public abstract class BaseResultIterators extends ExplainTable implements Result public BaseResultIterators(QueryPlan plan, Integer perScanLimit, Integer offset, ParallelScanGrouper scanGrouper, Scan scan) throws SQLException { super(plan.getContext(), plan.getTableRef(), plan.getGroupBy(), plan.getOrderBy(), - plan.getStatement().getHint(), plan.getLimit(), offset); + plan.getStatement().getHint(), QueryUtil.getOffsetLimit(plan.getLimit(), plan.getOffset()), offset); this.plan = plan; this.scan = scan; this.scanGrouper = scanGrouper; http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/main/java/org/apache/phoenix/iterate/MergeSortTopNResultIterator.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/main/java/org/apache/phoenix/iterate/MergeSortTopNResultIterator.java b/phoenix-core/src/main/java/org/apache/phoenix/iterate/MergeSortTopNResultIterator.java index a9d8046..42429b1 100644 --- a/phoenix-core/src/main/java/org/apache/phoenix/iterate/MergeSortTopNResultIterator.java +++ b/phoenix-core/src/main/java/org/apache/phoenix/iterate/MergeSortTopNResultIterator.java @@ -103,6 +103,9 @@ public class MergeSortTopNResultIterator extends MergeSortResultIterator { if (offset > 0) { planSteps.add("CLIENT OFFSET " + offset); } + if (limit > 0) { + planSteps.add("CLIENT LIMIT " + limit); + } } @Override http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/test/java/org/apache/phoenix/compile/StatementHintsCompilationTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/compile/StatementHintsCompilationTest.java b/phoenix-core/src/test/java/org/apache/phoenix/compile/StatementHintsCompilationTest.java index 394bf27..8e3bb59 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/compile/StatementHintsCompilationTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/compile/StatementHintsCompilationTest.java @@ -101,7 +101,7 @@ public class StatementHintsCompilationTest extends BaseConnectionlessQueryTest { assertEquals("CLIENT PARALLEL 1-WAY RANGE SCAN OVER EH ['111111111111111','foo ','2012-11-01 00:00:00.000'] - ['111111111111111','fop ','2012-11-30 00:00:00.000']\n" + " SERVER FILTER BY FIRST KEY ONLY AND (CREATED_DATE >= DATE '2012-11-01 00:00:00.000' AND CREATED_DATE < DATE '2012-11-30 00:00:00.000')\n" + " SERVER TOP 100 ROWS SORTED BY [ORGANIZATION_ID, PARENT_ID, CREATED_DATE DESC, ENTITY_HISTORY_ID]\n" + - "CLIENT MERGE SORT",QueryUtil.getExplainPlan(rs)); + "CLIENT MERGE SORT\nCLIENT LIMIT 100",QueryUtil.getExplainPlan(rs)); } @Test http://git-wip-us.apache.org/repos/asf/phoenix/blob/29632f9e/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java ---------------------------------------------------------------------- diff --git a/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java b/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java index 37010b3..d24131a 100644 --- a/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java +++ b/phoenix-core/src/test/java/org/apache/phoenix/query/QueryPlanTest.java @@ -112,7 +112,8 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { "SELECT a_string FROM atable ORDER BY a_string DESC LIMIT 3", "CLIENT PARALLEL 1-WAY FULL SCAN OVER ATABLE\n" + " SERVER TOP 3 ROWS SORTED BY [A_STRING DESC]\n" + - "CLIENT MERGE SORT", + "CLIENT MERGE SORT\n" + + "CLIENT LIMIT 3" , "SELECT count(1) FROM atable GROUP BY a_string,b_string HAVING max(a_string) = 'a'", "CLIENT PARALLEL 1-WAY FULL SCAN OVER ATABLE\n" + @@ -144,7 +145,8 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' ORDER BY a_string ASC NULLS FIRST LIMIT 10", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" + " SERVER TOP 10 ROWS SORTED BY [A_STRING]\n" + - "CLIENT MERGE SORT", + "CLIENT MERGE SORT\n" + + "CLIENT LIMIT 10", "SELECT max(a_integer) FROM atable WHERE organization_id = '000000000000001' GROUP BY organization_id,entity_id,ROUND(a_date,'HOUR') ORDER BY entity_id NULLS LAST LIMIT 10", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" + @@ -155,7 +157,8 @@ public class QueryPlanTest extends BaseConnectionlessQueryTest { "SELECT a_string,b_string FROM atable WHERE organization_id = '000000000000001' ORDER BY a_string DESC NULLS LAST LIMIT 10", "CLIENT PARALLEL 1-WAY RANGE SCAN OVER ATABLE ['000000000000001']\n" + " SERVER TOP 10 ROWS SORTED BY [A_STRING DESC NULLS LAST]\n" + - "CLIENT MERGE SORT", + "CLIENT MERGE SORT\n" + + "CLIENT LIMIT 10", "SELECT a_string,b_string FROM atable WHERE organization_id IN ('000000000000001', '000000000000005')", "CLIENT PARALLEL 1-WAY SKIP SCAN ON 2 KEYS OVER ATABLE ['000000000000001'] - ['000000000000005']",
