[
https://issues.apache.org/jira/browse/DERBY-6558?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13984124#comment-13984124
]
Knut Anders Hatlen commented on DERBY-6558:
-------------------------------------------
The MERGE statement has inherited these flaws:
{noformat}
ij> create table t1(x int);
0 rows inserted/updated/deleted
ij> create table t2(y int);
0 rows inserted/updated/deleted
ij> merge into t1 using t2 on x = y when not matched then insert ("schema does
not exist".t1.x) values (y);
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.
ij> merge into t1 using t2 on x = y when matched then update set "I really
don't exist!".t1.x = y;
0 rows inserted/updated/deleted
WARNING 02000: No row was found for FETCH, UPDATE or DELETE; or the result of a
query is an empty table.
{noformat}
> Too lax checking of schema in INSERT and UPDATE statements
> ----------------------------------------------------------
>
> Key: DERBY-6558
> URL: https://issues.apache.org/jira/browse/DERBY-6558
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.10.2.0
> Reporter: Knut Anders Hatlen
>
> The following INSERT and UPDATE statements succeed, but they should have
> failed:
> ij> create table t(x int, y int);
> 0 rows inserted/updated/deleted
> ij> insert into t(adfadfa.t.x, kdkdkdkd.t.y) values (1,2);
> 1 row inserted/updated/deleted
> ij> update t set "schema does not exist".t.x = "neither does this".t.y;
> 1 row inserted/updated/deleted
> They should have failed for the following reasons:
> 1) The statements refer to columns in tables that are not in scope (and they
> don't even exist), so an error should have been raised at bind time.
> 2) The SQL standard says that the target columns of INSERT and UPDATE
> statements should be <column name>, which does not allow for qualification
> with table and schema (which would be redundant anyway). So if Derby's
> grammar had strictly adhered to the standard, an error should have been
> raised by the parser.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
