You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Tushar Pathare <tp...@sidra.org> on 2016/10/02 07:56:25 UTC
IN operator can take how many inputs ?
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
tpathare@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.
Re: IN operator can take how many inputs ?
Posted by Andries Engelbrecht <ae...@maprtech.com>.
Here is the link to upgrading Drill on MapR 5.1
http://maprdocs.mapr.com/51/Drill/upgrading_drill_5.1.html <http://maprdocs.mapr.com/51/Drill/upgrading_drill_5.1.html>
Drill 1.8 is supported on MapR 5.1
http://maprdocs.mapr.com/home/InteropMatrix/r_eco_matrix.html <http://maprdocs.mapr.com/home/InteropMatrix/r_eco_matrix.html>
--Andries
> On Oct 8, 2016, at 1:35 AM, Nicolas Paris <ni...@gmail.com> wrote:
>
> Le sam. 8 oct. 2016 à 05:55, Jinfeng Ni <jni@apache.org <ma...@apache.org>> a écrit :
>
>> With larger value for such option, a big IN-list may not be converted
>> into a subquery. As such, the query performance might be suboptimal.
>>
>
> My use case is a predicate push-down on an external jdbc database.
>
>
>> On Fri, Oct 7, 2016 at 6:54 PM, Gautam Parai <gparai@maprtech.com <ma...@maprtech.com>> wrote:
>>> Yes, this was fixed in Drill 1.8 - please let us know if this does not
>> work
>>> in 1.8. As Jinfeng mentioned earlier, the option can be set to a very
>> large
>>> value (LONG type)
>>
>
> I am not sure I am able to upgrade drill version on mapr 5.1 distribution.
> I didn't find any documentation. Have you got such ?
>
>
>>> Gautam
>>>
>>> On Fri, Oct 7, 2016 at 2:29 AM, Tushar Pathare <tp...@sidra.org>
>> wrote:
>>>
>>>> Hello,
>>>>
>>>> I think it is fixed in 1.8 .try it on
>>>>
>>>> Get Outlook for iOS<https://aka.ms/o0ukef>
>>>>
>>>>
>>>>
>>>>
>>>> On Fri, Oct 7, 2016 at 12:18 PM +0300, "Nicolas Paris" <
>>>> niparisco@gmail.com<ma...@gmail.com>> wrote:
>>>>
>>>> Hi,
>>>>
>>>> I am in 1.6 Drill version with mapr distribution 5.1.
>>>> I get this error :
>>>> Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
>>>> not exist
>>>>
>>>>
>>>> Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
>>>>
>>>>> 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 <tp...@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 <+974%207479%203547>
>> <+974%207479%203547>
>>>>>> tpathare@sidra.org | www.sidra.org<http://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.
>>>>>
>>>> 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.
Re: IN operator can take how many inputs ?
Posted by Nicolas Paris <ni...@gmail.com>.
Le sam. 8 oct. 2016 à 05:55, Jinfeng Ni <jn...@apache.org> a écrit :
> With larger value for such option, a big IN-list may not be converted
> into a subquery. As such, the query performance might be suboptimal.
>
My use case is a predicate push-down on an external jdbc database.
> On Fri, Oct 7, 2016 at 6:54 PM, Gautam Parai <gp...@maprtech.com> wrote:
> > Yes, this was fixed in Drill 1.8 - please let us know if this does not
> work
> > in 1.8. As Jinfeng mentioned earlier, the option can be set to a very
> large
> > value (LONG type)
>
I am not sure I am able to upgrade drill version on mapr 5.1 distribution.
I didn't find any documentation. Have you got such ?
> > Gautam
> >
> > On Fri, Oct 7, 2016 at 2:29 AM, Tushar Pathare <tp...@sidra.org>
> wrote:
> >
> >> Hello,
> >>
> >> I think it is fixed in 1.8 .try it on
> >>
> >> Get Outlook for iOS<https://aka.ms/o0ukef>
> >>
> >>
> >>
> >>
> >> On Fri, Oct 7, 2016 at 12:18 PM +0300, "Nicolas Paris" <
> >> niparisco@gmail.com<ma...@gmail.com>> wrote:
> >>
> >> Hi,
> >>
> >> I am in 1.6 Drill version with mapr distribution 5.1.
> >> I get this error :
> >> Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
> >> not exist
> >>
> >>
> >> Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
> >>
> >> > 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 <tp...@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 <+974%207479%203547>
> <+974%207479%203547>
> >> > > tpathare@sidra.org | www.sidra.org<http://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.
> >> >
> >> 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.
> >>
>
Re: IN operator can take how many inputs ?
Posted by Jinfeng Ni <jn...@apache.org>.
With larger value for such option, a big IN-list may not be converted
into a subquery. As such, the query performance might be suboptimal.
On Fri, Oct 7, 2016 at 6:54 PM, Gautam Parai <gp...@maprtech.com> wrote:
> Yes, this was fixed in Drill 1.8 - please let us know if this does not work
> in 1.8. As Jinfeng mentioned earlier, the option can be set to a very large
> value (LONG type).
>
> Gautam
>
> On Fri, Oct 7, 2016 at 2:29 AM, Tushar Pathare <tp...@sidra.org> wrote:
>
>> Hello,
>>
>> I think it is fixed in 1.8 .try it on
>>
>> Get Outlook for iOS<https://aka.ms/o0ukef>
>>
>>
>>
>>
>> On Fri, Oct 7, 2016 at 12:18 PM +0300, "Nicolas Paris" <
>> niparisco@gmail.com<ma...@gmail.com>> wrote:
>>
>> Hi,
>>
>> I am in 1.6 Drill version with mapr distribution 5.1.
>> I get this error :
>> Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
>> not exist
>>
>>
>> Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
>>
>> > 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 <tp...@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 <+974%207479%203547>
>> > > tpathare@sidra.org | www.sidra.org<http://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.
>> >
>> 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.
>>
Re: IN operator can take how many inputs ?
Posted by Gautam Parai <gp...@maprtech.com>.
Yes, this was fixed in Drill 1.8 - please let us know if this does not work
in 1.8. As Jinfeng mentioned earlier, the option can be set to a very large
value (LONG type).
Gautam
On Fri, Oct 7, 2016 at 2:29 AM, Tushar Pathare <tp...@sidra.org> wrote:
> Hello,
>
> I think it is fixed in 1.8 .try it on
>
> Get Outlook for iOS<https://aka.ms/o0ukef>
>
>
>
>
> On Fri, Oct 7, 2016 at 12:18 PM +0300, "Nicolas Paris" <
> niparisco@gmail.com<ma...@gmail.com>> wrote:
>
> Hi,
>
> I am in 1.6 Drill version with mapr distribution 5.1.
> I get this error :
> Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
> not exist
>
>
> Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
>
> > 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 <tp...@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 <+974%207479%203547>
> > > tpathare@sidra.org | www.sidra.org<http://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.
> >
> 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.
>
Re: IN operator can take how many inputs ?
Posted by Tushar Pathare <tp...@sidra.org>.
Hello,
I think it is fixed in 1.8 .try it on
Get Outlook for iOS<https://aka.ms/o0ukef>
On Fri, Oct 7, 2016 at 12:18 PM +0300, "Nicolas Paris" <ni...@gmail.com>> wrote:
Hi,
I am in 1.6 Drill version with mapr distribution 5.1.
I get this error :
Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
not exist
Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
> 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 <tp...@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 <+974%207479%203547>
> > tpathare@sidra.org | www.sidra.org<http://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.
>
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.
Re: IN operator can take how many inputs ?
Posted by Nicolas Paris <ni...@gmail.com>.
Hi,
I am in 1.6 Drill version with mapr distribution 5.1.
I get this error :
Error: VALIDATION ERROR: The option 'planner.in_subquery_threshold' does
not exist
Le lun. 3 oct. 2016 à 17:18, Jinfeng Ni <jn...@apache.org> a écrit :
> 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 <tp...@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 <+974%207479%203547>
> > tpathare@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.
>
Re: IN operator can take how many inputs ?
Posted by Jinfeng Ni <jn...@apache.org>.
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 <tp...@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
> tpathare@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.