db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jeremy Boynes (JIRA)" <derby-...@db.apache.org>
Subject [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.
Date Sat, 22 Jan 2005 01:19:17 GMT
     [ 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


should complete with a warning but


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:
>     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:
If you want more information on JIRA, or have a bug to report see:

View raw message