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)
