[
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:
VALUES CAST(? AS MY_TYPE)
VALUES CAST(NULL AS MY_TYPE)
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;
S
---------------
abcdef
abc
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.
