[
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/%[email protected]%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/%[email protected]%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/%[email protected]%3E
--
This message was sent by Atlassian JIRA
(v6.2#6252)
