Wrong coercion priority

[monetdb-team]

Date: 2016-03-03 10:39:38 +0100
From: @mlkersten
To: SQL devs <>
Version: 11.17.13 (Jan2014-SP1)
CC: @njnes, @drstmane

Last updated: 2020-11-13 17:51:43 +0100

Comment 21855

Date: 2016-03-03 10:39:38 +0100
From: @mlkersten

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

From http://stackoverflow.com/questions/35730919/monetdb-select-query-slow-when-we-dont-add-single-quote-in-where-clause

Both plans should be the same.

sql> create table tmp(i decimal(8));
sql>explain select count() from tmp where i = 20160222;
+-------------------------------------------------------------------------------------------------------------+
| mal |
+=============================================================================================================+
| function user.s3_1(A0:lng):void; |
| X_28:void := querylog.define("explain select count() from tmp where i = 20160222;","default_pipe",14); |
| X_2 := sql.mvc(); |
| X_6:bat[:int] := sql.bind(X_2,"sys","tmp","i",0); |
| (C_9,r1_9) := sql.bind(X_2,"sys","tmp","i",2); |
| X_12:bat[:int] := sql.bind(X_2,"sys","tmp","i",1); |
| X_14 := sql.delta(X_6,C_9,r1_9,X_12); |
| X_15:bat[:lng] := batcalc.lng(0,X_14,10,0); |
| C_3:bat[:oid] := sql.tid(X_2,"sys","tmp"); |
| C_17 := algebra.subselect(X_15,C_3,A0,A0,true,false,false); |
| X_20 := sql.projectdelta(C_17,X_6,C_9,r1_9,X_12); |
| X_21 := aggr.count(X_20); |
| sql.resultSet("sys.L1","L1","wrd",64,0,7,X_21); |
| end user.s3_1; |
| optimizer.mitosis() |
| optimizer.dataflow() |
+-------------------------------------------------------------------------------------------------------------+
16 tuples (2.182ms)
sql>explain select count() from tmp where i = '20160222';
+-------------------------------------------------------------------------------------------------------------------+
| mal |
+===================================================================================================================+
| function user.s4_1(A0:str):void; |
| X_27:void := querylog.define("explain select count() from tmp where i = \'20160222\';","default_pipe",16); |
| X_2 := sql.mvc(); |
| X_6:bat[:int] := sql.bind(X_2,"sys","tmp","i",0); |
| C_3:bat[:oid] := sql.tid(X_2,"sys","tmp"); |
| X_14 := calc.int(A0,8,0); |
| C_38 := algebra.subselect(X_6,C_3,X_14,X_14,true,false,false); |
| (C_9,r1_9) := sql.bind(X_2,"sys","tmp","i",2); |
| C_39 := algebra.subselect(r1_9,nil:bat[:oid],X_14,X_14,true,false,false); |
| X_12:bat[:int] := sql.bind(X_2,"sys","tmp","i",1); |
| C_41 := algebra.subselect(X_12,C_3,X_14,X_14,true,false,false); |
| C_16 := sql.subdelta(C_38,C_3,C_9,C_39,C_41); |
| X_19 := sql.projectdelta(C_16,X_6,C_9,r1_9,X_12); |
| X_20 := aggr.count(X_19); |
| sql.resultSet("sys.L1","L1","wrd",64,0,7,X_20); |
| end user.s4_1; |
| optimizer.mitosis() |
| optimizer.dataflow() |
+-------------------------------------------------------------------------------------------------------------------+
18 tuples (2.163ms)

Reproducible: Always

Comment 21863

Date: 2016-03-03 21:23:07 +0100
From: @drstmane

This is IMHO not a coercion priority problem,
but rather triggered by parameterized query plan caching with the "ambition" to use the most generic parameter type, i.e., largest integer type (bigint / lng) for the integer literal, and thus the least plan variations to be cached.

Given that, up-casting the decimal(8) (4 byte) BAT to the bigint/lng (8-byte) literal is IMHO the only viable / correct option; down-casting the literal is IMHO no option.

Alternative, we might want to consider using the most specific/string (integer) parameter type, and, if required, cache more than one variant of the same plan for different integer type(-combination)s. Then, (up-)casting (if necessary at all) the literal would be possible.
Or drop plan-caching for select queries in general, i.e., for all but simple insert queries?

With the string literal, this "problem" does not occur.

Comment 21916

Date: 2016-03-16 14:40:13 +0100
From: @njnes

With a bit of stricker bits2digits/digits2bits conversion functions this can be properly handled.

Comment 21972

Date: 2016-03-25 09:59:42 +0100
From: @sjoerdmullender

Jul2015-SP3 has been released.

Comment 28268

Date: 2020-11-13 17:51:43 +0100
From: MonetDB Mercurial Repository <>

Changeset 1223293c87fe made by Pedro Ferreira pedro.ferreira@monetdbsolutions.com in the MonetDB repo, refers to this bug.

For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=1223293c87fe

Changeset description:

Use profiler traces instead of explain statement at decimal_vs_integer.Bug-3941 test