You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Mamta A. Satoor (JIRA)" <de...@db.apache.org> on 2005/01/21 21:28:19 UTC
[jira] Created: (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.
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
[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.
Posted by "Knut Anders Hatlen (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12867896#action_12867896 ]
Knut Anders Hatlen commented on DERBY-129:
------------------------------------------
I don't think Jeremy suggested that this issue was invalid, only that the appropriate response is a warning and not an exception. Derby doesn't currently give a warning.
> 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: https://issues.apache.org/jira/browse/DERBY-129
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects 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.
-
You can reply to this email to add a comment to the issue online.
[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.
Posted by "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
[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.
Posted by "Myrna van Lunteren (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/DERBY-129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12730964#action_12730964 ]
Myrna van Lunteren commented on DERBY-129:
------------------------------------------
[10.5.2 Triage] Mamta, do you agree with Jeremy's comments? If so, can this oldy be closed as Invalid?
> 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: https://issues.apache.org/jira/browse/DERBY-129
> Project: Derby
> Issue Type: Bug
> Components: JDBC
> Affects 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.
-
You can reply to this email to add a comment to the issue online.