The total number of IN(...) list elements is too large

subhasish
Contributor

Hi,

Anybody faced this type of error in exasol. Is there any limit for IN(<elements>)

[0A000] Feature not supported: The total number of IN(...) list elements (200015) is too large (current IN list size: 83)

 

Thanks,

Subhasish

1 ACCEPTED SOLUTION

mwellbro
Xpert

Hi subhasish,

depends on how you implement "calling that calc field" - if you were to use the local keyword in the same 
query block then you´ll end up with the kind of multiplication you described ( because local as I understand it just "copy-pastes" the expression so you don´t have to ).

As an example:

 

-- let´s create a large IN-List
with base as (
select 
level idx from dual connect by level<=100000
)
select group_concat(idx order by idx) from base;

with base(a) as (select 2 from dual)
--, one_calc as (
select case when a in (/* paste the 100k entries here */ )
            then 1
            else 2 end as single_case
,local.single_case * 10 as t10
,local.single_case * 20 as t20
from base

 

Constructed like this you will hit an error in the form of:

[0A000] Feature not supported: The total number of IN(...) list elements (300000) is too large (current IN list size: 100000)

 

However, if you just build the "single_case" column and use it in a following CTE-part or the main query, you can use it "as much as you like" without encountering said error:

 

with base(a) as (select 2 from dual)
, one_calc as (
select case when a in (/* paste the 100k entries here */ )
            then 1
            else 2 end as single_case
--,local.single_case * 10 as t10
--,local.single_case * 20 as t20
from base
)
select 
 single_case * 1 
,single_case * 2 
,single_case * 3 
,single_case * 4 
,single_case * 5 
,single_case * 6 
,single_case * 7 
,single_case * 8 
,single_case * 9 
,single_case * 10 
,case when single_case = 1 then 1 else 0 end
,case when single_case = 1 then 1 else 0 end
,case when single_case = 1 then 1 else 0 end
from one_calc

 

 Could you share a rough "skeleton"-SQL to show how the query is structured where you end up hitting the IN-limit ?
Maybe things behave different when you throw an analytical function in the mix or some other influence I did not yet think of - hence the question how your query is actually structured 🙂

Cheers,
Malte

View solution in original post

9 REPLIES 9

exa-GeorgD
Team Exasol
Team Exasol

Hi Subhasish,
per default Exasol limits the number of IN list elements to 200000.
This avoids queries that run out of memory.
Note that since 6.0 the limit is not applied to a single IN list, but to the complete query (see also https://www.exasol.com/support/browse/EXASOL-2044 ).

Best wishes
Georg

subhasish
Contributor

Hi Georg,

Bit confused here, "per default Exasol limits the number of IN list elements to 200000" - I don't understand this statement. What does this 200000 mean ? is it bytes/KB or no of elements or length of the script/view? . I queried the exa_dba_audit_sql view and the length of the SQL is 37774 and for this view the in List elements also not more than 200. Does it mean that the all In list elements for the whole Database ? Please clarify.

mwellbro
Xpert

Hi subahasish,

I think Georg is referring to the elements of the IN-List itself, think of it like this:

with base(sql_text) as (
select 'select a,b,c, from dual where c in (1,2,3,4,5)' from dual
)
select length(
-- replace anything that is not a comma with blank so we can use length as count
regexp_replace(
  --isolate IN-List ( case sensitive and assuming we won´t find a comma as payload
  regexp_substr(sql_text,'in \([\w\W]*.\)')
,'[^,]',''))+1 as elements from base;


Cheers,
Malte

subhasish
Contributor

Hi mwellbro,

Thanks for the info, just want to understand one more thing - suppose I have created one calc field with case statement having 200 IN() list elements and calling that calc field in 10 different calc fields, so exasol would treat it 200 IN list elements or (200*10) = 2000 ?

I can't find any reason why it shows my in list elements is more than 200000

 

 

Thanks,

Subhasish

mwellbro
Xpert

Hi subhasish,

depends on how you implement "calling that calc field" - if you were to use the local keyword in the same 
query block then you´ll end up with the kind of multiplication you described ( because local as I understand it just "copy-pastes" the expression so you don´t have to ).

As an example:

 

-- let´s create a large IN-List
with base as (
select 
level idx from dual connect by level<=100000
)
select group_concat(idx order by idx) from base;

with base(a) as (select 2 from dual)
--, one_calc as (
select case when a in (/* paste the 100k entries here */ )
            then 1
            else 2 end as single_case
,local.single_case * 10 as t10
,local.single_case * 20 as t20
from base

 

Constructed like this you will hit an error in the form of:

[0A000] Feature not supported: The total number of IN(...) list elements (300000) is too large (current IN list size: 100000)

 

However, if you just build the "single_case" column and use it in a following CTE-part or the main query, you can use it "as much as you like" without encountering said error:

 

with base(a) as (select 2 from dual)
, one_calc as (
select case when a in (/* paste the 100k entries here */ )
            then 1
            else 2 end as single_case
--,local.single_case * 10 as t10
--,local.single_case * 20 as t20
from base
)
select 
 single_case * 1 
,single_case * 2 
,single_case * 3 
,single_case * 4 
,single_case * 5 
,single_case * 6 
,single_case * 7 
,single_case * 8 
,single_case * 9 
,single_case * 10 
,case when single_case = 1 then 1 else 0 end
,case when single_case = 1 then 1 else 0 end
,case when single_case = 1 then 1 else 0 end
from one_calc

 

 Could you share a rough "skeleton"-SQL to show how the query is structured where you end up hitting the IN-limit ?
Maybe things behave different when you throw an analytical function in the mix or some other influence I did not yet think of - hence the question how your query is actually structured 🙂

Cheers,
Malte

View solution in original post

subhasish
Contributor

Hi Malte,

Yes due to using lots of LOCAL it created the problem, I have changed the script. Now it's working fine.

 

Thanks a lot for your support.

mwellbro
Xpert

Hi again  @subhasish ,

were you able to get around the IN list limitation ? Just tried it with 200k elements which worked so you seem to have cut
it close here - tried again with 250k elements and got the same error you got ( note to self, and maybe note to @Charlie : trying a statement with that much text (1.6 MB .sql file ) in DBeaver is a pain 😉 ).

mwellbro_0-1628018706097.png

 

Charlie
Xpert

... trying a statement with that much text (1.6 MB .sql file ) in DBeaver is a pain 😉 ).

I know. The jsqlparser library used by dbeaver is not really fast

mwellbro
Xpert

Hi @subhasish ,

haven´t seen this one till now, but it probably makes sense that there is some kind of limit - would it be a solution to "dump" those 200015 elements into a table and join them ?
Might even be faster from what I remember about "unusually large IN-List parse times".

Cheers,
Malte