Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

prepare doesn't recognize merge statement #6706

Closed
monetdb-team opened this issue Nov 30, 2020 · 0 comments
Closed

prepare doesn't recognize merge statement #6706

monetdb-team opened this issue Nov 30, 2020 · 0 comments
Labels
bug Something isn't working normal SQL

Comments

@monetdb-team
Copy link

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:

Throw a more detailed error message for bug #6706.

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:

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.

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:

Further improvements for Bug #6706. When the column expression is an alias, search for the referencing column.
@monetdb-team monetdb-team added bug Something isn't working normal SQL labels Nov 30, 2020
@sjoerdmullender sjoerdmullender added this to the Ancient Release milestone Feb 7, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
bug Something isn't working normal SQL
Projects
None yet
Development

No branches or pull requests

2 participants