[HIVE-25659] Metastore direct sql queries with IN/(NOT IN) should be split based on max parameters allowed by SQL DB

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 3.1.0, 4.0.0
• Fix Version/s: 4.0.0-alpha-1
• Component/s: Standalone Metastore
• Labels: pull-request-available
• Target Version/s: 4.0.0
• Assignee: Nikhil Gupta
• Reporter: Nikhil Gupta
• Created: 28/Oct/21 14:34
• Updated: 17/Nov/22 08:47
• Resolved: 08/Nov/21 05:53

Description

Function org.apache.hadoop.hive.metastore.txn.TxnUtils#buildQueryWithINClauseStrings can generate queries with huge number of parameters with very small value of DIRECT_SQL_MAX_ELEMENTS_IN_CLAUSE and DIRECT_SQL_MAX_QUERY_LENGTH while generating delete query for completed_compactions table

Example:

DIRECT_SQL_MAX_ELEMENTS_IN_CLAUSE = 100
DIRECT_SQL_MAX_QUERY_LENGTH = 10 (10 KB)
Number of parameters in a single query = 4759

Issue Links

• duplicates: HIVE-25868 AcidHouseKeeperService fails to purgeCompactionHistory if the entries in COMPLETED_COMPACTIONS tables
• links to: GitHub Pull Request #2758