[DRILL-3621] Wrong results when Drill on Hbase query contains rowkey "or" or "IN"

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 1.1.0
• Fix Version/s: 1.2.0
• Component/s: Query Planning & Optimization
• Assignee: Khurram Faraaz
• Reporter: Hao Zhu
• Created: 10/Aug/15 20:41
• Updated: 22/Sep/15 02:37
• Resolved: 18/Sep/15 00:44

Description

If Drill on Hbase query contains row_key "in" or "or", it produces wrong results.

For example:
1. Create a hbase table

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'

2. Drill queries:

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)

From explain plan, filter is pushed down to hbase scan layer.

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`]]])
 |