Date: 2014-10-04 07:49:07 +0200
From: @mlkersten
To: MonetDB5 devs <>
Version: 11.21.19 (Jul2015-SP4)
CC: cszqfeng

Last updated: 2016-12-21 13:08:34 +0100

Comment 20241

Date: 2014-10-04 07:49:07 +0200
From: @mlkersten

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier:

The EXPLAIN over SQL queries does not always keep the fixed types from the sql.bind(). Example, TPCH query 2, using the sequential_pipe.

Reproducible: Always

Comment 20242

Date: 2014-10-04 09:19:51 +0200
From: @mlkersten

Fixed by setting the type in sql.gencode()

Comment 20333

Date: 2014-10-31 14:13:41 +0100
From: @sjoerdmullender

Oct2014 has been released.

Comment 22015

Date: 2016-04-06 11:26:47 +0200
From: Neo <>

This bug is not entirely fixed in the newest release.

CREATE TABLE "sys"."book" (
"id" INTEGER,
"title" VARCHAR(100)
);

EXPLAIN select * from book;

function user.s1_1{autoCommit=true}():void;
X_48:void := querylog.define("explain select * from book;","default_pipe",31);
... omit
X_2 := sql.mvc();
X_3:bat[:oid,:oid] := sql.tid(X_2,"sys","book");
X_6:bat[:oid,:int] := sql.bind(X_2,"sys","book","id",0);
(X_9,r1_9) := sql.bind(X_2,"sys","book","id",2); Needs fix
X_12:bat[:oid,:int] := sql.bind(X_2,"sys","book","id",1);
X_14 := sql.delta(X_6,X_9,r1_9,X_12);
X_15 := algebra.leftfetchjoin(X_3,X_14);
X_16:bat[:oid,:str] := sql.bind(X_2,"sys","book","title",0);
(X_18,r1_18) := sql.bind(X_2,"sys","book","title",2); Needs fix
X_20:bat[:oid,:str] := sql.bind(X_2,"sys","book","title",1);
X_21 := sql.delta(X_16,X_18,r1_18,X_20);
X_22 := algebra.leftfetchjoin(X_3,X_21);
sql.resultSet(X_40,X_41,X_42,X_44,X_46,X_15,X_22);
end user.s1_1;

Executing the above MAL instructions yields:

ERROR = !TypeException:user.s1_1[22]:'sql.delta' undefined in: X_14:any := sql.delta(X_6:bat[:oid,:int],X_9:bat[:oid,:oid],r1_9:bat[:oid,:any],X_12:bat[:oid,:int]);

Problem can be fixed manually by adding types to the return values of sql.bind().

From:
(X_9,r1_9) := sql.bind(X_2,"sys","book","id",2);

To:
(X_9:bat[:oid,:oid],r1_9:bat[:oid,:int]) := sql.bind(X_2,"sys","book","id",2);

Comment 22029

Date: 2016-04-12 05:58:56 +0200
From: Neo <>

Follow-up:

The below hack temporarily solved my problem:

--- a/monetdb5/mal/mal_listing.c
+++ b/monetdb5/mal/mal_listing.c
@@ -301,7 +301,7 @@ instruction2str(MalBlkPtr mb, MalStkPtr stk, InstrPtr p, int flg)
*t++ = '(';

             for (i = 0; i < p->retc; i++) {

•                   arg= renderTerm(mb, stk, p, i, flg);
  

•                   arg= renderTerm(mb, stk, p, i, flg | LIST_MAL_TYPE);    // Force print MAL type in assignment left hand side
                    snprintf(t,(len-(t-base)), "%s", arg);
                    GDKfree(arg);
                    advance(t,base,len);
  

Comment 22252

Date: 2016-07-25 20:40:54 +0200
From: @mlkersten

Indeed. The return type of all bind operations should be fixed, because it comes from the SQL catalog and may not be changed.
The solution is too deep in the code base. A patch at the SQL code generation is needed.

The output API changes, so it should go to the next release.

Comment 22258

Date: 2016-07-25 21:39:55 +0200
From: MonetDB Mercurial Repository <>

Changeset a40f551077dc made by Martin Kersten mk@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

Make type explicit for bind operation
This fixes bug #3597
All sql.bind operations should make the type fixed.