[
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
