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
Date: 2019-05-25 08:30:17 +0200
From: Peter Prib <<peter.prib>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @PedroTadim
Last updated: 2019-09-02 16:05:27 +0200
Comment 27006
Date: 2019-05-25 08:30:17 +0200
From: Peter Prib <<peter.prib>>
User-Agent: Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36
Build Identifier:
Cannot prepare merge statement
Reproducible: Always
Steps to Reproduce:
1.prepare
merge into test.share_daily_history
as target
USING select * from (
values('BHP',? ,? ,? ,? ,? ,?)) as (id,timeid,c1,c2,c3,c4,volume) as source
on source.id=target.id and source.timeid=target.timeid
when not matched then
insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
Hello Peter, I'm confused about your description. On the title you say the bug is from the Aug2018 version, but in the version description is Apr2019. The merge statements were added in the Apr2019 version, so you can't use them in the Aug2018 version. I just tried a prepared merge statement in the Apr2019 version and I didn't get any error.
Comment 27008
Date: 2019-05-25 10:13:10 +0200
From: Peter Prib <<peter.prib>>
My apologies thought I was running latest version. Must admit just copied and pasted description without checking dates. Will download latest and try again.
Comment 27009
Date: 2019-05-25 14:16:43 +0200
From: Peter Prib <<peter.prib>>
Upgraded and prepare works for merge but not with parameters
sql>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',1,1 ,1 ,1 ,1 ,1)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;;
0 affected rows
sql>prepare
more>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',1,1 ,1 ,1 ,1 ,1)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;;
execute prepared statement using: EXEC 7(...)
+------+--------+-------+--------+-------+--------+
| type | digits | scale | schema | table | column |
+======+========+=======+========+=======+========+
+------+--------+-------+--------+-------+--------+
0 tuples
sql>prepare
more>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',?,? ,? ,? ,? ,?)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;
42000!
sql>
Thanks for your feedback Peter, I just reproduced the bug in our development branch. The problem is the compiler fails to infer the types of the prepared statement parameters to perform the merge statement's join. I will look into it next Monday.
I checked further the causes of the error message, and I found out the types of the prepared statement parameters could not be inferred. This happens because they are used inside a sub-query, which are later used to generate the merge statement's inner join. Because the types are not known, the inner join cannot be generated and the error is thrown.
We could fix this by hinting the types of the parameters at the beginning of the query, much alike PostgreSQL does. However this will be a feature request.
Comment 27014
Date: 2019-05-27 12:16:33 +0200
From: Peter Prib <<peter.prib>>
Problem point makes sense. Wonder how other DBMS managed to handle. May have written into stored proc which would give data typing definition to parameter as could be determined from other statements.
Would it be possible to have such parameters have a default of string so they could be cast or requires cast so they could be inferred?
Could one accept typing as given on exec and then fails if typing mismatch at execution of SQL. Suspect this not easy as would require agility .
Problem clear as the following fails with typing issue.
prepare select * from (values(?)) as a(id);
Comment 27015
Date: 2019-05-27 12:33:13 +0200
From: Peter Prib <<peter.prib>>
Have seen the other way to overcome the issue. Defer the actual prepare until the execute statement which then presents the parameters thus know the data typing. This would give more flexibility as casting could be done if inconsistencies in data types.
We have looked back at this bug, and we can fix the type resolution for the parameters inside the compiler for situations alike this one. However the fix will take some time to get done.
Improvements for bug #6706. When resolving the type of an expression on rel_set_type_param, find it's underlying column/atom reference is a parameter without a type, set it.
This implied to change the prototype of the rel_check_type function to pass optionally the relation of the expression.
Also when calling a SQL exec, we should check if the number of arguments correspond to the number of arguments of the underlying prepared statement.
Date: 2019-05-25 08:30:17 +0200
From: Peter Prib <<peter.prib>>
To: SQL devs <>
Version: 11.33.3 (Apr2019)
CC: @PedroTadim
Last updated: 2019-09-02 16:05:27 +0200
Comment 27006
Date: 2019-05-25 08:30:17 +0200
From: Peter Prib <<peter.prib>>
User-Agent: Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36
Build Identifier:
Cannot prepare merge statement
Reproducible: Always
Steps to Reproduce:
1.prepare
merge into test.share_daily_history
as target
USING select * from (
values('BHP',? ,? ,? ,? ,? ,?)) as (id,timeid,c1,c2,c3,c4,volume) as source
on source.id=target.id and source.timeid=target.timeid
when not matched then
insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
Actual Results:
syntax error, unexpected MERGE in: "prepare
merge"
Expected Results:
statement prepared for execution
Using merge as no upsert. Trying to use merge in node.js with parameters which is generating prepare. Prepare should work on merge statement.
Comment 27007
Date: 2019-05-25 10:00:30 +0200
From: @PedroTadim
Hello Peter, I'm confused about your description. On the title you say the bug is from the Aug2018 version, but in the version description is Apr2019. The merge statements were added in the Apr2019 version, so you can't use them in the Aug2018 version. I just tried a prepared merge statement in the Apr2019 version and I didn't get any error.
Comment 27008
Date: 2019-05-25 10:13:10 +0200
From: Peter Prib <<peter.prib>>
My apologies thought I was running latest version. Must admit just copied and pasted description without checking dates. Will download latest and try again.
Comment 27009
Date: 2019-05-25 14:16:43 +0200
From: Peter Prib <<peter.prib>>
Upgraded and prepare works for merge but not with parameters
sql>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',1,1 ,1 ,1 ,1 ,1)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;;
0 affected rows
sql>prepare
more>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',1,1 ,1 ,1 ,1 ,1)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;;
execute prepared statement using: EXEC 7(...)
+------+--------+-------+--------+-------+--------+
| type | digits | scale | schema | table | column |
+======+========+=======+========+=======+========+
+------+--------+-------+--------+-------+--------+
0 tuples
sql>prepare
more>merge into test.share_daily_history as target
more>USING (
more>select * from (values('BHP',?,? ,? ,? ,? ,?)) as a(id,timeid,c1,c2,c3,c4,volume)
more>) as source
more>on source.id=target.id and source.timeid=target.timeid
more>when not matched then
more>insert (id,timeid,c1,c2,c3,c4,volume) values(source.id,source.timeid,source.c1,source.c2,source.c3,source.c4,source.volume)
more>;
42000!
sql>
Comment 27010
Date: 2019-05-25 16:50:16 +0200
From: @PedroTadim
Thanks for your feedback Peter, I just reproduced the bug in our development branch. The problem is the compiler fails to infer the types of the prepared statement parameters to perform the merge statement's join. I will look into it next Monday.
Comment 27012
Date: 2019-05-27 10:53:20 +0200
From: MonetDB Mercurial Repository <>
Changeset ea82b864645d 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=ea82b864645d
Changeset description:
Comment 27013
Date: 2019-05-27 11:13:50 +0200
From: @PedroTadim
I checked further the causes of the error message, and I found out the types of the prepared statement parameters could not be inferred. This happens because they are used inside a sub-query, which are later used to generate the merge statement's inner join. Because the types are not known, the inner join cannot be generated and the error is thrown.
We could fix this by hinting the types of the parameters at the beginning of the query, much alike PostgreSQL does. However this will be a feature request.
Comment 27014
Date: 2019-05-27 12:16:33 +0200
From: Peter Prib <<peter.prib>>
Problem point makes sense. Wonder how other DBMS managed to handle. May have written into stored proc which would give data typing definition to parameter as could be determined from other statements.
Would it be possible to have such parameters have a default of string so they could be cast or requires cast so they could be inferred?
Could one accept typing as given on exec and then fails if typing mismatch at execution of SQL. Suspect this not easy as would require agility .
Problem clear as the following fails with typing issue.
prepare select * from (values(?)) as a(id);
Comment 27015
Date: 2019-05-27 12:33:13 +0200
From: Peter Prib <<peter.prib>>
Have seen the other way to overcome the issue. Defer the actual prepare until the execute statement which then presents the parameters thus know the data typing. This would give more flexibility as casting could be done if inconsistencies in data types.
Comment 27019
Date: 2019-06-04 09:50:30 +0200
From: @sjoerdmullender
Can somebody please change the bug summary? The version number is not a good summary of the bug.
Comment 27021
Date: 2019-06-04 11:00:43 +0200
From: Peter Prib <<peter.prib>>
Fixed title
Note, have found out that if I cast parameter it handles typing issue. May be worthwhile mentioning in documentation.
Comment 27058
Date: 2019-06-12 15:40:02 +0200
From: @PedroTadim
We have looked back at this bug, and we can fix the type resolution for the parameters inside the compiler for situations alike this one. However the fix will take some time to get done.
Comment 27069
Date: 2019-06-14 17:07:14 +0200
From: MonetDB Mercurial Repository <>
Changeset 04ee0b5b1658 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=04ee0b5b1658
Changeset description:
Comment 27071
Date: 2019-06-17 17:54:23 +0200
From: MonetDB Mercurial Repository <>
Changeset 84a23e24786f 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=84a23e24786f
Changeset description:
The text was updated successfully, but these errors were encountered: