[DRILL-6654] Data verification failure with lateral unnest query having filter in and order by

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.14.0
• Fix Version/s: 1.15.0
• Labels: ready-to-commit
• Assignee: Sorabh Hamirwasia
• Reporter: Kedar Sankar Behera
• Reviewer: Parth Chandra
• Created: 31/Jul/18 22:18
• Updated: 04/Aug/18 01:01
• Resolved: 04/Aug/18 01:01

Description

Data verification failure with lateral unnest query having filter in and order by .

lateral query -

select customer.c_custkey, customer.c_name, orders.totalprice from customer, lateral (select sum(t.o.o_totalprice) as totalprice from unnest(customer.c_orders) t(o) WHERE t.o.o_totalprice in (89230.03,270087.44,246408.53,82657.72,153941.38,65277.06,180309.76)) orders order by customer.c_custkey limit 50;

result :-

+------------+---------------------+-------------+
| c_custkey | c_name | totalprice |
+------------+---------------------+-------------+
| 101276 | Customer#000101276 | 82657.72 |
| 120295 | Customer#000120295 | 266119.96 |
| 120376 | Customer#000120376 | 180309.76 |
+------------+---------------------+-------------+

flatten query -

select f.c_custkey, f.c_name, sum(f.o.o_totalprice) from (select c_custkey, c_name, flatten(c_orders) as o from customer) f WHERE f.o.o_totalprice in (89230.03,270087.44,246408.53,82657.72,153941.38,65277.06,180309.76) group by f.c_custkey, f.c_name order by f.c_custkey limit 50;

result :-

+------------+---------------------+------------+
| c_custkey | c_name | EXPR$2 |
+------------+---------------------+------------+
| 101276 | Customer#000101276 | 82657.72 |
| 120376 | Customer#000120376 | 180309.76 |
+------------+---------------------+------------+

PS :- The above results are for Parquet type data .The same query for JSON data gives identical result given as follows :-

+------------+---------------------+------------+
| c_custkey | c_name | EXPR$2 |
+------------+---------------------+------------+
| 101276 | Customer#000101276 | 82657.72 |
| 120376 | Customer#000120376 | 180309.76 |

Issue Links

• links to: GitHub Pull Request #1418