column type conversion sticks to subsequent queries

[monetdb-team]

Date: 2015-07-08 15:59:59 +0200
From: Dennis Lorson <>
To: SQL devs <>
Version: 11.19.15 (Oct2014-SP4)
CC: @njnes

Last updated: 2015-08-28 13:43:07 +0200

Comment 20970

Date: 2015-07-08 15:59:59 +0200
From: Dennis Lorson <>

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_10_4) AppleWebKit/600.7.12 (KHTML, like Gecko) Version/8.0.7 Safari/600.7.12
Build Identifier:

The query planner will keep including a type conversion (e.g. batcalc.lng) on a column, even if not required, after a previous query has been performed where the type conversion was appropriate.

Reproducible: Always

Steps to Reproduce:

1. create table test(value int);
2. insert into test values (1), (2), (3);
3. select * from test where value = 12345678900; value > INT_MAX
4. trace select * from test where value = 1; value < INT_MAX

Actual Results:

The trace includes an (expensive) <batcalc.lng> instruction

Expected Results:

The trace does not include a <batcalc.lng> instruction, since the last query did not provide a LONG-range literal in its condition.

Niels Nes indicated this has to do with the query cache.

While this issue does not affect the correctness of the result set, it has a serious impact on performance, since all later queries will be orders of magnitude slower than needed on large tables.

Comment 21090

Date: 2015-08-01 21:56:06 +0200
From: @njnes

added an optimization which removes the case (changes the compare expression also if needed). The optimizer also handles cases for <, <=, >=, > and <>.

Comment 21091

Date: 2015-08-01 21:59:07 +0200
From: MonetDB Mercurial Repository <>

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

Changeset description:

fixed bug #3756, ie incase of up casts and comparisons with a simple atom (literal or stacked)
we convert to < max (of smaller type) of > max (of smaller type)

Comment 21231

Date: 2015-08-28 13:43:07 +0200
From: @sjoerdmullender

Jul2015 has been released.