db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Rick Hillegas (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (DERBY-6340) Add support for CREATE TYPE AS <existing type> (synonym types)
Date Tue, 01 Oct 2013 14:21:24 GMT

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

Mime
View raw message