[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Pham Phuong Tu updated PHOENIX-1057: ------------------------------------ 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/%3c1398727966.67421.yahoomail...@web165003.mail.bf1.yahoo.com%3E was: Hi guys, I have one big problem with Phoenix is some time, range query like: >, <, <=, >= return missing one or more result, E.g: >>> SELECT count(1) AS total, hour_time FROM device2 where hour_time >>> >=1403974800 and hour_time < 1404061199 GROUP BY hour_time ORDER BY >>> hour_time +------------+------------+ | TOTAL | HOUR_TIME | +------------+------------+ | 90 | 1403974800 | | 73 | 1403978400 | | 70 | 1403982000 | | 66 | 1403985600 | | 51 | 1403989200 | | 39 | 1403992800 | | 33 | 1403996400 | | 73 | 1404003600 | | 77 | 1404007200 | | 77 | 1404010800 | | 97 | 1404014400 | | 74 | 1404018000 | | 92 | 1404021600 | | 84 | 1404025200 | | 83 | 1404028800 | | 89 | 1404032400 | | 93 | 1404036000 | | 93 | 1404039600 | | 99 | 1404043200 | | 120 | 1404046800 | | 113 | 1404050400 | | 85 | 1404054000 | | 80 | 1404057600 | +------------+------------+ The result of this query return missing value 1404000000 of hour_time, of course 1403974800 <= 1404000000 < 1404061199. After run 2 queries below, we can see that value 1404000000 exist in device2 table: >>> select distinct hour_time from device2 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 device2 where day_time =1403974800 >>> GROUP BY hour_time ORDER BY hour_time +------------+------------+ | TOTAL | HOUR_TIME | +------------+------------+ | 90 | 1403974800 | | 73 | 1403978400 | | 70 | 1403982000 | | 66 | 1403985600 | | 51 | 1403989200 | | 39 | 1403992800 | | 33 | 1403996400 | | 60 | 1404000000 | | 73 | 1404003600 | | 77 | 1404007200 | | 77 | 1404010800 | | 97 | 1404014400 | | 74 | 1404018000 | | 92 | 1404021600 | | 84 | 1404025200 | | 83 | 1404028800 | | 89 | 1404032400 | | 93 | 1404036000 | | 93 | 1404039600 | | 99 | 1404043200 | | 120 | 1404046800 | | 113 | 1404050400 | | 85 | 1404054000 | | 80 | 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 > 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)