Date: 2012-08-21 13:32:14 +0200
From: @mlkersten
To: SQL devs <>
Version: 11.11.7 (Jul2012-SP1)
CC: @romulogoncalves, @njnes

Last updated: 2013-01-22 09:29:17 +0100

Comment 17633

Date: 2012-08-21 13:32:14 +0200
From: @mlkersten

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:13.0) Gecko/20100101 Firefox/13.0.1
Build Identifier:

Based on the Datacell/Tests/emili.sql script

This procedure ends up in the function table;

CREATE PROCEDURE datacell.enrich()
BEGIN
INSERT INTO datacell.sensors(ip, location, kind,value)
SELECT ip, substring(location,0,3), kind, value FROM datacell.istream;
IF TRUE
THEN
INSERT INTO datacell.area SELECT ip, substring(location,0,3) FROM datacell.istream;
END IF;
END;

This one does not, see IF-expression, and does not produce an error message.

CREATE PROCEDURE datacell.enrich()
BEGIN
INSERT INTO datacell.sensors(ip, location, kind,value)
SELECT ip, substring(location,0,3), kind, value FROM datacell.istream;
IF (SELECT count(*) FROM datacell.area ) = 0
THEN
INSERT INTO datacell.area SELECT ip, substring(location,0,3) FROM datacell.istream;
END IF;
END;

En this one works again:

CREATE PROCEDURE datacell.enrich()
BEGIN
DECLARE cnt INTEGER;
SET cnt = (SELECT count(*) FROM datacell.area ) ;
INSERT INTO datacell.sensors(ip, location, kind,value)
SELECT ip, substring(location,0,3), kind, value FROM datacell.istream;
IF cnt = 0
THEN
INSERT INTO datacell.area SELECT ip, substring(location,0,3) FROM datacell.istream;
END IF;
END;

Reproducible: Always

Comment 17688

Date: 2012-08-24 14:56:03 +0200
From: @sjoerdmullender

Jul2012-SP1 has been released.

Comment 18117

Date: 2012-11-27 15:25:21 +0100
From: @romulogoncalves

Test for this bug added to the SQL module under the directory BugTracker_2012.
The test is called:
type_resolution_error_in_SQL_procedural_code.Buf-3143

The stable out was added, however, the stable err is dependent of the ERROR message used to report that the procedure creation failed. For the moment a temporary message was added to trigger its fail when Mtest is run.

Comment 18213

Date: 2012-11-28 16:23:36 +0100
From: @njnes

Changeset 1ee4a27c1126 made by Niels Nes niels@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fixed bugs 2977 and 3143

return errors in psm when we don't expect select statements (solves 3143)

Give proper error when a function isn't found and don't allow converts from
date into second intervals. (solves 2977)

Comment 18381

Date: 2013-01-22 09:29:17 +0100
From: @sjoerdmullender

Oct2012-SP3 has been released.