Date: 2011-10-05 09:52:51 +0200
From: @bartscheers
To: SQL devs <>
Version: 11.5.3 (Aug2011-SP1) [obsolete]
CC: @njnes, @drstmane

Last updated: 2011-10-26 13:22:00 +0200

Comment 16371

Date: 2011-10-05 09:52:51 +0200
From: @bartscheers

User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.5; rv:7.0.1) Gecko/20100101 Firefox/7.0.1
Build Identifier:

No initial cast of integer to double, but implicit in re-execuion after double is involved in calculation. After client reconnection, re-execution does not cast integer.

Reproducible: Always

Steps to Reproduce:

create table t1 (i int, d double);
insert into t1 values (1,1.0),(2,2.0),(3,3.0),(4,4.0);
select * from t1;
select i,1/(i-1) from t1 where i > 1;
select i,1/(i-1.0) from t1 where i > 1;
select i,1/(i-1) from t1 where i > 1;
\q
reconnect
select i,1/(i-1) from t1 where i > 1;

Actual Results:

The same query gives different results:

sql> select i,1/(i-1) from t1 where i > 1;
+------+----------------------+
| i | sql_div_single_value |
+======+======================+
| 2 | 1 |
| 3 | 0 |
| 4 | 0 |
+------+----------------------+

sql> select i,1/(i-1) from t1 where i > 1;
+------+-----------------------+
| i | sql_div_single_value |
+======+=======================+
| 2 | 1.000 |
| 3 | 0.500 |
| 4 | 0.333 |
+------+-----------------------+

Expected Results:

Same query should produce identical results

Comment 16372

Date: 2011-10-05 12:34:56 +0200
From: @sjoerdmullender

A simpler way to reproduce the problem (without client restart):

create table t1 (i int, d double);
insert into t1 values (1,1.0),(2,2.0),(3,3.0),(4,4.0);
select i,1/(i-1) from t1 where i > 1;
select i,1/(i-1.0) from t1 where i > 1;
select i,1/(i-1) from t1 where i > 1;

The first and third selects are identical but give different results.

Comment 16373

Date: 2011-10-05 12:37:54 +0200
From: @drstmane

running both queries with TRACE (first Q1, then Q2, then Q1, again) suggest
that
query caching is the "problem":

Q1: select i,1/(i-1) from t1 where i > 1;
is cached in a MAL function.

Q2: select i,1/(i-1.0) from t1 where i > 1;
does not match the cached plan/MAL function (probably because double is not a
subtype of int); hence, it's translated and cache in a new plan/MAL function.

Then, calling Q1 again matched the second plan (probably because int is a
subtype of double), and thus calls the second MAL function with
double-semantics, rather than the more-specific first one with int-semantics
(I'm actually not sure, whether the first plan is indeed still there ...).

Comment 16374

Date: 2011-10-05 12:38:37 +0200
From: @drstmane

client restart empties the query cache.

Comment 16375

Date: 2011-10-05 12:40:48 +0200
From: @sjoerdmullender

Changeset c159291245ff made by Sjoerd Mullender sjoerd@acm.org in the MonetDB repo, refers to this bug.

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

Changeset description:

Added test for bug #2894.
I'm not sure whether the first or third select gives the right
answer.  I've chosen the third for now.

Comment 16376

Date: 2011-10-05 13:48:50 +0200
From: @drstmane

I'm not completely sure, but I would not be surprised if the standard SQl semantics requires to stil to integer arithmetic if only integer columns and literals are involved. If so, each execution of the first variant of the query (with integer literal) should result in the pure integer result while each execution of the second variant (with decimal/double literal) should return the respective result using decimal/double arithmetic. This could be achieved by "simply" making the cache matching more strict, i.e., not matching cached plans with super-type parameters if these have different semantics/arithmetic.

Comment 16402

Date: 2011-10-13 08:01:48 +0200
From: @drstmane

Changeset 44f8c57e32bf made by Stefan Manegold Stefan.Manegold@cwi.nl in the MonetDB repo, refers to this bug.

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

Changeset description:

fixed stable output of test for bug #2894
as it IMHO should be:
the query with integer constants yields an integer result,
the query with decimal constants yields a  decimal result;
the test still fails, i.e., the bug is still to be fixed.

Comment 16426

Date: 2011-10-14 12:52:45 +0200
From: @njnes

fixed by no longer casting int's to double in the query cache.

Comment 16431

Date: 2011-10-14 13:02:19 +0200
From: @njnes

Changeset 54c3db74703a 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=54c3db74703a

Changeset description:

fixed bug #2894
make sure we don't cast int's to double on queries.