[ https://issues.apache.org/jira/browse/DERBY-7132?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17494060#comment-17494060 ]
Richard N. Hillegas commented on DERBY-7132: -------------------------------------------- Can you include the table definition, including constraints and indexes? Thanks. > 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 > > > {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)