Derby
  1. Derby
  2. DERBY-1306

Support more than just varchar with vertical bars on query

    Details

    • Type: Improvement Improvement
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 10.1.2.1
    • Fix Version/s: None
    • Component/s: SQL
    • Environment:
      Relevant for this? Windows XP
    • Urgency:
      Low

      Description

      I'm not sure if this is ij or derby proper, but the concat operator || should work for just about any data type. Derby is zero maintenance, but data rarely is, and a good sql interface is important. Often it's very convenient to write a query that generates other sql statements and that's difficult w/o concatination.

      e.g. create a table with a timestamp column, assume that the column name is originated.

      Try the following query:

      ij> select month(originated) || '-' || year(originated) from review;

      get the following error:

      ERROR 42846: Cannot convert types 'INTEGER' to 'VARCHAR'.

      The conversion to a string should pretty much work all the time unless the operand is some sort of blob type.

        Activity

        Hide
        Bernt M. Johnsen added a comment -

        The concatenation operator is in SQL 2003 defined

        In Ch 6.28 < string value expression>
        as
        <concatenation> ::= <character value expression> <concatenation operator> <character factor>
        or
        <blob concatenation> ::= <blob value expression> <concatenation operator> <blob factor>

        and in Ch. 6.35 <value array expression>
        as
        <array concatenation> ::= <array value expression 1> <concatenation operator> <array primary>

        Thus Derby behaves correctly wrt. the SQL specification.

        Show
        Bernt M. Johnsen added a comment - The concatenation operator is in SQL 2003 defined In Ch 6.28 < string value expression> as <concatenation> ::= <character value expression> <concatenation operator> <character factor> or <blob concatenation> ::= <blob value expression> <concatenation operator> <blob factor> and in Ch. 6.35 <value array expression> as <array concatenation> ::= <array value expression 1> <concatenation operator> <array primary> Thus Derby behaves correctly wrt. the SQL specification.
        Hide
        Christian d'Heureuse added a comment -

        The Derby documentation of the concatenation operator at http://db.apache.org/derby/docs/dev/ref/rrefsqlj40899.html states:
        "Because all built-in data types are implicitly converted to strings, this function can act on all built-in data types."

        This is currently not true. Values of type integer, which is a built-in data type, are not implicitly converted into strings with the concat operator.

        The SQL statement

          VALUES 'abc' || 123

        produces the error:

          Cannot convert types 'INTEGER' to 'VARCHAR'.

        The following statement works, but it's inconvenient to use RTRIM(CHAR()) for all non-string values:

          VALUES 'abc' || RTRIM(CHAR(123)) || 'xyz'

        Show
        Christian d'Heureuse added a comment - The Derby documentation of the concatenation operator at http://db.apache.org/derby/docs/dev/ref/rrefsqlj40899.html states: "Because all built-in data types are implicitly converted to strings, this function can act on all built-in data types." This is currently not true. Values of type integer, which is a built-in data type, are not implicitly converted into strings with the concat operator. The SQL statement   VALUES 'abc' || 123 produces the error:   Cannot convert types 'INTEGER' to 'VARCHAR'. The following statement works, but it's inconvenient to use RTRIM(CHAR()) for all non-string values:   VALUES 'abc' || RTRIM(CHAR(123)) || 'xyz'
        Hide
        Bernt M. Johnsen added a comment -

        This should probably be considered a doc bug?

        Show
        Bernt M. Johnsen added a comment - This should probably be considered a doc bug?
        Hide
        Bill Robertson added a comment -

        I don't think it's a bug at all. It's a request for a feature improvment. Even if it's not according to the sql standard I think that the current behavior violates the principle of least surprise, which is never a good thing.

        Show
        Bill Robertson added a comment - I don't think it's a bug at all. It's a request for a feature improvment. Even if it's not according to the sql standard I think that the current behavior violates the principle of least surprise, which is never a good thing.
        Hide
        Christian d'Heureuse added a comment -

        I found the following comment within org.apache.derby.impl.sql.compile.ConcatenationOperatorNode:

        /* If either the left or right operands are non-string, non-bit types,

        • then we generate an implicit cast to VARCHAR.
          */

        The concatenation operator probably worked in the documented way in a previous Derby version.

        The statement
        VALUES CAST(123 AS VARCHAR(10));
        Produces the same error:
        Cannot convert types 'INTEGER' to 'VARCHAR'.
        The statement
        VALUES CAST(123 AS CHAR(10));
        works (but produces trailing blanks).
        This is correct according to the documentation of CAST (see also DERBY-1804).
        But it's a bit strange that conversions from INTEGER to CHAR and even from VARCHAR to INTEGER are allowed, but the conversion from INTEGER to VARCHAR is not allowed.

        I don't understand why the conversion from INTEGER to VARCHAR is not supported. The class org.apache.derby.iapi.types.SQLVarchar inherits the setValue(int) method from SQLChar and there is nothing special about it:
        public void setValue(int theValue) throws StandardException

        { setValue(Integer.toString(theValue)); }

        Even DATE and TIME values can be converted to VARCHAR, so why not INTEGER?

        Another issue:
        Why does the JavaDoc of the ConcatenationOperatorNode class say: "This node represents a concatenation comparison operator"? The concatenation operator is not a comparison operator.

        Show
        Christian d'Heureuse added a comment - I found the following comment within org.apache.derby.impl.sql.compile.ConcatenationOperatorNode: /* If either the left or right operands are non-string, non-bit types, then we generate an implicit cast to VARCHAR. */ The concatenation operator probably worked in the documented way in a previous Derby version. The statement VALUES CAST(123 AS VARCHAR(10)); Produces the same error: Cannot convert types 'INTEGER' to 'VARCHAR'. The statement VALUES CAST(123 AS CHAR(10)); works (but produces trailing blanks). This is correct according to the documentation of CAST (see also DERBY-1804 ). But it's a bit strange that conversions from INTEGER to CHAR and even from VARCHAR to INTEGER are allowed, but the conversion from INTEGER to VARCHAR is not allowed. I don't understand why the conversion from INTEGER to VARCHAR is not supported. The class org.apache.derby.iapi.types.SQLVarchar inherits the setValue(int) method from SQLChar and there is nothing special about it: public void setValue(int theValue) throws StandardException { setValue(Integer.toString(theValue)); } Even DATE and TIME values can be converted to VARCHAR, so why not INTEGER? Another issue: Why does the JavaDoc of the ConcatenationOperatorNode class say: "This node represents a concatenation comparison operator"? The concatenation operator is not a comparison operator.

          People

          • Assignee:
            Unassigned
            Reporter:
            Bill Robertson
          • Votes:
            2 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

            • Created:
              Updated:

              Development