Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Please see the Cloudera blog for information on the Cloudera Response to CVE-2021-4428

Phoenix query issue while using performing select

Expert Contributor

When performing certain select queries to Apache Phoenix, it can report incorrect results (resultset=0 when there are records to be reported).
This happens for query of the form:

 

 

 

 

SELECT queries that are selecting all named columns FROM <some doctypeview> WHERE doc_modify_date >= "date"T 00.00.00.000 and doc_modify_date < "date+1"T00.00.00.000Zorder by doc_modify_date asc limit 5000

 

 

 

 

These queries are using the DARE-index for the document type, which only has the doc_modify_date as a field.

 

I ran the query with so many combination but didn't get any things in logs 

Below are the test cases.

  1. applied the condition > 2021-05-03 00:00:00.000 and < 2021-05-04 00:00:00.00

 

 

 

SELECT queries that are selecting all named columns FROM <some doctypeview> WHERE doc_modify_date >= "date"T 00.00.00.000 and doc_modify_date < "date+1"T00.00.00.000Zorder by doc_modify_date asc limit 5000​

 

 

 

  • applied the only condition > 2021-05-03 00:00:00.000 then got the data.

 

 

 

SELECT DOC_KEY, DOC_STATE, "DOC_CREATE_DATE", "DOC_CONTENT_TYPE", "DOC_CONTENT_ID", "DOC_MODIFY_DATE", "DOC_SIZE", "DOC_COMPRESSED_SIZE", "DOC_FILENAME", "DOC_SCHEMA_VERSION", "DOC_SOURCE", "DOC_TYPE_VERSION", "DOC_MIGRATED", DOCUMENTID, CREATETIME, PHYSICALQUANTITY, JOBID, DOCUMENTTYPEVERSION, SOFTWARERELEASE FROM OBST.GEN_ADELFINGERP_VIEW WHERE "DOC_STATE"='PERSISTENT' AND "DOC_MODIFY_DATE" > TO_TIMESTAMP('2021-05-03 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS','UTC') ORDER BY "DOC_MODIFY_DATE" ASC limit 10;

 

 

 

  • got the data even when we also applied >=  2021-05-03 00:00:00.000

  • Even it's working fine for <= condition also.

 

 

 

jdbc:phoenix:> SELECT DOC_KEY, DOC_STATE, "DOC_CREATE_DATE", "DOC_CONTENT_TYPE", "DOC_CONTENT_ID", "DOC_MODIFY_DATE", "DOC_SIZE", "DOC_COMPRESSED_SIZE", "DOC_FILENAME", "DOC_SCHEMA_VERSION", "DOC_SOURCE", "DOC_TYPE_VERSION", "DOC_MIGRATED", DOCUMENTID, CREATETIME, PHYSICALQUANTITY, JOBID, DOCUMENTTYPEVERSION, SOFTWARERELEASE FROM OBST.GEN_ADELFINGERP_VIEW WHERE "DOC_STATE"='PERSISTENT' AND "DOC_MODIFY_DATE" <= TO_TIMESTAMP('2021-05-03 00:00:00.000','yyyy-MM-dd HH:mm:ss.SSS','UTC') ORDER BY "DOC_MODIFY_DATE" ASC limit 10;

 

 


So The data is not coming when we are running the query with >= and < condition.

 

Can you please help if there any issue/BUG with phoenix?

 

0 REPLIES 0