Repository: phoenix Updated Branches: refs/heads/4.x-HBase-0.98 c22d5b38b -> 3dd1df291
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/3dd1df29 Tree: http://git-wip-us.apache.org/repos/asf/phoenix/tree/3dd1df29 Diff: http://git-wip-us.apache.org/repos/asf/phoenix/diff/3dd1df29 Branch: refs/heads/4.x-HBase-0.98 Commit: 3dd1df291506fe8b416a335c3053798465a6723f Parents: c22d5b3 Author: Ankit Singhal <ankitsingha...@gmail.com> Authored: Fri Oct 21 20:13:16 2016 +0530 Committer: Ankit Singhal <ankitsingha...@gmail.com> Committed: Fri Oct 21 20:13:16 2016 +0530 ---------------------------------------------------------------------- .../java/org/apache/phoenix/end2end/QueryWithOffsetIT.java | 8 ++++++++ .../src/it/java/org/apache/phoenix/end2end/UnionAllIT.java | 6 ++++-- .../src/main/java/org/apache/phoenix/execute/ScanPlan.java | 6 ++++-- .../src/main/java/org/apache/phoenix/execute/UnionPlan.java | 3 ++- .../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(+), 10 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/phoenix/blob/3dd1df29/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 d61b447..a531379 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; @@ -104,6 +105,13 @@ public class QueryWithOffsetIT extends ParallelStatsDisabledIT { 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/3dd1df29/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 419459a..eaeb6e3 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 @@ -625,10 +625,12 @@ public class UnionAllIT extends ParallelStatsDisabledIT { " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName1 + "\n" + " SERVER TOP 1 ROW SORTED BY [COL1]\n" + " CLIENT MERGE SORT\n" + + " CLIENT LIMIT 1\n" + " CLIENT PARALLEL 1-WAY FULL SCAN OVER " + tableName2 + "\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/3dd1df29/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/3dd1df29/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 e2c18b1..c6c5dd8 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 @@ -43,6 +43,7 @@ import org.apache.phoenix.jdbc.PhoenixStatement.Operation; import org.apache.phoenix.parse.FilterableStatement; import org.apache.phoenix.query.KeyRange; import org.apache.phoenix.schema.TableRef; + import com.google.common.collect.Sets; @@ -170,7 +171,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/3dd1df29/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 d4c8bef..25f3bec 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; @@ -347,7 +348,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/3dd1df29/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/3dd1df29/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/3dd1df29/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 7aef5a3..787ca33 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']",
