[DERBY-7146] NullPointerException when executing MERGE statement with NULL value in INSERT clause

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.15.2.0, 10.17.1.0
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached, Workaround attached
• Bug behavior facts: Deviation from standard, Seen in production
• Assignee: Unassigned
• Reporter: Lukas Eder
• Created: 07/Oct/22 14:22
• Updated: 07/Oct/22 19:07

Description

Try this:

create table x (
  i int,
  c varchar(10),
  primary key (i)
);

merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, null);

It fails with

Caused by: java.sql.SQLException: Java exception: ': java.lang.NullPointerException'.
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:115)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:141)
	at org.apache.derby.impl.jdbc.Util.seeNextException(Util.java:252)
	at org.apache.derby.impl.jdbc.Util.javaException(Util.java:274)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:444)
	at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:360)
	at org.apache.derby.impl.jdbc.EmbedConnection.handleException(EmbedConnection.java:2405)
	at org.apache.derby.impl.jdbc.ConnectionChild.handleException(ConnectionChild.java:88)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:697)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:637)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.execute(JDBCStatementImpl.java:329)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.lambda$0(JDBCStatementImpl.java:131)
	at org.jkiss.dbeaver.utils.SecurityManagerUtils.wrapDriverActions(SecurityManagerUtils.java:94)
	at org.jkiss.dbeaver.model.impl.jdbc.exec.JDBCStatementImpl.executeStatement(JDBCStatementImpl.java:131)
	... 12 more
Caused by: ERROR XJ001: Java exception: ': java.lang.NullPointerException'.
	at org.apache.derby.shared.common.error.StandardException.newException(StandardException.java:300)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.wrapArgsForTransportAcrossDRDA(SQLExceptionFactory.java:170)
	at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:75)
	... 25 more
Caused by: java.lang.NullPointerException
	at org.apache.derby.impl.sql.compile.CharTypeCompiler.convertible(CharTypeCompiler.java:47)
	at org.apache.derby.impl.sql.compile.CharTypeCompiler.storable(CharTypeCompiler.java:93)
	at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1041)
	at org.apache.derby.impl.sql.compile.ResultColumn.checkStorableExpression(ResultColumn.java:1033)
	at org.apache.derby.impl.sql.compile.ResultColumnList.checkStorableExpressions(ResultColumnList.java:1087)
	at org.apache.derby.impl.sql.compile.InsertNode.bindStatement(InsertNode.java:474)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bindInsert(MatchingClauseNode.java:839)
	at org.apache.derby.impl.sql.compile.MatchingClauseNode.bind(MatchingClauseNode.java:214)
	at org.apache.derby.impl.sql.compile.MergeNode.bindStatement(MergeNode.java:387)
	at org.apache.derby.impl.sql.GenericStatement.prepMinion(GenericStatement.java:401)
	at org.apache.derby.impl.sql.GenericStatement.prepare(GenericStatement.java:99)
	at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(GenericLanguageConnectionContext.java:1114)
	at org.apache.derby.impl.jdbc.EmbedStatement.execute(EmbedStatement.java:689)
	... 17 more

As a workaround, this works (removing the explicit NULL value from the INSERT clause):

merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i)
values (1);

Using a CAST also helps work around the problem:

merge into x
using SYSIBM.SYSDUMMY1
on i = 1
when matched then update set c = null
when not matched then insert (i, c)
values (1, cast(null as varchar(10)));

(Sorry, I didn't try the latest version, because I didn't get it to work in Dbeaver)