Data type changes when arithmetic in prepared statements.

[monetdb-team]

Date: 2013-06-05 02:53:19 +0200
From: Ben Reilly <>
To: SQL devs <>
Version: 11.15.7 (Feb2013-SP2)
CC: ben, @njnes

Last updated: 2013-07-03 08:48:00 +0200

Comment 18778

Date: 2013-06-05 02:53:19 +0200
From: Ben Reilly <>

User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/27.0.1453.94 Safari/537.36
Build Identifier:

When using a prepared statement that includes arithmetic on an 'int' parameter (e.g. "? - 20"), with operands that are in the range [-127,+127], the prepared statement converts the expression to the type 'tinyint', and limits the parameter accordingly to the 'tinyint' type.

So, for instance, "? - 20" will only accept 8-bit integers, but "? - 200" will accept any (16? 32? 64bit?) value.

Reproducible: Always

Steps to Reproduce:

sql> CREATE TABLE ints ( val int );
sql> PREPARE INSERT INTO ints VALUES ( ? - 20 );
sql> EXEC 2(1000);

Actual Results:

Error message:
EXEC: wrong type for argument 1 of prepared statement: smallint, expected tinyint

Comment 18804

Date: 2013-06-09 20:14:42 +0200
From: @njnes

improved type handling in case of numerics.

Comment 18805

Date: 2013-06-09 20:20:41 +0200
From: MonetDB Mercurial Repository <>

Changeset 834adef227d0 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=834adef227d0

Changeset description:

fixed bug #3297. More general types are used in case of numerics.