[
https://issues.apache.org/jira/browse/DERBY-6340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13782977#comment-13782977
]
Rick Hillegas commented on DERBY-6340:
--------------------------------------
Thanks for the second rev of the spec, Dyre. Some comments follow...
--------------------------------------------------------
I agree with your interpretation of the assignment behavior when storing a
distinct type in a column of the corresponding builtin type or vice versa: you
do NOT need an explicit cast. This agrees with DB2's interpretation of the spec
as described here:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_distincttypecomparisons.htm
So if you have the following distinct type definition...
CREATE TYPE ssn AS INTEGER
...then the following statements are fine as is and they do NOT need casts...
INSERT INTO t( intCol ) select ssnCol from s;
INSERT INTO s( ssnCol ) select intCol from s;
...but the following statements will raise assignment violations...
INSERT INTO t( bigintCol ) select ssnCol from s;
INSERT INTO s( ssnCol ) select bigintCol from s;
--------------------------------------------------------
However, I disagree with what the second rev of the spec says about comparisons
between distinct types and their corresponding builtin types. When comparing
distinct and builtin types (including constants), you DO NEED explicit casts.
The spec cites the SQL Standard, part 2, section 4.75 (User-defined type
comparison and assignment). That section describes the comparison of distinct
types to one another, but it does not describe the comparison of distinct types
to their corresponding builtin types. DB2 agrees that explicit casts are
needed:
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_distincttypecomparisons.htm
So given the type definition above, the following statements are ok...
SELECT * FROM t, s WHERE intCol = CAST( ssnCol AS INTEGER );
SELECT * FROM t, s WHERE ssnCol > CAST( intCol AS INTEGER );
...but the following statements raise datatype mismatch errors...
SELECT * FROM t, s WHERE intCol = ssnCol;
SELECT * FROM t, s WHERE ssnCol > intCol;
--------------------------------------------------------
In addition, I think that the spec needs to talk about the implicitly created
functions which are used by the implicit assignment casts and the explicit
comparison casts. According to the SQL Standard, part 2, section 11.51
(user-defined type definition), general rule 2.b, the declaration of a distinct
type implicitly creates the following functions...
CREATE FUNCTION builtinTypeName( a distinctTypeName ) returns builtinType ...
DETERMINISTIC...
CREATE FUNCTION distinctTypeName( a builtinType ) returns distinctTypeName
... DETERMINISTIC...
...where builtinType is the name of the Derby builtin type declared in the
CREATE TYPE statement and builtinTypeName is the corresponding abbreviated form
of that name as listed in the SQL Standard, part 2, section 9.9 (Type name
determination). So, for instance, the following type definition...
CREATE TYPE featureBits AS VARCHAR( 128 ) FOR BIT DATA;
...implicitly creates the following coercion functions...
CREATE FUNCTION featureBits( inValue VARCHAR( 128 ) FOR BIT DATA ) RETURNS
featureBits ... DETERMINISTIC...
CREATE FUNCTION varbinary( inValue featureBits ) RETURNS VARCHAR( 128 ) FOR
BIT DATA ... DETERMINISTIC...
...which can then be invoked as follows...
INSERT INTO t( varbinaryCol ) SELECT varbinary( featureBitsCol ) FROM s;
INSERT INTO s( featureBitsCol ) SELECT featureBits( varbinaryCol ) FROM t;
--------------------------------------------------------
I think that the spec should say something about the namespace of distinct
types:
o The non-schema-qualified name of a distinct type may not collide with the
name of a builtin type.
o The non-schema-qualified name of a distinct type may not collide with the
name of a builtin function or builtin aggregate. This is because of the
implicitly created coercion functions.
o The schema-qualified name of a distinct type may not collide with the
schema-qualified name of a Serializable Java UDT.
o The schema-qualified name of a distinct type may not collide with the
schema-qualified name of a user-defined function or user-defined aggregate.
These namespace restrictions will need to be documented in the following
Reference Manual sections:
o CREATE DERBY AGGREGATE statement
o CREATE FUNCTION statement
o CREATE TYPE statement
--------------------------------------------------------
Concerning the implementation note in the System Tables section: You're in
luck. UDTAliasInfo has a version number in it. So you can use it to store the
extra bits needed by distinct types. All UDTAliasInfos whose version number is
0 can be assumed to be the old-style aliases for Serializable Java UDTs. If the
version number is 1 or higher, then an extra boolean (or some other state) can
distinguish the two kinds of type aliases.
Thanks!
-Rick
> Add support for CREATE TYPE AS <existing type> (synonym types)
> --------------------------------------------------------------
>
> Key: DERBY-6340
> URL: https://issues.apache.org/jira/browse/DERBY-6340
> Project: Derby
> Issue Type: Improvement
> Components: SQL
> Reporter: Dyre Tjeldvoll
> Assignee: Dyre Tjeldvoll
> Attachments: CreateTypeAs_fs_draft_2.html, CreateTypeAs_fs_draft.html
>
>
> The SQL standard (2003) chapter 11.41 <user-defined type definition> allows
> the creation of synonyms or aliases for an existing type: CREATE TYPE AS
> <predefined type>. By allowing this in Derby we would simplify migration
> from, and interoperation with, other databases.
--
This message was sent by Atlassian JIRA
(v6.1#6144)
