[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14053789#comment-14053789 ]
James Taylor commented on PHOENIX-1057: --------------------------------------- [~phamptu] - if you can repro this on 0.98.1+, please let us know. Otherwise I'll go ahead and close this as we don't support any version before 0.98.1. > Phoenix wrong range query result > -------------------------------- > > Key: PHOENIX-1057 > URL: https://issues.apache.org/jira/browse/PHOENIX-1057 > Project: Phoenix > Issue Type: Bug > Affects Versions: 4.0.0 > Environment: Centos 6.5, Hbase 0.98 > Reporter: Pham Phuong Tu > Labels: bug, query > > 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/%3c1398727966.67421.yahoomail...@web165003.mail.bf1.yahoo.com%3E -- This message was sent by Atlassian JIRA (v6.2#6252)