[
https://issues.apache.org/jira/browse/PHOENIX-7982?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18102730#comment-18102730
]
Alejandro Anadon commented on PHOENIX-7982:
-------------------------------------------
I noticed a slight inaccuracy in the example SQL statements that might
lead one to think the example is invalid; however, it is valid.
Integers were being assigned to the primary key even though
it is defined as 'VARBINARY_ENCODED'; nevertheless, the result is the same,
since the integer is internally converted to `VARBINARY_ENCODED`.
Given that 1 is encoded as \x80\x00\x00\x01 in 'VARBINARY' and
as \x80\x00\xFF\x00\xFF\x01 in 'VARBINARY_ENCODED', the error was also clearly
visible.
But if you wish to verify this, instead of assigning 1 or 2,
try assigning X'0001' or X'0002' (which are actual binary values);
you will see that the error still occurs.
(For the error to be clearly visible, the primary key must contain a 0x00).
DELETE FROM TEST_TABLE_BINARY_ENCODED;
DELETE FROM TEST_TABLE_BINARY_ENCODED2;
UPSERT INTO TEST_TABLE_BINARY_ENCODED
(BIN_FIELD , ID, CHAR_FIELD )
VALUES (X'010203',X'0001', 'TEXT1');
UPSERT INTO TEST_TABLE_BINARY_ENCODED
(BIN_FIELD , ID, CHAR_FIELD )
VALUES (X'04050607',X'0002', 'TEXT2');
UPSERT INTO TEST_TABLE_BINARY_ENCODED2
(BIN_FIELD , ID )
VALUES (X'00000000',X'0001');
UPSERT INTO TEST_TABLE_BINARY_ENCODED2
(BIN_FIELD , ID )
VALUES (X'00000000',X'0002');
UPSERT INTO TEST_TABLE_BINARY_ENCODED (ID, BIN_FIELD )
SELECT ID, BIN_FIELD FROM TEST_TABLE_BINARY_ENCODED2;
> Data corruption in an UPSERT SELECT with VARBINARY_ENCODED
> ----------------------------------------------------------
>
> Key: PHOENIX-7982
> URL: https://issues.apache.org/jira/browse/PHOENIX-7982
> Project: Phoenix
> Issue Type: Bug
> Components: phoenix
> Affects Versions: 5.3.1
> Environment: O.S.=Ubuntu 26.04 LTS
> JDK=17.0.19
> ZOOKEEPER=3.8.6
> HADOOP=3.4.3
> HBASE_VERSION=2.6.6
> PHOENIX_VERSION=5.3.1
> Reporter: Alejandro Anadon
> Assignee: Viraj Jasani
> Priority: Blocker
> Attachments: BugVarBinaryEncodedTest.java
>
>
> Tested only with 5.3.1; I assume it happens in all 5.3.x versions too.
> Data corruption occurs when performing an UPSERT INTO ... SELECT operation on
> a VARBINARY_ENCODED field.
> It can be easily verified with the following SQL statements:
> ----
> DROP TABLE IF EXISTS TEST_TABLE_BINARY_ENCODED;
> DROP TABLE IF EXISTS TEST_TABLE_BINARY_ENCODED2;
> CREATE TABLE IF NOT EXISTS TEST_TABLE_BINARY_ENCODED (
> ID VARBINARY_ENCODED NOT NULL,
> BIN_FIELD VARBINARY_ENCODED,
> CHAR_FIELD VARCHAR
> CONSTRAINT PK PRIMARY KEY (ID));
> CREATE INDEX IF NOT EXISTS TEST_INDEX ON TEST_TABLE_BINARY_ENCODED
> (BIN_FIELD, CHAR_FIELD);
> CREATE TABLE IF NOT EXISTS TEST_TABLE_BINARY_ENCODED2 (
> ID VARBINARY_ENCODED NOT NULL,
> BIN_FIELD VARBINARY_ENCODED
> CONSTRAINT PK PRIMARY KEY (ID));
> UPSERT INTO TEST_TABLE_BINARY_ENCODED
> (BIN_FIELD , ID, CHAR_FIELD )
> VALUES (X'010203',1, 'TEXT1');
> UPSERT INTO TEST_TABLE_BINARY_ENCODED
> (BIN_FIELD , ID, CHAR_FIELD )
> VALUES (X'04050607',2, 'TEXT2');
> UPSERT INTO TEST_TABLE_BINARY_ENCODED2
> (BIN_FIELD , ID )
> VALUES (X'00000000',1);
> UPSERT INTO TEST_TABLE_BINARY_ENCODED2
> (BIN_FIELD , ID )
> VALUES (X'00000000',2);
> UPSERT INTO TEST_TABLE_BINARY_ENCODED (ID, BIN_FIELD )
> SELECT ID, BIN_FIELD FROM TEST_TABLE_BINARY_ENCODED2;
> SELECT COUNT(*) FROM TEST_TABLE_BINARY_ENCODED;
> ----
> There should only be two entries, but four appear.
> It is evident that the data in the HBase tables has become corrupted.
> Specifically, the encoding for `VARBINARY_ENCODED` values—which
> should have been encoded as:
> '\x00\xFF\x00\xFF\x00\xFF\x00\xFF'
> —was instead encoded as if it were standard, unencoded `VARBINARY`:
> '\x00\x00\x00\x00'
> This has severely affected the data and left the index completely corrupted.
> This is the output that appears in HBase table (I've removed the timestamps
> to make it easier to view here):
> hbase:349:0> scan 'TEST_TABLE_BINARY_ENCODED';
> ROW COLUMN+CELL
> \x80\x00\x00\x01 column=0:\x00\x00\x00\x00, value=x
> \x80\x00\x00\x01 column=0:\x80\x0B,
> value=\x00\x00\x00\x00
> \x80\x00\x00\x02 column=0:\x00\x00\x00\x00, value=x
> \x80\x00\x00\x02 column=0:\x80\x0B,
> value=\x00\x00\x00\x00
> \x80\x00\xFF\x00\xFF\x01 column=0:\x00\x00\x00\x00, value=x
> \x80\x00\xFF\x00\xFF\x01 column=0:\x80\x0B, value=\x01\x02\x03
> \x80\x00\xFF\x00\xFF\x01 column=0:\x80\x0C, value=TEXT1
> \x80\x00\xFF\x00\xFF\x02 column=0:\x00\x00\x00\x00, value=x
> \x80\x00\xFF\x00\xFF\x02 column=0:\x80\x0B,
> value=\x04\x05\x06\x07
> \x80\x00\xFF\x00\xFF\x02 column=0:\x80\x0C, value=TEXT2
> 4 row(s)
> Took 0.0297 seconds
> This is the output that appears in HBase INDEX table (I'm leaving just the
> keys for simplicity):
> hbase:355:0> scan 'TEST_INDEX'
> ROW
> \x00\x00\x00\x00\x00\x01\x00\x80\x00\x00\x01
> \x00\x00\x00\x00\x00\x01\x00\x80\x00\x00\x02
> \x01\x02\x03\x00\x01TEXT1\x00\x80\x00\xFF\x00\xFF\x01
> \x04\x05\x06\x07\x00\x01TEXT2\x00\x80\x00\xFF\x00\xFF\x02
> 4 row(s)
> Took 0.0211 seconds
> I am attaching a simplified Java class with another example —using only a
> single table— that
> also reproduces the error. This example reflects how the issue was originally
> detected.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
