phoenix-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From venk sham <shamv...@gmail.com>
Subject Phoenix 4.8.1 client returns multiple records for single record in table
Date Thu, 26 Jan 2017 23:20:31 GMT
Hi,

Phoenix 4.8.1 client

SELECT is returning multiple records when non primary key is used in where
clause.

where are returning only one record when primary key is used in where
clause.


Please find the details



*Table Description*

0: jdbc:phoenix:localhost> !describe
CDS_TEST.MONITORED_QUEUE_MESSAGE_DETAILS;
+------------+--------------+----------------------------------+----------------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+
| TABLE_CAT  | TABLE_SCHEM  |            TABLE_NAME            |
COLUMN_NAME      | DATA_TYPE  | TYPE_NAME  | COLUMN_SIZE  | BUFFER_LENGTH
 | DECIMAL_DIGITS  | NUM_PREC_RADIX  | NULLABLE  |
+------------+--------------+----------------------------------+----------------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  | ROW_KEY
           | 12         | VARCHAR    | null         | null           | null
           | null            | 0         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
JMS_MESSAGE_ID       | 12         | VARCHAR    | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  | QUEUE_NAME
          | 12         | VARCHAR    | null         | null           | null
           | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
ORGANIZATION_ID      | 12         | VARCHAR    | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
SUB_ORGANIZATION_ID  | 12         | VARCHAR    | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  | USER
          | 12         | VARCHAR    | null         | null           | null
           | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
MESSAGE_TEXT         | 12         | VARCHAR    | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
MESSAGE_BYTES        | -3         | VARBINARY  | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  | CREATED_BY
          | 12         | VARCHAR    | null         | null           | null
           | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
CREATED_DATE         | 93         | TIMESTAMP  | null         | null
    | null            | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  | UPDATED_BY
          | 12         | VARCHAR    | null         | null           | null
           | null            | 1         |
|            | CDS_TEST     | MONITORED_QUEUE_MESSAGE_DETAILS  |
UPDATED_DATE         | 93         | TIMESTAMP  | null         | null
    | null            | null            | 1         |
+------------+--------------+----------------------------------+----------------------+------------+------------+--------------+----------------+-----------------+-----------------+-----------+




*Query with primary key returned single record.*


0: jdbc:phoenix:localhost> SELECT
row_key,jms_message_id,created_by,message_text,message_bytes,organization_id,sub_organization_id,message_id
FROM CDS_TEST.MONITORED_QUEUE_MESSAGE_DETAILS (message_id VARCHAR) WHERE
row_key = 'c8014083-f749-4caf-bee1-f12392fc0673';
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
|                ROW_KEY                |                JMS_MESSAGE_ID
            |   CREATED_BY   | MESSAGE_TEXT  | MESSAGE_BYTES  |
 ORGANIZATION_ID            | SUB_ORGANIZ |
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
| c8014083-f749-4caf-bee1-f12392fc0673  |
ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243  | cds_test_user  |
    | [B@77128dab    | f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60  |             |
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
1 row selected (0.105 seconds)





Query with non-primary key returned 4 records with same primary Key.

0: jdbc:phoenix:localhost> SELECT
row_key,jms_message_id,created_by,message_text,message_bytes,organization_id,sub_organization_id,message_id
FROM CDS_TEST.MONITORED_QUEUE_MESSAGE_DETAILS (message_id VARCHAR) WHERE
message_id = '0d4c5a22-7f89-4cc8-8727-2bc372b3539f';
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
|                ROW_KEY                |                JMS_MESSAGE_ID
            |   CREATED_BY   | MESSAGE_TEXT  | MESSAGE_BYTES  |
 ORGANIZATION_ID            | SUB_ORGANIZ |
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
| c8014083-f749-4caf-bee1-f12392fc0673  |
ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243  | cds_test_user  |
    | [B@4d847d32    | f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60  |             |
| c8014083-f749-4caf-bee1-f12392fc0673  |
ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243  | cds_test_user  |
    | [B@5f462e3b    | f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60  |             |
| c8014083-f749-4caf-bee1-f12392fc0673  |
ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243  | cds_test_user  |
    | [B@3d7fa3ae    | f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60  |             |
| c8014083-f749-4caf-bee1-f12392fc0673  |
ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243  | cds_test_user  |
    | [B@58065f0c    | f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60  |             |
+---------------------------------------+-----------------------------------------------+----------------+---------------+----------------+---------------------------------------+-------------+
4 rows selected (0.808 seconds)



*HBase Raw Scan*

hbase(main):001:0> scan
'CDS_TEST.MONITORED_QUEUE_MESSAGE_DETAILS',{STARTROW=>'c8014083-f749-4caf-bee1-f12392fc0673',STOPROW=>'c8014083-f749-4caf-bee1-f12392fc0673',RAW=>TRUE}
ROW                                               COLUMN+CELL
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:CREATED_BY, timestamp=1485447313416, value=cds_test_user
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:CREATED_DATE, timestamp=1485447313416,
value=\x80\x00\x01Y\xDB\x8F7@\x00\x00\x00\x00
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:JMS_MESSAGE_ID, timestamp=1485447313416,
value=ID:CDS-HDE-EMS-Server.9B8585353AB3F43D4:1243
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:MESSAGE_BYTES, timestamp=1485447313416, value=\x0A
8176fbb974d94ba3c1d5f682e18920af\x12$0d4c5a22-7f89-4cc8-8727-2bc372b3539f\x18
                                                  \x010\x00
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:MESSAGE_ID, timestamp=1485447313416,
value=0d4c5a22-7f89-4cc8-8727-2bc372b3539f
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:MESSAGE_TEXT, timestamp=1485447313416, type=DeleteColumn
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:ORGANIZATION_ID, timestamp=1485447313416,
value=f3f424f4-2fa0-446c-afc5-f0d6d6ac7f60
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:QUEUE_NAME, timestamp=1485447313416,
value=Q.CDS.JobNotification.QA.Internal.Epsilon
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:SUB_ORGANIZATION_ID, timestamp=1485447313416,
type=DeleteColumn
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:UPDATED_BY, timestamp=1485447313416, value=cds_test_user
 c8014083-f749-4caf-bee1-f12392fc0673
column=default:UPDATED_DATE, timestamp=1485447313416,
value=\x80\x00\x01Y\xDB\x8F7@\x00\x00\x00\x00
 c8014083-f749-4caf-bee1-f12392fc0673             column=default:USER,
timestamp=1485447313416, value=cds_test_user
 c8014083-f749-4caf-bee1-f12392fc0673             column=default:_0,
timestamp=1485447313416, type=DeleteColumn
1 row(s) in 0.6670 seconds


Thanks
Venkat

Mime
View raw message