db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Christian d'Heureuse (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-1306) Support more than just varchar with vertical bars on query
Date Sat, 11 Nov 2006 22:35:39 GMT
    [ http://issues.apache.org/jira/browse/DERBY-1306?page=comments#action_12449042 ] 
            
Christian d'Heureuse commented on DERBY-1306:
---------------------------------------------

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.

> Support more than just varchar with vertical bars on query
> ----------------------------------------------------------
>
>                 Key: DERBY-1306
>                 URL: http://issues.apache.org/jira/browse/DERBY-1306
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.1.2.1
>         Environment: Relevant for this?  Windows XP
>            Reporter: Bill Robertson
>
> 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.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira

        

Mime
View raw message