[ https://issues.apache.org/jira/browse/DRILL-3621?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14901814#comment-14901814 ]
Khurram Faraaz commented on DRILL-3621: --------------------------------------- Verified that Filter gets pushed down into the Scan operator on master commit id b525692e case 1) {code} 0: jdbc:drill:schema=dfs.tmp> select convert_from(row_key,'UTF8') from testrowkey WHERE ROW_KEY='DUMMY7' OR ROW_KEY BETWEEN 'DUMMY1' AND 'DUMMY10'; +----------+ | EXPR$0 | +----------+ | DUMMY1 | | DUMMY10 | | DUMMY7 | +----------+ 3 rows selected (0.865 seconds) explain plan for select convert_from(row_key,'UTF8') from testrowkey WHERE ROW_KEY='DUMMY7' OR ROW_KEY BETWEEN 'DUMMY1' AND 'DUMMY10'; 00-01 Project(EXPR$0=[CONVERT_FROMUTF8($0)]) 00-02 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=testrowkey, startRow=DUMMY1, stopRow=DUMMY7\x00, filter=FilterList OR (2/2): [RowFilter (EQUAL, DUMMY7), FilterList AND (2/2): [RowFilter (GREATER_OR_EQUAL, DUMMY1), RowFilter (LESS_OR_EQUAL, DUMMY10)]]], columns=[`row_key`]]]) {code} case 2) {code} 0: jdbc:drill:schema=dfs.tmp> select convert_from(row_key,'UTF8') from testrowkey WHERE ROW_KEY in ('DUMMY1' , 'DUMMY10'); +----------+ | EXPR$0 | +----------+ | DUMMY1 | | DUMMY10 | +----------+ 2 rows selected (0.867 seconds) 00-01 Project(EXPR$0=[CONVERT_FROMUTF8($0)]) 00-02 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=testrowkey, startRow=DUMMY1, stopRow=DUMMY10\x00, filter=FilterList OR (2/2): [RowFilter (EQUAL, DUMMY1), RowFilter (EQUAL, DUMMY10)]], columns=[`row_key`]]]) {code} case 3) {code} : jdbc:drill:schema=dfs.tmp> select convert_from(row_key,'UTF8') from testrowkey WHERE ROW_KEY ='DUMMY1' OR ROW_KEY = 'DUMMY10'; +----------+ | EXPR$0 | +----------+ | DUMMY1 | | DUMMY10 | +----------+ 2 rows selected (0.854 seconds) 00-01 Project(EXPR$0=[CONVERT_FROMUTF8($0)]) 00-02 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec [tableName=testrowkey, startRow=DUMMY1, stopRow=DUMMY10\x00, filter=FilterList OR (2/2): [RowFilter (EQUAL, DUMMY1), RowFilter (EQUAL, DUMMY10)]], columns=[`row_key`]]]) {code} > Wrong results when Drill on Hbase query contains rowkey "or" or "IN" > -------------------------------------------------------------------- > > Key: DRILL-3621 > URL: https://issues.apache.org/jira/browse/DRILL-3621 > Project: Apache Drill > Issue Type: Bug > Components: Query Planning & Optimization > Affects Versions: 1.1.0 > Reporter: Hao Zhu > Assignee: Khurram Faraaz > Priority: Critical > Fix For: 1.2.0 > > Attachments: > 0001-DRILL-3621-Fix-incorrect-result-if-HBase-filter-cont.patch > > > If Drill on Hbase query contains row_key "in" or "or", it produces wrong > results. > For example: > 1. Create a hbase table > {code} > create 'testrowkey','cf' > put 'testrowkey','DUMMY1','cf:c','value1' > put 'testrowkey','DUMMY2','cf:c','value2' > put 'testrowkey','DUMMY3','cf:c','value3' > put 'testrowkey','DUMMY4','cf:c','value4' > put 'testrowkey','DUMMY5','cf:c','value5' > put 'testrowkey','DUMMY6','cf:c','value6' > put 'testrowkey','DUMMY7','cf:c','value7' > put 'testrowkey','DUMMY8','cf:c','value8' > put 'testrowkey','DUMMY9','cf:c','value9' > put 'testrowkey','DUMMY10','cf:c','value10' > {code} > 2. Drill queries: > {code} > 0: jdbc:drill:zk=h2.poc.com:5181,h3.poc.com:5> SELECT > CONVERT_FROM(ROW_KEY,'UTF8') RK FROM hbase.testrowkey T WHERE ROW_KEY = > 'DUMMY10'; > +----------+ > | RK | > +----------+ > | DUMMY10 | > +----------+ > 1 row selected (1.186 seconds) > 0: jdbc:drill:zk=h2.poc.com:5181,h3.poc.com:5> SELECT > CONVERT_FROM(ROW_KEY,'UTF8') RK FROM hbase.testrowkey T WHERE ROW_KEY = > 'DUMMY1'; > +---------+ > | RK | > +---------+ > | DUMMY1 | > +---------+ > 1 row selected (0.691 seconds) > 0: jdbc:drill:zk=h2.poc.com:5181,h3.poc.com:5> SELECT > CONVERT_FROM(ROW_KEY,'UTF8') RK FROM hbase.testrowkey T WHERE ROW_KEY IN > ('DUMMY1' , 'DUMMY10'); > +---------+ > | RK | > +---------+ > | DUMMY1 | > +---------+ > 1 row selected (0.71 seconds) > 0: jdbc:drill:zk=h2.poc.com:5181,h3.poc.com:5> SELECT > CONVERT_FROM(ROW_KEY,'UTF8') RK FROM hbase.testrowkey T WHERE ROW_KEY > ='DUMMY1' OR ROW_KEY = 'DUMMY10'; > +---------+ > | RK | > +---------+ > | DUMMY1 | > +---------+ > 1 row selected (0.693 seconds) > {code} > From explain plan, filter is pushed down to hbase scan layer. > {code} > 0: jdbc:drill:zk=h2.poc.com:5181,h3.poc.com:5> explain plan for SELECT > CONVERT_FROM(ROW_KEY,'UTF8') RK FROM hbase.testrowkey T WHERE ROW_KEY IN > ('DUMMY1' , 'DUMMY10'); > +------+------+ > | text | json | > +------+------+ > | 00-00 Screen > 00-01 Project(RK=[CONVERT_FROMUTF8($0)]) > 00-02 Scan(groupscan=[HBaseGroupScan [HBaseScanSpec=HBaseScanSpec > [tableName=testrowkey, startRow=DUMMY1, stopRow=DUMMY10, filter=null], > columns=[`row_key`]]]) > | > {code} -- This message was sent by Atlassian JIRA (v6.3.4#6332)