Derby
  1. Derby
  2. DERBY-6340

Add support for CREATE TYPE AS <existing type> (synonym types)

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      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.

      1. CreateTypeAs_fs_draft.html
        11 kB
        Dyre Tjeldvoll
      2. CreateTypeAs_fs_draft_2.html
        20 kB
        Dyre Tjeldvoll
      3. CreateTypeAs_fs_draft_3.html
        27 kB
        Dyre Tjeldvoll
      4. CreateTypeAs_fs_draft_3.html
        27 kB
        Dyre Tjeldvoll

        Activity

        Hide
        Dyre Tjeldvoll added a comment -

        Attaching a first draft of the functional specification.

        Show
        Dyre Tjeldvoll added a comment - Attaching a first draft of the functional specification.
        Hide
        Rick Hillegas added a comment -

        Thanks for the first rev of this functional spec, Dyre. A couple comments follow:

        o In several places you say that Derby's existing CREATE TYPE language is non-standard. It seems to me that it conforms to part 13 of the SQL Standard (SQL/JRT), section 9.4 (user defined type). Can you help me understand why you think that the existing Derby CREATE TYPE language falls outside the standard?

        o Under "New Keywords" you say that this spec introduces no new keywords. However, right now it does introduce the FINAL keyword, which is listed as a non-reserved keyword in the 2011 version of the Standard, part 2, section 5.2 (token and separator). FINAL does not appear in Derby's reserved or non-reserved keyword lists right now. It seems to me that the spec could finesse this issue by not introducing FINAL at all. The spec only allows for FINAL types and that is the default finality of a user-defined type. The FINAL keyword can therefore be omitted.

        o Under "New CREATE TYPE Statement Syntax" you present a grammar with many sections in light-gray. Does light gray mean that those parts of the grammar will not be implemented?

        o It would be good if that section also clarified that you can't specify any of the user-defined type options more than once.

        o It would be good if the spec described the effect of the cast-to-distinct and cast-to-source clauses. It is my understanding that they declare names for functions which cast between the distinct type and its underlying predefined type. It would be good to see a description of the comparison and casting behavior of distinct types and how these functions would be used.

        o But much of the preceding comments may be moot if you take the following advice. It's your judgment call, but I think that most of the value of this language is supplied by the following simplified syntax. This would reduce the size of this feature. That is, you could omit the implicit FINAL clause (which is a NOP) and the casting clauses (which I don't think will be used widely):

        CREATE TYPE [ schemaName. ] typeName AS builtinType

        o There's a little more to say about DatabaseMetaData, so I recommend moving that material out of the "Public API" section into a section devoted to DatabaseMetaData. In particular, DatabaseMetaData.getTypeInfo() will need to return an additional row stating that Derby supports DISTINCT types (see the javadoc for that method).

        o Under "System Tables": I think that you should be able to hide all of the details of the distinct type inside the Formatable AliasInfo object stored in SYS.SYSALIASES.ALIASINFO. You may find it is convenient to declare a new NAMESPACE constant, but I'm cautiously hopeful that won't be necessary. Due to the way the code is structured, introducing a new NAMESPACE constant will introduce more cases in the switch statements which already handle the alias descriptors.

        o Because you will have to extend UDTAliasInfo or introduce a new DistinctAliasInfo class, there will be changes to the system catalogs. So this feature can only be enabled after hard-upgrade.

        o However, I don't see any backward compatibility issues.

        o Under "Documentation", you will want to note that changes will need to be made to the Reference Manual sections on the CAST operator and on "Data type assignments and comparison, sorting, and ordering".

        Thanks!
        -Rick

        Show
        Rick Hillegas added a comment - Thanks for the first rev of this functional spec, Dyre. A couple comments follow: o In several places you say that Derby's existing CREATE TYPE language is non-standard. It seems to me that it conforms to part 13 of the SQL Standard (SQL/JRT), section 9.4 (user defined type). Can you help me understand why you think that the existing Derby CREATE TYPE language falls outside the standard? o Under "New Keywords" you say that this spec introduces no new keywords. However, right now it does introduce the FINAL keyword, which is listed as a non-reserved keyword in the 2011 version of the Standard, part 2, section 5.2 (token and separator). FINAL does not appear in Derby's reserved or non-reserved keyword lists right now. It seems to me that the spec could finesse this issue by not introducing FINAL at all. The spec only allows for FINAL types and that is the default finality of a user-defined type. The FINAL keyword can therefore be omitted. o Under "New CREATE TYPE Statement Syntax" you present a grammar with many sections in light-gray. Does light gray mean that those parts of the grammar will not be implemented? o It would be good if that section also clarified that you can't specify any of the user-defined type options more than once. o It would be good if the spec described the effect of the cast-to-distinct and cast-to-source clauses. It is my understanding that they declare names for functions which cast between the distinct type and its underlying predefined type. It would be good to see a description of the comparison and casting behavior of distinct types and how these functions would be used. o But much of the preceding comments may be moot if you take the following advice. It's your judgment call, but I think that most of the value of this language is supplied by the following simplified syntax. This would reduce the size of this feature. That is, you could omit the implicit FINAL clause (which is a NOP) and the casting clauses (which I don't think will be used widely): CREATE TYPE [ schemaName. ] typeName AS builtinType o There's a little more to say about DatabaseMetaData, so I recommend moving that material out of the "Public API" section into a section devoted to DatabaseMetaData. In particular, DatabaseMetaData.getTypeInfo() will need to return an additional row stating that Derby supports DISTINCT types (see the javadoc for that method). o Under "System Tables": I think that you should be able to hide all of the details of the distinct type inside the Formatable AliasInfo object stored in SYS.SYSALIASES.ALIASINFO. You may find it is convenient to declare a new NAMESPACE constant, but I'm cautiously hopeful that won't be necessary. Due to the way the code is structured, introducing a new NAMESPACE constant will introduce more cases in the switch statements which already handle the alias descriptors. o Because you will have to extend UDTAliasInfo or introduce a new DistinctAliasInfo class, there will be changes to the system catalogs. So this feature can only be enabled after hard-upgrade. o However, I don't see any backward compatibility issues. o Under "Documentation", you will want to note that changes will need to be made to the Reference Manual sections on the CAST operator and on "Data type assignments and comparison, sorting, and ordering". Thanks! -Rick
        Hide
        Dyre Tjeldvoll added a comment -

        Hi Rick, thanks for the feedback. I'll incorporate your suggestions into a new rev of the fs.

        Wrt. your questions: I'm not very familiar with various parts of the sql standard so I should probably be more careful about what I assert. So when I said that the exiting UDTs are non-standard, I really meant to say that I cannot see that the grammar, as specified in Part 2, section 11.51 <user-defined type definition> permits the current CREATE TYPE syntax. I'll have a look at SQL/JRT.

        Wrt. FINAL I agree that it is meaningless for distinct types, but as I read the grammar it is nevertheless permitted. I had not considered the fact that FINAL is not currently a reserved word. If we need to add it as a reserved word just to allow this syntax, then I agree that it makes sense to omit it.

        Wrt. grammar rules in light gray. It really means that those clauses are not permitted because of the rule mentioned in the comment. Or that was the intention, I see now that I have forgotten to update the first grammar snippet:
        [ <user-defined type option list> ] – S028
        is incorrectly gray, but should have been green.

        [ <method specification list> ] – S023

        is supposed to be gray, but the comment should have been – CR 12
        Because conformance rule 12) of 11.51 states: "Without Feature S023, “Basic structured types”, conforming SQL language shall not contain a <method specification list>."

        But yes, it also implies that they will not be implemented.

        Show
        Dyre Tjeldvoll added a comment - Hi Rick, thanks for the feedback. I'll incorporate your suggestions into a new rev of the fs. Wrt. your questions: I'm not very familiar with various parts of the sql standard so I should probably be more careful about what I assert. So when I said that the exiting UDTs are non-standard, I really meant to say that I cannot see that the grammar, as specified in Part 2, section 11.51 <user-defined type definition> permits the current CREATE TYPE syntax. I'll have a look at SQL/JRT. Wrt. FINAL I agree that it is meaningless for distinct types, but as I read the grammar it is nevertheless permitted. I had not considered the fact that FINAL is not currently a reserved word. If we need to add it as a reserved word just to allow this syntax, then I agree that it makes sense to omit it. Wrt. grammar rules in light gray. It really means that those clauses are not permitted because of the rule mentioned in the comment. Or that was the intention, I see now that I have forgotten to update the first grammar snippet: [ <user-defined type option list> ] – S028 is incorrectly gray, but should have been green. [ <method specification list> ] – S023 is supposed to be gray, but the comment should have been – CR 12 Because conformance rule 12) of 11.51 states: "Without Feature S023, “Basic structured types”, conforming SQL language shall not contain a <method specification list>." But yes, it also implies that they will not be implemented.
        Hide
        Mamta A. Satoor added a comment -

        Dyre, thanks for working on this feature. I looked through the SQL spec and the proposed syntax in functional spec indeed adhers to the SQL Spec. Thanks for including the section numbers, it made it much easier to find the relevant info. As for the upgrade, I agree that this functionality should be available only after hard upgrade because system table will have additional information which will not be recognized by the prior releases. Also, like Rick mentioned, can you share more information on the casting part of the syntax. Additionally, some complete examples with how to declare a type and how it will be used will be nice.

        Show
        Mamta A. Satoor added a comment - Dyre, thanks for working on this feature. I looked through the SQL spec and the proposed syntax in functional spec indeed adhers to the SQL Spec. Thanks for including the section numbers, it made it much easier to find the relevant info. As for the upgrade, I agree that this functionality should be available only after hard upgrade because system table will have additional information which will not be recognized by the prior releases. Also, like Rick mentioned, can you share more information on the casting part of the syntax. Additionally, some complete examples with how to declare a type and how it will be used will be nice.
        Hide
        Mamta A. Satoor added a comment -

        For example, if my understanding is clear. one can specify
        CREATE TYPE SHOW_SIZE AS INT;
        but there is no way to enforce say that the allowed values for SHOE_SIZE is only 1 through 22.

        Show
        Mamta A. Satoor added a comment - For example, if my understanding is clear. one can specify CREATE TYPE SHOW_SIZE AS INT; but there is no way to enforce say that the allowed values for SHOE_SIZE is only 1 through 22.
        Hide
        Dyre Tjeldvoll added a comment -

        Thanks for Rick and Mamta, for your comments.
        Attaching a new rev of the fs.

        Show
        Dyre Tjeldvoll added a comment - Thanks for Rick and Mamta, for your comments. Attaching a new rev of the fs.
        Hide
        Rick Hillegas added a comment -

        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

        Show
        Rick Hillegas added a comment - 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
        Hide
        Dyre Tjeldvoll added a comment -

        Hi Rick, thank you for your comments.

        I agree that the std does not mention comparison of distinct types to their STs, so that a cast is required in this case. I have updated the fs accordingly. I have also added a description of the the default identifiers for the casting functions.

        When it comes to the namespace of distinct UDT, I wondered if you could clear up a few things:

        My understanding of Part 2, section 11.51 (user-defined type definition), general rule 2.b, is that the casting functions are created in the explicit or implied schema of the UDT. "CREATE FUNCTION SN.FNUDT ( ... where: SN is the explicit or implicit <schema name> of UDTN". Is DERBY-5901 the reason why the name must not conflict with a builtin function or aggregate?

        A couple of observations:

        • Without the ability to specify <cast to source> you can create at most one alias for each builtin type in the same schema?
        • Due to DERBY-5901, we must either always specify <cast to source> or let the default identifier be non-standard as the default names mandated by the standard are already used by existing builtin functions in Derby?

        Do you agree?

        Show
        Dyre Tjeldvoll added a comment - Hi Rick, thank you for your comments. I agree that the std does not mention comparison of distinct types to their STs, so that a cast is required in this case. I have updated the fs accordingly. I have also added a description of the the default identifiers for the casting functions. When it comes to the namespace of distinct UDT, I wondered if you could clear up a few things: My understanding of Part 2, section 11.51 (user-defined type definition), general rule 2.b, is that the casting functions are created in the explicit or implied schema of the UDT. "CREATE FUNCTION SN.FNUDT ( ... where: SN is the explicit or implicit <schema name> of UDTN". Is DERBY-5901 the reason why the name must not conflict with a builtin function or aggregate? A couple of observations: Without the ability to specify <cast to source> you can create at most one alias for each builtin type in the same schema? Due to DERBY-5901 , we must either always specify <cast to source> or let the default identifier be non-standard as the default names mandated by the standard are already used by existing builtin functions in Derby? Do you agree?
        Hide
        Rick Hillegas added a comment -

        Hi Dyre,

        Thanks for continuing to button-down this spec. Some responses follow:

        > Is DERBY-5901 the reason why the name must not conflict with a builtin
        > function or aggregate?

        Yes, that's right.

        > Without the ability to specify <cast to source> you can create at
        > most one alias for each builtin type in the same schema?

        That's not my reading. The Standard lets you create multiple routines with the same schema-qualified name provided that their argument signatures are different. Derby does not support this flexibility with user-created routines, but that's a limitation we could lift with a little work. For the SQL Standard rules, see part 2, section 4.28; in the 2011 edition, the relevant paragraph is the last paragraph on page 98.

        > Due to DERBY-5901, we must either always specify
        > <cast to source> or let the default identifier be non-standard as
        > the default names mandated by the standard are already
        > used by existing builtin functions in Derby?

        Again, that's not my reading. I don't think that we need the <cast to source> clause. The different distinct types will implicitly create <cast to source> functions which have the same name but different argument signatures.

        Thanks!
        -Rick

        Show
        Rick Hillegas added a comment - Hi Dyre, Thanks for continuing to button-down this spec. Some responses follow: > Is DERBY-5901 the reason why the name must not conflict with a builtin > function or aggregate? Yes, that's right. > Without the ability to specify <cast to source> you can create at > most one alias for each builtin type in the same schema? That's not my reading. The Standard lets you create multiple routines with the same schema-qualified name provided that their argument signatures are different. Derby does not support this flexibility with user-created routines, but that's a limitation we could lift with a little work. For the SQL Standard rules, see part 2, section 4.28; in the 2011 edition, the relevant paragraph is the last paragraph on page 98. > Due to DERBY-5901 , we must either always specify > <cast to source> or let the default identifier be non-standard as > the default names mandated by the standard are already > used by existing builtin functions in Derby? Again, that's not my reading. I don't think that we need the <cast to source> clause. The different distinct types will implicitly create <cast to source> functions which have the same name but different argument signatures. Thanks! -Rick
        Hide
        Knut Anders Hatlen added a comment -

        DERBY-5901 might not be an issue for the implicitly created functions after all. The built-in conversion functions (INT, CHAR, DOUBLE, ...) have special handling in the parser, so they don't behave like the SIN function that's used in the example in DERBY-5901.

        Since the names of the built-in types are reserved keywords, user-defined functions with the same name as a built-in type need to be quoted in order to get CREATE FUNCTION to accept them. Conversely, when invoking a function with the same name as a built-in type, the parser always knows whether to use the built-in one (if the function name is a keyword) or look for a user-defined one (if the function name is an identifier).

        That is, VALUES INT(1) will always invoke the built-in function, regardless of the existence of a user-defined function with the same name in the current schema. And VALUES "INT"(1) will never invoke the built-in function, as the function is referenced with an identifier rather than a keyword, regardless of the existence of a user-defined function with that name.

        So given the statement

        CREATE TYPE SSN AS INT

        a new function called "INT" will be created in the current schema. But its existence will not affect any existing callers of the INT built-in conversion function, I believe.

        That doesn't help with the other issue, though. If you create a second type that also maps to INT, such as CREATE SHOE_SIZE AS INT, it will attempt to create yet another "INT" function, and Derby doesn't support that kind of overloading yet.

        In short, I think that means we could allow CREATE TYPE AS statements without <cast to source> without worrying about DERBY-5901. But if we create a second type in the same schema with the same source type, that second CREATE TYPE AS statement must include <cast to source> and specify a non-conflicting name as long as function overloading isn't implemented.

        Show
        Knut Anders Hatlen added a comment - DERBY-5901 might not be an issue for the implicitly created functions after all. The built-in conversion functions (INT, CHAR, DOUBLE, ...) have special handling in the parser, so they don't behave like the SIN function that's used in the example in DERBY-5901 . Since the names of the built-in types are reserved keywords, user-defined functions with the same name as a built-in type need to be quoted in order to get CREATE FUNCTION to accept them. Conversely, when invoking a function with the same name as a built-in type, the parser always knows whether to use the built-in one (if the function name is a keyword) or look for a user-defined one (if the function name is an identifier). That is, VALUES INT(1) will always invoke the built-in function, regardless of the existence of a user-defined function with the same name in the current schema. And VALUES "INT"(1) will never invoke the built-in function, as the function is referenced with an identifier rather than a keyword, regardless of the existence of a user-defined function with that name. So given the statement CREATE TYPE SSN AS INT a new function called "INT" will be created in the current schema. But its existence will not affect any existing callers of the INT built-in conversion function, I believe. That doesn't help with the other issue, though. If you create a second type that also maps to INT, such as CREATE SHOE_SIZE AS INT , it will attempt to create yet another "INT" function, and Derby doesn't support that kind of overloading yet. In short, I think that means we could allow CREATE TYPE AS statements without <cast to source> without worrying about DERBY-5901 . But if we create a second type in the same schema with the same source type, that second CREATE TYPE AS statement must include <cast to source> and specify a non-conflicting name as long as function overloading isn't implemented.
        Hide
        Dyre Tjeldvoll added a comment -

        Thanks Rick and Knut for helping me understand the details here.

        Wrt. to implementing signature overloading as described in 2-4.28: Looking at the rules in
        2-9.6 SR 1)-7) and from there 2-9.7 SR 1)-18); IMHO it would be good to track this work in a separate Jira issue.

        Show
        Dyre Tjeldvoll added a comment - Thanks Rick and Knut for helping me understand the details here. Wrt. to implementing signature overloading as described in 2-4.28: Looking at the rules in 2-9.6 SR 1)-7) and from there 2-9.7 SR 1)-18); IMHO it would be good to track this work in a separate Jira issue.
        Hide
        Dyre Tjeldvoll added a comment -

        Uploading yet another preliminary draft. The issue of whether the default <cast to source> identifiers are quoted or not is left open.

        Show
        Dyre Tjeldvoll added a comment - Uploading yet another preliminary draft. The issue of whether the default <cast to source> identifiers are quoted or not is left open.
        Hide
        Dyre Tjeldvoll added a comment -

        Clarification from Fred Zemke who is a member of the SQL standard committee:

        > The question is: since INTEGER is a reserved word, do I need to
        > double-quote it when I use it in queries, or can I omit the double quotes?
        strictly speaking, yes
        > That is, is the following legal:
        >
        > SET SCHEMA MYSCHEMA;
        > SELECT * FROM T WHERE INTEGER( SSN_COL )> INT_COL;
        >
        > ...or do I need to double-quote the function name:
        >
        > SET SCHEMA MYSCHEMA;
        > SELECT * FROM T WHERE "INTEGER"( SSN_COL )> INT_COL;
        >
        > I see that DB2 does not require double-quotes and that is certainly
        > more convenient for the user:
        I agree. I think it is a good idea to permit this usage. The double
        quotes would be required strictly speaking
        for portability. Syntax Rules are a burden on the user who wants
        portability. They are not a limitation
        on what the product can also support.

        Show
        Dyre Tjeldvoll added a comment - Clarification from Fred Zemke who is a member of the SQL standard committee: > The question is: since INTEGER is a reserved word, do I need to > double-quote it when I use it in queries, or can I omit the double quotes? strictly speaking, yes > That is, is the following legal: > > SET SCHEMA MYSCHEMA; > SELECT * FROM T WHERE INTEGER( SSN_COL )> INT_COL; > > ...or do I need to double-quote the function name: > > SET SCHEMA MYSCHEMA; > SELECT * FROM T WHERE "INTEGER"( SSN_COL )> INT_COL; > > I see that DB2 does not require double-quotes and that is certainly > more convenient for the user: I agree. I think it is a good idea to permit this usage. The double quotes would be required strictly speaking for portability. Syntax Rules are a burden on the user who wants portability. They are not a limitation on what the product can also support.
        Hide
        Dyre Tjeldvoll added a comment -

        New rev of the spec.

        Show
        Dyre Tjeldvoll added a comment - New rev of the spec.
        Hide
        Rick Hillegas added a comment -

        Thanks for the new rev of the spec, Dyre. For the record, I'm in favor of the following choices:

        o Don't bother adding the FINAL keyword.

        o Don't bother supporting the <cast to distinct> and <cast to source> clauses.

        o Allow using builtin type keywords without double quotes when invoking the implicit coercion functions. E.g.:

        SELECT * FROM employees WHERE INTEGER(ssn) > 42;

        Thanks,
        -Rick

        Show
        Rick Hillegas added a comment - Thanks for the new rev of the spec, Dyre. For the record, I'm in favor of the following choices: o Don't bother adding the FINAL keyword. o Don't bother supporting the <cast to distinct> and <cast to source> clauses. o Allow using builtin type keywords without double quotes when invoking the implicit coercion functions. E.g.: SELECT * FROM employees WHERE INTEGER(ssn) > 42; Thanks, -Rick

          People

          • Assignee:
            Dyre Tjeldvoll
            Reporter:
            Dyre Tjeldvoll
          • Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

            • Created:
              Updated:

              Development