TO_DATE with LIKE causing issues in Prepared statements using JDBC

Database

TO_DATE with LIKE causing issues in Prepared statements using JDBC

Facts:

We are using the JDBC 15.00.00.20 driver

Teradata Version: 14.10.03.03

Problem:

Looks like the combination of TO_DATE and LIKE is causing issues when using a JDBC driver and prepared statements. Here are the queries which are explain this. When we use the like clause by itself without the TO_DATE line, it works just fine. Seems like a combination of TO_DATE and LIKE (on different fields) in the WHERE is causing this which definitely seems like a huge bug and I would like to if anybody has run into this and has talked to Customer Support at TD and what has been their response - FIX coming soon? The work around we have employed is to CAST the field before using like even if it is defined as VARCHAR in the table. 

Query 1 works fine:

SELECT COUNT(*) CNT

FROM   (INFMT15_INFMT.MEDC_CPPROF "CP")  INNER JOIN  (INFMT15_INFMT.MEDC_D5PROF_ICD9_DIAG "D5")

       ON "CP".ICN_LINE_NO = "D5".ICN_LINE_NO

       AND "CP".PAYMENT_DATE = "D5".PAYMENT_DATE

       AND "CP".ICN = "D5".ICN

       AND "CP".ICN_VER = "D5".ICN_VER

WHERE  ("CP".PAYMENT_DATE BETWEEN TO_DATE(?, 'MM/DD/YYYY')

       AND TO_DATE(?, 'MM/DD/YYYY'));

Query 2 does not work fine:

SELECT COUNT(*) CNT

FROM   (INFMT15_INFMT.MEDC_CPPROF "CP")  INNER JOIN  (INFMT15_INFMT.MEDC_D5PROF_ICD9_DIAG "D5")

       ON "CP".ICN_LINE_NO = "D5".ICN_LINE_NO

       AND "CP".PAYMENT_DATE = "D5".PAYMENT_DATE

       AND "CP".ICN = "D5".ICN

       AND "CP".ICN_VER = "D5".ICN_VER

       WHERE  "CP".PAYMENT_DATE BETWEEN TO_DATE(?, 'MM/DD/YYYY')

       AND TO_DATE(?, 'MM/DD/YYYY')

              AND "CP".PRINCIPAL_DIAG LIKE ?

              ;

Error Received:

3536   UPPERCASE or CASESPECIFIC specified for non-CHAR data from the JDBC driver

Query 3 works fine (we had to cast the column used in the like clause):

SELECT COUNT(*) CNT

FROM   (INFMT15_INFMT.MEDC_CPPROF "CP")  INNER JOIN  (INFMT15_INFMT.MEDC_D5PROF_ICD9_DIAG "D5")

       ON "CP".ICN_LINE_NO = "D5".ICN_LINE_NO

       AND "CP".PAYMENT_DATE = "D5".PAYMENT_DATE

       AND "CP".ICN = "D5".ICN

       AND "CP".ICN_VER = "D5".ICN_VER

       WHERE  ("CP".PAYMENT_DATE BETWEEN TO_DATE(?, 'MM/DD/YYYY')

       AND TO_DATE(?, 'MM/DD/YYYY'))

              AND CAST("CP".PRINCIPAL_DIAG AS VARCHAR(255)) LIKE ?

       ;

1 REPLY
Teradata Employee

Re: TO_DATE with LIKE causing issues in Prepared statements using JDBC

Error 3536 is a Teradata Database error. The Teradata JDBC Driver does not generate that error.

You may have found a limitation with the Teradata Database. If you are a customer, please open an incident with Teradata Customer Service.