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

[Lukas Eder (Jira)]

Lukas Eder created DERBY-7146:
---------------------------------

             Summary: 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


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 

{{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):

{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)