Function AVG fails during runtime in a procedure

[monetdb-team]

Date: 2013-07-30 15:56:32 +0200
From: Radovan Bičiště <<radovan.biciste>>
To: SQL devs <>
Version: 11.15.11 (Feb2013-SP3)
CC: @njnes

Last updated: 2013-09-27 13:47:14 +0200

Comment 18943

Date: 2013-07-30 15:56:32 +0200
From: Radovan Bičiště <<radovan.biciste>>

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

We use SQLWorkbench as a client but the same problem happens using mclient.
This SQL runs fine as standalone:
INSERT INTO crmkartastitek (
cislo_karty,
stitek_id,
stitek_nazev,
stitek_upresneni,
stitek_hodnota_decimal
)
select cislo_karty,170,'KEO','',round(avg(obrat),2)
from
(select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat
from crmkartapohyby ckp
join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty
where ckp.pohyb in ('VR', 'VV')
and ck.aktivita between 1 and 3 -- omezení na LM a L3M
group by ckp.cislo_karty, ckp.id_expedice
) as exp_pripady
group by cislo_karty;

But when wrapped in procedure raises an error as follows:
CREATE PROCEDURE crm_segm_keo_rl(p_interval INT, p_datum timestamp)
BEGIN
-- hlavička procedury
DECLARE v_proc VARCHAR(250);
SET v_proc = 'crm_segm_kpn_rl';
-- nastav id pravidla
DECLARE v_stitek_id int;
DECLARE v_stitek_nazev,v_stitek_upresneni VARCHAR(200);
-- nastav štítek
SET v_stitek_id = 170;
SELECT nazev,upresneni INTO v_stitek_nazev,v_stitek_upresneni FROM crmregstitek WHERE stitek_id = v_stitek_id;
-- proveď výpočet pouze pokud spouštím měsíční přepočet
IF p_interval = crm_segm_vrat_spusteni(v_stitek_id) THEN
INSERT INTO crmkartastitek (
cislo_karty,
stitek_id,
stitek_nazev,
stitek_upresneni,
stitek_hodnota_decimal
)
select cislo_karty,v_stitek_id,v_stitek_nazev,v_stitek_upresneni,round(avg(obrat),2)
from
(select ckp.cislo_karty, ckp.id_expedice, sum(ckp.pcena_sd) as obrat
from crmkartapohyby ckp
join crmkartaaktivita ck on ckp.cislo_karty = ck.cislo_karty
where ckp.pohyb in ('VR', 'VV')
and ck.aktivita between 1 and 3 -- omezení na LM a L3M
group by ckp.cislo_karty, ckp.id_expedice
) as exp_pripady
group by cislo_karty;
END IF;
END;
Procedure is created fine.

call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00');
Causes:
An error occurred when executing the SQL command:
call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00')

TypeException:user.crm_segm_keo_rl[362]:'aggr.subavg' undefined in: _546:bat[:any,:dbl] := aggr.subavg(_543:bat[:oid,:dbl], _209:bat[:oid,:oid], r1_209:bat[:oid,:oid], _535:bit) [SQL State=22000]
Next: TypeException:user.s4_24[5]:'user.crm_segm_keo_rl' undefined in: _9:void := user.crm_segm_keo_rl(_5:int, _7:timestamp) [SQL State=22000]
Next: program contains errors [SQL State=39000]

Reproducible: Always

Steps to Reproduce:

1. create procedure with SQL that uses AVG function.
2. run procedure.

Actual Results:

Received the error:
An error occurred when executing the SQL command:
call crm_segm_keo_rl(3,timestamp '2013-07-01 00:00:00')

TypeException:user.crm_segm_keo_rl[362]:'aggr.subavg' undefined in: _546:bat[:any,:dbl] := aggr.subavg(_543:bat[:oid,:dbl], _209:bat[:oid,:oid], r1_209:bat[:oid,:oid], _535:bit) [SQL State=22000]
Next: TypeException:user.s4_24[5]:'user.crm_segm_keo_rl' undefined in: _9:void := user.crm_segm_keo_rl(_5:int, _7:timestamp) [SQL State=22000]
Next: program contains errors [SQL State=39000]

Expected Results:

successful end of the procedure run

Comment 18944

Date: 2013-07-30 16:07:13 +0200
From: Radovan Bičiště <<radovan.biciste>>

Created attachment 213
simple test case

Simplified test case.

Attached file: test_avg.sql (text/x-sql, 252 bytes)
Description: simple test case

Comment 18954

Date: 2013-07-31 10:01:43 +0200
From: @njnes

fixed, the correct interface requires another bit (abort on error).

Comment 18957

Date: 2013-07-31 10:49:29 +0200
From: MonetDB Mercurial Repository <>

Changeset 6b9d21862a6a 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=6b9d21862a6a

Changeset description:

fixed bug #3329, ie handle abort on error bit on subavg call
fixed bug #3319, return error on unique/primary/foreign constraints on declared tables
fixed bug #3318, correctly find the declared tables

Comment 18962

Date: 2013-07-31 12:52:49 +0200
From: Radovan Bičiště <<radovan.biciste>>

Simple workaround is to use combination of SUM and COUNT.