You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:15:25 UTC
[jira] [Created] (TRAFODION-264) LP Bug: 1318859 - index to varchar
column affect the other varchar column in select
Alice Chen created TRAFODION-264:
------------------------------------
Summary: LP Bug: 1318859 - index to varchar column affect the other varchar column in select
Key: TRAFODION-264
URL: https://issues.apache.org/jira/browse/TRAFODION-264
Project: Apache Trafodion
Issue Type: Bug
Reporter: Apache Trafodion
Assignee: Anoop Sharma
Priority: Critical
1) create index I1TAB2 on TAB2(vca)
2) select vce from TAB2 caused all data in vce volumn displayed as upper cases
================================================================================
Spooling started at May 13, 2014 12:34:11 AM
================================================================================
SQL>env;
COLSEP " "
HISTOPT DEFAULT [No expansion of script files]
IDLETIMEOUT 30 min(s)
LIST_COUNT 0 [All Rows]
LOG FILE t5log
LOG OPTIONS CLEAR,CMDTEXT ON
MARKUP RAW
PROMPT SQL>
SCHEMA ODBCTEST
SERVER rhel-cdh1.hpl.hp.com:37800
SQLTERMINATOR ;
STATISTICS OFF
TIME OFF
TIMING OFF
USER trafodion
SQL>drop schema debug_tab2 cascade;
--- SQL operation complete.
SQL>create schema debug_tab2;
--- SQL operation complete.
SQL>set schema debug_tab2;
--- SQL operation complete.
SQL>-- Primary key of dis-contiguous columns.
SQL> create table TAB2 (
vca varchar(9)
, vcb varchar(9)
, nintc smallint not null
, noindexvcd varchar(9)
, vce varchar(9) not null
, endcomposxf smallint not null
, pkvcg varchar(9) not null
, primary key ( pkvcg , nintc , vce )
) ;
--- SQL operation complete.
SQL>-- Inserts.
SQL>insert into TAB2 values ( 'ddd', 'ee' , 3 , 'd' , 'e' , 7 , 'Dup Key 1');
--- 1 row(s) inserted.
SQL>-- First 2 columns of primary key (pkvcg , nintc) match
SQL>-- TAB1's primary key (pkvca , nintb).
SQL>insert into TAB2 values ( NULL, 'long1 c2'
, 1 , 'long1 c4' , 'long1 c5', 101 , '01 No dup');
--- 1 row(s) inserted.
SQL>-- First 2 columns of primary key (pkvcg , nintc) match on another
SQL>-- TAB1's primary key (pkvca , nintb).
SQL>insert into TAB2 values (
'long1 c1' , 'long1 c2'
, 7 , 'long1 c4' , 'long1 c5' , 101 , '02 No dup');
--- 1 row(s) inserted.
SQL>-- First 2 columns of primary key (pkvcg , nintc) match on another
SQL>-- TAB1's primary key (pkvca , nintb).
SQL>insert into TAB2 values ( 'long1 c1' , 'long1 c2'
, 4 , NULL , 'long1 c7' , 100 , '03 No dup');
--- 1 row(s) inserted.
SQL>-- Match between TAB2 and TAB1 on non-primary cols.
SQL>insert into TAB2 values ( 'ddd' , 'ee'
, 2 , 'LONG3 c4' , 'long1 c5' , 5 , 'Dup Key 1');
--- 1 row(s) inserted.
SQL>select * from TAB2 order by 1;
VCA VCB NINTC NOINDEXVCD VCE ENDCOMPOSXF PKVCG
--------- --------- ------ ---------- --------- ----------- ---------
ddd ee 3 d e 7 Dup Key 1
ddd ee 2 LONG3 c4 long1 c5 5 Dup Key 1
long1 c1 long1 c2 4 NULL long1 c7 100 03 No dup
long1 c1 long1 c2 7 long1 c4 long1 c5 101 02 No dup
NULL long1 c2 1 long1 c4 long1 c5 101 01 No dup
--- 5 row(s) selected.
SQL>select vca from TAB2;
VCA
---------
NULL
long1 c1
long1 c1
ddd
ddd
--- 5 row(s) selected.
SQL>select vcb from TAB2;
VCB
---------
long1 c2
long1 c2
long1 c2
ee
ee
--- 5 row(s) selected.
SQL>select vce from TAB2;
VCE
---------
long1 c5
long1 c5
long1 c7
long1 c5
e
--- 5 row(s) selected.
SQL>select vca, vcb from TAB2;
VCA VCB
--------- ---------
NULL long1 c2
long1 c1 long1 c2
long1 c1 long1 c2
ddd ee
ddd ee
--- 5 row(s) selected.
SQL>select vca, vce from TAB2;
VCA VCE
--------- ---------
NULL long1 c5
long1 c1 long1 c5
long1 c1 long1 c7
ddd long1 c5
ddd e
--- 5 row(s) selected.
SQL>select vca, vcb, vce from TAB2;
VCA VCB VCE
--------- --------- ---------
NULL long1 c2 long1 c5
long1 c1 long1 c2 long1 c5
long1 c1 long1 c2 long1 c7
ddd ee long1 c5
ddd ee e
--- 5 row(s) selected.
SQL>select vca, vcb, noindexvcd, vce from TAB2;
VCA VCB NOINDEXVCD VCE
--------- --------- ---------- ---------
NULL long1 c2 long1 c4 long1 c5
long1 c1 long1 c2 long1 c4 long1 c5
long1 c1 long1 c2 NULL long1 c7
ddd ee LONG3 c4 long1 c5
ddd ee d e
--- 5 row(s) selected.
SQL>-- Populate index I1TAB2.
SQL>create index I1TAB2 on TAB2(vca);
--- SQL operation complete.
SQL>select vca from TAB2;
VCA
---------
ddd
ddd
long1 c1
long1 c1
NULL
--- 5 row(s) selected.
SQL>select vcb from TAB2;
VCB
---------
long1 c2
long1 c2
long1 c2
ee
ee
--- 5 row(s) selected.
SQL>select vce from TAB2;
VCE
---------
LONG1 C5
E
LONG1 C5
LONG1 C7
LONG1 C5
--- 5 row(s) selected.
SQL>select vca, vcb from TAB2;
VCA VCB
--------- ---------
NULL long1 c2
long1 c1 long1 c2
long1 c1 long1 c2
ddd ee
ddd ee
--- 5 row(s) selected.
SQL>select vca, vce from TAB2;
VCA VCE
--------- ---------
ddd LONG1 C5
ddd E
long1 c1 LONG1 C5
long1 c1 LONG1 C7
NULL LONG1 C5
--- 5 row(s) selected.
SQL>select vca, vcb, vce from TAB2;
VCA VCB VCE
--------- --------- ---------
NULL long1 c2 long1 c5
long1 c1 long1 c2 long1 c5
long1 c1 long1 c2 long1 c7
ddd ee long1 c5
ddd ee e
--- 5 row(s) selected.
SQL>select vca, vcb, noindexvcd, vce from TAB2;
VCA VCB NOINDEXVCD VCE
--------- --------- ---------- ---------
NULL long1 c2 long1 c4 long1 c5
long1 c1 long1 c2 long1 c4 long1 c5
long1 c1 long1 c2 NULL long1 c7
ddd ee LONG3 c4 long1 c5
ddd ee d e
--- 5 row(s) selected.
SQL>exit;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)