[jira] [Commented] (IMPALA-7310) Compute Stats not computing NULLs as a distinct value causing wrong estimates

["Paul Rogers (JIRA)" <ji...@apache.org>]

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

Paul Rogers commented on IMPALA-7310:
-------------------------------------

Simple reproduction:

{noformat}
create table t1 (x int, y varchar);
insert into t1 values (1, null), (2, null), (3, null), (4, null);
select ndv(y) from t1;

+--------+
| ndv(y) |
+--------+
| 0      |
+--------+
{noformat}

Reference to Impala's [null handling in {{COUNT DISTINCT}}|https://impala.apache.org/docs/build/html/topics/impala_distinct.html]: "NULL is not included as a distinct value in the count."

Conclusion: given the definition of "distinct" above, the {{ndv\(x)}} function appears defined as the "number of distinct non-null values".

There appear to be two other cases where the NDV value can be null:

* New table, no stats at all.
* Empty table.

Where else might the NDV come into play?

Like other databases, when using a {{GROUP BY}} query, Impala places all {{NULL}} values into a single group. This means that, when using the NDV value to estimate group count, a simple floor (say, 1) will not badly underestimate group counts.

All this suggests that [~joemcdonnell]'s suggestion of setting a floor in the planner is the best place to start.

> Compute Stats not computing NULLs as a distinct value causing wrong estimates
> -----------------------------------------------------------------------------
>
>                 Key: IMPALA-7310
>                 URL: https://issues.apache.org/jira/browse/IMPALA-7310
>             Project: IMPALA
>          Issue Type: Bug
>          Components: Frontend
>    Affects Versions: Impala 2.7.0, Impala 2.8.0, Impala 2.9.0, Impala 2.10.0, Impala 2.11.0, Impala 3.0, Impala 2.12.0
>            Reporter: Zsombor Fedor
>            Assignee: Paul Rogers
>            Priority: Major
>
> As seen in other DBMSs
> {code:java}
> NDV(col){code}
> not counting NULL as a distinct value. The same also applies to
> {code:java}
> COUNT(DISTINCT col){code}
> This is working as intended, but when computing column statistics it can cause some anomalies (i.g. bad join order) as compute stats uses NDV() to determine columns NDVs.
>  
> For example when aggregating more columns, the estimated cardinality is [counted as the product of the columns' number of distinct values.|https://github.com/cloudera/Impala/blob/64cd0bb0c3529efa0ab5452c4e9e2a04fd815b4f/fe/src/main/java/org/apache/impala/analysis/Expr.java#L669] If there is a column full of NULLs the whole product will be 0.
>  
> There are two possible fix for this.
> Either we should count NULLs as a distinct value when Computing Stats in the query:
> {code:java}
> SELECT NDV(a) + COUNT(DISTINCT CASE WHEN a IS NULL THEN 1 END) AS a, CAST(-1 as BIGINT), 4, CAST(4 as DOUBLE) FROM test;{code}
> instead of
> {code:java}
> SELECT NDV(a) AS a, CAST(-1 as BIGINT), 4, CAST(4 as DOUBLE) FROM test;{code}
>  
>  
> Or we should change the planner [function|https://github.com/cloudera/Impala/blob/2d2579cb31edda24457d33ff5176d79b7c0432c5/fe/src/main/java/org/apache/impala/planner/AggregationNode.java#L169] to take care of this bug.
>  



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)

---------------------------------------------------------------------
To unsubscribe, e-mail: issues-all-unsubscribe@impala.apache.org
For additional commands, e-mail: issues-all-help@impala.apache.org