[jira] [Created] (CALCITE-2907) AggregateExpandDistinctAggregatesRule produces a wrong relational algebra

["Lai Zhou (JIRA)" <ji...@apache.org>]

Lai Zhou created CALCITE-2907:
---------------------------------

             Summary: AggregateExpandDistinctAggregatesRule produces a wrong relational algebra
                 Key: CALCITE-2907
                 URL: https://issues.apache.org/jira/browse/CALCITE-2907
             Project: Calcite
          Issue Type: Bug
          Components: core
    Affects Versions: 1.18.0
            Reporter: Lai Zhou


In my usecase:

an Aggegate which contains distinct call was converted improperly to an error  relational algebra.

 
{code:java}
SELECT user_id,
                       order_id,
                       product_id,
                       count(DISTINCT phone) AS contact_count,
                       count(DISTINCT (CASE
                                           WHEN is_cell_phone=0 THEN phone
                                       END)) AS fixedphone_count,
                       count(DISTINCT (CASE
                                           WHEN is_cell_phone=1 THEN phone
                                       END)) AS telehone_count,
                       count(DISTINCT substr((CASE
                                                  WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                              END),1,3)) AS seg1uv,
              count(DISTINCT substr((CASE
                                         WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                     END),4,4)) AS seg2uv,
              count(DISTINCT substr((CASE
                                         WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                     END),8,4)) AS seg3uv,
              stddev_pop(substr((CASE
                                     WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                 END),1,3)) AS seg1stddev,
              stddev_pop(substr((CASE
                                     WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                 END),4,4)) AS seg2stddev,
              stddev_pop(substr((CASE
                                     WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                 END),8,4)) AS seg3stddev,
              entropy(substr((CASE
                                        WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                    END),1,3)) AS seg1entropy,
              entropy(substr((CASE
                                        WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                    END),4,4)) AS seg2entropy,
              entropy(substr((CASE
                                        WHEN is_cell_phone=1 THEN secured_libs.u51decrypt(phone)
                                    END),8,4)) AS seg3entropy

                FROM dw_risk__mygravitation_v_snap_contacts_contacts
                GROUP BY user_id,
                         order_id,
                         product_id
{code}
 

 

After digging into the code,I found at the line 444 of the AggregateExpandDistinctAggregatesRule.java :

 
{code:java}
int x = groupCount;
final List<AggregateCall> newCalls = new ArrayList<>();
for (AggregateCall aggCall : aggregate.getAggCallList()) {
  final int newFilterArg;
  final List<Integer> newArgList;
  final SqlAggFunction aggregation;
  if (!aggCall.isDistinct()) {
    aggregation = SqlStdOperatorTable.MIN;
    newArgList = ImmutableIntList.of(x++);
    newFilterArg = filters.get(aggregate.getGroupSet());
  } else {
{code}
the undistinct aggregate call `stddev_pop` was converted to a 

 SqlStdOperatorTable.MIN, I don't understand how it works.

I guess someone make a faulty assumption here. [~julianhyde] ,can someone help me?

It’s very important for my business.

 

 



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