[jira] [Commented] (HIVE-25659) Divide IN/(NOT IN) queries based on number of max parameters SQL engine can support

["Nikhil Gupta (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/HIVE-25659?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17435458#comment-17435458 ] 

Nikhil Gupta commented on HIVE-25659:
-------------------------------------

Code to find number of parameters
{noformat}
public static void main(String[] args) {
  Configuration conf = MetastoreConf.newMetastoreConf();
  conf.set(ConfVars.DIRECT_SQL_MAX_ELEMENTS_IN_CLAUSE.getVarname(), "100");
  conf.set(ConfVars.DIRECT_SQL_MAX_QUERY_LENGTH.getVarname(), "10");
  List<String> queries = new ArrayList<>();
  List<Long> deleteSet = new ArrayList<>();
  for (long i=0; i < 10000; i++) {
    deleteSet.add(i+1);
  }
  StringBuilder prefix = new StringBuilder();
  StringBuilder suffix = new StringBuilder();

  prefix.append("delete from COMPLETED_COMPACTIONS where ");
  suffix.append("");

  List<String> questions = new ArrayList<>(deleteSet.size());
  for (int  i = 0; i < deleteSet.size(); i++) {
    questions.add("?");
  }
  List<Integer> counts = TxnUtils.buildQueryWithINClauseStrings(conf, queries, prefix, suffix, questions, "cc_id", false, false);
  System.out.println(queries.get(0).chars().filter(ch -> ch == '?').count());
}{noformat}

> Divide IN/(NOT IN) queries based on number of max parameters SQL engine can support
> -----------------------------------------------------------------------------------
>
>                 Key: HIVE-25659
>                 URL: https://issues.apache.org/jira/browse/HIVE-25659
>             Project: Hive
>          Issue Type: Bug
>          Components: Standalone Metastore
>    Affects Versions: 3.1.0, 4.0.0
>            Reporter: Nikhil Gupta
>            Assignee: Nikhil Gupta
>            Priority: Minor
>             Fix For: 4.0.0
>
>
> 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:
> {code:java}
> DIRECT_SQL_MAX_ELEMENTS_IN_CLAUSE = 100
> DIRECT_SQL_MAX_QUERY_LENGTH = 10 (10 KB)
> Number of parameters in a single query = 4759
> {code}



--
This message was sent by Atlassian Jira
(v8.3.4#803005)