Details

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

      Description

      I would like to collect here the arguments for and against re-enabling the TINYINT datatype. Once this discussion calms down, we can schedule a vote on the issue.

      Background: Cloudscape used to support the TINYINT datatype, which was an 8 bit int. This datatype was hidden from customers as part of an effort to remove all datatypes not supported by DB2. Re-enabling the datatype would not require a lot of effort. Some arguments for and against re-enabling this datatype can be found on the November 2005 email thread titled "New features for next release .... (Was: Grant and Revoke ... DERBY-464...)".

      Here are the arguments in favor so far:

      + This datatype is defined by one of our key standards, JDBC. It is in JDBC 2, 3, and 4, all of the JDBC revs supported by Derby 10.2.
      + This datatype is supported by some important databases, including MySQL, Microsoft SQL Server, and Sybase.

      Here are the arguments against so far:

      • This datatype is not defined by our other key standard, ANSI SQL. Here our two main standards diverge.
      • This datatype is not supported by some important databases, including Oracle, DB2, and (some) Informix databases.

      Against this proposal, it was also argued that there was some sort of friction with ODBC. I do not understand this argument: SQL_TINYINT is an ODBC datatype. See http://msdn.microsoft.com/library/default.asp?url=/library/en-us/odbc/htm/odbcsql_data_types.asp.

      A friction with .NET was also suggested but I don't understand this either. "byte" and "Sbyte" are the .NET 8-bit integer types. See http://www.codersource.net/csharp_tutorial_data_types.html.

      A friction with Perl was also suggested but I don't understand this either.

        Activity

        Hide
        Rick Hillegas added a comment -

        I'm sorry for garbling your argument. I share your feeling that where ANSI does propose solutions, we should consider them carefully. Merely relying on ANSI though, we would not have indexes or system procedures. We have not recently felt constrained by the fact that ANSI omits features. If you are willing, I would like to explore why TINYINT should have to sustain an attack not mounted against, say, optimizer hints.

        The 8-bit integer is a useful feature, as evidenced by its presence in Java, C#, JDBC, ODBC, DRDA, and several major databases.

        Show
        Rick Hillegas added a comment - I'm sorry for garbling your argument. I share your feeling that where ANSI does propose solutions, we should consider them carefully. Merely relying on ANSI though, we would not have indexes or system procedures. We have not recently felt constrained by the fact that ANSI omits features. If you are willing, I would like to explore why TINYINT should have to sustain an attack not mounted against, say, optimizer hints. The 8-bit integer is a useful feature, as evidenced by its presence in Java, C#, JDBC, ODBC, DRDA, and several major databases.
        Hide
        Daniel John Debrunner added a comment -

        We should be clear that the JDBC spec defines JDBC Types and the SQL standard defines SQL types.

        Thus they are not really in conflict, here, just defining types in different domains.

        A JDBC driver implementation defines how it maps SQL types to JDBC types, I believe there is nothing violated by either standard by having a JDBC Type TINYINT being mapped to the SQL type SMALLINT for example.

        Informix 9.2 according to this JDBC guide maps SMALLINT to TINYINT.

        http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html

        This mapping is reflected in the data returned by DatabaseMetaData.getTypeInfo

        On an implementation note, the link above says TINYINT is valued 0-255 signed or unsigned, I do not believe that is what was implemented by Cloudscape, or any existing code in Derby. I seem to remember Cloudscape used a Java byte which holds values -128 to 127, thus its a good thing the TINYINT type was removed/disabled because it was non-standard on several fronts.

        Show
        Daniel John Debrunner added a comment - We should be clear that the JDBC spec defines JDBC Types and the SQL standard defines SQL types . Thus they are not really in conflict, here, just defining types in different domains. A JDBC driver implementation defines how it maps SQL types to JDBC types, I believe there is nothing violated by either standard by having a JDBC Type TINYINT being mapped to the SQL type SMALLINT for example. Informix 9.2 according to this JDBC guide maps SMALLINT to TINYINT. http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/mapping.html This mapping is reflected in the data returned by DatabaseMetaData.getTypeInfo On an implementation note, the link above says TINYINT is valued 0-255 signed or unsigned, I do not believe that is what was implemented by Cloudscape, or any existing code in Derby. I seem to remember Cloudscape used a Java byte which holds values -128 to 127, thus its a good thing the TINYINT type was removed/disabled because it was non-standard on several fronts.
        Hide
        Satheesh Bandaram added a comment -

        I wasn't saying Derby shouldn't have TINYINT because it causes problems for ODBC or .NET or perl...Not sure where that was implied.

        I had a comment about, in general, what Derby should do in case of a conflict between SQL standard specification Vs what JDBC spec. I was only saying, in my opinion, Derby might put more weigh on SQL specification. This part of the comment had NOTHING to do with TINYINT issue...

        Show
        Satheesh Bandaram added a comment - I wasn't saying Derby shouldn't have TINYINT because it causes problems for ODBC or .NET or perl...Not sure where that was implied. I had a comment about, in general, what Derby should do in case of a conflict between SQL standard specification Vs what JDBC spec. I was only saying, in my opinion, Derby might put more weigh on SQL specification. This part of the comment had NOTHING to do with TINYINT issue...

          People

          • Assignee:
            Unassigned
            Reporter:
            Rick Hillegas
          • Votes:
            2 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:

              Development