[
https://issues.apache.org/jira/browse/DERBY-7146?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17614217#comment-17614217
]
Richard N. Hillegas commented on DERBY-7146:
--------------------------------------------
Thanks for the simple repro and the workaround. I can confirm that the bug
occurs on the development trunk too.
> NullPointerException when executing MERGE statement with NULL value in INSERT
> clause
> ------------------------------------------------------------------------------------
>
> Key: DERBY-7146
> URL: https://issues.apache.org/jira/browse/DERBY-7146
> Project: Derby
> Issue Type: Bug
> Components: SQL
> Affects Versions: 10.15.2.0
> Reporter: Lukas Eder
> Priority: Major
>
> Try this:
> {code:sql}
> 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);
> {code}
> It fails with
> {code}
> 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
> {code}
> As a workaround, this works (removing the explicit NULL value from the INSERT
> clause):
> {code:sql}
> merge into x
> using SYSIBM.SYSDUMMY1
> on i = 1
> when matched then update set c = null
> when not matched then insert (i)
> values (1);
> {code}
> Using a CAST also helps work around the problem:
> {code:sql}
> 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)));
> {code}
> (Sorry, I didn't try the latest version, because I didn't get it to work in
> Dbeaver)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
