prepare doesn't recognize merge statement #6706
Date: 2019-05-25 08:30:17 +0200
Last updated: 2019-09-02 16:05:27 +0200
Date: 2019-05-25 08:30:17 +0200
User-Agent: Mozilla/5.0 (Windows NT 10.0) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/74.0.3729.169 Safari/537.36
Cannot prepare merge statement
Steps to Reproduce:
syntax error, unexpected MERGE in: "prepare
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.
Date: 2019-05-25 10:00:30 +0200
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.
Date: 2019-05-25 10:13:10 +0200
My apologies thought I was running latest version. Must admit just copied and pasted description without checking dates. Will download latest and try again.
Date: 2019-05-25 14:16:43 +0200
Upgraded and prepare works for merge but not with parameters
sql>merge into test.share_daily_history as target
Date: 2019-05-25 16:50:16 +0200
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.
Date: 2019-05-27 10:53:20 +0200
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=ea82b864645d
Date: 2019-05-27 11:13:50 +0200
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.
Date: 2019-05-27 12:16:33 +0200
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.
Date: 2019-05-27 12:33:13 +0200
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.
Date: 2019-06-04 09:50:30 +0200
Can somebody please change the bug summary? The version number is not a good summary of the bug.
Date: 2019-06-04 11:00:43 +0200
Note, have found out that if I cast parameter it handles typing issue. May be worthwhile mentioning in documentation.
Date: 2019-06-12 15:40:02 +0200
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.
Date: 2019-06-14 17:07:14 +0200
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=04ee0b5b1658
Date: 2019-06-17 17:54:23 +0200
For complete details, see https//devmonetdborg/hg/MonetDB?cmd=changeset;node=84a23e24786f
The text was updated successfully, but these errors were encountered: