Date: 2015-04-08 05:14:47 +0200
From: John <>
To: SQL devs <>
Version: 11.19.9 (Oct2014-SP2)
CC: martin.van.dinther, @njnes

Last updated: 2015-05-07 12:37:40 +0200

Comment 20762

Date: 2015-04-08 05:14:47 +0200
From: John <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_2) AppleWebKit/600.4.10 (KHTML, like Gecko) Version/8.0.4 Safari/600.4.10
Build Identifier:

DB crash every time I execute statement:

WITH cte(qtime, open, close, sdate, id, rnk) as (
select qtime, sdate, id, row_number() over (partition by sdate order by qtime asc) as rnk
from marketdata.quotes where sym='SPY'
) select * from cte where rnk=1;

Reproducible: Always

Steps to Reproduce:

1. Create New DB, Create Table, Load data ~3M records
2. Connect over jdbc to monetdb server
3. Execute statement specified above.

Actual Results:

368925.504436] mserver5[26946]: segfault at 0 ip 00007f3d0e1ab808 sp 00007f3cefbfcad0 error 4 in lib_sql.so[7f3d0e180000+16c000]

Expected Results:

No crash

System: CentOS Linux release 7.1.1503 (Core) 32 GB,
DB -> SSD Disk, mount options: rw,noexec,nodev,noatime,nodiratime,nobarrier

Bug is repeatable with package installed from yum repository as well as compiled manually.

Comment 20764

Date: 2015-04-08 19:19:03 +0200
From: @njnes

I'm missing the ddl statement to create the table.

Comment 20765

Date: 2015-04-08 22:02:54 +0200
From: John <>

Sorry for missing this information,

Here you go:

CREATE TABLE "marketdata"."quotes" (

"id" INTEGER NOT NULL DEFAULT next value for "marketdata"."seq_6391",
"qtime" TIMESTAMP WITH TIME ZONE NOT NULL,
"sdate" TIMESTAMP WITH TIME ZONE,
"sym" VARCHAR(10) NOT NULL,
"cur" VARCHAR(10) NOT NULL,
"open" DOUBLE NOT NULL,
"high" DOUBLE NOT NULL,
"low" DOUBLE NOT NULL,
"close" DOUBLE NOT NULL,
"volume" DOUBLE,
CONSTRAINT "quotes_id_pkey" PRIMARY KEY ("id")
);

if any additional info required, just let me know.

Comment 20766

Date: 2015-04-09 14:01:22 +0200
From: Martin van Dinther <<martin.van.dinther>>

For a working DDL, you will also need to create a sequence first, e.g.:
CREATE SEQUENCE "seq_6391" AS INTEGER ;

Next the:
CREATE TABLE "quotes" (
"id" INTEGER NOT NULL DEFAULT next value for "seq_6391",
"qtime" TIMESTAMP WITH TIME ZONE NOT NULL,
"sdate" TIMESTAMP WITH TIME ZONE,
"sym" VARCHAR(10) NOT NULL,
"cur" VARCHAR(10) NOT NULL,
"open" DOUBLE NOT NULL,
"high" DOUBLE NOT NULL,
"low" DOUBLE NOT NULL,
"close" DOUBLE NOT NULL,
"volume" DOUBLE,
CONSTRAINT "quotes_id_pkey" PRIMARY KEY ("id")
);

It appears that the query:

WITH cte(qtime, open, close, sdate, id, rnk) as (
select qtime, sdate, id, row_number() over (partition by sdate order by qtime asc) as rnk
from marketdata.quotes where sym='SPY'
) select * from cte where rnk=1;

is not well defined. The select has less columns (4) than the WITH definition (6).
The corrected version would be:

WITH cte(qtime, open, close, sdate, id, rnk) as (
select qtime, open, close, sdate, id, row_number() over (partition by sdate order by qtime asc) as rnk
from marketdata.quotes where sym='SPY'
) select * from cte where rnk=1;

Alternatively you can do:
CREATE VIEW cte (qtime, open, close, sdate, id, rnk) as
select qtime, open, close, sdate, id, row_number() over (partition by sdate order by qtime asc) as rnk
from "quotes" where sym='SPY';

and next use the simple query:
select * from cte where rnk=1;

I tested above on 11.19.9 SP2 and on 11.20.0 (dev version) and the reported problem did not occurr, so it is NOT reproducable without data.

John, could you please provide data (preferably a subset) which allows us to reproduce the problem.

Also try yourself the version with the VIEW instead of the WITH and let us know if that makes a difference.

Comment 20768

Date: 2015-04-09 16:29:20 +0200
From: John <>

Created attachment 331
Data Subset for bug reproduction

Attached file: SPY.csvaa.zip (application/zip, 946778 bytes)
Description: Data Subset for bug reproduction

Comment 20769

Date: 2015-04-09 17:03:29 +0200
From: John <>

Hi Martin,

I have attached the DataSet, I've checked it and the bug is reproducible on this specific file. So for bug debuging purposes it should work.

Thank you for pointing out on the problem with sql! I've tried your edition and it works fine and DB did not crash.

It seems the bug is related exactly to the not valid SQL.

Even though the criticality of this bug is much lower now for my specific case I am available to continue debug and provide any additional information.

Thanks,
John

Comment 20770

Date: 2015-04-10 09:45:46 +0200
From: MonetDB Mercurial Repository <>

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

For complete details, see http//devmonetdborg/hg/MonetDB?cmd=changeset;node=855e0d266900

Changeset description:

Check number of columns in WITH clause with those in subquery.
This fixes bug #3697.

Comment 20771

Date: 2015-04-10 09:47:27 +0200
From: @sjoerdmullender

The bug was that there was no proper check that the number of columns specified in the WITH clause was equal to the number of columns produced in the query. The fix doesn't make the query run but it does make that the server doesn't crash.

Comment 20779

Date: 2015-04-10 16:23:22 +0200
From: John <>

Hi,

I've checked change set and see:
if (!create) {

•  	if (column_spec) {
  

•  		dnode *n = column_spec->h;
  

•  		node *m = sq->exps->h;
  

•  		for (; n && m; n = n->next, m = m->next)
  

•  			;
  

•  		if (n || m) {
  

•  			sql_error(sql, 01, "21S02!WITH CLAUSE: number of columns does not match");
  

•  			rel_destroy(sq);
  

•  			return NULL;
  

•  		}
  

•  	}
  

Is it intention was:

•  		for (; n && m; n = n->next, m = m->next)
  

•  		//Need to be removed -->	; 
  

•  		if (n || m) { ...
  

Just checking ...

Thanks,
John

Comment 20780

Date: 2015-04-10 17:03:43 +0200
From: @sjoerdmullender

No, the intention is what's in the code.
The for loop just loops until the end of either list, the if then checks whether that was the end of both lists.