[jira] [Updated] (PHOENIX-1000) Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions
[ https://issues.apache.org/jira/browse/PHOENIX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anoop Sam John updated PHOENIX-1000: Attachment: PHOENIX-1000-master-addendum_2.patch Master addendum needed a rebase again after Ram's commit. This is the right one. Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions -- Key: PHOENIX-1000 URL: https://issues.apache.org/jira/browse/PHOENIX-1000 Project: Phoenix Issue Type: New Feature Reporter: James Taylor Fix For: 5.0.0, 3.1, 4.1 Attachments: PHOENIX-1000-3-v2.patch, PHOENIX-1000-3.0-addendum.patch, PHOENIX-1000-3.patch, PHOENIX-1000-4-v2.patch, PHOENIX-1000-4-v3.patch, PHOENIX-1000-4.0-addendum.patch, PHOENIX-1000-4.patch, PHOENIX-1000-master-addendum.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-v2.patch, PHOENIX-1000-master.patch Support the standard FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Resolved] (PHOENIX-1000) Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions
[ https://issues.apache.org/jira/browse/PHOENIX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] Anoop Sam John resolved PHOENIX-1000. - Resolution: Fixed Addendum pushed to all branches. Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions -- Key: PHOENIX-1000 URL: https://issues.apache.org/jira/browse/PHOENIX-1000 Project: Phoenix Issue Type: New Feature Reporter: James Taylor Fix For: 5.0.0, 3.1, 4.1 Attachments: PHOENIX-1000-3-v2.patch, PHOENIX-1000-3.0-addendum.patch, PHOENIX-1000-3.patch, PHOENIX-1000-4-v2.patch, PHOENIX-1000-4-v3.patch, PHOENIX-1000-4.0-addendum.patch, PHOENIX-1000-4.patch, PHOENIX-1000-master-addendum.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-v2.patch, PHOENIX-1000-master.patch Support the standard FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-952) Support ANY and ALL built-ins for ARRAYs
[ https://issues.apache.org/jira/browse/PHOENIX-952?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052233#comment-14052233 ] Hudson commented on PHOENIX-952: SUCCESS: Integrated in Phoenix | Master | Hadoop1 #261 (See [https://builds.apache.org/job/Phoenix-master-hadoop1/261/]) Phoenix-952 Support ANY and ALL built-ins for ARRAYs (Ram) (ramkrishna: rev f7c1889bce385e46f774b90368a5acd5c44674f2) * phoenix-core/src/main/java/org/apache/phoenix/compile/StatementNormalizer.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeRewriter.java * phoenix-core/src/main/java/org/apache/phoenix/schema/PArrayDataType.java * phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseNoParseNodeVisitor.java * phoenix-core/src/main/java/org/apache/phoenix/parse/TraverseAllParseNodeVisitor.java * phoenix-core/src/main/java/org/apache/phoenix/compile/ExpressionCompiler.java * phoenix-core/src/main/antlr3/PhoenixSQL.g * phoenix-core/src/it/java/org/apache/phoenix/end2end/ArrayIT.java * phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeFactory.java * phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayIndexFunction.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ParseNodeVisitor.java * phoenix-core/src/main/java/org/apache/phoenix/parse/UnsupportedAllParseNodeVisitor.java Phoenix-952 Support ANY and ALL built-ins for ARRAYs - Adding new files (ramkrishna: rev d23ebef2148eef5843e5e5331d556f0ffe260900) * phoenix-core/src/main/java/org/apache/phoenix/expression/function/InlineArrayElemRefExpression.java * phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayAllComparisonExpression.java * phoenix-core/src/main/java/org/apache/phoenix/expression/function/ArrayAnyComparisonExpression.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ArrayAllComparisonNode.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ArrayElemRefNode.java * phoenix-core/src/main/java/org/apache/phoenix/parse/ArrayAnyComparisonNode.java Support ANY and ALL built-ins for ARRAYs Key: PHOENIX-952 URL: https://issues.apache.org/jira/browse/PHOENIX-952 Project: Phoenix Issue Type: Bug Affects Versions: 3.0.0, 4.0.0 Reporter: James Taylor Assignee: ramkrishna.s.vasudevan Fix For: 3.1, 4.1 Attachments: Phoenix-932_1.patch, Phoenix-932_2.patch, Phoenix-952_4.patch, Phoenix-952_5.patch, Phoenix-952_6.patch There's currently no good way to search array elements. We should support the ANY and ALL built-ins for our ARRAY type like Postgres does: http://www.postgresql.org/docs/9.1/static/arrays.html#ARRAYS-SEARCHING -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1000) Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions
[ https://issues.apache.org/jira/browse/PHOENIX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052234#comment-14052234 ] Hudson commented on PHOENIX-1000: - SUCCESS: Integrated in Phoenix | Master | Hadoop1 #261 (See [https://builds.apache.org/job/Phoenix-master-hadoop1/261/]) PHOENIX-1000 Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. (Addendum) (anoopsamjohn: rev a61a1a48d4cc8eaf1175664ba328be0d4d64eb24) * phoenix-core/src/main/java/org/apache/phoenix/expression/aggregator/FirstLastValueBaseClientAggregator.java * phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions -- Key: PHOENIX-1000 URL: https://issues.apache.org/jira/browse/PHOENIX-1000 Project: Phoenix Issue Type: New Feature Reporter: James Taylor Fix For: 5.0.0, 3.1, 4.1 Attachments: PHOENIX-1000-3-v2.patch, PHOENIX-1000-3.0-addendum.patch, PHOENIX-1000-3.patch, PHOENIX-1000-4-v2.patch, PHOENIX-1000-4-v3.patch, PHOENIX-1000-4.0-addendum.patch, PHOENIX-1000-4.patch, PHOENIX-1000-master-addendum.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-v2.patch, PHOENIX-1000-master.patch Support the standard FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Updated] (PHOENIX-1000) Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions
[ https://issues.apache.org/jira/browse/PHOENIX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-1000: -- Assignee: Vaclav Loffelmann Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions -- Key: PHOENIX-1000 URL: https://issues.apache.org/jira/browse/PHOENIX-1000 Project: Phoenix Issue Type: New Feature Reporter: James Taylor Assignee: Vaclav Loffelmann Fix For: 5.0.0, 3.1, 4.1 Attachments: PHOENIX-1000-3-v2.patch, PHOENIX-1000-3.0-addendum.patch, PHOENIX-1000-3.patch, PHOENIX-1000-4-v2.patch, PHOENIX-1000-4-v3.patch, PHOENIX-1000-4.0-addendum.patch, PHOENIX-1000-4.patch, PHOENIX-1000-master-addendum.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-v2.patch, PHOENIX-1000-master.patch Support the standard FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Created] (PHOENIX-1057) Phoenix wrong range query result
Pham Phuong Tu created PHOENIX-1057: --- Summary: 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 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 140400 of hour_time, of course 1403974800 = 140400 1404061199. After run 2 queries below, we can see that value 140400 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 | | 140400 | | 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 | 140400 | | 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 -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1057) Phoenix wrong range query result
[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052259#comment-14052259 ] James Taylor commented on PHOENIX-1057: --- What version of Phoenix and HBase are you using? If you can provide a complete unit test that reproduces the issue, we'll be happy to fix it. 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, 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 140400 of hour_time, of course 1403974800 = 140400 1404061199. After run 2 queries below, we can see that value 140400 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 | | 140400 | | 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 | 140400 | | 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 -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1056) A ImportTsv tool for phoenix to build table data and all index data.
[ https://issues.apache.org/jira/browse/PHOENIX-1056?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052268#comment-14052268 ] James Taylor commented on PHOENIX-1056: --- Fantastic work, [~jaywong]! Would love to get this into Phoenix. Can you send us a pull request so folks can review it? A ImportTsv tool for phoenix to build table data and all index data. Key: PHOENIX-1056 URL: https://issues.apache.org/jira/browse/PHOENIX-1056 Project: Phoenix Issue Type: Task Affects Versions: 3.0.0 Reporter: jay wong Fix For: 3.1 I have just build a tool for build table data and index table data just like ImportTsv job. http://hbase.apache.org/book/ops_mgt.html#importtsv when ImportTsv work it write HFile in a CF name path. for example A table has two cf, A and B. the output is ./outputpath/A ./outputpath/B In my job. we has a table. TableOne. and two Index IdxOne, IdxTwo. the output will be ./outputpath/TableOne/A ./outputpath/TableOne/B ./outputpath/IdxOne ./outputpath/IdxTwo. If anyone need it .I will build a clean tool. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Updated] (PHOENIX-1059) Support index regions merge on their corresponding data regions merge
[ https://issues.apache.org/jira/browse/PHOENIX-1059?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] rajeshbabu updated PHOENIX-1059: Issue Type: Sub-task (was: Bug) Parent: PHOENIX-933 Support index regions merge on their corresponding data regions merge - Key: PHOENIX-1059 URL: https://issues.apache.org/jira/browse/PHOENIX-1059 Project: Phoenix Issue Type: Sub-task Reporter: rajeshbabu Assignee: rajeshbabu When data regions merge corresponding index regions with the same start keys also should merge. Check how we can replace the start key in the second region data with first region start key. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1057) Phoenix wrong range query result
[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052296#comment-14052296 ] Pham Phuong Tu commented on PHOENIX-1057: - My system eviroment: - Hadoop 2.4 - Hbase 0.98 - Phoenix 4.0 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, 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 140400 of hour_time, of course 1403974800 = 140400 1404061199. After run 2 queries below, we can see that value 140400 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 | | 140400 | | 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 | 140400 | | 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 -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Created] (PHOENIX-1060) Replace ReferencingColumn with ColumnReference
James Taylor created PHOENIX-1060: - Summary: Replace ReferencingColumn with ColumnReference Key: PHOENIX-1060 URL: https://issues.apache.org/jira/browse/PHOENIX-1060 Project: Phoenix Issue Type: Sub-task Reporter: rajeshbabu Assignee: rajeshbabu When data region split corresponding index region also should split to ensure co-location. At the same time we should ensure index region data split properly into it's daughter regions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Updated] (PHOENIX-1057) Phoenix wrong range query result
[ 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',140400,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 | 140400 | | 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 | | 140400 | | 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 | 140400 | | 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
[jira] [Commented] (PHOENIX-1057) Phoenix wrong range query result
[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052308#comment-14052308 ] Pham Phuong Tu commented on PHOENIX-1057: - Updated bug description, hope that help! 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',140400,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 | 140400 | | 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 | | 140400 | | 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
[jira] [Updated] (PHOENIX-1060) Replace ReferencingColumn with ColumnReference
[ https://issues.apache.org/jira/browse/PHOENIX-1060?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ] James Taylor updated PHOENIX-1060: -- Description: We currently have two separate classes that represent a column family/column qualifier combo: ColumnReference and ReferencingColumn. I believe the only difference is that ReferencingColumn allows a backing byte array to be used with an offset and length. Since ColumnReference already has an ImmutableBytesPtr we can add a constructor that takes two offsets and lengths for the family and qualifier and do a bit of minor refactoring (removing the family and qualifier member variables and just use the familyPtr and qualifierPtr ones exclusivly) so that we don't have two classes for the same thing: {code} public class ColumnReference implements ComparableColumnReference { public ColumnReference(byte[] family, int familyOffset, int familyLength, byte[] qualifier, int qualifierOffset, int qualiferLength) { this.familyPtr = new ImmutableBytesPtr(family, familyOffset, familyLength); this.qualifierPtr = new ImmutableBytesPtr(qualifier, qualifierOffset, qualifierLength); this.hashCode = calcHashCode(familyPtr.get(), qualifierPtr.get()); } {code} Another useful addition would be to implement Writable so we don't duplicate the serialization logic. was:When data region split corresponding index region also should split to ensure co-location. At the same time we should ensure index region data split properly into it's daughter regions. Replace ReferencingColumn with ColumnReference -- Key: PHOENIX-1060 URL: https://issues.apache.org/jira/browse/PHOENIX-1060 Project: Phoenix Issue Type: Sub-task Reporter: rajeshbabu Assignee: rajeshbabu We currently have two separate classes that represent a column family/column qualifier combo: ColumnReference and ReferencingColumn. I believe the only difference is that ReferencingColumn allows a backing byte array to be used with an offset and length. Since ColumnReference already has an ImmutableBytesPtr we can add a constructor that takes two offsets and lengths for the family and qualifier and do a bit of minor refactoring (removing the family and qualifier member variables and just use the familyPtr and qualifierPtr ones exclusivly) so that we don't have two classes for the same thing: {code} public class ColumnReference implements ComparableColumnReference { public ColumnReference(byte[] family, int familyOffset, int familyLength, byte[] qualifier, int qualifierOffset, int qualiferLength) { this.familyPtr = new ImmutableBytesPtr(family, familyOffset, familyLength); this.qualifierPtr = new ImmutableBytesPtr(qualifier, qualifierOffset, qualifierLength); this.hashCode = calcHashCode(familyPtr.get(), qualifierPtr.get()); } {code} Another useful addition would be to implement Writable so we don't duplicate the serialization logic. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1000) Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions
[ https://issues.apache.org/jira/browse/PHOENIX-1000?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052316#comment-14052316 ] Hudson commented on PHOENIX-1000: - SUCCESS: Integrated in Phoenix | 3.0 | Hadoop1 #124 (See [https://builds.apache.org/job/Phoenix-3.0-hadoop1/124/]) PHOENIX-1000 Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. (Addendum) (anoopsamjohn: rev a66ffca460ca1e42ddc84a6a0679d64475781d81) * phoenix-core/src/main/java/org/apache/phoenix/expression/ExpressionType.java * phoenix-core/src/main/java/org/apache/phoenix/expression/aggregator/FirstLastValueBaseClientAggregator.java Support FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions -- Key: PHOENIX-1000 URL: https://issues.apache.org/jira/browse/PHOENIX-1000 Project: Phoenix Issue Type: New Feature Reporter: James Taylor Assignee: Vaclav Loffelmann Fix For: 5.0.0, 3.1, 4.1 Attachments: PHOENIX-1000-3-v2.patch, PHOENIX-1000-3.0-addendum.patch, PHOENIX-1000-3.patch, PHOENIX-1000-4-v2.patch, PHOENIX-1000-4-v3.patch, PHOENIX-1000-4.0-addendum.patch, PHOENIX-1000-4.patch, PHOENIX-1000-master-addendum.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-addendum_2.patch, PHOENIX-1000-master-v2.patch, PHOENIX-1000-master.patch Support the standard FIRST_VALUE, LAST_VALUE, and NTH_VALUE aggregate functions. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1015) Support joining back to data table row from local index when query condition involves leading columns in local index
[ https://issues.apache.org/jira/browse/PHOENIX-1015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052324#comment-14052324 ] James Taylor commented on PHOENIX-1015: --- Or maybe [~ramkrishna] or [~anoop.hbase], you'd have a sec to commit this to the local-index branch? Support joining back to data table row from local index when query condition involves leading columns in local index Key: PHOENIX-1015 URL: https://issues.apache.org/jira/browse/PHOENIX-1015 Project: Phoenix Issue Type: Sub-task Reporter: rajeshbabu Assignee: rajeshbabu Attachments: PHOENIX-1015.patch, PHOENIX-1015_v6.patch, PHOENIX-1015_v7.patch, PHOENIX-1015_v8.patch, PHOENIX-1015_v8.rar, PHOENIX-1015_v9.patch When a query involves more columns to project than columns in index and query condition involves leading columns in local index then first we can get matching rowkeys from local index table and then get the required columns from data table. In local index both data region and index region co-reside in the same RS, we can call get on data region to get the missing columns in the index, without any n/w overhead. So it's efficient. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1015) Support joining back to data table row from local index when query condition involves leading columns in local index
[ https://issues.apache.org/jira/browse/PHOENIX-1015?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052347#comment-14052347 ] rajeshbabu commented on PHOENIX-1015: - Thanks [~ramkrishna] and [~anoop.hbase]. Support joining back to data table row from local index when query condition involves leading columns in local index Key: PHOENIX-1015 URL: https://issues.apache.org/jira/browse/PHOENIX-1015 Project: Phoenix Issue Type: Sub-task Reporter: rajeshbabu Assignee: rajeshbabu Attachments: PHOENIX-1015.patch, PHOENIX-1015_v6.patch, PHOENIX-1015_v7.patch, PHOENIX-1015_v8.patch, PHOENIX-1015_v8.rar, PHOENIX-1015_v9.patch When a query involves more columns to project than columns in index and query condition involves leading columns in local index then first we can get matching rowkeys from local index table and then get the required columns from data table. In local index both data region and index region co-reside in the same RS, we can call get on data region to get the missing columns in the index, without any n/w overhead. So it's efficient. -- This message was sent by Atlassian JIRA (v6.2#6252)
[jira] [Commented] (PHOENIX-1057) Phoenix wrong range query result
[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=14052707#comment-14052707 ] Jeffrey Zhong commented on PHOENIX-1057: Just to confirm that you're using hbase0.98.0 or hbase0.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',140400,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 | 140400 | | 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 | | 140400 | | 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 |
[jira] [Commented] (PHOENIX-1057) Phoenix wrong range query result
[ https://issues.apache.org/jira/browse/PHOENIX-1057?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanelfocusedCommentId=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',140400,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 | 140400 | | 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 | | 140400 | | 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 |