SQL module truncates DECIMALS when passing integers to MAL module

[monetdb-team]

Date: 2012-01-31 15:05:08 +0100
From: sellam
To: SQL devs <>
Version: -- development
CC: @njnes

Last updated: 2012-05-25 12:58:49 +0200

Comment 16854

Date: 2012-01-31 15:05:08 +0100
From: sellam

User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/535.11 (KHTML, like Gecko) Chrome/17.0.963.44 Safari/535.11
Build Identifier: Dec2011 - changeset 42883:baf7d5fc1f6f (Thu Jan 19)

Consider the following UDF:

create function truncode(lon decimal(9,6), lat decimal(9,6))
returns int external name truncatenate.encode;

The SQL module should pass the argument to MAL as integers. That works fine except when an argument has more than 2 digits after the comma

EXAMPLES:
sql>explain select truncode(12,12); ALL GOOD
+-------------------------------------------------------------------------+
| mal |
+=========================================================================+
| function user.s8_1{autoCommit=true}():void; |
| X_4 := truncatenate.encode(12000000:int,12000000:int); |
| sql.exportValue(1,".","truncode_single_value","int",32,0,6,X_4,""); |
| end s8_1; |
+-------------------------------------------------------------------------+
4 tuples (0.770ms)
sql>explain select truncode(12.0,12);* ALL GOOD
+-------------------------------------------------------------------------+
| mal |
+=========================================================================+
| function user.s9_1{autoCommit=true}():void; |
| X_5 := truncatenate.encode(12000000:int,12000000:int); |
| sql.exportValue(1,".","truncode_single_value","int",32,0,6,X_5,""); |
| end s9_1; |
+-------------------------------------------------------------------------+
4 tuples (0.680ms)
sql>explain select truncode(12.00,12); ******* NOT GOOD !
+-------------------------------------------------------------------------+
| mal |
+=========================================================================+
| function user.s10_1{autoCommit=true}():void; |
| X_4 := truncatenate.encode(1200:int,1200:int); |<-!!
| sql.exportValue(1,".","truncode_single_value","int",32,2,6,X_4,""); |
| end s10_1; |
+-------------------------------------------------------------------------+
4 tuples (0.626ms)

• SQL:
  create function truncode(lon decimal(9,6), lat decimal(9,6))
  returns int external name truncatenate.encode;

• MAL:
  module truncatenate;
  command encode(lon:int, lat:int):int
  address Truncatenate
  comment "Pastes the two leftmost bytes of two integers";

• C:
  str Truncatenate(int *res, int *lon, int *lat) {
  *res = (*lon & 0xFFFF0000) | (*lat >> 16);
  return MAL_SUCCEED;

Reproducible: Always

Comment 16855

Date: 2012-01-31 15:25:18 +0100
From: sellam

There seems to be a workaround: putting arguments in variables instead of putting the function:

• WORKS:

sql>declare x decimal(9,6);
sql>set x=12.00;
sql>select truncode(x,0);
+-----------------------+
| truncode_single_value |
+=======================+
| 11993088 |
+-----------------------+
1 tuple (0.571ms)

• DOES NOT WORK:

sql>select truncode(12.00,0);
+-----------------------+
| truncode_single_value |
+=======================+
| 0 |
+-----------------------+
1 tuple (0.556ms)

• EXPLAINS
  sql>explain select truncode(12.00,0);
  +-------------------------------------------------------------------------+
  | mal |
  +=========================================================================+
  | function user.s11_1{autoCommit=true}():void; |
  | X_4 := truncatenate.encode(1200:int,0:int); |->!!!!! PROBLEM
  | sql.exportValue(1,".","truncode_single_value","int",32,2,6,X_4,""); |
  | end s11_1; |
  +-------------------------------------------------------------------------+
  4 tuples (0.677ms)
  sql>explain select truncode(x,0);
  +-------------------------------------------------------------------------+
  | mal |
  +=========================================================================+
  | function user.s12_1{autoCommit=true}():void; |
  | X_2 := sql.mvc(); |
  | X_5:int := sql.getVariable(X_2,"x"); |
  | X_7 := truncatenate.encode(X_5,0:int); |
  | sql.exportValue(1,".","truncode_single_value","int",32,6,6,X_7,""); |
  | end s12_1; |
  +-------------------------------------------------------------------------+
  6 tuples (0.724ms)

Comment 17222

Date: 2012-05-07 08:34:59 +0200
From: @njnes

Changeset a8c386663dfe 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=a8c386663dfe

Changeset description:

fixed bug in handeling decimals in user defined functions (bug #2992)

Comment 17223

Date: 2012-05-07 08:36:35 +0200
From: @njnes

fixed, user defined functions need matching scales with the argument types. No need for the dynamic scales as needed for internal functions such as 'sql_sub' etc.