Rick, Dan had also brought up this point. Dan's comment from *
http://www.nabble.com/Collation-feature-discussion-p9526316.html*
"This approach means that CHAR(varchar_col, 20) behaves differently to CAST
(varchar_col AS CHAR(20)). Not sure if that's good or bad, but they might be
implemented today using the same code path. "

I think what you are proposing will be easier to implement and easier to
explain to the users and fits in the SQL spec model. I wasn't trying to
solve any paritcular scenario but was just trying to make CHAR work like
TRIM when a character string type was it's first parameter. If no objections
by the end of the day, then I will go ahead and change the wiki page for
CHAR/VARCHAR functions to have the same collation as current schema's
character set no matter what kind of parameter is passed to it.


On 4/3/07, Rick Hillegas <Richard.Hillegas@sun.com> wrote:
>
> Hi Mamta,
>
> Thanks for describing this behavior on a tidy wiki page. Having all of
> this material collected in one place is great. I have a comment:
>
> 6)CHAR, VARCHAR functions do not look like they are defined in the SQL
> spec. But based on 5) above, the result character string type's
> collation can be considered same as the first argument's collation type
> if the first argument to CHAR/VARCHAR function is a character string
> type. If the first argument is not character string type, then the
> result character string of CHAR/VARCHAR will have the same collation as
> current schema's character set. The collation derivation will be implicit.
>
> I think the behavior would be easier to understand if it were uniform,
> that is, if the CHAR and VARCHAR operators always returned strings which
> had the collation of the current schema. I suspect you will find that
> this is easier to implement. I also think that this is the intention of
> the SQL Standard. Here is my reasoning:
>
> It seems to me that there is a default (implementation-defined)
> character set and collation for the whole database. That default can be
> overridden at the session, schema, and client-module levels. That is,
> once you know what database, session, schema, and client-module you are
> in, you know the default character set and collation for string
> datatypes mentioned by your SQL statements. This default can be
> explicitly overridden with a CAST or COLLATE clause. There are also
> explicit exceptions to this behavior for certain operators ( e.g., TRIM,
> UPPER, LOWER, SUBSTR). The default character set and collation apply
> unless the SQL Standard explicitly defines an exception or your
> statement explicitly overrides the default. The default character set
> and collation apply to the return types of the CHAR and VARCHAR
> operators because the SQL Standard does not carve out an explicit
> exception for these operators.
>
> Is there some problem that would be solved by adopting the non-uniform
> behavior proposed on the wiki page?
>
> Thanks,
> -Rick
>
> Mamta Satoor wrote:
> > Hi,
> >
> > I have created a wiki page for DERBY-1478 : Add built in language
> > based ordering and like processing to Derby
> >
> > The wiki page is located at
> > http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478and
> > it includes the current design proposal along with line items. If
> > anyone has any comments, please let me know.
> >
> > thanks,
> > Mamta
>
>