[jira] [Commented] (DRILL-6844) Query with ORDER BY DESC on indexed column does not pick secondary index

["ASF GitHub Bot (JIRA)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/DRILL-6844?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16683055#comment-16683055 ] 

ASF GitHub Bot commented on DRILL-6844:
---------------------------------------

HanumathRao opened a new pull request #1534: DRILL-6844: Query with ORDER BY DESC on indexed column does not pick …
URL: https://github.com/apache/drill/pull/1534
 
 
   …secondary index.
   
   This PR fixes the issue related to query not picking index when desc is used in the order by clause.
   The code is changed to create the sort collation with nulls first when indexed field is desc otherwise it creates nulls last.
   
   @amansinha100  Can you please review this PR.

----------------------------------------------------------------
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:
users@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
>
> 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)