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 "Richard N. Hillegas (Jira)" <ji...@apache.org> on 2022/03/22 13:45:00 UTC
[jira] [Updated] (DERBY-7132) SQLDataException when executing CAST inside a CASE WHEN clause
[ https://issues.apache.org/jira/browse/DERBY-7132?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Richard N. Hillegas updated DERBY-7132:
---------------------------------------
Bug behavior facts: Seen in production,Wrong query result
Issue & fix info: Repro attached,Workaround attached
Urgency: Normal
> 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, 10.15.2.0
> Reporter: Stamatis Zampetakis
> Priority: Major
> Attachments: derby-dump.tar.gz, schemaddl.sql, uml_schema.svg
>
>
> {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)