[ https://issues.apache.org/jira/browse/DERBY-7146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Richard N. Hillegas updated DERBY-7146: --------------------------------------- Bug behavior facts: Deviation from standard,Seen in production Issue & fix info: Repro attached,Workaround attached > 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, 10.17.0.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)