[
https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14052757#comment-14052757
]
Pham Phuong Tu commented on PHOENIX-1057:
-----------------------------------------
Confirm that my hbase is 0.98
> 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/%[email protected]%3E
--
This message was sent by Atlassian JIRA
(v6.2#6252)
