db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Knut Anders Hatlen <knut.hat...@oracle.com>
Subject Re: silent truncation, correct?
Date Tue, 08 May 2012 18:53:08 GMT
Rick Hillegas <rick.hillegas@oracle.com> writes:

> On 5/8/12 10:12 AM, Rick Hillegas wrote:
>> On 5/8/12 9:40 AM, dag.wanvik@oracle.com wrote:
>>> Hi,
>>>
>>> I see that Derby silently truncates a too long string argument to a
>>> stored procedure with a formal argument of VARCHAR(n), cf. enclosed
>>> program. Is this correct behavior? I'll try to grok the standard on
>>> this, but it looks suspect to me.. The program prints 5 at the
>>> "cs.execute", but throws an exception as expected at the "ps.execute".
>>>
>>> Dag
>>>
>>> -----------------------------------------------------------
>>> package silentvarcharargtruncation;
>>>
>>> import java.sql.CallableStatement;
>>> import java.sql.Connection;
>>> import java.sql.DriverManager;
>>> import java.sql.PreparedStatement;
>>> import java.sql.SQLException;
>>> import java.sql.Statement;
>>>
>>> public class SilentVarcharArgTruncation {
>>>
>>>      public static void main(String[] args) throws SQLException {
>>>          Connection c =
>>> DriverManager.getConnection("jdbc:derby:wombat;create=true");
>>>          Statement s = c.createStatement();
>>>
>>>          try {
>>>              s.executeUpdate("drop table t");
>>>          } catch (SQLException e) {}
>>>
>>>          try {
>>>              s.executeUpdate("drop procedure p");
>>>          } catch (SQLException e) {}
>>>
>>>
>>>          s.executeUpdate("create table t(v varchar(5))");
>>>          s.executeUpdate("create procedure p (a varchar(5))
>>> modifies sql data " +
>>>              "external name
>>> silentvarcharargtruncation.SilentVarcharArgTruncation.testLength' "
>>> +
>>>              "language java parameter style java");
>>>          CallableStatement cs = c.prepareCall("call p(?)");
>>>          cs.setString(1, "123456");
>>>
>>>          // This silently truncates
>>>          cs.execute();
>>>
>>>
>>>          PreparedStatement ps = c.prepareStatement("insert into t
>>> values(?)");
>>>          ps.setString(1, "123456");
>>>          // This does not truncate, throws
>>>          ps.execute();
>>>      }
>>>
>>>
>>>      public static void testLength (String s) throws SQLException {
>>>          System.out.println(s.length());
>>>      }
>>> }
>>>
>> The same behavior is seen with Derby function:
>>
>> connect 'jdbc:derby:memory:db;create=true';
>>
>> create function parseInt( raw varchar( 2 ) ) returns int
>> language java parameter style java no sql external name
>> java.lang.Integer.parseInt';
>>
>> values ( parseInt( '1' ) );
>> values ( parseInt( '12' ) );
>>
>> -- succeeds but is truncated to '12'
>> values ( parseInt( '123' ) );
>>
>> Regards,
>> -Rick
>>
> Another possibly relevant detail: The CAST operator is supposed to
> truncate String types as necessary, raising a warning (but not an
> error) on truncation. See part 2 of the SQL Standard, section 6.12
> (<cast specification>), General Rules 10.c.ii and 11.c.ii.

AKA https://issues.apache.org/jira/browse/DERBY-5537 :)

> The truncation behavior we're seeing may be due to the insertion of
> implicit CASTs.

The above mentioned bug has a patch attached. Would be interesting to
see if it makes these implicit casts raise a warning too.

> Note also that Derby is not the only database with this behavior:
> http://stackoverflow.com/questions/4628140/sql-server-silently-truncates-varchars-in-stored-procedures
>
> Regards,
> -Rick

-- 
Knut Anders

Mime
View raw message