setting the attribute DEFAULT_CHARSET to UTF8 produces wrong results for metadata queries

[Suresh Subbiah]

Steps to reproduce

From sqlci

insert into "_MD_".defaults values('DEFAULT_CHARSET', 'UTF8', 'inserted by jerry') ;
exit ;

From another sqlci session

set schema "_MD_" ;
get tables ;

The "get tables" command returns 0 rows, while it should return a dozen or so rows with all metadata tables. This problem is not sen if a cqd was used to specify the DEFAULT_CHARSET attribute, but that could be due to a query cache issue.

I am placing this bug to have a critical priority since we have PoCs interested in using this attribute.

Output seen in 0.9.0 build. The last line (with command "get tables") shows the wrong result.

[ssubbiah@g4t3017 scripts]$ sqlci
Trafodion Conversational Interface 0.9.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>insert into "_MD_".defaults values('DEFAULT_CHARSET', 'UTF8', 'inserted by jerry') ;

set schema "_MD_" ;

get tables ;
--- 1 row(s) inserted.
>>>>
--- SQL operation complete.
>>>>

Tables in Schema TRAFODION._MD_
===============================

AUTHS
COLUMNS
DEFAULTS
INDEXES
KEYS
LIBRARIES
LIBRARIES_USAGE
OBJECTS
REF_CONSTRAINTS
ROUTINES
SEQ_GEN
TABLES
TABLE_CONSTRAINTS
TEXT
UNIQUE_REF_CONSTR_USAGE
VERSIONS
VIEWS
VIEWS_USAGE

--- SQL operation complete.
>>cqd query_cache '0' ;

--- SQL operation complete.
>>get tables ;

Tables in Schema TRAFODION._MD_
===============================

AUTHS
COLUMNS
DEFAULTS
INDEXES
KEYS
LIBRARIES
LIBRARIES_USAGE
OBJECTS
REF_CONSTRAINTS
ROUTINES
SEQ_GEN
TABLES
TABLE_CONSTRAINTS
TEXT
UNIQUE_REF_CONSTR_USAGE
VERSIONS
VIEWS
VIEWS_USAGE

--- SQL operation complete.
>>exit ;

End of MXCI Session

[ssubbiah@g4t3017 scripts]$ sqlci
Trafodion Conversational Interface 0.9.0
(c) Copyright 2014 Hewlett-Packard Development Company, LP.
>>set schema "_MD_" ;

--- SQL operation complete.
>>get tables ;

--- SQL operation complete.

[Anoop Sharma (anoop-sharma)]

For OSS poc, a cqd called TRAF_DEFAULT_COL_CHARSET was added.
this sets the default charset for all columns of user tables which do not
have an explicit charset clause specified. It does not impact metadata tables.

This cqd is not yet checked into mainline but will be part of december release.

Use of the cqd mentioned in this LP case( DEFAULT_CHARSET) is unclear based on
its implementation in code.

POCs which want to have UTF8 as their default col type should use traf_default_col_charset.
Until then, they should add an explicit 'charset' clause to their column definition.

[Anoop Sharma (anoop-sharma)]

cqd traf_default_col_charset has been checked in.
It could be used to set the default charset to UTF8. It could be set
in the session or inserted in system defaults table.

All char/varchar colummns in created tables will have utf8 charset.
One can explicitly set iso88591 in the create statement to override
the default value.

>>cqd traf_default_col_charset 'UTF8';

--- SQL operation complete.
>>create table c (a char(10));

--- SQL operation complete.
>>invoke c;

-- Definition of Trafodion table TRAFODION.MYTEST.C
-- Definition current Mon Nov 24 17:51:07 2014

  (
    SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A CHAR(10 CHARS) CHARACTER SET UTF8 COLLATE
      DEFAULT DEFAULT NULL
  )

--- SQL operation complete.
>>create table c1 (a char(5) character set iso88591);

--- SQL operation complete.
>>invoke c1;

-- Definition of Trafodion table TRAFODION.MYTEST.C1
-- Definition current Mon Nov 24 17:52:55 2014

  (
    SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE
  , A CHAR(5) CHARACTER SET ISO88591 COLLATE
      DEFAULT DEFAULT NULL
  )

--- SQL operation complete.
>>