Hello, It's a bug - thanks for the detail on how to reproduce. I filed PHOENIX-2039 for it.
There's a workaround - use the time-based ROUND function instead, like this: select round(cast(k as date),'MINUTE',30) x from round_test group by x; and if you need the output to stay a BIGINT, you can do this: select cast(round(cast(k as date),'MINUTE',30) as bigint) x from round_test group by x; Thanks, James On Fri, Jun 12, 2015 at 6:49 AM, Dhruv Gohil <[email protected]> wrote: > Hi, > Can some one explain, am I hitting a bug here or the "null" output in > second query is expected? > The actual query objective is to have count of event occured in half > hour intervals, but below examples is minimal version where I can re-produce > this "null" behaviour. > > > == Details below ========= > > MYTABLE's schema: > msg_ts BIGINT > <other columns> > > ===== > > dhruv@localhost:~/tools/phoenix-3.3.0-bin/hadoop1/bin$ ./sqlline.py > localhost > Setting property: [isolation, TRANSACTION_READ_COMMITTED] > issuing: !connect jdbc:phoenix:localhost none none > org.apache.phoenix.jdbc.PhoenixDriver > Connecting to jdbc:phoenix:localhost > Connected to: Phoenix (version 3.3) > Driver: PhoenixEmbeddedDriver (version 3.3) > Autocommit status: true > Transaction isolation: TRANSACTION_READ_COMMITTED > > > 0: jdbc:phoenix:localhost.> select ROUND(msg_ts,0) as rounded_date, > count(1) as record_count > . . . . . . . . . . . . . . . . . . . . . . .> from MYTABLE > . . . . . . . . . . . . . . . . . . . . . . .> group by rounded_date LIMIT > 10; > +------------------------------------------+------------------------------------------+ > | ROUNDED_DATE | RECORD_COUNT > | > +------------------------------------------+------------------------------------------+ > | 1380603285331 | 1 > | > | 1380603285872 | 1 > | > | 1380603288602 | 1 > | > | 1380603289684 | 1 > | > | 1380603292222 | 1 > | > | 1380603301472 | 1 > | > | 1380603303192 | 1 > | > | 1380603304522 | 1 > | > | 1380603304662 | 1 > | > | 1380603308885 | 1 > | > +------------------------------------------+------------------------------------------+ > 10 rows selected (7.087 seconds) > > > 0: jdbc:localhost.> select ROUND(msg_ts/1000000,0) as rounded_date, > count(1) as record_count > . . . . . . . . . . . . . . . . . . . . . . .> from MYTABLE > . . . . . . . . . . . . . . . . . . . . . . .> group by rounded_date LIMIT > 10; > +----------------------------------------+------------------------------------------+ > | ROUNDED_DATE | RECORD_COUNT > | > +----------------------------------------+------------------------------------------+ > | null | 381 > | > | null | 509 > | > | null | 399 > | > | null | 385 > | > | null | 344 > | > | null | 251 > | > | null | 228 > | > | null | 205 > | > | null | 185 > | > | null | 139 > | > +----------------------------------------+------------------------------------------+ > 10 rows selected (31.633 seconds) >
