[
https://issues.apache.org/jira/browse/DRILL-6844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16690183#comment-16690183
]
ASF GitHub Bot commented on DRILL-6844:
---------------------------------------
vdiravka closed pull request #1534: DRILL-6844: Query with ORDER BY DESC on
indexed column does not pick …
URL: https://github.com/apache/drill/pull/1534
This is a PR merged from a forked repository.
As GitHub hides the original diff on merge, it is displayed below for
the sake of provenance:
As this is a foreign pull request (from a fork), the diff is supplied
below (as it won't show otherwise due to GitHub magic):
diff --git
a/contrib/format-maprdb/src/main/java/org/apache/drill/exec/planner/index/MapRDBIndexDiscover.java
b/contrib/format-maprdb/src/main/java/org/apache/drill/exec/planner/index/MapRDBIndexDiscover.java
index f828ba02daf..d949634c815 100644
---
a/contrib/format-maprdb/src/main/java/org/apache/drill/exec/planner/index/MapRDBIndexDiscover.java
+++
b/contrib/format-maprdb/src/main/java/org/apache/drill/exec/planner/index/MapRDBIndexDiscover.java
@@ -293,9 +293,9 @@ private LogicalExpression getIndexExpression(IndexFieldDesc
desc) throws Invalid
if (direction != null) {
// assume null direction of NULLS UNSPECIFIED for now until MapR-DB
adds that to the APIs
RelFieldCollation.NullDirection nulldir =
- desc.getMissingAndNullOrdering() ==
MissingAndNullOrdering.MissingAndNullFirst ? NullDirection.FIRST :
- (desc.getMissingAndNullOrdering() ==
MissingAndNullOrdering.MissingAndNullLast ?
- NullDirection.LAST : NullDirection.UNSPECIFIED);
+ direction == RelFieldCollation.Direction.ASCENDING ?
NullDirection.LAST :
+ (direction == RelFieldCollation.Direction.DESCENDING ?
+ NullDirection.FIRST : NullDirection.UNSPECIFIED);
RelFieldCollation c = new RelFieldCollation(i++, direction, nulldir);
fieldCollations.add(c);
} else {
diff --git
a/contrib/format-maprdb/src/test/java/com/mapr/drill/maprdb/tests/index/IndexPlanTest.java
b/contrib/format-maprdb/src/test/java/com/mapr/drill/maprdb/tests/index/IndexPlanTest.java
index 67542207118..fd3b5cca169 100644
---
a/contrib/format-maprdb/src/test/java/com/mapr/drill/maprdb/tests/index/IndexPlanTest.java
+++
b/contrib/format-maprdb/src/test/java/com/mapr/drill/maprdb/tests/index/IndexPlanTest.java
@@ -106,6 +106,7 @@ public static void setupTableIndexes() throws Exception {
{"i_ssn", "id.ssn", "contact.phone",
"i_state_city", "address.state,address.city",
"name.fname,name.lname",//mainly for composite key test
"i_age", "personal.age", "",
+ "i_age_desc", "personal.age:desc", "",
"i_income", "personal.income", "",
"i_lic", "driverlicense", "reverseid",
"i_state_city_dl", "address.state,address.city", "driverlicense",
@@ -1935,27 +1936,58 @@ public void testRowkeyJoinPushdown_12() throws
Exception {
public void testRowkeyJoinPushdown_13() throws Exception {
// Check option planner.rowkeyjoin_conversion_using_hashjoin works as
expected!
String query = "select t1.id.ssn as ssn from hbase.`index_test_primary` t1
where _id in (select t2._id " +
- " from hbase.`index_test_primary` t2 where
cast(t2.activity.irs.firstlogin as timestamp) = " +
- " to_timestamp('2013-02-04 22:34:38.0', 'YYYY-MM-dd HH:mm:ss.S'))";
+ " from hbase.`index_test_primary` t2 where
cast(t2.activity.irs.firstlogin as timestamp) = " +
+ " to_timestamp('2013-02-04 22:34:38.0', 'YYYY-MM-dd HH:mm:ss.S'))";
try {
test(incrRowKeyJoinConvSelThreshold + ";" +
lowNonCoveringSelectivityThreshold + ";");
- PlanTestBase.testPlanMatchingPatterns(query, new String[]
{"RowKeyJoin"}, new String[] {});
+ PlanTestBase.testPlanMatchingPatterns(query, new String[]{"RowKeyJoin"},
new String[]{});
testBuilder()
- .sqlQuery(query)
- .ordered()
- .baselineColumns("ssn").baselineValues("100007423")
- .go();
+ .sqlQuery(query)
+ .ordered()
+ .baselineColumns("ssn").baselineValues("100007423")
+ .go();
test(incrRowKeyJoinConvSelThreshold + ";" +
lowNonCoveringSelectivityThreshold + ";" +
- forceRowKeyJoinConversionUsingHashJoin + ";");
- PlanTestBase.testPlanMatchingPatterns(query, new String[] {"HashJoin"},
new String[] {"RowKeyJoin"});
+ forceRowKeyJoinConversionUsingHashJoin + ";");
+ PlanTestBase.testPlanMatchingPatterns(query, new String[]{"HashJoin"},
new String[]{"RowKeyJoin"});
testBuilder()
- .sqlQuery(query)
- .ordered()
- .baselineColumns("ssn").baselineValues("100007423")
- .go();
+ .sqlQuery(query)
+ .ordered()
+ .baselineColumns("ssn").baselineValues("100007423")
+ .go();
} finally {
test(defaultRowKeyConvSelThreshold + ";" +
defaultnonCoveringSelectivityThreshold + ";" +
- defaultRowKeyJoinConversionUsingHashJoin);
+ defaultRowKeyJoinConversionUsingHashJoin);
+ }
+ }
+
+ public void TestIndexScanWithDescOrderByNullsFirst() throws Exception {
+
+ String query = "select t.personal.age from hbase.`index_test_primary` t
order by t.personal.age desc nulls first limit 1";
+ try {
+ test(defaultHavingIndexPlan + ";" + lowRowKeyJoinBackIOFactor + ";");
+ PlanTestBase.testPlanMatchingPatterns(query,
+ new String[]{".*JsonTableGroupScan.*indexName=i_age_desc.*"},
+ new String[]{}
+ );
+ } finally {
+ test(defaultRowKeyJoinBackIOFactor);
+ }
+ return;
+ }
+
+ @Test
+ public void TestIndexScanWithDescOrderByNullsLast() throws Exception {
+
+ String query = "select t.personal.age from hbase.`index_test_primary` t
order by t.personal.age desc nulls last limit 1";
+ try {
+ test(defaultHavingIndexPlan + ";" + lowRowKeyJoinBackIOFactor + ";");
+ PlanTestBase.testPlanMatchingPatterns(query,
+ new String[]{},
+ new String[]{".*indexName=i_age_desc.*"}
+ );
+ } finally {
+ test(defaultRowKeyJoinBackIOFactor);
}
+ return;
}
}
diff --git
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/index/IndexPlanUtils.java
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/index/IndexPlanUtils.java
index c0758c7fb88..3242761e630 100644
---
a/exec/java-exec/src/main/java/org/apache/drill/exec/planner/index/IndexPlanUtils.java
+++
b/exec/java-exec/src/main/java/org/apache/drill/exec/planner/index/IndexPlanUtils.java
@@ -563,7 +563,8 @@ public static RelCollation
buildCollationForExpressions(Map<LogicalExpression, I
}
RelCollation idxCollation = indexDesc.getCollation();
- RelFieldCollation.NullDirection nullsDir =
indexDesc.getNullsOrderingDirection();
+ RelFieldCollation.NullDirection nullsDir = idxCollation == null ?
RelFieldCollation.NullDirection.UNSPECIFIED :
+
idxCollation.getFieldCollations().get(idxFieldCount).nullDirection;
RelFieldCollation.Direction dir = (idxCollation == null)?
null :
idxCollation.getFieldCollations().get(idxFieldCount).direction;
if (dir == null) {
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
us...@infra.apache.org
> Query with ORDER BY DESC on indexed column does not pick secondary index
> ------------------------------------------------------------------------
>
> Key: DRILL-6844
> URL: https://issues.apache.org/jira/browse/DRILL-6844
> Project: Apache Drill
> Issue Type: Bug
> Components: Query Planning & Optimization
> Affects Versions: 1.14.0
> Reporter: Hanumath Rao Maduri
> Assignee: Hanumath Rao Maduri
> Priority: Major
> Labels: ready-to-commit
> Fix For: 1.15.0
>
>
> Query with ORDER BY DESC on indexed column does not pick secondary index
> {noformat}
> // Query that uses the secondary index defined on ts.
> 0: jdbc:drill:schema=dfs.tmp> explain plan for
> . . . . . . . . . . . . . . > select ts from dfs.`/c8/test3` order by ts
> limit 1;
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 Project(ts=[$0])
> 00-02 SelectionVectorRemover
> 00-03 Limit(fetch=[1])
> 00-04 Scan(table=[[dfs, /c8/test3]], groupscan=[JsonTableGroupScan
> [ScanSpec=JsonScanSpec [tableName=maprfs:///c8/test3, condition=null,
> indexName=ts], columns=[`ts`], limit=1, maxwidth=125]])
> {noformat}
> // Same query with ORDER BY ts DESC does not use the secondary index defined
> on ts.
> 0: jdbc:drill:schema=dfs.tmp> explain plan for
> . . . . . . . . . . . . . . > select ts from dfs.`/c8/test3` order by ts desc
> limit 1;
> +------+------+
> | text | json |
> +------+------+
> | 00-00 Screen
> 00-01 Project(ts=[$0])
> 00-02 SelectionVectorRemover
> 00-03 Limit(fetch=[1])
> 00-04 SingleMergeExchange(sort0=[0 DESC])
> 01-01 OrderedMuxExchange(sort0=[0 DESC])
> 02-01 SelectionVectorRemover
> 02-02 Limit(fetch=[1])
> 02-03 SelectionVectorRemover
> 02-04 TopN(limit=[1])
> 02-05 HashToRandomExchange(dist0=[[$0]])
> 03-01 Scan(table=[[dfs, /c8/test3]], groupscan=[JsonTableGroupScan
> [ScanSpec=JsonScanSpec [tableName=maprfs:///c8/test3, condition=null],
> columns=[`ts`], maxwidth=8554]])
> {noformat}
> { noformat}
> Index definition is,
> maprcli table index list -path /c8/test3 -json
> {
> "timestamp":1538066303932,
> "timeofday":"2018-09-27 04:38:23.932 GMT+0000 PM",
> "status":"OK",
> "total":2,
> "data":[
> {
> "cluster":"c8",
> "type":"maprdb.si",
> "indexFid":"2176.68.131294",
> "indexName":"ts",
> "hashed":false,
> "indexState":"REPLICA_STATE_REPLICATING",
> "idx":1,
> "indexedFields":"ts:ASC",
> "isUptodate":false,
> "minPendingTS":1538066077,
> "maxPendingTS":1538066077,
> "bytesPending":0,
> "putsPending":0,
> "bucketsPending":1,
> "copyTableCompletionPercentage":100,
> "numTablets":32,
> "numRows":80574368,
> "totalSize":4854052160
> },
> {
> "cluster":"c8",
> "type":"maprdb.si",
> "indexFid":"2176.72.131302",
> "indexName":"ts_desc",
> "hashed":false,
> "indexState":"REPLICA_STATE_REPLICATING",
> "idx":2,
> "indexedFields":"ts:DESC",
> "isUptodate":false,
> "minPendingTS":1538066077,
> "maxPendingTS":1538066077,
> "bytesPending":0,
> "putsPending":0,
> "bucketsPending":1,
> "copyTableCompletionPercentage":100,
> "numTablets":32,
> "numRows":80081344,
> "totalSize":4937154560
> }
> ]
> }
> {noformat}
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
