You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@phoenix.apache.org by venk sham <sh...@gmail.com> on 2017/01/26 23:20:31 UTC
Phoenix 4.8.1 client returns multiple records for single record in table
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