db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (JIRA)" <j...@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 Fri, 18 May 2012 13:10:08 GMT

    [ https://issues.apache.org/jira/browse/DERBY-129?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13278790#comment-13278790
] 

Knut Anders Hatlen commented on DERBY-129:
------------------------------------------

One other thing that's probably worth mentioning, is that the
truncation warning doesn't always end up on the same row as the one
where the truncation happened. This seems to be the case when there's
been a join or the result has been sorted.

For example:

ij> create table t1 (x varchar(5));
0 rows inserted/updated/deleted
ij> insert into t1 values 'hello', 'hi', 'hey', 'yo';
4 rows inserted/updated/deleted
ij> select cast(x as char(3)) from t1 order by x desc;
1   
----
yo  
WARNING 01004: Data truncation
hi  
hey 
hel 

4 rows selected

Here, the warning comes with the first row, whereas the truncated
value is in the last row.

This appears to be an existing problem with warnings in Derby, also
seen with other types of warnings. For example, with 10.8.2.2, I see
this in a query that generates a warning of a different kind:

ij> create table t2(x int, y int);
0 rows inserted/updated/deleted
ij> insert into t2 values (1,1),(2,2),(3,null);
3 rows inserted/updated/deleted
ij> select x, avg(y) from t2 group by x order by x;
X          |2          
-----------------------
1          |1          
WARNING 01003: Null values were eliminated from the argument of a column function.
2          |2          
3          |NULL       

3 rows selected

Here, too, the warning comes with the first row, whereas the row
containing the NULL value that it warns about, shows up in the last
row. So the problem isn't introduced by the patch.
                
> 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
>            Assignee: Knut Anders Hatlen
>              Labels: derby_triage10_8
>         Attachments: d129-1a.diff
>
>
> 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, please contact your JIRA administrators: https://issues.apache.org/jira/secure/ContactAdministrators!default.jspa
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message