You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
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:
The text was updated successfully, but these errors were encountered: