Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

SQL statement too large #36592

Closed
amrm opened this issue Dec 13, 2018 · 8 comments
Closed

SQL statement too large #36592

amrm opened this issue Dec 13, 2018 · 8 comments
Assignees
@matriv
Labels
:Analytics/SQL SQL querying

Comments

@amrm
Copy link

amrm commented Dec 13, 2018 •
edited

Caused by: java.sql.SQLSyntaxErrorException: line 7:51: SQL statement too large; halt parsing to prevent memory errors (stopped at depth 200)

Elesticsearch version: 6.5.1
JVM version: 1.8
OS version: Linux

Method is JDBC driver and Sql query request:

select count(*) as count,mainBusinessProcessDefinitionKey.name as name from
ksa_request_index
where id in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217,218,219,220,221,222,223,224,225,226,227,228,229,230,231,232,233,234,235,236,237,238,239,240,241,242,243,244,245,246,247,248,249,250,251,252,253,254,255,256,257,258,259,260,261,262,263,264,265,266,267,268,269,270,271,272,273,274,275,276,277,278,279,280,281,282,283,284,285,286,287,288,289,290,291,292,293,294,295,296,297,298,299,300,301,302,303,304,305,306,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321,322,323,324,325,326,327,328,329,330,331,332,333,334,335,336,337,338,339,340,341,342,343,344,345,346,347,348,349,350,351,352,353,354,355,356,357,358,359,360,361,362,363,364,365,366,367,368,369,370,371,372,373,374,375,376,377,378,379,380,381,382,383,384,385,386,387,388,389,390,391,392,393,394,395,396,397,398,399,400)

group by mainBusinessProcessDefinitionKey.name;
@colings86 colings86 added the :Analytics/SQL SQL querying label Dec 13, 2018
@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search

@colings86
Copy link
Contributor

@amrm Could you provide some more details here? For example the SQL request that you sent and the method (CLI, REST API directly, ODBC driver, JDBC driver, etc.) that you used to send the request. Without this it is hard for us to know what to fix. Thanks

@amrm
Copy link
Author

amrm commented Dec 13, 2018

@colings86
I added more details about the issue

@matriv matriv self-assigned this Dec 13, 2018
@matriv
Copy link
Contributor

matriv commented Dec 14, 2018

@amrm Checking this, to see if we can relax the constraint for such cases.

matriv added a commit to matriv/elasticsearch that referenced this issue Dec 17, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN
e0023ce 
Fix grammar so that each element inside the list of values for IN
is a `valueExpression` and not a more generic `expression`. Also change
some names in the grammar so that they match the primary rule name
plus "Default". This helps so that the decrement of depth counts in
the Parser's `CircuitBreakerListener` works correctly.

For the list of values for `IN` don't count the
`PrimaryExpressionContext` as this is not visited on exit due to
the peculiarity in our gramamr with the `predicate` and `predicated`.

Fixes: elastic#36592
@matriv matriv mentioned this issue Dec 17, 2018
Merged
matriv added a commit to matriv/elasticsearch that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN
00bb58e 
Fix grammar so that each element inside the list of values for IN
is a `valueExpression` and not a more generic `expression`. Also change
some names in the grammar so that they match the primary rule name
plus "Default". This helps so that the decrement of depth counts in
the Parser's `CircuitBreakerListener` works correctly.

For the list of values for `IN` don't count the
`PrimaryExpressionContext` as this is not visited on exit due to
the peculiarity in our gramamr with the `predicate` and `predicated`.

Fixes: elastic#36592
matriv added a commit to matriv/elasticsearch that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN
73a1e1c 
Fix grammar so that each element inside the list of values for IN
is a `valueExpression` and not a more generic `expression`. Also change
some names in the grammar so that they match the primary rule name
plus "Default". This helps so that the decrement of depth counts in
the Parser's `CircuitBreakerListener` works correctly.

For the list of values for `IN` don't count the
`PrimaryExpressionContext` as this is not visited on exit due to
the peculiarity in our gramamr with the `predicate` and `predicated`.

Fixes: elastic#36592
matriv added a commit to matriv/elasticsearch that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN
3b4053d 
Fix grammar so that each element inside the list of values for IN
is a `valueExpression` and not a more generic `expression`. Also change
some names in the grammar so that they match the primary rule name
plus "Default". This helps so that the decrement of depth counts in
the Parser's `CircuitBreakerListener` works correctly.

For the list of values for `IN` don't count the
`PrimaryExpressionContext` as this is not visited on exit due to
the peculiarity in our gramamr with the `predicate` and `predicated`.

Fixes: elastic#36592
matriv added a commit that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN (#36724)
74502d7 
Fix grammar so that each element inside the list of values for IN
is a valueExpression and not a more generic expression. Introduce a
mapping for context names as some rules in the grammar are exited with
a different rule from the one they entered.This helps so that the decrement
of depth counts in the Parser's CircuitBreakerListener works correctly.

For the list of values for IN, don't count the
PrimaryExpressionContext as this is not visited on exitRule() due to
the peculiarity in our gramamr with the predicate and predicated.

Fixes: #36592
matriv added a commit that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN (#36724)
7d2e2cf 
Fix grammar so that each element inside the list of values for IN
is a valueExpression and not a more generic expression. Introduce a
mapping for context names as some rules in the grammar are exited with
a different rule from the one they entered.This helps so that the decrement
of depth counts in the Parser's CircuitBreakerListener works correctly.

For the list of values for IN, don't count the
PrimaryExpressionContext as this is not visited on exitRule() due to
the peculiarity in our gramamr with the predicate and predicated.

Fixes: #36592
@matriv
Copy link
Contributor

matriv commented Dec 18, 2018

@amrm Thank you for reporting this. The limit no longer kicks in for long lists of values for IN expressions.

matriv added a commit that referenced this issue Dec 18, 2018
@matriv
SQL: Fix wrong appliance of StackOverflow limit for IN (#36724)
7fa3154 
Fix grammar so that each element inside the list of values for IN
is a valueExpression and not a more generic expression. Introduce a
mapping for context names as some rules in the grammar are exited with
a different rule from the one they entered.This helps so that the decrement
of depth counts in the Parser's CircuitBreakerListener works correctly.

For the list of values for IN, don't count the
PrimaryExpressionContext as this is not visited on exitRule() due to
the peculiarity in our gramamr with the predicate and predicated.

Fixes: #36592
@amrm
Copy link
Author

amrm commented Dec 18, 2018

@matriv thanks for the quick response

@shubham-agarwal-ngi
Copy link

shubham-agarwal-ngi commented May 29, 2019 •
edited

I am facing the same issue
{
"type": "parsing_exception",
"reason": "line 1:5069: SQL statement too large; halt parsing to prevent memory errors (stopped at depth 200)"
}
Elesticsearch version: 6.6.0
JVM version: 1.8
OS : Window server

please suggest

@matriv
Copy link
Contributor

matriv commented May 31, 2019

@shubham-agarwal-ngi The issue has been addressed with #41835 so you'll need to upgrade to 6.8 or 7.1 versions to have it fixed. For reference could you please post here your query?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@matriv matriv

Labels
:Analytics/SQL SQL querying
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
5 participants
@colings86 @amrm @matriv @elasticmachine @shubham-agarwal-ngi