You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
COPY INTO category FROM 'category.csv' DELIMITERS '|' , '\n', '"';
COPY INTO all_dates FROM 'all_dates.csv' DELIMITERS '|' , '\n', '"';
COPY INTO book FROM 'book.csv' DELIMITERS '|' , '\n', '"';
COPY INTO fact FROM 'fact.csv' DELIMITERS '|' , '\n', '"';
COPY INTO location FROM 'location.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment1 FROM 'segment1.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment2 FROM 'segment2.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment3 FROM 'segment3.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment4 FROM 'segment4.csv' DELIMITERS '|' , '\n', '"';
SELECT loc.code
,cat.subcategory_code
,cat.category_code
,s1.code
,s2.code
,s3.code
,s4.code
,d.d_date
,d.month_code
FROM category cat
,book b
,location loc
,all_dates d
,segment4 s4
,segment3 s3
,segment2 s2
,segment1 s1
,fact f
WHERE b.code = f.book_code
AND d.d_date = f.d_period
AND s4.id = f.s4_id
AND s3.id = f.s3_id
AND s2.id = f.s2_id
AND s1.id = f.s1_id
AND loc.id = f.location_id
AND b.code = 'G2FIN'
AND cat.id = f.category_id
AND f.book_code = 'G2FIN'
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code >= 221 )
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code <= 290 )
AND ( s1.code = '03' OR cat.subcategory_code = 25 )
AND d.month_code BETWEEN '2016.01' AND '2016.10';
Actual Results:
GDK reported error.
GDKextendf: could not extend file.
!OS: No space left on device.
HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes.
Expected Results:
Query returns results
It seems there is something wrong with "OR" parsing/processing.
As soon as I remove "( s1.code = '03' OR cat.subcategory_code = 25 )", query momentary returns results.
Also depending on how many tables/columns are in SQL, sometimes it just takes 100G of hdd space/virtual memory and never returns result.
Still hapens only latest version.
I found one workaround: if I change order of tables listed in FROM section, then query executes successfully in less than 1 second.
However we can't control order of tables listed as SQL generated by BI.
Comment 27233
Date: 2019-08-19 09:19:27 +0200
From: winney.deng
We see the same error with SQL select query in version: v11.33.3, v11.31.13 and current development version.
Test environment:
Memory: 4G
Swap: 1.8G
Free disk: 31G
Steps to reproduce:
Load data into monetdb:
We loaded about 760k tuples into tableA and 2846k tuples into tableB from csv files by sql COPY command.
Do below SQL select command:
select fdd.*
from "tableA" fdd,
"tableB" rp
where fdd."me" = rp."me" and
( fdd."date_id" = '2019-06-26' or fdd."date_id" = '2019-06-27' or
fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27' or
fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27');
After doing the above select query, we found
Monetdb data file increase very fast (before query it is 3.7G, after query 34G)
When monetdb crashed, killed and restarted, the disk space was freed.
Swap was exhausted by monetdb
Below errors were seen in log (v11.33.13, dev version) or screen(v11.33.3):
GDK reported error: GDKextendf: could not extend file
OS: No space left on device
HEAPalloc: Insufficient space for HEAP of 9093120 bytes.
Below are logs from the current dev version (Aug. 2019)
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!OS: No space left on device
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR:MALException:mat.pack:HY001!Could not allocate space
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: GDKextendf: could not extend file
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!OS: No space left on device
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR:MALException:mat.pack:HY001!Could not allocate space
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: GDKextendf: could not extend file
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!OS: No space left on device
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: HEAPalloc: Insufficient space for HEAP of 587792384 bytes.
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR:MALException:mat.pack:HY001!Could not allocate space
In the main development branch of MonetDB we optimized IN statements as well OR clauses inside WHERE statements to perform a single semi-join instead of multiple theta-select statements. The performance of these queries should improve in the next feature release of MonetDB.
Fixes for bug #6134, ie run rel_push_select_down optimizer after rel_join_order, so the later gets more optimizing options to push down joins.
Also some identitation fixes and limit the depth of rel_find_joins to 20 relations bellow in the relation tree.
The text was updated successfully, but these errors were encountered:
Date: 2016-12-01 13:30:30 +0100
From: Gatis Ozolins <<g.ozolins>>
To: GDK devs <>
Version: 11.27.5 (Jul2017-SP1)
CC: ajdamico, @hannesmuehleisen, @PedroTadim, winney.deng
Last updated: 2019-11-28 10:00:06 +0100
Comment 24740
Date: 2016-12-01 13:30:30 +0100
From: Gatis Ozolins <<g.ozolins>>
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:50.0) Gecko/20100101 Firefox/50.0
Build Identifier:
Query results in: HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes.
Reproducible: Always
Steps to Reproduce:
table_data.tar.gz
CREATE TABLE all_dates (d_date date, month_code varchar(7));
CREATE TABLE book (code varchar(15));
CREATE TABLE category (id bigint, category_code varchar(30), subcategory_code varchar(30));
CREATE TABLE fact (s1_id varchar(25), s2_id varchar(25), s3_id varchar(25), s4_id varchar(25), book_code varchar(15), category_id bigint, location_id bigint, d_period date);
CREATE TABLE location (id bigint, code varchar(30));
CREATE TABLE segment1 (id varchar(150), code varchar(150));
CREATE TABLE segment2 (id varchar(150), code varchar(150));
CREATE TABLE segment3 (id varchar(150), code varchar(150));
CREATE TABLE segment4 (id varchar(150), code varchar(150));
COPY INTO category FROM 'category.csv' DELIMITERS '|' , '\n', '"';
COPY INTO all_dates FROM 'all_dates.csv' DELIMITERS '|' , '\n', '"';
COPY INTO book FROM 'book.csv' DELIMITERS '|' , '\n', '"';
COPY INTO fact FROM 'fact.csv' DELIMITERS '|' , '\n', '"';
COPY INTO location FROM 'location.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment1 FROM 'segment1.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment2 FROM 'segment2.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment3 FROM 'segment3.csv' DELIMITERS '|' , '\n', '"';
COPY INTO segment4 FROM 'segment4.csv' DELIMITERS '|' , '\n', '"';
SELECT loc.code
,cat.subcategory_code
,cat.category_code
,s1.code
,s2.code
,s3.code
,s4.code
,d.d_date
,d.month_code
FROM category cat
,book b
,location loc
,all_dates d
,segment4 s4
,segment3 s3
,segment2 s2
,segment1 s1
,fact f
WHERE b.code = f.book_code
AND d.d_date = f.d_period
AND s4.id = f.s4_id
AND s3.id = f.s3_id
AND s2.id = f.s2_id
AND s1.id = f.s1_id
AND loc.id = f.location_id
AND b.code = 'G2FIN'
AND cat.id = f.category_id
AND f.book_code = 'G2FIN'
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code >= 221 )
AND ( cat.subcategory_code = 200 OR cat.subcategory_code = 301 OR cat.subcategory_code <= 290 )
AND ( s1.code = '03' OR cat.subcategory_code = 25 )
AND d.month_code BETWEEN '2016.01' AND '2016.10';
Actual Results:
GDK reported error.
GDKextendf: could not extend file.
!OS: No space left on device.
HEAPalloc: Insufficient space for HEAP of 1168033427456 bytes.
Expected Results:
Query returns results
It seems there is something wrong with "OR" parsing/processing.
As soon as I remove "( s1.code = '03' OR cat.subcategory_code = 25 )", query momentary returns results.
Also depending on how many tables/columns are in SQL, sometimes it just takes 100G of hdd space/virtual memory and never returns result.
Comment 24741
Date: 2016-12-01 13:31:10 +0100
From: Gatis Ozolins <<g.ozolins>>
Created attachment 520
Data to reproduce this issue
Comment 24842
Date: 2016-12-21 16:03:54 +0100
From: Gatis Ozolins <<g.ozolins>>
Tested with 11.25.3: still produces same error
Comment 24875
Date: 2017-01-10 15:26:59 +0100
From: MonetDB Mercurial Repository <>
Changeset d21bef4f8fdd made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=d21bef4f8fdd
Changeset description:
Comment 24876
Date: 2017-01-10 15:36:39 +0100
From: MonetDB Mercurial Repository <>
Changeset f0eaa26022fe made by Hannes Muehleisen hannes@cwi.nl in the MonetDB repo, refers to this bug.
For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=f0eaa26022fe
Changeset description:
Comment 24877
Date: 2017-01-10 15:41:49 +0100
From: @hannesmuehleisen
From looking at plan, it seems like the joins deteriorate into cross products when adding that single condition.
Comment 24878
Date: 2017-01-10 15:55:08 +0100
From: @hannesmuehleisen
This also happens in default
Comment 25681
Date: 2017-10-06 18:05:10 +0200
From: Gatis Ozolins <<g.ozolins>>
Still hapens only latest version.
I found one workaround: if I change order of tables listed in FROM section, then query executes successfully in less than 1 second.
However we can't control order of tables listed as SQL generated by BI.
Comment 27233
Date: 2019-08-19 09:19:27 +0200
From: winney.deng
We see the same error with SQL select query in version: v11.33.3, v11.31.13 and current development version.
Test environment:
Memory: 4G
Swap: 1.8G
Free disk: 31G
Steps to reproduce:
Load data into monetdb:
We loaded about 760k tuples into tableA and 2846k tuples into tableB from csv files by sql COPY command.
Do below SQL select command:
select fdd.*
from "tableA" fdd,
"tableB" rp
where fdd."me" = rp."me" and
( fdd."date_id" = '2019-06-26' or fdd."date_id" = '2019-06-27' or
fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27' or
fdd."date_id" = '2019-06-28' or fdd."date_id" = '2019-06-27');
After doing the above select query, we found
Monetdb data file increase very fast (before query it is 3.7G, after query 34G)
When monetdb crashed, killed and restarted, the disk space was freed.
Swap was exhausted by monetdb
Below errors were seen in log (v11.33.13, dev version) or screen(v11.33.3):
GDK reported error: GDKextendf: could not extend file
OS: No space left on device
HEAPalloc: Insufficient space for HEAP of 9093120 bytes.
Below are logs from the current dev version (Aug. 2019)
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!OS: No space left on device
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
2019-08-19 06:26:15 ERR db[214]: DFLOWworker5:!ERROR:MALException:mat.pack:HY001!Could not allocate space
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: GDKextendf: could not extend file
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!OS: No space left on device
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR: HEAPalloc: Insufficient space for HEAP of 587759616 bytes.
2019-08-19 06:26:16 ERR db[214]: DFLOWworker16:!ERROR:MALException:mat.pack:HY001!Could not allocate space
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: GDKextendf: could not extend file
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!OS: No space left on device
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR: HEAPalloc: Insufficient space for HEAP of 587792384 bytes.
2019-08-19 06:26:16 ERR db[214]: DFLOWworker12:!ERROR:MALException:mat.pack:HY001!Could not allocate space
Comment 27254
Date: 2019-08-23 12:10:16 +0200
From: @PedroTadim
In the main development branch of MonetDB we optimized IN statements as well OR clauses inside WHERE statements to perform a single semi-join instead of multiple theta-select statements. The performance of these queries should improve in the next feature release of MonetDB.
Comment 27411
Date: 2019-11-15 13:47:05 +0100
From: MonetDB Mercurial Repository <>
Changeset 3cf4189df085 made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=3cf4189df085
Changeset description:
The text was updated successfully, but these errors were encountered: