Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-1132

Truncation Error with Concat

    XMLWordPrintableJSON

Details

    • Bug
    • Status: Closed
    • Major
    • Resolution: Fixed
    • 10.1.1.0
    • 10.2.2.1, 10.3.1.4
    • JDBC
    • None
    • Solaris, Windwos, JDK 1.5

    Description

      Consider the table
      CREATE TABLE CUSTOMER_TABLE ( ID VARCHAR(255) PRIMARY KEY NOT NULL, NAMEZ VARCHAR(255) , COUNTRY VARCHAR(255) )

      Sql:
      PreparedStatement ps = conn.prepateStatement ("SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ = VARCHAR ( CAST (? AS VARCHAR(32672) ) || CAST (? AS VARCHAR(32672) ) )" );
      ps.setString(1, "Alan E. ");
      ps.setString(2, "Frechette");
      ps.executeQuery()

      Error:
      "A truncation error was encountered trying to shrink VARCHAR 'Alan E. Frechette' to length 15."
      getErrorCode()-1
      getSQLState()22001

      Please note that
      -The query executes ok against DB2 database
      -The query executes ok if the total length of both the parameters bound is less than 15. That is as follows
      //Total length of parameters bound = len("Alan E. Fre") = 11
      ps.setString(1, "Alan E. ");
      ps.setString(2, "Fre");
      -The error occurs both with embedded and network mode of derby
      -Omitting the casts as follows also gives the same error
      query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ = VARCHAR( 'Frechette' || ? )
      error: SQLState: 22001 "A truncation error was encountered trying to shrink VARCHAR 'FrechetteAlan E. ' to length 15"
      -Using parameter markers for both the variables without cast like as follows results in error
      query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ = ? || ?
      error: SQLState: 42X35 "It is not allowed for both operands of '||' to be ? parameters."
      -Using parameter markers for only one variables without cast like as follows results in error
      query: SELECT * FROM CUSTOMER_TABLE WHERE NAMEZ = 'Frechette' || ?
      error:SQLState: 42818 "Comparisons between 'VARCHAR' and 'LONG VARCHAR' are not supported."
      -It works to cast to VARCHAR(2000), but not VARCHAR(2001) or larger. Regardless of length, if it fails, the magic number is always 15.

      Attachments

        1. derby_1132_v1.diff
          6 kB
          Saurabh Vyas
        2. derby_1132_v1.stat
          0.4 kB
          Saurabh Vyas
        3. repro.java
          1 kB
          Bryan Pendleton

        Issue Links

          Activity

            People

              sv204098 Saurabh Vyas
              miteshm Mitesh Meswani
              Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: