[DERBY-1478] Add built in language based ordering and like processing to Derby

• Type: Improvement
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.1.2.1
• Fix Version/s: 10.4.1.3
• Component/s: SQL
• Assignee: Mamta A. Satoor
• Reporter: Katherine Marsden
• Created: 06/Jul/06 05:43
• Updated: 21/Jan/11 18:16
• Resolved: 29/Apr/08 18:20

Description

It would be good for Derby to have built in Language based ordering based on locale specific Collator.

Language based ordering is an important feature for international deployment. DERBY-533 offers one implementation option for this but according to the discussion in that issue National Character Types carry a fair amount of baggage with them especially in the form of concerns about conversion to and from datetime and number types. Rick mentioned SQL language for collations as an option for language based ordering. There may be other options too, but I thought it worthwhile to add an issue for the high level functional concern, so the best choice can be made for implementation without assuming that National Character Types is the only solution.

For possible 10.1 workaround and examples see:
http://wiki.apache.org/db-derby/LanguageBasedOrdering

Issue Links

• incorporates: DERBY-2336 Enable collation based ordering for CHAR data type.
• incorporates: DERBY-2669 If no territory attribute is not specified at create database time, then we should ignore the collation attribute if specified.
• is duplicated by: DERBY-554 ORDER By clause doesn't work with national strings
• is related to: DERBY-2879 CREATE TABLE AS <subquery> does not maintain the collation for character types.
• is related to: DERBY-2725 If all the operands involved in collation operation have a collation derivation of NONE, then Derby should throw an exception for that as per the SQL standards.
• is related to: DERBY-2894 calling DatabaseMetaData.getColumns() with % for matching column character in a territory based collated db does not work in 1.4.2 jvms
• is related to: DERBY-2909 TernaryOperatorNode does not check the collation type of it's operands when implementing TRIM, LOCATE functions.
• is related to: DERBY-2910 SimpleStringOperatorNode in it's bindExpression method generates a character string CAST if required but does not set the correct collation.
• is related to: DERBY-2831 NullPointerException creating a function in a schema that doesn't exist when the return type is a character type
• is related to: DERBY-2678 Derby should follow SQL standards when operand(s) in the collation operation have collation derivation of NONE
• is related to: DERBY-3854 Implement LIKE transformations and optimizations for databases using territory-based collations
• is related to: DERBY-533 Re-enable national character datatypes
• is related to: DERBY-2668 At the time of compilation of a comparison operation, if the collation types of the operands do not match, then we should throw a meaningful error message.
• is related to: DERBY-2723 Set correct collation type and derivation for result from user defined functions' that return character string type.
• is related to: DERBY-2724 Set correct collation type and derivation for result character string types from CURRENT ISOLATION, CURRENT_USER, SESSION_USER, SYSTEM_USER, CURRENT SCHEMA and CURRENT SQLID.
• relates to: DERBY-2967 Single character does not match high value unicode character with collation TERRITORY_BASED
• relates to: DERBY-2863 Collation doc corrections/improvements
• relates to: DERBY-2731 String literal constants currently take the collation of the compilation schema but the wiki page http://wiki.apache.org/db-derby/BuiltInLanguageBasedOrderingDERBY-1478 expects USER schema collation.
• relates to: DERBY-2866 Improve error message when comparing user table and system table columns if collation doesn't match

Sub-Tasks

• Accept the new collation attribute and store it as a database property. Will be used for language based collation ordering.: Closed
• Compare character datatypes with different collation ordering.: Closed
• Enable collation based ordering for CHAR data type.: Closed
• Enable collation based ordering for VARCHAR, LONG VARCHAR and CLOB data types.: Closed
• Document language based ordering which will be implemented by code related sub-tasks of DERBY-1478.: Closed
• implement pushing collation info to store, storing collation info in store metadata, and creating templates based on store metadata: Closed
• Create initial testing framework for Junit Collation testing: Closed
• calling DatabaseMetaData.getColumns() with % for matching column character in a territory based collated db does not work in 1.4.2 jvms: Closed
• fix like clauses with trailing %: Closed
• disable dynamic like optimization for collated databases: Closed
• throw error if non-matching collation ids in like.: Closed
• Make sure that all the DTDs for character string types have correct collation assigned to them.: Closed
• Ensure the DataTypeDescriptor.comparable() implements the correct rules for determing the collation to use.: Closed
• Address testing todo items in CollationTest.java: Closed