[jira] [Created] (TRAFODION-977) LP Bug: 1417743 - multi-temperature data: able to define a RI constraint on a system column.

["Alice Chen (JIRA)" <ji...@apache.org>]

Alice Chen created TRAFODION-977:
------------------------------------

             Summary: LP Bug: 1417743 - multi-temperature data: able to define a RI constraint on a system column.
                 Key: TRAFODION-977
                 URL: https://issues.apache.org/jira/browse/TRAFODION-977
             Project: Apache Trafodion
          Issue Type: Bug
          Components: sql-cmp
            Reporter: Julie Thai
            Assignee: Hans Zeller


Two issues here:
1. not catching the lower case name
2. allowing RI constraint on system columns

>>create table mtd_n14a(
+>        col_a int not null,
+>        col_b date not null,
+>        col_c char(10),
+>        primary key (col_a, col_b))
+>        division by (date_part('year', col_b));

--- SQL operation complete.
>>
>>  insert into mtd_n14a values (100, date '2011-12-16', 'year 2011');

--- 1 row(s) inserted.
>>
>>  create table mtd_n14b(
+>        col_x int not null,
+>        col_y numeric(4,0) unsigned unique not null,
+>        col_z char(20),
+>        primary key (col_x));

--- SQL operation complete.
>>  insert into mtd_n14b values (1000, 2011, '2011 dummy data');

--- 1 row(s) inserted.
>>
>>  alter table mtd_n14a add constraint con1
+>        foreign key ("_division_1_") references mtd_n14b(col_y);

*** ERROR[1009] Column "_division_1_" does not exist in the specified table.

--- SQL operation failed with errors.
>>
>>  alter table mtd_n14a add constraint con1
+>        foreign key ("_DIVISION_1_") references mtd_n14b(col_y);

--- SQL operation complete.

To reproduce:
create table mtd_n14a(
        col_a int not null,
        col_b date not null,
        col_c char(10),
        primary key (col_a, col_b))
        division by (date_part('year', col_b));
insert into mtd_n14a values (100, date '2011-12-16', 'year 2011');
create table mtd_n14b(
        col_x int not null,
        col_y numeric(4,0) unsigned unique not null,
        col_z char(20),
        primary key (col_x));
insert into mtd_n14b values (1000, 2011, '2011 dummy data');
alter table mtd_n14a add constraint con1
        foreign key ("_division_1_") references mtd_n14b(col_y);
alter table mtd_n14a add constraint con1
        foreign key ("_DIVISION_1_") references mtd_n14b(col_y);



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)