[DERBY-7132] SQLDataException when executing CAST inside a CASE WHEN clause

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 10.14.2.0, 10.15.2.0
• Component/s: SQL
• Urgency: Normal
• Issue & fix info: Repro attached, Workaround attached
• Bug behavior facts: Seen in production, Wrong query result
• Assignee: Unassigned
• Reporter: Stamatis Zampetakis
• Created: 17/Feb/22 14:36
• Updated: 28/Sep/22 15:17
• Resolved: 28/Sep/22 15:17

Description

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

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.

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)

The problem can be reproduced by running the query above in the derby database attached to the case.

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));
            }
          }
        }
      }

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.

Issue Links

• causes: HIVE-25965 SQLDataException when obtaining partitions from HMS via direct SQL over Derby
• duplicates: DERBY-6358 WHERE clause should be evaluated after the joins in the FROM clause
• is related to: HIVE-8739 handle Derby and Oracle errors with joins and filters in Direct SQL in a invalid-DB-specific path
• relates to: DERBY-6358 WHERE clause should be evaluated after the joins in the FROM clause