[
https://issues.apache.org/jira/browse/PHOENIX-7282?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17889994#comment-17889994
]
Szucs Villo commented on PHOENIX-7282:
--------------------------------------
|Example:
create table test203 (mykey integer not null primary key, col1 bigint,
new_column_1 FLOAT, new_column_2 DOUBLE, new_column_3 BIGINT);
create index indexName203 on test203 (col1) INCLUDE
(new_column_1,new_column_2,new_column_3);
UPSERT INTO TEST203(mykey,col1,new_column_1,new_column_2,new_column_3)
VALUES(5,43,201,202,203);
SELECT TENANT_ID, TABLE_SCHEM, TABLE_NAME, DATA_TYPE, COLUMN_NAME,
DATA_TABLE_NAME, COLUMN_FAMILY,ORDINAL_POSITION, KEY_SEQ FROM system.catalog
WHERE TABLE_NAME = 'INDEXNAME203' or TABLE_NAME = 'TEST203';
[!https://private-user-images.githubusercontent.com/81696283/375374663-77937291-3b81-4b8c-ad3b-c73a1aeb6125.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjkwNjY5OTgsIm5iZiI6MTcyOTA2NjY5OCwicGF0aCI6Ii84MTY5NjI4My8zNzUzNzQ2NjMtNzc5MzcyOTEtM2I4MS00YjhjLWFkM2ItYzczYTFhZWI2MTI1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDEwMTYlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQxMDE2VDA4MTgxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWVlMWE4OGY2ZTI4OTRjZDIyODhkM2U3Y2M5ZTkyYjgxN2QzMWQ4ZDIzOTYzM2IzNTg3NGU0NGY4OGYyZmQ0ODkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0In0.wNQU8woYzQvFDETlijQg0vcmzTKtb-g3lZ6_feHELCk|width=586,height=161!|https://private-user-images.githubusercontent.com/81696283/375374663-77937291-3b81-4b8c-ad3b-c73a1aeb6125.png?jwt=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJpc3MiOiJnaXRodWIuY29tIiwiYXVkIjoicmF3LmdpdGh1YnVzZXJjb250ZW50LmNvbSIsImtleSI6ImtleTUiLCJleHAiOjE3MjkwNjY5OTgsIm5iZiI6MTcyOTA2NjY5OCwicGF0aCI6Ii84MTY5NjI4My8zNzUzNzQ2NjMtNzc5MzcyOTEtM2I4MS00YjhjLWFkM2ItYzczYTFhZWI2MTI1LnBuZz9YLUFtei1BbGdvcml0aG09QVdTNC1ITUFDLVNIQTI1NiZYLUFtei1DcmVkZW50aWFsPUFLSUFWQ09EWUxTQTUzUFFLNFpBJTJGMjAyNDEwMTYlMkZ1cy1lYXN0LTElMkZzMyUyRmF3czRfcmVxdWVzdCZYLUFtei1EYXRlPTIwMjQxMDE2VDA4MTgxOFomWC1BbXotRXhwaXJlcz0zMDAmWC1BbXotU2lnbmF0dXJlPWVlMWE4OGY2ZTI4OTRjZDIyODhkM2U3Y2M5ZTkyYjgxN2QzMWQ4ZDIzOTYzM2IzNTg3NGU0NGY4OGYyZmQ0ODkmWC1BbXotU2lnbmVkSGVhZGVycz1ob3N0In0.wNQU8woYzQvFDETlijQg0vcmzTKtb-g3lZ6_feHELCk]
When creating an index like indexName203 on test203 (col1) with included
columns (new_column_1, new_column_2, new_column_3), the data types of the
included columns in the catalog table do not change to DECIMAL. This is because
these columns are only included in the index for retrieval efficiency and are
not part of the indexed key, like col1.
That's why I made the following changes in AlterAddCascadeIndexIT to ensure the
included columns retain their original data types, such as FLOAT or DOUBLE,
rather than converting them to DECIMAL, since they are not part of the index
key and don't require transformation for indexing purposes.|
> Incorrect data in index column for corresponding BIGIT type column in data
> table
> --------------------------------------------------------------------------------
>
> Key: PHOENIX-7282
> URL: https://issues.apache.org/jira/browse/PHOENIX-7282
> Project: Phoenix
> Issue Type: Bug
> Affects Versions: 5.3.0
> Reporter: Sanjeet Malhotra
> Assignee: Szucs Villo
> Priority: Major
>
> If we add a new column of type BIGINT to an existing data table and use
> CASCADE INDEX option then the column in index is aded as DECIMAL type. If we
> query such a column and the query plan gets resolved to use index table
> instead of data table then value returned will be different from the case
> when data table would have been used by query plan.
> IT to reproduce:
> {code:java}
> @Test
> public void testBigIntData() throws Exception {
> String dataTableName = generateUniqueName();
> String indexName = generateUniqueName();
> try(Connection conn = DriverManager.getConnection(getUrl())) {
> conn.createStatement().execute("create table " + dataTableName
> + " (id varchar not null primary key, col1 integer)");
> conn.createStatement().execute("create index " + indexName + " on " +
> dataTableName + " (col1)");
> conn.createStatement().execute("alter table " + dataTableName + " add
> if not exists col3 bigint cascade index all");
> conn.createStatement().execute("upsert into " + dataTableName + "
> (id, col3) values ('a', 3)");
> conn.commit();
> ResultSet rs = conn.createStatement().executeQuery("select col3 from
> " + dataTableName);
> while(rs.next()) {
> System.out.println(rs.getObject(1));
> }
> }
> } {code}
> {{So far this issue has been observed when new column of type BIGINT is added
> to data table/view. But if there is already a column of type BIGINT in the
> data table.view then above error is not observed even if query uses index
> table as per query plan.}}
>
> {{Further findings so far:}}
> # {{During alter table when we add new column in data table/view of type
> BIGINT then we also add a column of type DECIMAL (and not BIGINT) to
> corresponding index/view index.}}
> # {{Above finding is not true for create table but alter table only.}}
> # {{We write to data table/view in column of BIGIT type and also write same
> *byte array* in index/view index also but in a column of DECIMAL type. Byte
> array written in data table at HBase layer was serialized via {{PLong}} class
> but as we write same byte array in index column so at the time of reading
> value (from index table as per query plan) the byte array gets desrialized by
> {{PDecimal}} class. As we are not using compatible serializaion and
> deserialization logic for index column so, a value in data table becomes
> totally another value in index table when read. Serialization logic of PLong
> and PDecimal are completely different thus, so are their deserialization
> logics.}}
> ## {{One such example we saw was that in above IT (for reproducing the
> error) we insert 3 in data table column (of type BIGINT and newly added via
> alter) but the corresponding desrialized value in index is
> `-1.010101010098E+126`}}
> {{Still need to figure out why this issue only happens for new columns added
> via alter DDL but not via create DDL at table/view creation time.}}
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
