[jira] Commented: (DERBY-129) Derby should throw a truncation error or warning when CASTing a parameter/constant to char or char for bit datatypes and the data is too large for the datatype.

["Jeremy Boynes (JIRA)" <de...@db.apache.org>]

     [ http://issues.apache.org/jira/browse/DERBY-129?page=comments#action_57928 ]
     
Jeremy Boynes commented on DERBY-129:
-------------------------------------

I don't believe Derby should throw an exception under these circumstances.

According to the SQL spec (SQL-03 4.2.1), "if a retrieval assignment or evaluation of a
<cast specification> would result in the loss of characters due to truncation, then a warning condition is raised." And according to JDBC (3.0 8.2) "when data truncation occurs on a read from the data source, a SQLWarning is reported." The SQL spec considers this a retrieve/cast operation so instead of throwing an exception we should complete execution of the statement and add a java.sql.DataTruncation warning to the list returned from Statement.getWarnings()

In contrast, if the truncation occurs during a write operation then we do need to throw an exception; we actually do this, throwing an SQLException with SQLState 22001. However, I believe this is not compliant with JDBC which states that "when data truncation occurs on a write to the data source, a DataTruncation object is thrown." So instead of throwing the SQLException base class we should be throwing a DataTruncation subclass.

To clarify, the reason this is a read operation even though we are executing an INSERT is that the data is being used the CAST function not directly in the INSERT. So if we have a table with a single VARCHAR(3) column

INSERT INTO TEST VALUES (CAST('01234' AS VARCHAR(3))) 

should complete with a warning but

INSERT INTO TEST VALUES ('01234')

should fail by throwing a DataTruncation exception.

> Derby should throw a truncation error or warning when CASTing a parameter/constant to char or char for bit datatypes and the data is too large for the datatype.
> ----------------------------------------------------------------------------------------------------------------------------------------------------------------
>
>          Key: DERBY-129
>          URL: http://issues.apache.org/jira/browse/DERBY-129
>      Project: Derby
>         Type: Bug
>   Components: JDBC
>     Versions: 10.0.2.1
>     Reporter: Mamta A. Satoor

>
> Derby doesn't throw a truncation exception/warning when data is too large during casting of constants or parameters to character string or bit string data types. 
> Following is ij example for constants which is too big for the datatype it is getting cast to
> ij> values (cast ('hello' as char(3)));
> 1
> ----
> hel
> 1 row selected
> ij> values (cast (X'0102' as char(1) for bit data));
> 1
> ----
> 01
> 1 row selected
> Following code snippet is when using parameters through a JDBC program
>    s.executeUpdate("create table ct (c CLOB(100K))");
>    //the following Formatters just loads cData with 32700 'c' characters
>    String cData = org.apache.derbyTesting.functionTests.util.Formatters.repeatChar("c",32700);
>    //notice that ? in the preared statement below is bound to length 32672
>    pSt = con.prepareStatement("insert into ct values (cast (? as varchar(32672)))");
>    pSt.setString(1, cData);
>    //Derby doesn't throw an exception at ps.execute time for 32700 characters into 32672 parameter. It silently
>    truncates it to 32672
>    pSt.execute();

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
If you want more information on JIRA, or have a bug to report see:
   http://www.atlassian.com/software/jira