[PHOENIX-1057] Phoenix wrong range query result

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Cannot Reproduce
• Affects Version/s: 4.0.0
• Labels: bug query

Environment

Centos 6.5, Hbase 0.98

• Assignee: Unassigned
• Reporter: Pham Phuong Tu
• Created: 04/Jul/14 08:33
• Updated: 21/Oct/14 20:19
• Resolved: 21/Oct/14 20:19

Description

Hi guys,

I have one big problem with Phoenix is some time, range query like: >, <, <=, >= return missing one or more result,

>>> REPROCEDURE ERROR CASE:
CREATE TABLE IF NOT EXISTS device3 (id CHAR(50) NOT NULL PRIMARY KEY, manufacture CHAR(50), brand CHAR(50), model CHAR(50), os CHAR(50), os_version CHAR(50), resolution CHAR(50), language CHAR(50), carrier CHAR(50), country CHAR(50), day_time INTEGER, time INTEGER, hour_time INTEGER, status INTEGER);

UPSERT INTO device3(id, hour_time, day_time) values ('1',1403974800,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('2',1403978400,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('3',1403982000,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('4',1403985600,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('5',1403989200,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('6',1403992800,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('7',1403996400,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('8',1404000000,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('9',1404003600,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('10',1404007200,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('11',1404010800,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('12',1404014400,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('13',1404018000,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('14',1404021600,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('15',1404025200,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('16',1404028800,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('17',1404032400,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('18',1404036000,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('19',1404039600,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('20',1404043200,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('21',1404046800,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('22',1404050400,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('23',1404054000,1403974800);
UPSERT INTO device3(id, hour_time, day_time) values ('24',1404057600,1403974800);

SELECT count(1) AS total, hour_time FROM device3 where hour_time >=1403974800 and hour_time < 1404061199 GROUP BY hour_time ORDER BY hour_time
----------------------+

TOTAL

HOUR_TIME

----------------------+

1	1403974800
1	1403978400
1	1403982000
1	1403985600
1	1403989200
1	1403992800
1	1403996400
1	1404000000
1	1404003600
1	1404007200
1	1404010800
1	1404014400
1	1404018000
1	1404021600
1	1404025200
1	1404028800
1	1404032400
1	1404036000
1	1404039600
1	1404043200
1	1404046800
1	1404050400
1	1404054000
1	1404057600

----------------------+

select distinct hour_time from device3 where day_time = 1403974800 order by hour_time;
------------

HOUR_TIME

------------

1403974800
1403978400
1403982000
1403985600
1403989200
1403992800
1403996400
1404000000
1404003600
1404007200
1404010800
1404014400
1404018000
1404021600
1404025200
1404028800
1404032400
1404036000
1404039600
1404043200
1404046800
1404050400
1404054000
1404057600

------------

SELECT count(1) AS total, hour_time FROM device3 where day_time =1403974800 GROUP BY hour_time ORDER BY hour_time
----------------------+

TOTAL

HOUR_TIME

----------------------+

1	1403974800
1	1403978400
1	1403982000
1	1403985600
1	1403989200
1	1403992800
1	1403996400
1	1404000000
1	1404003600
1	1404007200
1	1404010800
1	1404014400
1	1404018000
1	1404021600
1	1404025200
1	1404028800
1	1404032400
1	1404036000
1	1404039600
1	1404043200
1	1404046800
1	1404050400
1	1404054000
1	1404057600

----------------------+

Before create index, everything is OK!
CREATE INDEX IDX_DEVICE3_HOUR_TIME ON device3 (hour_time DESC);
CREATE INDEX IDX_DEVICE3_DAY_TIME ON device3 (day_time DESC);

Here is very strange order in IDX_DEVICE3_HOUR_TIME index, queries return missing value after create index!
select * from IDX_DEVICE3_HOUR_TIME;
-----------------------------------------------------+

0:HOUR_TIME

:ID

-----------------------------------------------------+

1.404E+9	8
1.4040576E+9	24
1.404054E+9	23
1.4040504E+9	22
1.4040468E+9	21
1.4040432E+9	20
1.4040396E+9	19
1.404036E+9	18
1.4040324E+9	17
1.4040288E+9	16
1.4040252E+9	15
1.4040216E+9	14
1.404018E+9	13
1.4040144E+9	12
1.4040108E+9	11
1.4040072E+9	10
1.4040036E+9	9
1.4039964E+9	7
1.4039928E+9	6
1.4039892E+9	5
1.4039856E+9	4
1.403982E+9	3
1.4039784E+9	2
1.4039748E+9	1

-----------------------------------------------------+

WRONG QUERY RESULT HERE ! (Missing 1404000000 value)
SELECT count(1) AS total, hour_time FROM device3 where hour_time >=1403974800 and hour_time < 1404061199 GROUP BY hour_time ORDER BY hour_time;
----------------------+

TOTAL

HOUR_TIME

----------------------+

1	1403974800
1	1403978400
1	1403982000
1	1403985600
1	1403989200
1	1403992800
1	1403996400
1	1404003600
1	1404007200
1	1404010800
1	1404014400
1	1404018000
1	1404021600
1	1404025200
1	1404028800
1	1404032400
1	1404036000
1	1404039600
1	1404043200
1	1404046800
1	1404050400
1	1404054000
1	1404057600

----------------------+

This kind of bug resolved in a lot of topic, i don't understand why it still apper:
http://mail-archives.apache.org/mod_mbox/phoenix-dev/201403.mbox/%3CJIRA.12700068.1394495218035.81228.1394954374732@arcas%3E
https://groups.google.com/forum/#!topic/phoenix-hbase-user/mZxSFxpqjS4
http://mail-archives.apache.org/mod_mbox/phoenix-user/201404.mbox/[e-mail]%3E