Any idea why we convert division of long/long to d...
# generaly
Yupeng Fu
05/18/2021, 4:45 PMAny idea why we convert division of long/long to double by default?
for example, in presto
select (1618900560000 - 1618437600000)/604800000*604800000SELECT (1618900560000 - 1618437600000)/604800000a
Amrish Lal
05/18/2021, 5:23 PMIt is in large part due to
ArithmeticFunctions.javadoubley
Yupeng Fu
05/18/2021, 5:26 PMyeha, my question is that shall we consider the behavior with other SQL engines like Hive/Presto/Mysql
a
Amrish Lal
05/18/2021, 5:29 PMI would definitely second making it more standardized 🙂 I have been trying to do some of that with my PRs on numerical type conversion (#6811, #6927).
Amrish Lal
05/18/2021, 5:30 PM@User wanted to change ArithmeticFunctions.java to use templates so that we can return the right output type for given input types.
y
Yupeng Fu
05/18/2021, 5:31 PMyeah, that will be awesome
Yupeng Fu
05/18/2021, 5:31 PMin the meanwhile, any workaround that you suggest to make the query above to output the same as presto?
s
Sidd
05/18/2021, 5:32 PMUse cast function in the meantime?
j
Jackie
05/18/2021, 6:03 PMWhat is the standard sql behavior? Are all numbers without decimal point treated as integer/long?
a
Amrish Lal
05/18/2021, 6:04 PMyes, the output of simple arithmetic computation on integer/long values should be integer/long.
s
Sidd
05/18/2021, 6:07 PMThis needs to be fixed across Pinot. Like sum function over long returns double.
y
Yupeng Fu
05/18/2021, 6:43 PM+1
Yupeng Fu
05/18/2021, 6:43 PMalso, why
Copy code
SELECT
cast((1618900560000 - 1618437600000) / 604800000 as integer ) * 604800000Yupeng Fu
05/18/2021, 6:43 PMbut
Copy code
SELECT
cast((1618900560000 - 1618437600000) / 604800000 as integer )x
Xiang Fu
05/18/2021, 6:49 PM
I guess this is a bug in the literal evaluation
y
Yupeng Fu
05/18/2021, 6:49 PMmore than literal..
Yupeng Fu
05/18/2021, 6:49 PMi am debugging a query
x
Xiang Fu
05/18/2021, 6:49 PM
Ideally this query should directly return
Xiang Fu
05/18/2021, 6:49 PM
Not hitting servers
y
Yupeng Fu
05/18/2021, 6:50 PM Copy code
cast((column - 1618437600000) / 604800000 as integer ) * 604800000x
Xiang Fu
05/18/2021, 9:29 PM
let’s create a github issue to track this?
y
Yupeng Fu
05/18/2021, 9:31 PMyeah, we should
Yupeng Fu
05/18/2021, 9:32 PMbtw, is there a workaround to achieve bucketing?
x
Xiang Fu
05/18/2021, 10:33 PM
Xiang Fu
05/18/2021, 10:34 PM
you can pick the function
Xiang Fu
05/18/2021, 10:34 PM
Copy code
ToEpoch<TIME_UNIT>Bucket(timeInMillis, bucketSize)y
Yupeng Fu
05/18/2021, 10:35 PMi see
Yupeng Fu
05/18/2021, 10:36 PMso in my case, i group by week
x
Xiang Fu
05/18/2021, 10:36 PM
date_trunc? then?
y
Yupeng Fu
05/18/2021, 10:36 PMi can use
ToEpochDayBucket(time, 7)x
Xiang Fu
05/18/2021, 10:40 PM
then you need to multiply 7 * 86400000 to get the millis
Xiang Fu
05/18/2021, 10:41 PM
or try
DATETIMECONVERT(ts, '1:MILLISECONDS:EPOCH', '1:MILLISECONDS:EPOCH', '7:DAYS')y
Yupeng Fu
05/18/2021, 10:42 PMyup, get it works
x
Xiang Fu
05/18/2021, 10:43 PM
also
DATETRUNC('week', ts, 'MILLISECONDS')Xiang Fu
05/18/2021, 10:43 PM
so really up to you
Xiang Fu
05/18/2021, 10:43 PM
you can try multiple functions and find the most efficient one
Xiang Fu
05/18/2021, 10:43 PM
they are all different implementations
y
Yupeng Fu
05/18/2021, 10:44 PMi see
j
Jackie
05/19/2021, 5:00 AMFix the cast bug: https://github.com/apache/incubator-pinot/pull/6941
Jackie
05/19/2021, 5:00 AM@User @User Please take a look ^^
👍 2