Re: IN operator can take how many inputs ?

[Jinfeng Ni]

You can modify option `planner.in_subquery_threshold`. By default,
it's set to be 20. That's the threshold when planner decides to
convert IN-list to a subquery.

select * from sys.options where name like '%in_subquery%';
+--------------------------------+-------+---------+----------+----------+-------------+-----------+------------+
|              name              | kind  |  type   |  status  |
num_val  | string_val  | bool_val  | float_val  |
+--------------------------------+-------+---------+----------+----------+-------------+-----------+------------+
| planner.in_subquery_threshold  | LONG  | SYSTEM  | DEFAULT  | 20
  | null        | null      | null       |
+--------------------------------+-------+---------+----------+----------+-------------+-----------+------------+

On Sun, Oct 2, 2016 at 12:56 AM, Tushar Pathare <tpath...@sidra.org> wrote:
> Hello Team,
>
> A select clause with IN operator creates a issue if the count of goes beyond 
> 19 for params.Is this a tunable or this is a drawback.
> If the number of params is made less that 19 the same select statement works
>
> Select something……
>
> IN ( '94479 ', '296979 ', '219579 ', '109179 ', '97179 ', '223179 ', '96279 
> ', '224979 ', '282879 ', '33279 ', '277179 ', '177879 ', '272049 ', '49179 ', 
> '104049 ','177879 ', '272049 ', '49179 ', '104049 ', '104049 ')
>
> Error thrown
> org.apache.drill.common.exceptions.UserRemoteException: DATA_READ ERROR: The 
> JDBC storage plugin failed while trying setup the SQL query
>
>
>
>
> Tushar B Pathare
> High Performance Computing (HPC) Administrator
> General Parallel File System
> Scientific Computing
> Bioinformatics Division
> Research
>
> Sidra Medical and Research Centre
> Sidra OPC Building
> PO Box 26999  |  Doha, Qatar
> Near QNCC,5th Floor
> Office 4003 3333 ext 37443 | M +974 74793547
> tpath...@sidra.org | www.sidra.org <http://www.sidra.org/>
>
>
>
>
>
>
> Disclaimer: This email and its attachments may be confidential and are 
> intended solely for the use of the individual to whom it is addressed. If you 
> are not the intended recipient, any reading, printing, storage, disclosure, 
> copying or any other action taken in respect of this e-mail is prohibited and 
> may be unlawful. If you are not the intended recipient, please notify the 
> sender immediately by using the reply function and then permanently delete 
> what you have received. Any views or opinions expressed are solely those of 
> the author and do not necessarily represent those of Sidra Medical and 
> Research Center.