We defined the max(trans_at) as one of the measures of the cube.
our cube json looks like:
{
"uuid": "4d57736b-d3e4-40fe-8480-cbc80f78b4ef",
"name": "test_max_0",
"description": "",
"dimensions": [
{
"id": 1,
"name": "ID",
"join": null,
"hierarchy": null,
"table": "TEST_MAX_ON_NEG",
"column": "ID",
"datatype": null,
"derived": null
}
],
"measures": [
{
"id": 1,
"name": "_COUNT_",
"function": {
"expression": "COUNT",
"parameter": {
"type": "constant",
"value": "1"
},
"returntype": "bigint"
},
"dependent_measure_ref": null
},
{
"id": 2,
"name": "SUM_TRANS_AT",
"function": {
"expression": "SUM",
"parameter": {
"type": "column",
"value": "TRANS_AT"
},
"returntype": "double"
},
"dependent_measure_ref": null
},
{
"id": 3,
"name": "MAX_TRANS_AT",
"function": {
"expression": "MAX",
"parameter": {
"type": "column",
"value": "TRANS_AT"
},
"returntype": "double"
},
"dependent_measure_ref": null
}
],
"rowkey": {
"rowkey_columns": [
{
"column": "ID",
"length": 0,
"dictionary": "true",
"mandatory": false
}
],
"aggregation_groups": [
[
"ID"
]
]
},
"signature": "R/zfoefWoDBHzfzSUh5thA==",
"capacity": "MEDIUM",
"last_modified": 1430279551744,
"fact_table": "TEST_MAX_ON_NEG",
"null_string": null,
"filter_condition": null,
"cube_partition_desc": {
"partition_date_column": null,
"partition_date_start": 0,
"cube_partition_type": "APPEND"
},
"hbase_mapping": {
"column_family": [
{
"name": "F1",
"columns": [
{
"qualifier": "M",
"measure_refs": [
"_COUNT_",
"SUM_TRANS_AT",
"MAX_TRANS_AT"
]
}
]
}
]
},
"notify_list": []
}
The cube is based on only a fact table called test_max_on_neg which has only
six records:
2c9083862fe1d8f00130062167480ce8-211316617 -199.6
2c9083862fe1d8f00130062167480ce8-21782293 -229.0
2c9083862fe1d8f00130062167480ce8-211955199 -151.7
2c9083862fe1d8f00130062167480ce8-213582789 -86.4
2c9083862fe1d8f00130062167480ce8-115552384 -353.5
2c9083862fe1d8f00130062167480ce8-0114097017 -59.0
When the cube is ready, we did the query: select id, max(trans_at),
sum(trans_at) from test_max_on_neg group by id, which returned 4.9E-324 for
MAX(trans_at) column. I think the problem still exists for kylin-0.6.6, and you
can reproduce the problem if you like.
> -----邮件原件-----
> 发件人: Li Yang [mailto:[email protected]]
> 发送时间: 2015年4月29日 14:56
> 收件人: [email protected]
> 主题: Re: Max on negative double values is not working
>
> Depend on whether max(trans_at) is a measure of cube or not, the problem
> and the answer will be different.
>
>
> On Wed, Apr 29, 2015 at 10:43 AM, hongbin ma <[email protected]>
> wrote:
>
> > can you share your cube's definition? you can go to cubes tab, click
> > on the cube, and get json tab.
> >
> > we'd like to help check the definition of your cube
> >
> > On Tue, Apr 28, 2015 at 8:01 PM, Huang Hua <[email protected]>
> > wrote:
> >
> > > Hi there,
> > >
> > >
> > >
> > > We are using kylin 0.6.6, and we found out the max function might
> > > not
> > work
> > > for negative values in double type.
> > >
> > >
> > >
> > > The fact table in Hive only has two columns: id and trans_at and
> > > contains six records as shown below:
> > >
> > > Id trans_at
> > >
> > > 1, -59.0
> > >
> > > 2, -229.0
> > >
> > > 3, -86.4
> > >
> > > 4, -151.7
> > >
> > > 5, -199.6
> > >
> > > 6, -353.5
> > >
> > >
> > >
> > > Then based on the fact table, we created and built a cube with 1
> > dimension
> > > and 3 measures including the default _count_ one:
> > >
> > > Dimension: id
> > >
> > > Measures: max(trans_at), sum(trans_at), _count_
> > >
> > >
> > >
> > > The sql used in query was: select id, MAX(trans_at), SUM(trans_at)
> > > from the_table group by id, which produced the following output:
> > >
> > >
> > >
> > > ID,EXPR$1,EXPR$2
> > >
> > > 1, 4.9E-324, -59.0
> > >
> > > 2, 4.9E-324, -229.0
> > >
> > > 3, 4.9E-324, -86.4
> > >
> > > 4, 4.9E-324, -151.7
> > >
> > > 5, 4.9E-324, -199.6
> > >
> > > 6, 4.9E-324, -353.5
> > >
> > >
> > >
> > > As you can see, max(trans_at) produced 4.9E-324 for each row which
> > > is not what we expect to see.
> > >
> > >
> > >
> > > Best Regards.
> > >
> > > Hua
> > >
> > >
> >
> >
> > --
> > Regards,
> >
> > *Bin Mahone | 马洪宾*
> > Apache Kylin: http://kylin.io
> > Github: https://github.com/binmahone
> >
