[
https://issues.apache.org/jira/browse/DERBY-7134?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17503649#comment-17503649
]
Richard N. Hillegas commented on DERBY-7134:
--------------------------------------------
With that table definition, the MERGE statement does not even compile for me.
The following script...
{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';
CREATE TABLE HUND
(
id INTEGER GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1),
name VARCHAR(100) NOT NULL,
wurfdatum DATE NOT NULL,
zwinger VARCHAR(100),
zbn VARCHAR(100) NOT NULL,
CONSTRAINT PK_HUND PRIMARY KEY (id),
constraint uk_hund unique (name)
);
MERGE INTO HUND USING SYSIBM.SYSDUMMY1 ON HUND.name='name'
WHEN MATCHED THEN UPDATE SET zwinger='ZW',zbn='zb'
WHEN NOT MATCHED THEN INSERT (name,zwinger,zbn) VALUES('a','zw','zb')
;
{noformat}
...produces the following output...
{noformat}
ij version 10.16
ij> CONNECT 'jdbc:derby:memory:db;create=true';
ij> CREATE TABLE HUND
(
id INTEGER GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1),
name VARCHAR(100) NOT NULL,
wurfdatum DATE NOT NULL,
zwinger VARCHAR(100),
zbn VARCHAR(100) NOT NULL,
CONSTRAINT PK_HUND PRIMARY KEY (id),
constraint uk_hund unique (name)
);
0 rows inserted/updated/deleted
ij> MERGE INTO HUND USING SYSIBM.SYSDUMMY1 ON HUND.name='name'
WHEN MATCHED THEN UPDATE SET zwinger='ZW',zbn='zb'
WHEN NOT MATCHED THEN INSERT (name,zwinger,zbn) VALUES('a','zw','zb')
;
ERROR 23502: Column 'WURFDATUM' cannot accept a NULL value.
{noformat}
The error looks correct to me. The INSERT branch of the MERGE statement
defaults wurfdatum to NULL, which violates the NOT NULL constraint on the
column.
I modified the MERGE statement to INSERT CURRENT_DATE into wurfdatum and
expanded the script so that it ran the experiment against both a local embedded
database and against a remote, network-accessed database:
{noformat}
CONNECT 'jdbc:derby:memory:db;create=true';
CREATE TABLE HUND
(
id INTEGER GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1),
name VARCHAR(100) NOT NULL,
wurfdatum DATE NOT NULL,
zwinger VARCHAR(100),
zbn VARCHAR(100) NOT NULL,
CONSTRAINT PK_HUND PRIMARY KEY (id),
constraint uk_hund unique (name)
);
MERGE INTO HUND USING SYSIBM.SYSDUMMY1 ON HUND.name='name'
WHEN MATCHED THEN UPDATE SET zwinger='ZW',zbn='zb'
WHEN NOT MATCHED THEN INSERT (name,zwinger,zbn,wurfdatum)
VALUES('a','zw','zb', CURRENT_DATE)
;
CONNECT 'jdbc:derby://localhost:8246/memory:remote_db;create=true';
CREATE TABLE HUND
(
id INTEGER GENERATED ALWAYS AS IDENTITY(Start with 1, Increment by 1),
name VARCHAR(100) NOT NULL,
wurfdatum DATE NOT NULL,
zwinger VARCHAR(100),
zbn VARCHAR(100) NOT NULL,
CONSTRAINT PK_HUND PRIMARY KEY (id),
constraint uk_hund unique (name)
);
MERGE INTO HUND USING SYSIBM.SYSDUMMY1 ON HUND.name='name'
WHEN MATCHED THEN UPDATE SET zwinger='ZW',zbn='zb'
WHEN NOT MATCHED THEN INSERT (name,zwinger,zbn,wurfdatum)
VALUES('a','zw','zb', CURRENT_DATE)
;
{noformat}
The modified script ran correctly against both databases.
I ran these experiments with clients and servers both running Java 11. I tried
the experiment both with the Derby trunk release (10.16) and with Derby
10.15.2.0. The results were consistent across Derby releases.
What happens when you run these experiments? Thanks.
> MERGE not Working over Nework
> -----------------------------
>
> Key: DERBY-7134
> URL: https://issues.apache.org/jira/browse/DERBY-7134
> Project: Derby
> Issue Type: Bug
> Components: Network Client
> Affects Versions: 10.15.2.0
> Environment: Windows , Derby
> Reporter: Thomas Lußnig
> Priority: Major
>
> I Receive an error after sending Merge Statement via Netowrk to derby network
> server.
> The error was first mentioned 5 Years ago:
> *
> [https://stackoverflow.com/questions/41587515/derby-merge-statement-results-in-insufficient-data-while-reading-from-the-netwo]
> And is till now active in derby.
> SQL-Fehler: Unzureichende Daten beim Lesen aus dem Netz. Erwartet wurden
> mindestens 21.272 Byte, empfangen wurden jedoch nur 0 Byte. Die Verbindung
> wurde beendet.
> MERGE INTO HUND USING SYSIBM.SYSDUMMY1 ON HUND.name='name'
> WHEN MATCHED THEN UPDATE SET zwinger='ZW',zbn='zb'
> WHEN NOT MATCHED THEN INSERT (name,zwinger,zbn)VALUES('a','zw','zb')
> ;
> Tue Mar 08 07:23:32 CET 2022 : Cannot invoke "String.length()" because
> "<parameter1>" is null
> java.lang.NullPointerException: Cannot invoke "String.length()" because
> "<parameter1>" is null
> at
> org.apache.derby.impl.drda.DDMWriter.maxEncodedLength(DDMWriter.java:1138)
> at org.apache.derby.impl.drda.DDMWriter.writeString(DDMWriter.java:1252)
> at
> org.apache.derby.impl.drda.DRDAConnThread.writeSQLCAGRP(DRDAConnThread.java:6338)
> at
> org.apache.derby.impl.drda.DRDAConnThread.writeSQLCARD(DRDAConnThread.java:6167)
> at
> org.apache.derby.impl.drda.DRDAConnThread.writeSQLCARDs(DRDAConnThread.java:6109)
> at
> org.apache.derby.impl.drda.DRDAConnThread.processCommands(DRDAConnThread.java:836)
> at org.apache.derby.impl.drda.DRDAConnThread.run(DRDAConnThread.java:300)
>
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
