[DRILL-1434] count of a nullable column in tpcds gives incorrect results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 0.6.0
• Fix Version/s: 0.8.0
• Component/s: Functions - Drill
• Assignee: Aman Sinha
• Reporter: Chun Chang
• Created: 18/Sep/14 17:29
• Updated: 25/Feb/15 02:33
• Resolved: 07/Jan/15 21:25

Description

code base
#Fri Sep 12 14:08:02 PDT 2014
git.commit.id.abbrev=9e16466

I have a parquet file (tpcds data) which contains null value on a column. The total count of the column:

0: jdbc:drill:schema=dfs> select count(ss_quantity) from `tpcds/p1/store_sales.parquet`;
------------

EXPR$0

------------

2880404

------------

The count without considering null is:

0: jdbc:drill:schema=dfs> select count(ss_quantity) from `tpcds/p1/store_sales.parquet` where ss_quantity is not null;
------------

EXPR$0

------------

2750408

------------

But the count for null value is zero:

0: jdbc:drill:schema=dfs> select count(ss_quantity) from `tpcds/p1/store_sales.parquet` where ss_quantity is null;
------------

EXPR$0

------------

0

------------

Here is the physical plan look like for this query:

0: jdbc:drill:schema=dfs> explain plan for select count(ss_quantity) from `tpcds/p1/store_sales.parquet` where ss_quantity is null;
----------------------+

text

json

----------------------+

00-00 Screen 00-01 StreamAgg(group=[{}], EXPR$0=[COUNT($0)]) 00-02 Filter(condition=[IS NULL($0)]) 00-03 ProducerConsumer 00-04 Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=maprfs:/user/root/mondrian/tpcds/p1/store_sales.parquet]], selectionRoot=/user/root/mondrian/tpcds/p1/store_sales.parquet, columns=[SchemaPath [`ss_quantity`]]]])

{ "head" : Unknown macro: { "version" } , "graph" : [ { "pop" : "parquet-scan", "@id" : 4, "entries" : [ { "path" : "maprfs:/user/root/mondrian/tpcds/p1/store_sales.parquet" } ], "storage" : { "type" : "file", "enabled" : true, "connection" : "maprfs:///", "workspaces" : Unknown macro: { "default" } , "formats" : Unknown macro: { "psv" } }, "format" : { "type" : "parquet" } , "columns" : [ "`ss_quantity`" ], "selectionRoot" : "/user/root/mondrian/tpcds/p1/store_sales.parquet", "cost" : 2880404.0 }, { "pop" : "producer-consumer", "@id" : 3, "child" : 4, "size" : 10, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 2880404.0 } , { "pop" : "filter", "@id" : 2, "child" : 3, "expr" : "isnull(`ss_quantity`) ", "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 720101.0 } , Unknown macro: { "pop" } , { "pop" : "screen", "@id" : 0, "child" : 1, "initialAllocation" : 1000000, "maxAllocation" : 10000000000, "cost" : 72010.1 } ] }

----------------------+

Issue Links

• is duplicated by: DRILL-1362 Count(nullable-column) is incorrectly pushed into group scan operator