db-derby-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Apache Wiki <wikidi...@apache.org>
Subject [Db-derby Wiki] Update of "BuiltInLanguageBasedOrderingDERBY-1478" by MamtaSatoor
Date Sun, 01 Apr 2007 01:51:13 GMT
Dear Wiki user,

You have subscribed to a wiki page or wiki category on "Db-derby Wiki" for change notification.

The following page has been changed by MamtaSatoor:
http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478

------------------------------------------------------------------------------
  3)Collation derivation will be none if the aggregate methods are dealing with character
strings with different collations (Section 9.3 Data types of results of aggregations Syntax
Rule 3aii). 
  
  Following lists the collation type association for various occurences of character string
types and for operations involving character string types in Derby 10.3
+ 
  1)String literal - USER will be the character set of string literals. And collation type
will be the default collation of USER which can be UCS_BASIC/TERRITORY_BASED. The collation
derivation will be implicit.
+ 
  2)<column definition> (of character string type) SQL spec Section 11.1 <schema
definition>, General Rule 3 specifies that the character set associated with schema is
used as the default character set for all <column definitions>. Based on this, character
columns in user tables will have USER character set associated with them and hence collation
type of UCS_BASIC/TERRITORY_BASED. The collation derivation will be implicit. 
+ 
  3)<character string type> (SQL spec section 6.1 <data type> Syntax Rule 3b and
16) - Rule 3b says that collation type of character string type is the character set's collation
AND rule 16 says that if <character string type> is not contained in a <column definition>,
then an implementation-defined character set is associated with the <character string type>.
We define Derby's implementation-defined character set for such <character string type>
to be current schema's character set. The collation derivation will be implicit. 
+ 
  4)<cast specification> Section 6.12 Syntax Rule 10 says that if the target data type
of CAST is character string type, then collation type of result character string of CAST specification
is the collation of the character set of the target data type. Based on 3) above, that will
be the collation of the current schema's character set. The collation derivation will be implicit.

+ 
  5)Result character string types from UPPER, LOWER, TRIM(LTRIM, RTRIM), SUBSTR will have
the same collation as their operand. This comes from SQL spec Section 6.29 <string value
function> Syntax Rules 8, 8, 11d, 4 respectively). The collation derivation will be implicit.

+ 
  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.
+ 
  7)For user defined functions' that return character string type, the return type's collation
will have the same collation as current schema's character set. The collation derivation will
be implicit. 
+ 
  8)JDBC parameters (ie. ?) where the type of the parameter is a character type will have
the same collation as current schema's character set, based on 3) above.
+ 
  9)For CURRENT_USER, SESSION_USER, SYSTEM_USER, SQL spec Section 6.4 Syntax Rule 4 says that
their collation type is the collation of character set SQL_IDENTIFIER. In Derby's case, that
will mean, the collation of these functions will be UCS_BASIC. The collation derivation will
be implicit. 
+ 
  10)CURRENT SCHEMA and CURRENT SQLID seem to be Derby specific functions, I didn't find them
in the SQL spec. But in order to match their behavior with the other functions covered in
8) above, their return character string type's collation will be the collation of character
set SQL_IDENTIFIER. The collation derivation will be implicit. 
+ 
  11)Aggregate operators involving all character string type operands(Concatenation, CASE,
NULLIF, COALESCE) will follow SQL spec Section 9.3 Data types of results of aggregations.
In other words, if all the operands have the same collation associated with them, then the
collation of result character string type will be same and the collation derivation will be
implicit. But if operands of different collation types are involved, then the result character
string type will have collation derivation of none. 
+ 
  12)And last but not least, the collation methods will follow the rules defined by SQL spec
in Section 9.13 Collation determination Syntax Rules 2 and 3e. In other words, at least one
operand shall have a declared type collation (that means if the comparison is sysChar1|userChar1
> sysChar2|userChar2, then such a comparison will fail because both sides of > operator
have collation derivation of none after applying the rules of 10 above) AND every operand
whose collation derivation is implicit shall have the same declared type collation (that means
if the comparison is sysChar1 > userChar1WithTerritoryBasedCollation, then such a comparison
will fail because left operand has collation derivation as implicit and collation type as
UCS_BASIC and the right operand has collation derivation implicit and collation type as TERRITORY_BASED)

   
  == Outstanding items ==

Mime
View raw message