You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Atanu Mishra (JIRA)" <ji...@apache.org> on 2015/08/24 03:06:45 UTC
[jira] [Commented] (TRAFODION-264) LP Bug: 1318859 - index to
varchar column affect the other varchar column in select
[ https://issues.apache.org/jira/browse/TRAFODION-264?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14708616#comment-14708616 ]
Atanu Mishra commented on TRAFODION-264:
----------------------------------------
Anoop Sharma (anoop-sharma) on 2014-05-30
Changed in trafodion:
status: In Progress → Fix Committed
ying-wen ku (ying-wen-ku) on 2014-06-02
Changed in trafodion:
importance: Undecided → High
ying-wen ku (ying-wen-ku) wrote on 2014-06-05: #3
The fix is verified and behaved correctly.
Changed in trafodion:
status: Fix Committed → Fix Released
> 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
> Fix For: 0.8 (pre-incubation)
>
>
> 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)