You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Lukas Eder (Jira)" <ji...@apache.org> on 2022/10/07 14:24:00 UTC
[jira] [Updated] (DERBY-7146) NullPointerException when executing MERGE statement with NULL value in INSERT clause
[ https://issues.apache.org/jira/browse/DERBY-7146?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Lukas Eder updated DERBY-7146:
------------------------------
Description:
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)
was:
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)
> 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)