[
https://issues.apache.org/jira/browse/HIVE-28909?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Kokila N updated HIVE-28909:
----------------------------
Description:
For Hive with JDK17 compile support, Data-Nucleus needed to be upgraded from
5.x to 6.x
After upgrading to Hive 6.x (specifically version 6.0.8), we observed multiple
QTest failures, particularly for SELECT queries due to unexpected UPDATE
statements being triggered on the COLUMNS_V2 table during read operations.
{code:java}
Caused by: org.apache.hadoop.hive.metastore.api.MetaException:
JDOUserException: One or more instances could not be retrieved
Root cause: Error : 22848, Position : 102, Sql = UPDATE COLUMNS_V2 SET
"COMMENT" = :1 WHERE CD_ID=:2 AND "COMMENT" = :3 AND "COLUMN_NAME" = :4 AND
TYPE_NAME = :5,
OriginalSql = UPDATE COLUMNS_V2 SET "COMMENT" = ? WHERE CD_ID=? AND "COMMENT" =
? AND "COLUMN_NAME" = ? AND TYPE_NAME = ?,
Error Msg = ORA-22848: cannot use CLOB type as comparison key{code}
*Analysis:*
When we ran a SELECT query like SELECT * FROM test_table, we noticed (via
Oracle audit logs) that 18 unexpected UPDATE statements were issued on the
COLUMNS_V2 table.
These UPDATEs were not user-initiated, but instead triggered internally
from DataNucleus, specifically during column descriptor retrieval (e.g.,
loading embedded elements from MColumnDescriptor).
One INSERT (expected) was for the actual CREATE TABLE DDL.
However, these 18 UPDATEs are redundant, as no actual column change was
made — these are simply side effects of dirty state synchronization inside the
JDO lifecycle.
The use of CLOB columns (TYPE_NAME) in Oracle becomes problematic in such
UPDATE WHERE clauses due to the following error:
{code:java}
ORA-22848: cannot use CLOB type as comparison key{code}
*Why This Is Not Oracle-Specific*
We see the same issue in Derby (in terms of unnecessary updates), although
without the CLOB-specific exception.
In PostgreSQL or MySQL, the issue is not encountered because the Hive
schemas use VARCHAR (STRING) instead of CLOB for these fields — and those types
do support equality comparisons.
was:
For Hive with JDK17 compile support, Data-Nucleus needed to be upgraded from
5.x to 6.x
After upgrading to Hive 6.x (specifically version 6.0.8), we observed multiple
QTest failures, particularly for SELECT queries due to unexpected UPDATE
statements being triggered on the COLUMNS_V2 table during read operations.
{code:java}
Caused by: org.apache.hadoop.hive.metastore.api.MetaException:
JDOUserException: One or more instances could not be retrieved
Root cause: Error : 22848, Position : 102, Sql = UPDATE COLUMNS_V2 SET
"COMMENT" = :1 WHERE CD_ID=:2 AND "COMMENT" = :3 AND "COLUMN_NAME" = :4 AND
TYPE_NAME = :5,
OriginalSql = UPDATE COLUMNS_V2 SET "COMMENT" = ? WHERE CD_ID=? AND "COMMENT" =
? AND "COLUMN_NAME" = ? AND TYPE_NAME = ?,
Error Msg = ORA-22848: cannot use CLOB type as comparison key{code}
*Analysis:*
When we ran a SELECT query like SELECT * FROM test_table, we noticed (via
Oracle audit logs) that 18 unexpected UPDATE statements were issued on the
COLUMNS_V2 table.
These UPDATEs were not user-initiated, but instead triggered internally from
DataNucleus, specifically during column descriptor retrieval (e.g., loading
embedded elements from MColumnDescriptor).
One INSERT (expected) was for the actual CREATE TABLE DDL.
However, these 18 UPDATEs are redundant, as no actual column change was made —
these are simply side effects of dirty state synchronization inside the JDO
lifecycle.
The use of CLOB columns (TYPE_NAME) in Oracle becomes problematic in such
UPDATE WHERE clauses due to the following error:
ORA-22848: cannot use CLOB type as comparison key
Why This Is Not Oracle-Specific
We see the same issue in Derby (in terms of unnecessary updates), although
without the CLOB-specific exception.
In PostgreSQL or MySQL, the issue is not encountered because the Hive schemas
use VARCHAR (STRING) instead of CLOB for these fields — and those types do
support equality comparisons.
> After DataNucleus upgrade to 6.0.8 for JDK17 compatibility, there are
> unnecessary update queries executing by DN
> ----------------------------------------------------------------------------------------------------------------
>
> Key: HIVE-28909
> URL: https://issues.apache.org/jira/browse/HIVE-28909
> Project: Hive
> Issue Type: Bug
> Reporter: Kokila N
> Assignee: Sai Hemanth Gantasala
> Priority: Major
>
> For Hive with JDK17 compile support, Data-Nucleus needed to be upgraded from
> 5.x to 6.x
> After upgrading to Hive 6.x (specifically version 6.0.8), we observed
> multiple QTest failures, particularly for SELECT queries due to unexpected
> UPDATE statements being triggered on the COLUMNS_V2 table during read
> operations.
> {code:java}
> Caused by: org.apache.hadoop.hive.metastore.api.MetaException:
> JDOUserException: One or more instances could not be retrieved
> Root cause: Error : 22848, Position : 102, Sql = UPDATE COLUMNS_V2 SET
> "COMMENT" = :1 WHERE CD_ID=:2 AND "COMMENT" = :3 AND "COLUMN_NAME" = :4 AND
> TYPE_NAME = :5,
> OriginalSql = UPDATE COLUMNS_V2 SET "COMMENT" = ? WHERE CD_ID=? AND "COMMENT"
> = ? AND "COLUMN_NAME" = ? AND TYPE_NAME = ?,
> Error Msg = ORA-22848: cannot use CLOB type as comparison key{code}
> *Analysis:*
> When we ran a SELECT query like SELECT * FROM test_table, we noticed (via
> Oracle audit logs) that 18 unexpected UPDATE statements were issued on the
> COLUMNS_V2 table.
> These UPDATEs were not user-initiated, but instead triggered internally
> from DataNucleus, specifically during column descriptor retrieval (e.g.,
> loading embedded elements from MColumnDescriptor).
> One INSERT (expected) was for the actual CREATE TABLE DDL.
> However, these 18 UPDATEs are redundant, as no actual column change was
> made — these are simply side effects of dirty state synchronization inside
> the JDO lifecycle.
> The use of CLOB columns (TYPE_NAME) in Oracle becomes problematic in such
> UPDATE WHERE clauses due to the following error:
>
> {code:java}
> ORA-22848: cannot use CLOB type as comparison key{code}
>
> *Why This Is Not Oracle-Specific*
> We see the same issue in Derby (in terms of unnecessary updates),
> although without the CLOB-specific exception.
> In PostgreSQL or MySQL, the issue is not encountered because the Hive
> schemas use VARCHAR (STRING) instead of CLOB for these fields — and those
> types do support equality comparisons.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
