db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Knut Anders Hatlen (Commented) (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-5525) Precision for UPPER function is wrong if the returned value is longer than the literal argument
Date Fri, 09 Dec 2011 07:56:40 GMT

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

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

> What is the meaning of "precision" for character strings in the result set? (...) But
does this mean the actual column's length or the maximum length of the column type? If we
have a VARCHAR(n), I think "n" just as reasonable as LENGTH(<col>) here...

It couldn't be LENGTH(<col>), I think, since there might be many rows, and the ResultSetMetaData
is per ResultSet, not per row. The actual length of the value might be different in every
row.

> If it were "n", I guess this issue would go away..

Not entirely. Since the returned value is "STRASSE" (7 characters), and the data type of the
returned value is VARCHAR(6), we'd either need to truncate the value, or change the returned
data type to VARCHAR(7). Otherwise, the value and the type would not be consistent.

> In any case, solving this just for the literal case isn't enough, I think.

Agreed. There might be a benefit of having special handling of the literal case, though. Not
only because constant folding would move work from execution time to compile time, but also
because the meta-data for expressions like "UPPER('abc')" would stay the same as before, even
if the fix for the general case would set the precision to the maximum length of the data
type.

> What should happen if an UPPER(col) leads to column type overflow? The SQL standard says
"warning: truncation", cf. section 6.29 <string value function>, GR 6) g) ii).

Yes, that sounds reasonable.
                
> Precision for UPPER function is wrong if the returned value is longer than the literal
argument
> -----------------------------------------------------------------------------------------------
>
>                 Key: DERBY-5525
>                 URL: https://issues.apache.org/jira/browse/DERBY-5525
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.8.2.2
>            Reporter: Knut Anders Hatlen
>
> Seen in ij in a database with territory based collation and German locale:
> ==vv= COPIED FROM IJ CONSOLE =vv==
> ij> VALUES UCASE('Straßenbahn');
> 1
> -----------
> STRASSENBA&
> 1 Zeile ausgewählt
> ==================================
> And with JDBC calls:
>     Connection c = DriverManager.getConnection(
>             "jdbc:derby:memory:db;create=true;territory=de_DE;" +
>             "collation=TERRITORY_BASED");
>     Statement s = c.createStatement();
>     ResultSet rs = s.executeQuery("values upper('Straße')");
>     System.out.println(rs.getMetaData().getPrecision(1));
>     rs.next();
>     System.out.println(rs.getString(1));
> This prints
> 6
> STRASSE
> The precision is wrong, since the returned value is 7 characters long.

--
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