[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17055031#comment-17055031
]
Hudson commented on PHOENIX-4845:
-
FAILURE: Integrated in Jenkins build PreCommit-PHOENIX-Build #3553 (See
[https://builds.apache.org/job/PreCommit-PHOENIX-Build/3553/])
PHOENIX-4845 Support using Row Value Constructors in OFFSET clause for
(yanxinyi: rev 6a2ad3ed4517ff23e0e5dff3f364c1c0673b8efc)
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/OffsetNode.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetInternalErrorException.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotCoercibleException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
* (edit)
phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/parse/QueryParserTest.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/TraceQueryPlan.java
* (add)
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/ListJarsQueryPlan.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotAllowedInQueryException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
* (edit) phoenix-core/src/main/antlr3/PhoenixSQL.g
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/QueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
* (add)
phoenix-core/src/test/java/org/apache/phoenix/compile/RVCOffsetCompilerTest.java
* (edit) phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
* (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054679#comment-17054679
]
Hudson commented on PHOENIX-4845:
-
SUCCESS: Integrated in Jenkins build Phoenix-4.x-HBase-1.4 #411 (See
[https://builds.apache.org/job/Phoenix-4.x-HBase-1.4/411/])
PHOENIX-4845 Support using Row Value Constructors in OFFSET clause for
(yanxinyi: rev 53813baf84ee8ffc946682a85b953995800d6a12)
* (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/TraceQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/OffsetNode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotAllowedInQueryException.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/parse/QueryParserTest.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/QueryPlan.java
* (add)
phoenix-core/src/test/java/org/apache/phoenix/compile/RVCOffsetCompilerTest.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
* (edit)
phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetInternalErrorException.java
* (add)
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotCoercibleException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
* (edit) phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/ListJarsQueryPlan.java
* (edit) phoenix-core/src/main/antlr3/PhoenixSQL.g
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054678#comment-17054678
]
Hudson commented on PHOENIX-4845:
-
SUCCESS: Integrated in Jenkins build Phoenix-4.x-HBase-1.3 #698 (See
[https://builds.apache.org/job/Phoenix-4.x-HBase-1.3/698/])
PHOENIX-4845 Support using Row Value Constructors in OFFSET clause for
(yanxinyi: rev c717d6ab0519c9c6dabe6c82981963f643f0a879)
* (edit)
phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetInternalErrorException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
* (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotCoercibleException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/OffsetNode.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/parse/QueryParserTest.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
* (edit) phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotAllowedInQueryException.java
* (edit) phoenix-core/src/main/antlr3/PhoenixSQL.g
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/TraceQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
* (add)
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/ListJarsQueryPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/QueryPlan.java
* (add)
phoenix-core/src/test/java/org/apache/phoenix/compile/RVCOffsetCompilerTest.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054665#comment-17054665
]
Hudson commented on PHOENIX-4845:
-
SUCCESS: Integrated in Jenkins build Phoenix-4.x-HBase-1.5 #286 (See
[https://builds.apache.org/job/Phoenix-4.x-HBase-1.5/286/])
PHOENIX-4845 Support using Row Value Constructors in OFFSET clause for
(yanxinyi: rev 545330e90c7348fc1245438527d179373012452e)
* (edit)
phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryWithOffsetIT.java
* (add)
phoenix-core/src/test/java/org/apache/phoenix/compile/RVCOffsetCompilerTest.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/RVCOffsetCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OffsetCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/optimize/QueryOptimizer.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/exception/SQLExceptionCode.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotCoercibleException.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/jdbc/PhoenixStatement.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/parse/QueryParserTest.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/ListJarsQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/BaseQueryPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/ScanRanges.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/ScanPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/OffsetNode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/QueryCompiler.java
* (edit) phoenix-core/src/it/java/org/apache/phoenix/end2end/QueryMoreIT.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetNotAllowedInQueryException.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/compile/QueryPlan.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/compile/CompiledOffset.java
* (add)
phoenix-core/src/main/java/org/apache/phoenix/schema/RowValueConstructorOffsetInternalErrorException.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/parse/HintNode.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/WhereOptimizer.java
* (edit) phoenix-core/src/main/antlr3/PhoenixSQL.g
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/HashJoinPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/SortMergeJoinPlan.java
* (edit) phoenix-core/src/main/java/org/apache/phoenix/execute/UnionPlan.java
* (add)
phoenix-core/src/it/java/org/apache/phoenix/end2end/RowValueConstructorOffsetIT.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java
* (edit) phoenix-core/src/test/java/org/apache/phoenix/util/TestUtil.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java
* (edit)
phoenix-core/src/test/java/org/apache/phoenix/query/ParallelIteratorsSplitTest.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/OrderByCompiler.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/compile/TraceQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/execute/DelegateQueryPlan.java
* (edit)
phoenix-core/src/main/java/org/apache/phoenix/iterate/BaseResultIterators.java
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054603#comment-17054603
]
Xinyi Yan commented on PHOENIX-4845:
[~dbwong] thanks for the patch, I will commit to master and 4.x branches.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054357#comment-17054357
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12996034/PHOENIX-4845.patch
against master branch at commit ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12996034
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:green}+1 core tests{color}. The patch passed unit tests in .
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3551//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3551//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054278#comment-17054278
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12996025/PHOENIX-4845.patch
against master branch at commit ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12996025
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.ViewIT
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.TableSnapshotReadsMapReduceIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3550//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3550//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
>
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17054216#comment-17054216
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12996011/PHOENIX-4845.patch
against master branch at commit ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12996011
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.DropTableWithViewsIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3549//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3549//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17053920#comment-17053920
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12995957/PHOENIX-4845.patch
against master branch at commit ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12995957
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.AlterTableWithViewsIT
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.SystemCatalogCreationOnConnectionIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3548//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3548//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17053848#comment-17053848
]
Daniel Wong commented on PHOENIX-4845:
--
I ran these tests locally on master/4.x-HBase-1.3. Thoughts [~yanxinyi]
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17053065#comment-17053065
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12995802/PHOENIX-4845.patch
against master branch at commit ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12995802
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.join.HashJoinGlobalIndexIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3542//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3542//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17053016#comment-17053016
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12995803/PHOENIX-4845-4.x-HBase-1.3.v3.patch
against 4.x-HBase-1.3 branch at commit
ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12995803
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:green}+1 release audit{color}. The applied patch does not increase
the total number of release audit warnings.
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+private static final String SIMPLE_DDL = "CREATE TABLE %s (t_id
VARCHAR NOT NULL,\n" + "k1 INTEGER NOT NULL,\n"
+private static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT
NULL,\n" + "k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + INDEX_NAME + " ON
" + TABLE_NAME + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
DATA_INDEX_NAME + " ON " + DATA_TABLE_NAME
+String failureSql = String.format("SELECT %s FROM %s ORDER BY t_id
DESC, k1, k2 OFFSET (%s)=(%s)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (T1.t_id, T1.k1, T1.k2)=('a', 1, 2)",
+String failureSql = String.format("SELECT B.k2 FROM (SELECT %s FROM %s
OFFSET (%s)=(%s)) AS B",
+//Note subselect often gets rewritten to a flat query, in this case
offset is still viable, inner orderby should require failure
+String failureSql = String.format("SELECT * FROM (SELECT T_ID,K1,K2 AS
COL3 FROM %s ORDER BY K1 LIMIT 2) AS B OFFSET (%s)=(%s)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.OrderByWithSpillingIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3543//testReport/
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3543//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845-4.x-HBase-1.3.v3.patch,
> PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h 10m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17052637#comment-17052637
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12995799/PHOENIX-4845-4.x-HBase-1.3.v2.patch
against 4.x-HBase-1.3 branch at commit
ba2af47436a6b4ed3c2d633752b663893fe460c5.
ATTACHMENT ID: 12995799
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 36 new
or modified tests.
{color:red}-1 patch{color}. The patch command could not apply the patch.
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3541//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch,
> PHOENIX-4845-4.x-HBase-1.3.v2.patch, PHOENIX-4845.patch, PHOENIX-offset.txt
>
> Time Spent: 16h
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17015504#comment-17015504
]
Daniel Wong commented on PHOENIX-4845:
--
Checked the tests that appears to be a environmental hiccup and it ran clean
locally. If you have time can [~tdsilva] or [~jamestaylor] take a peek? Not
sure who else might have knowledge of the parser/optimizer.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch, PHOENIX-offset.txt
>
> Time Spent: 14h 50m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=17009303#comment-17009303
]
Hadoop QA commented on PHOENIX-4845:
{color:red}-1 overall{color}. Here are the results of testing the latest
attachment
http://issues.apache.org/jira/secure/attachment/12990051/PHOENIX-4845-4.x-HBase-1.3.patch
against 4.x-HBase-1.3 branch at commit
7bcc9bc89bbe86eedbf8223d101dadb3cf57cc4c.
ATTACHMENT ID: 12990051
{color:green}+1 @author{color}. The patch does not contain any @author
tags.
{color:green}+1 tests included{color}. The patch appears to include 13 new
or modified tests.
{color:green}+1 javac{color}. The applied patch does not increase the
total number of javac compiler warnings.
{color:red}-1 release audit{color}. The applied patch generated 1 release
audit warnings (more than the master's current 0 warnings).
{color:red}-1 lineLengths{color}. The patch introduces the following lines
longer than 100:
+static final String SIMPLE_DDL = "CREATE TABLE %s (t_id VARCHAR NOT
NULL,\n" + "k1 INTEGER NOT NULL,\n"
+static final String DATA_DDL = "CREATE TABLE %s (k1 TINYINT NOT NULL,\n" +
"k2 TINYINT NOT NULL,\n"
++ "k3 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "CONSTRAINT pk
PRIMARY KEY (k1, k2, k3)) ";
+String createIndex = "CREATE INDEX IF NOT EXISTS " + indexName + "
ON " + tableName + " (k2 DESC,k1)";
+String createDataIndex = "CREATE INDEX IF NOT EXISTS " +
dataIndexName + " ON " + dataTableName + " (k2 DESC,k1)";
+String failureSql = String.format("SELECT t_id, k1, k2 FROM %s OFFSET
(t_id, k1, k2)=('a', 'ab', 2)", tableName);
+String failureSql = String.format("SELECT t_id, k1, k2, v1 FROM %s
ORDER BY v1 OFFSET (t_id, k1, k2)=('a', 1, 2)", tableName);
+String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER
BY k1 OFFSET (t_id, k1, k2)=('a', 1, 2)", tableName);
+String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER
BY t_id DESC, k1, k2 OFFSET (k1,k2,k3)=('a', 1, 2)",
+String failureSql = String.format("SELECT T1.k1,T2.k2 FROM %s AS T1,
%s AS T2 WHERE T1.t_id=T2.t_id OFFSET (k1,k2,k3)=('a', 1, 2)",
{color:red}-1 core tests{color}. The patch failed these unit tests:
./phoenix-core/target/failsafe-reports/TEST-org.apache.phoenix.end2end.TenantIdTypeIT
Test results:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3237//testReport/
Release audit warnings:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3237//artifact/patchprocess/patchReleaseAuditWarnings.txt
Console output:
https://builds.apache.org/job/PreCommit-PHOENIX-Build/3237//console
This message is automatically generated.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-4845-4.x-HBase-1.3.patch, PHOENIX-offset.txt
>
> Time Spent: 1h 20m
> Remaining Estimate: 0h
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896648#comment-16896648
]
Thomas D'Silva commented on PHOENIX-4845:
-
Even for salter tables I think you will have ensure that users add an order by
row key order (the forceRowKeyOrder option can be used), or else the rows can
be returned in any arbitrary order if the RoundRobinResultIterator is used.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896605#comment-16896605
]
Daniel Wong commented on PHOENIX-4845:
--
I was implementing salt handling by not handling the "ORDER BY" type concerns
and just pagining through the salted table normally. That is first salt byte
0, then through salt byte 1, etc. The order by would have to be unconstrained
as we would not be able to return in a PK order otherwise.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896602#comment-16896602
]
Thomas D'Silva commented on PHOENIX-4845:
-
You will need to think through if OFFSET RVCs can be used to page through rows
from a salted table. I *think* it might work correctly if we set the startRow
byte[] based on the specified PK and issue N scans (one for each of the salt
buckets). Since we specify an order by on the pk columns a final merge would
happen on the client side, I think the rows would be returned in the correct
order.
I think its fine to not support this feature for salted tables as first step.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896577#comment-16896577
]
Daniel Wong commented on PHOENIX-4845:
--
How would we expect them to handle stuff like the SALT byte there. Will they
be able to even retrieve that data? This would also require them to understand
how Phoenix lays out the secondary indexes as given only their CREATE INDEX
statement they have no knowledge of those keys even existing and would have to
inspect the system catalog to know about. If we are okay with those
restrictions this is feasible but I dislike not being able to run on an index
who base table is salted for example.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896571#comment-16896571
]
Thomas D'Silva commented on PHOENIX-4845:
-
If the user wants to use RVC offset with an index I assumed they would have to
specify the index *AND* data table pk columns, so that the start of the scan is
for subsequent paged queries is correct.
So for your example they would have to specify K2 ,V1, K4, K1, K3, K5 and K6
for either index (k2 ,v1, k4) or (k2, v1, k4, k1).
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896562#comment-16896562
]
Daniel Wong commented on PHOENIX-4845:
--
I'll try, I could require INDEX or NO INDEX hint relatively easily, which maybe
I should. However I cannot determine all the user defined index PKs given the
current syscat as far as I can tell.
Consider the following code fragment and the table and indexes it creates:
{code:java}
String ddlTemplate = "CREATE TABLE %s (k1 TINYINT NOT NULL,\n" + "k2 TINYINT
NOT NULL,\n" + "k3 TINYINT NOT NULL,\n" + "k4 TINYINT NOT NULL,\n" + "k5
TINYINT NOT NULL,\n" + "k6 TINYINT NOT NULL,\n" + "v1 INTEGER,\n" + "v2
INTEGER,\n" + "v3 INTEGER,\n" + "v4 INTEGER,\n" + "CONSTRAINT pk PRIMARY KEY
(k1, k2, k3, k4, k5, k6)) ";
String longKeyTableName = "T_" + generateUniqueName(); String longKeyIndex1Name
= "INDEX_1_" + longKeyTableName;
String longKeyIndex2Name = "INDEX_2_" + longKeyTableName;
String ddl = String.format(ddlTemplate,longKeyTableName);
conn.createStatement().execute(ddl);
String createIndex1 = "CREATE INDEX IF NOT EXISTS " + longKeyIndex1Name + " ON
" + longKeyTableName + " (k2 ,v1, k4)";
String createIndex2 = "CREATE INDEX IF NOT EXISTS " + longKeyIndex2Name + " ON
" + longKeyTableName + " (v1, v3)";
{code}
Here the PK columns for the table are PKs = \{K1,K2,K3,K4,K5,K6}
For index 1 it is \{K2, 0:V1, K4, K1, K3, K5, K6} as we append the PKs to the
indexed row key. However I cannot differentiate that PK form a create index
that had for example CREATE INDEX (k2, v1, k4, k1). So how will I be able to
force the user to provide either k1 or not provide k1? I didn't see anything in
SYSCAT or PTable Interface that can allow me to differentiate these scenarios.
I could be missing something however.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896557#comment-16896557
]
Thomas D'Silva commented on PHOENIX-4845:
-
I am not clear why you cannot simply require the offset RVC to contain all the
columns of the index pk along with the user specifying the index hint, can you
explain?
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16896412#comment-16896412
]
Daniel Wong commented on PHOENIX-4845:
--
One additional concern I had based on my analysis there does not seem to be an
easy way to determine which PKs of an Index are user defined and which are
appended from the base table in order to facilitate uniqueness and indexed
lookup. While we have lists of both PKs I'm unable to determine which were
user specified and which were. This means that I cannot simply require the
offset to be fully specified for an indexed lookup. While I can implement with
the leading edge of hte key this introduces more. Assuming I'm correct in that
there is no way to reconstruct this currently should we add this information to
system catalog?
[~tdsilva] [~swaroopa] [~gjacoby]
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns of and Index or Primary Table.
> Note that there is some care is needed in the use of OFFSET with indexes. If
> the OFFSET is coercible to multiple indexes/base table it could mean very
> different positions based on key. To Handle This the INDEX hint needs to be
> used to specify an index offset for safety.
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16895666#comment-16895666
]
Daniel Wong commented on PHOENIX-4845:
--
Updating the high level description with the following note.
Note that there is some care neded in the use of OFFSET with indexes. If the
OFFSET is coercible to multiple indexes/base table it could mean very different
positions based on key. To Handle This the INDEX hint needs to be used to
specifiy an index offset for safety
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.14#76016)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16871653#comment-16871653
]
Daniel Wong commented on PHOENIX-4845:
--
Lars we discussed this a bit earlier in our threads, but essentially the reason
why OFFSET is good to have is it removes the burden of knowing the DDL and
Access Path from constructed the WHERE clause. As you have to inspect which
columns are ASC or DESC part of the pk in order to construct the right
comparator.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16870055#comment-16870055
]
Lars Hofhansl commented on PHOENIX-4845:
So meaning you just rewrite the query as such to get the right scan boundaries,
not to actually execute the query that way?
And then the task become to optimize
{{SELECT * FROM TABLE WHERE (A > 1 OR (A = 1 AND (B < 2 OR (B = 2 AND (C >=
3)}} in setting the start row on a set of scans?
In that case, why not just leave this as the syntax in the first place?
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16869972#comment-16869972
]
Daniel Wong commented on PHOENIX-4845:
--
Initial mostly working outline of approach 1:
[https://github.com/dbwong/phoenix/pull/2/files]
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16869950#comment-16869950
]
Daniel Wong commented on PHOENIX-4845:
--
[~lhofhansl] I don't quite follow the performance concern I assume it is due to
optimization time as the where optimizer generates the proper scan bounds based
on the new additional predicates which is linear in the number of pks? This
predicate should be entirely extractable into the pk so there should not be any
issue server side. After attempting approach 2 which I have a general outline
for PR to shortly follow, I can say that approach 1 is definitely easier in
the short term and less likely to miss an issue.
For the serial iterator I was simply planning to enforce that the select is row
key ordered this solves any worry about needing to serialize the parallel
pieces in any new way.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16868135#comment-16868135
]
Lars Hofhansl commented on PHOENIX-4845:
Approach 1 will likely be unacceptably slow.
I suppose I have not given this much detailed thought.
In the end we have to have enough information to set a start key on the
involved HBase scanner, so I assume this would have to imply a serial iterator.
(Sorry not near a computer for a few days, just phone, so hard to check code.)
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16868098#comment-16868098
]
Daniel Wong commented on PHOENIX-4845:
--
[~Bin Shi] might have some thoughts on this too.
> Support using Row Value Constructors in OFFSET clause for paging in tables
> where the sort order of PK columns varies
>
>
> Key: PHOENIX-4845
> URL: https://issues.apache.org/jira/browse/PHOENIX-4845
> Project: Phoenix
> Issue Type: New Feature
>Reporter: Thomas D'Silva
>Assignee: Daniel Wong
>Priority: Major
> Labels: DESC, SFDC
> Attachments: PHOENIX-offset.txt
>
>
> RVCs along with the LIMIT clause are useful for efficiently paging through
> rows (see [http://phoenix.apache.org/paged.html]). This works well if the pk
> columns are sorted ascending, we can always use the > operator to query for
> the next batch of row.
> However if the PK of a table is (A DESC, B DESC) we cannot use the following
> query to page through the data
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) > (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> Since the rows are sorted by A desc and then by B descending we need change
> the comparison order
> {code:java}
> SELECT * FROM TABLE WHERE (A, B) < (?, ?) ORDER BY A DESC, B DESC LIMIT 20
> {code}
> If the PK of a table contains columns with mixed sort order for eg (A DESC,
> B) then we cannot use RVC to page through data.
> If we supported using RVCs in the offset clause we could use the offset to
> set the start row of the scan. Clients would not have to have logic to
> determine the comparison operator. This would also support paging through
> data for tables where the PK columns are sorted in mixed order.
> {code:java}
> SELECT * FROM TABLE ORDER BY A DESC, B LIMIT 20 OFFSET (?,?)
> {code}
> We would only allow using the offset if the rows are ordered by the sort
> order of the PK columns.
>
> FYI [~jfernando_sfdc]
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
[jira] [Commented] (PHOENIX-4845) Support using Row Value Constructors in OFFSET clause for paging in tables where the sort order of PK columns varies
[
https://issues.apache.org/jira/browse/PHOENIX-4845?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel=16868090#comment-16868090
]
Daniel Wong commented on PHOENIX-4845:
--
So I have began implementing this Jira and have done some initial scoping and
approach thoughts.
Disallowed cases will not be supported:
{code:java}
//Test RVC Offset columns must be coercible to a base table
@Test
public void testRVCIndexLookup() throws SQLException {
String failureSql = String.format("SELECT t_id, k1, k2 FROM %s OFFSET ('a',
'ab', 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not allow Index lookup with RVC Offset");
}
//Test Not Allow Index Access Path On Base Table
@Test
public void testRVCOffsetNotCoercible() throws SQLException {
String failureSql = String.format("SELECT t_id, k1, k2 FROM %s OFFSET ('a',
'ab', 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not allow non coercible values to PK in RVC Offset");
}
//Test Order By Not PK Order By Exception
@Test
public void testRVCOffsetNotAllowNonPKOrderBy() throws SQLException {
String failureSql = String.format("SELECT t_id, k1, k2, v1 FROM %s ORDER BY v1
OFFSET ('a', 1, 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not allow no PK order by with RVC Offset");
}
//Test Order By Partial PK Order By Exception
@Test
public void testRVCOffsetNotAllowPartialPKOrderBy() throws SQLException {
String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER BY 2
OFFSET ('a', 1, 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not allow partial PK order by with RVC Offset");
}
//Test Order By Not PK Order By Exception
@Test
public void testRVCOffsetNotAllowDifferentPKOrderBy() throws SQLException {
String failureSql = String.format("SELECT t_id, k1, k2 FROM %s ORDER BY 1
DESC,2,3 OFFSET ('a', 1, 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not allow differnt PK order by with RVC Offset");
}
//Test Not allow joins
@Test
public void testRVCOffsetNotAllowedInJoins() throws SQLException {
String failureSql = String.format("SELECT * FROM %s AS T1, %s AS T2 WHERE
T1.t_id=T2.t_id OFFSET ('a', 1, 2)",tableName,tableName); //literal works
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not have JOIN in RVC Offset");
}
//Test Not allowed in subsquery
@Test
public void testRVCOffsetNotAllowedInSubQuery() throws SQLException {
String failureSql = String.format("SELECT * FROM (SELECT t_id, k2 FROM %s
OFFSET ('a', 1, 2))",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not have subquery with RVC Offset");
}
//Test Not allowed on subsquery
@Test
public void testRVCOffsetNotAllowedOnSubQuery() throws SQLException {
String failureSql = String.format("SELECT * FROM (SELECT t_id, k2 FROM %s)
OFFSET ('a', 1, 2)",tableName);
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not have subquery with RVC Offset");
}
//Test RVC Offset must be a literal, cannot have column reference
@Test
public void testRVCOffsetLiteral() throws SQLException {
String sql = "SELECT * FROM " + tableName + " OFFSET ('a', 1, 2)"; //literal
works
conn.createStatement().execute(sql);
String failureSql = "SELECT * FROM " + tableName + " OFFSET ('a', 1, k2)";
//column does works
try {
conn.createStatement().execute(failureSql);
} catch (Exception e) {
System.out.println(e.getMessage());
return;
}
fail("Should not have allowed column in RVC Offset");
}
{code}
Approach Possibilities:
I have been considering 2 posibilities for approaching this in Phoenix and am
open to other suggestions as well.
Given DDL/Query:
Create Table TABLE (a UNSIGNED_TINYINT, b UNSIGNED_TINYINT, c UNSIGNED_TINYINT,
CONSTANT pk PRIMARY KEY (a,b desc, c))
SELECT * FROM TABLE OFFSET (1,2,3)
Approach 1: Query Rewrite
SELECT * FROM TABLE OFFSET (1,2,3)
->
SELECT * FROM TABLE WHERE (A > 1 OR (A = 1 AND (B < 2 OR (B = 2 AND (C >=
3)
Approach 2: Mini - Resolution
SELECT * FROM TABLE OFFSET (1,2,3)
-> SELECT 1 FROM TABLE WHERE (a,b,c)=(1,2,3), (Pass the constructed tree to
the WhereOptimizer essentially)
-> Extract Byte Array From Constant [0x01FD03]
-> Filter Scans start/end key by Constant
Right now I'm favoring approach 2 but this will require some additional hooks
into the
