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-651) Re-enable the storing of java objects in the database
Date Fri, 11 Dec 2009 12:20:18 GMT

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

Knut Anders Hatlen commented on DERBY-651:

Hi Rick,

The Behavior section in the func spec says:
> Casts - A UDT  cannot be cast explicitly to any other type. The converse is also true:
no other type can be cast to a UDT.

With the current code, casting from a UDT to CHAR/VARCHAR appears to work:

ij> create type java_string external name 'java.lang.String' language java;
0 rows inserted/updated/deleted
ij> create table t(s java_string);
0 rows inserted/updated/deleted
ij> insert into t values ('a');
1 row inserted/updated/deleted
ij> insert into t values ('abc');
1 row inserted/updated/deleted
ij> insert into t values ('abcdef');
1 row inserted/updated/deleted
ij> select cast(s as varchar(7)), length(s) from t;
1      |2          
a      |1          
abc    |3          
abcdef |6          

3 rows selected

Casting to a UDT, on the other hand, gives a syntax error:

ij> values cast('abc' as java_string);
ERROR 42X01: Syntax error: Encountered "" at line 1, column 22.

I think it makes sense that one cannot in general cast to a UDT, but in some special cases
it may make sense. For instance, it may be useful to allow casting of parameters (?) or NULLs
to a UDT. That is, queries like these:



Further, the Behavior section says:
> Comparisons - A UDT has no ordering. This means that you cannot compare and sort UDTs.
You cannot use them in expressions involving the <, =, >, IN, BETWEEN, and LIKE operators.
You cannot use UDTs in aggregates, DISTINCT expressions, and GROUP/ORDER BY clauses. You cannot
build indexes on them.

This is not how the current code behaves. Possibly because of implicit casts to string types?

ij> select distinct * from t where s like 'ab%' group by s order by s desc;

2 rows selected

> Re-enable the storing of java objects in the database
> -----------------------------------------------------
>                 Key: DERBY-651
>                 URL: https://issues.apache.org/jira/browse/DERBY-651
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>            Reporter: Rick Hillegas
>            Assignee: Rick Hillegas
>         Attachments: derby-651-01-aa-basicCreateDropType.diff, derby-651-02-af-udtColumnsRetvalsParams.diff,
derby-651-03-aa-udttestInstability.diff, derby-651-04-aa-javadoc.diff, derby-651-05-ac-dependencyTable.diff,
UserDefinedTypes.html, UserDefinedTypes.html, UserDefinedTypes.html, UserDefinedTypes.html
> Islay Symonette, in an email thread called "Storing Java Objects in a table" on October
26, 2005 requests the ability to store java objects in the database.
> Old releases of Cloudscape allow users to declare a column's type to be a Serializable
class. This feature was removed from Derby because the syntax was non-standard. However, most
of the machinery to support objects serialized to columns is still in Derby and is even used
in system tables. We need to agree on some standard syntax here and re-expose this useful
feature. Some subset of the ANSI adt syntax, cumbersome as it is, would do.

This message is automatically generated by JIRA.
You can reply to this email to add a comment to the issue online.

View raw message