db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (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 16:31:39 GMT

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

Rick Hillegas commented on DERBY-5525:
--------------------------------------

Thanks, Dag. I see your point about generating a truncation warning for the compile-time constant
case. Agreed.

I think that we treat lcase() and ucase() as SQL operators (not defined in the Standard) rather
than SQL functions. As operators, lcase() and ucase() don't live in a schema, unlike functions.
Derby's lcase() and ucase() expressions have variable data type, depending on the type of
their arguments. In contrast, if they were SQL functions, they would have fixed return types
and fixed argument types. I suppose we could model lcase() and ucase() as functions with thousands
of overloads registered in every schema. The implications for DatabaseMetaData.getFunctions()/getFunctionColumns()
would be startling!

We also re-purpose lcase() and ucase() as JDBC escape functions. So they can be invoked like
this too:

  values ( {fn ucase( 'abc' ) } );

Appendix D of the JDBC spec partially describes the behavior of these escape functions. I
don't think that the JDBC spec provides any guidance on the data type issues we're puzzling
through here.

I guess what I'm saying is that lcase() and ucase() look to me like Derby extensions to the
SQL language. Trying to fit them into the SQL Standard by modelling them as vendor-supplied
functions doesn't make much sense to me.

Thanks,
-Rick
                
> 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