Date: 2017-07-08 16:32:27 +0200
From: Sreejith Sharma <<sreejith.sharma>>
To: MonetDB5 devs <>
Version: 11.25.3 (Dec2016)
CC: @mlkersten, sreejith.sharma

Last updated: 2019-04-30 12:36:04 +0200

Comment 25437

Date: 2017-07-08 16:32:27 +0200
From: Sreejith Sharma <<sreejith.sharma>>

User-Agent: Mozilla/5.0 (Windows NT 6.1; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/51.0.2704.103 Safari/537.36
Build Identifier:

If any SQL size if more than 1.2MB it fails with "memory exhausted in" error in the server.

Reproducible: Always

Steps to Reproduce:

1.Execute any SQL with more than 1.2MB in size will fail in any scenario.
2.
3.

Actual Results:

SQL fails with "memory exhausted in" error

Expected Results:

SQL should have gone through and given the expected results. Any query less than 1.2MB only will go through at this moment.

Comment 25438

Date: 2017-07-08 16:43:25 +0200
From: Sreejith Sharma <<sreejith.sharma>>

Please let me know if there is any alternative or any limitations on the SQL query size.

Comment 25439

Date: 2017-07-08 20:41:08 +0200
From: @mlkersten

Assuming you mean a single SQL statement of more then 1.2M in size, this sounds like a run away front-end query generator. Could also be containing a very large cblob value. More details on the actual query you sent is in order to make any suggestion.

Furthermore, could also be a limitation in your windows environment.

Comment 25441

Date: 2017-07-09 04:29:24 +0200
From: Sreejith Sharma <<sreejith.sharma>>

(In reply to Martin Kersten from comment 2)

Assuming you mean a single SQL statement of more then 1.2M in size, this
sounds like a run away front-end query generator. Could also be containing a
very large cblob value. More details on the actual query you sent is in
order to make any suggestion.

Furthermore, could also be a limitation in your windows environment.

Thanks Martin.

The SQL is generated from front end and it's not a clob value. It's just a combination of OR/AND condition. Also, it's running on Linux platform with enough memory. Is there any limitation as such for a single SQL size?

Comment 25442

Date: 2017-07-09 08:19:37 +0200
From: Sreejith Sharma <<sreejith.sharma>>

Created attachment 557
Attached is the zipped version of SQL which exceed 2.5mb and fails in server.

As SQL generated by the application is more of user-driven selections, we don't really have much control on the size. So, I would expect the SQL might group even more than 3MB.

Attached file: sql_2500mb_plus.zip (application/x-zip-compressed, 160380 bytes)
Description: Attached is the zipped version of SQL which exceed 2.5mb and fails in server.

Comment 25443

Date: 2017-07-09 09:53:45 +0200
From: @mlkersten

It seems your zip file contains an error, because the predicate uses c_5 twice. This leads to an empty result. Perhaps the file should be regenerated with c_6

			AND ("TEST_MONETDB".c_5 = 26450)
			AND ("TEST_MONETDB".c_5 = 1109937)

Comment 25444

Date: 2017-07-09 16:50:23 +0200
From: Sreejith Sharma <<sreejith.sharma>>

(In reply to Martin Kersten from comment 5)

It seems your zip file contains an error, because the predicate uses c_5
twice. This leads to an empty result. Perhaps the file should be regenerated
with c_6

 		AND ("TEST_MONETDB".c_5 = 26450)
 		AND ("TEST_MONETDB".c_5 = 1109937)

Apologisze, my bad... however even having it fixed "AND ("TEST_MONETDB".c_6=1109937) across SQL it will fail with below error if the size exceeds 1.2MB.

memory exhausted in: "select sum(c1)
from "TEST_MONETDB"
where (
("TES"
syntax error, unexpected IDENT in: "c_6"

if it's within 1.2MB limit, I will get below results.

+--------+
| L3 |
+========+
| 488835 |
+--------+
1 tuple

Is there any limitation as such for SQL size?

Comment 25445

Date: 2017-07-09 17:17:09 +0200
From: Sreejith Sharma <<sreejith.sharma>>

(In reply to Sreejith Sharma from comment 6)

(In reply to Martin Kersten from comment 5)

It seems your zip file contains an error, because the predicate uses c_5
twice. This leads to an empty result. Perhaps the file should be regenerated
with c_6

   		AND ("TEST_MONETDB".c_5 = 26450)
   		AND ("TEST_MONETDB".c_5 = 1109937)

Apologize, my bad... however even having it fixed "AND
("TEST_MONETDB".c_6=1109937) across SQL it will fail with below error if the
size exceeds 2.5MB.

---

memory exhausted in: "select sum(c1)
from "TEST_MONETDB"
where (
("TES"
syntax error, unexpected IDENT in: "c_6"

if it's within 2.5MB limit, I will get below results.

+--------+
| L3 |
+========+
| 488835 |
+--------+
1 tuple

Also, one more correction as made the changes and raised the size it's > > >going through till 2.5.

Is there any limitation as such for SQL size?
Correcting the subject line ---

Comment 25446

Date: 2017-07-09 17:59:53 +0200
From: Sreejith Sharma <<sreejith.sharma>>

(In reply to Sreejith Sharma from comment 7)

(In reply to Sreejith Sharma from comment 6)

(In reply to Martin Kersten from comment 5)

It seems your zip file contains an error, because the predicate uses c_5
twice. This leads to an empty result. Perhaps the file should be regenerated
with c_6

 		AND ("TEST_MONETDB".c_5 = 26450)
 		AND ("TEST_MONETDB".c_5 = 1109937)

Apologize, my bad... however even having it fixed "AND
("TEST_MONETDB".c_6=1109937) across SQL it will fail with below error if the
size exceeds 2.5MB.

---

memory exhausted in: "select sum(c1)
from "TEST_MONETDB"
where (
("TES"
syntax error, unexpected IDENT in: "c_6"

if it's within 2.5MB limit, I will get below results.

+--------+
| L3 |
+========+
| 488835 |
+--------+
1 tuple

Also, one more correction as made the changes and raised the size it's > > >going through till 2.5.

Is there any limitation as such for SQL size?
Correcting the subject line ---

Also, is there any way where we can increase the memory limit? As I noticed the limit varies from 2.5 to 2.7.

Comment 25447

Date: 2017-07-10 12:53:42 +0200
From: @sjoerdmullender

The problem is that the BISON parser runs out of stack size. This stack is an array internal to the parser that is 10000 entries big. This means the problem has nothing to do with the size of the query per se. Instead it has to do with the complexity of the query.

Given that it the bison parser that complains, the only solution for being able to parse the given query would be to increase the stack size (YYMAXDEPTH) and recompile.

An alternative would be to radically change the query. I'm thinking of instead of having this enormous list of OR-separated clauses, do a JOIN with a table that you specify in-line.

Something like

FROM "TEST_MONETDB", (VALUES (271459, 1150308, ...), (...)) AS t(c_1, c_2, ...);
WHERE "TEST_MONETDB".c_1 = t.c_1 and "TEST_MONETDB".c_2 = t.c_t ...

Comment 25448

Date: 2017-07-11 10:35:19 +0200
From: Sreejith Sharma <<sreejith.sharma>>

(In reply to Sjoerd Mullender from comment 9)

The problem is that the BISON parser runs out of stack size. This stack is
an array internal to the parser that is 10000 entries big. This means the
problem has nothing to do with the size of the query per se. Instead it has
to do with the complexity of the query.

Given that it the bison parser that complains, the only solution for being
able to parse the given query would be to increase the stack size
(YYMAXDEPTH) and recompile.

An alternative would be to radically change the query. I'm thinking of
instead of having this enormous list of OR-separated clauses, do a JOIN with
a table that you specify in-line.

Something like

FROM "TEST_MONETDB", (VALUES (271459, 1150308, ...), (...)) AS t(c_1, c_2,
...);
WHERE "TEST_MONETDB".c_1 = t.c_1 and "TEST_MONETDB".c_2 = t.c_t ...

Thanks Sjoerd. Resizing of default parameter for YYMAXDEPTH from 10000 has worked! However, there's limitation of using conjunction/disjunction OR/AND clause while usage/parsing the SQL.

Comment 25449

Date: 2017-07-11 13:22:07 +0200
From: MonetDB Mercurial Repository <>

Changeset fff18316fff8 made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=fff18316fff8

Changeset description:

Use left recursion instead of right recursion in parser.
This fixes bug #6347, at least as far as parsing the query is
concerned.  It may still happen that there is a too deep recursion
when creating the query plan.