[DERBY-2959] create table ... as select ... from systemtable with no data fails even when there is no character string type involved. This happens in a territory based database

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.3.1.4
• Fix Version/s: 10.3.1.4, 10.4.1.3
• Component/s: SQL
• Assignee: Mamta A. Satoor
• Reporter: Mamta A. Satoor
• Created: 19/Jul/07 17:53
• Updated: 23/Jul/07 17:05
• Resolved: 21/Jul/07 19:47

Description

Following query should fail because we are trying to create a user table with UCS_BASIC character columns when the user schema has collation of territory based.
CREATE TABLE T AS SELECT TABLENAME FROM SYS.SYSTABLES WITH NO DATA

But the following query should not fail because there are no character string columns involved. This jira entry is to fix the exception that is getting thrown for the query below
CREATE TABLE T AS SELECT COLUMNNUMBER FROM SYS.SYSCOLUMNS WITH NO DATA

I have put in a fix for this in main using revision 557693. The commit comments were as follows

This commit has 2 simple fixes (DERBY-2951 which gives assert failure and DERBY-2656 The table will have collation type UCS_BASIC which is different than the collation of the schema TERRITORY_BASED hence this operation is not supported.)

The failure in DERBY-2951 is because in store, we were not using correct format id and hence collation information was not getting written out and read from disk. Added a test case for this in CollationTest.

The failure in DERBY-2656 was because of the bug that we were comparing collation type for non-character types. Collation is only applicable to character types and hence we should check for character types before comparing the collation info. Added a test case for this one too.

Issue Links

• blocks: DERBY-2656 Run suites.All against a collated database