[HIVE-24925] Query materialized view invalidation info can cause ORA-01795

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Component/s: Metastore
• Assignee: Unassigned
• Reporter: Krisztian Kasa
• Created: 23/Mar/21 13:17
• Updated: 21/Oct/22 07:20

Description

Query materialized view invalidation info assemble direct sql query to pull update/delete completed transactions on source tables since the last rebuild of the materialized view.
Invalid writeIds are also used to filter the result. These writeIds are passed using an in operator.
Depend on the size of invalid writeId list the operands of the in operator or the overall query text can exceed limitations. Example: in case of Oracle backend db maximum number of expressions in a list is 1000.

SELECT "CTC_UPDATE_DELETE"
FROM "COMPLETED_TXN_COMPONENTS"
WHERE "CTC_UPDATE_DELETE" ='Y'
AND ( ("CTC_DATABASE"=? AND "CTC_TABLE"=? AND ("CTC_WRITEID" > 1 OR "CTC_WRITEID" IN (<invalid writeId1>, <invalid writeId2> ... <invalid writeIdn>)) ) OR
      ("CTC_DATABASE"=? AND "CTC_TABLE"=? AND ("CTC_WRITEID" > 1) )
      )
AND "CTC_TXNID" <= 16