Stamatis Zampetakis created DERBY-7132:
------------------------------------------
Summary: SQLDataException when executing CAST inside a CASE WHEN
clause
Key: DERBY-7132
URL: https://issues.apache.org/jira/browse/DERBY-7132
Project: Derby
Issue Type: Bug
Components: SQL
Affects Versions: 10.14.2.0
Reporter: Stamatis Zampetakis
Attachments: derby-dump.tar.gz
{code:sql}
SELECT "PARTITIONS"."PART_ID"
FROM "PARTITIONS"
INNER JOIN "TBLS" ON "PARTITIONS"."TBL_ID" = "TBLS"."TBL_ID"
INNER JOIN "DBS" ON "TBLS"."DB_ID" = "DBS"."DB_ID"
INNER JOIN "PARTITION_KEY_VALS" "FILTER0" ON "FILTER0"."PART_ID" =
"PARTITIONS"."PART_ID"
WHERE "DBS"."CTLG_NAME" = 'hive'
AND "TBLS"."TBL_NAME" = 'src_bucket_tbl'
AND "DBS"."NAME" = 'default'
AND "FILTER0"."INTEGER_IDX" = 0
AND (((CASE
WHEN "FILTER0"."PART_KEY_VAL" <> '__HIVE_DEFAULT_PARTITION__'
AND "TBLS"."TBL_NAME" = 'src_bucket_tbl'
AND "DBS"."NAME" = 'default'
AND "DBS"."CTLG_NAME" = 'hive'
AND "FILTER0"."INTEGER_IDX" = 0 THEN
cast("FILTER0"."PART_KEY_VAL" AS decimal(21, 0))
END) = 10))
{code}
The SQL query above fails with the following stacktrace when attempting to
evaluate the CAST expression. Note that the condition inside the CASE WHEN
clause guarantees that only legal values (numbers) should be passed inside the
CAST function. Apparently, the operations are somehow re-ordered and the CAST
is evaluated before the condition in the WHEN clause which has a result a
non-number to be passed in the CAST and cause the exception below.
{noformat}
Exception in thread "main" java.sql.SQLDataException: Invalid character string
format for type DECIMAL.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:84)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Util.java:230)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(TransactionResourceImpl.java:424)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(TransactionResourceImpl.java:353)
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.executeStatement(EmbedStatement.java:1436)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeStatement(EmbedPreparedStatement.java:1709)
at
org.apache.derby.impl.jdbc.EmbedPreparedStatement.executeQuery(EmbedPreparedStatement.java:286)
at com.github.zabetak.CaseProblem.main(CaseProblem.java:63)
Caused by: ERROR 22018: Invalid character string format for type DECIMAL.
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:290)
at
org.apache.derby.iapi.error.StandardException.newException(StandardException.java:285)
at
org.apache.derby.iapi.types.DataType.invalidFormat(DataType.java:1280)
at org.apache.derby.iapi.types.DataType.setValue(DataType.java:552)
at
org.apache.derby.exe.acf81e0010x017fx0812xbaa5x00003a07fe880.e3(Unknown Source)
at
org.apache.derby.impl.services.reflect.DirectCall.invoke(ReflectGeneratedClass.java:107)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.getNextRowCore(ProjectRestrictResultSet.java:302)
at
org.apache.derby.impl.sql.execute.NestedLoopJoinResultSet.getNextRowCore(NestedLoopJoinResultSet.java:119)
at
org.apache.derby.impl.sql.execute.JoinResultSet.openCore(JoinResultSet.java:149)
at
org.apache.derby.impl.sql.execute.ProjectRestrictResultSet.openCore(ProjectRestrictResultSet.java:182)
at
org.apache.derby.impl.sql.execute.BasicNoPutResultSetImpl.open(BasicNoPutResultSetImpl.java:266)
at
org.apache.derby.impl.sql.GenericPreparedStatement.executeStmt(GenericPreparedStatement.java:472)
at
org.apache.derby.impl.sql.GenericPreparedStatement.execute(GenericPreparedStatement.java:351)
at
org.apache.derby.impl.jdbc.EmbedStatement.executeStatement(EmbedStatement.java:1344)
{noformat}
The problem can be reproduced by running the query above in the derby database
attached to the case.
{code:sql}
try (Connection c =
DriverManager.getConnection("jdbc:derby:;databaseName=repro_derby_db")) {
try (PreparedStatement ps = c.prepareStatement(sql)) {
try (ResultSet rs = ps.executeQuery()) {
while (rs.next()) {
System.out.println(rs.getInt(1));
}
}
}
}
{code}
Unfortunately, I couldn't write a minimal reproducer cause slight changes to
the order of performing the operations in the database has an impact on the
plan and may hide the problem.
--
This message was sent by Atlassian Jira
(v8.20.1#820001)
