[
https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14060085#comment-14060085
]
James Taylor commented on PHOENIX-1057:
---------------------------------------
Actually, I don't know that 0.98.0 doesn't have anything to do with it. If you
could confirm that the issue occurs on 0.98.1, that would be much appreciated.
If you're planning on using Phoenix, you'll need to upgrade to 0.98.1 anyway,
so it's no additional work on your end.
> 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)
