[TRAFODION-796] LP Bug: 1396774 - Insert sees optimizer internal assertion in ../optimizer/BindRelExpr.cpp - ASF JIRA

Details

Type: Bug
Status: Closed
Priority: Blocker
Resolution: Fixed
Affects Version/s: None
Fix Version/s: None
Component/s: sql-cmu
Labels:
None

External issue URL:
https://bugs.launchpad.net/bugs/1396774

Description

When trying to insert values into a table, it sees the following internal assertion:

- - ERROR[2006] Internal error: assertion failure (tgtcols.entries() == baseColRefs().entries()) in file ../optimizer/BindRelExpr.cpp at line 12583.

- - ERROR[8822] The statement was not prepared.

This problem seems to only happen when the table was dropped and recreated. It’s possible that it is related to another bug https://bugs.launchpad.net/trafodion/+bug/1396746 ‘Min() and max() returned incorrect results with a dropped index’, since both involves dropping and recreating tables with the same names, as well as indexes. But since the symptom is different, we are tracking them separately for now until further analysis can be done.

This is seen on the v1125_0830 build installed on a workstation. It is a regression introduced between the v1119_0830 build and the v1125_0830 build.

-----------------------------------------------------------------------------------------------

Here is the entire script to reproduce this problem:

set schema mytest5;

Create table T1(A int not null not droppable primary key,
B smallint default null)
attribute extent 256;

Create table T2(C int default null,
D smallint not null not droppable primary key,
E largeint default null)
attribute extent 256;

Create table T3(F int default null,
G smallint default null,
H largeint not null not droppable primary key,
I numeric(9,3) default null)
attribute extent 256;

Create table T4(J int default null,
K smallint default null,
L largeint default null ,
M numeric(9,3) not null not droppable primary key,
N decimal(18,9) default null)
attribute extent 256;

Create table T5(O int default null,
P smallint default null,
Q largeint default null,
R numeric(9,3) not null not droppable primary key,
S decimal(18,9) default null,
T char(20) default null,
U float(52) default null,
V real default null,
W double precision default null,
X timestamp default null,
Y interval year to month default null ,
Z char (12) default null,
AA time default null)
attribute extent 256;

create index smint_idx on T1(B) attribute extent 256;
create index lint_idx on T2(E) attribute extent 256;
create index int_idx on T2(C) attribute extent 256;
create index num_idx on T3(I) attribute extent 256;
create index lint_idxT3 on T3(G) attribute extent 256;
create index int_idxT3 on T3(F) attribute extent 256;
create index dem_idxT4 on T4(N) attribute extent 256;
create index int_idxT4 on T4(J) attribute extent 256;
create index sint_idxT4 on T4(K) attribute extent 256;
create index lint_idxT4 on T4(L) attribute extent 256;
create index dat_idxT5 on T5(T) attribute extent 256;
create index int_idxT5 on T5(O) attribute extent 256;
create index smint_idxT5 on T5(P) attribute extent 256;
create index lint_idxT5 on T5(Q) attribute extent 256;
create index dem_idxT5 on T5(S) attribute extent 256;
create index flo_idxT5 on T5(U) attribute extent 256;
create index rel_idxT5 on T5(V) attribute extent 256;
create index dou_idxT5 on T5(W) attribute extent 256;
create index tms_idxT5 on T5(X) attribute extent 256;
create index intv_idxT5 on T5(Y) attribute extent 256;
create index char_idxT5 on T5(Z) attribute extent 256;
create index tim_idxT5 on T5(AA) attribute extent 256;

insert into T1 values(2, 1);
insert into T1 values(4, 2);
insert into T1 values(6, 3);
insert into T1 values(8,4);
insert into T1 values(10,5);
insert into T1 values(12, -32768);
insert into T1(A) values (11);

insert into T2 values(2, 1,1);
insert into T2 values(4, 2,1);
insert into T2 values(6, 3,1);
insert into T2 values(8, 4,1);
insert into T2 values(10,5,1);
insert into T2 values(6, 6,1);
insert into T2 values(8, -32768 , -923720368547588);
insert into T2(C,D) values (4,14);

insert into T3 values(2, 1,1,1);
insert into T3 values(4, 2,2,1);
insert into T3 values(6, 3,3,1);
insert into T3 values(8, 4,4,1);
insert into T3 values(10, -32768 , -923720368547588, -99999.999);
insert into T3(F,G,H) values (12,5,5);

insert into T4 values(2, 1,1,1,0);
insert into T4 values(4, 2,2,2,0);
insert into T4 values(6, 3,3,3,0);
insert into T4 values(8, 4,4,4,0);
insert into T4 values(10, 5,5,5,0);
insert into T4 values(12, -32768 , -923720368547588, -99999.999, -999999.99999999);
insert into T4(J,K,L,M) values (14, 6,6,6);

insert into T5 values(2,1,1,1,0, '1999-12-31', 1,1,1, TIMESTAMP'1999-01-01 00:00:00.000000', INTERVAL '99-02'YEAR TO MONTH, '1',TIME '00:00:00');
insert into T5 values(4, 2,1,2,0, '1990-12-31', 1,1,1, TIMESTAMP '1997-01-01 00:00:00.000000', INTERVAL '99-02'YEAR TO MONTH, '2', TIME '00:00:00');
insert into T5 values(6, 3,1,3,0, '1991-12-31', 1,1,1, TIMESTAMP'1998-01-01 00:00:00.000000',INTERVAL '99-02'YEAR TO MONTH, '2', TIME '00:00:00');
insert into T5 values(7, -32768 , -923720368547588, -99999.999, -999999.99999999, '1991-12-31', 1,1,1, TIMESTAMP '1998-01-01 00:00:00.000000', INTERVAL '99-02'YEAR TO MONTH, '2', TIME '00:00:00');
insert into T5(P,R) values (7, 7);
insert into T5 values(8, -32768, -923720368547588, -99999.900, -999999.99999000, '2006-06-15', 7.000000000001, -2.2250738585072014, -1.17549435e-38, TIMESTAMP '2006-06-23 17:56:59.300439', INTERVAL '06-06'YEAR TO MONTH, '1', TIME '17:05:45');
insert into T5 values(9, 32767, 923720368547587, 999998.999, 9999999.999999999, '2005-07-24', 6.000001, 4509.000000001, 3.40282347e+38, TIMESTAMP '2005-12-24 09:21:11.234039', INTERVAL '06-07'YEAR TO MONTH, '12', TIME '07:35:54');
insert into T5 values(10, 7892, 9809876586, 23990.78, 8769.1200454, '2000-04-12', 11897.9998877656, 898889.00000997, 555.23, TIMESTAMP '1999-01-01 02:11:33.100439', INTERVAL '06-11' YEAR TO MONTH, '123', TIME '00:21:30');

drop table T1 cascade;
drop table T2 cascade;
drop table T3 cascade;
drop table T4 cascade;
drop table T5 cascade;

Create table T1(A int not null not droppable primary key,
B smallint default null)
attribute extent 256;

Create table T2(C int default null,
D smallint not null not droppable primary key,
E largeint default null)
attribute extent 256;

Create table T3(F int not null not droppable ,
G smallint not null not droppable ,
H largeint not null not droppable ,
I numeric(9,3) default null,
primary key (F,G,H) )
attribute extent 256;

Create table T4 (J int default null,
K smallint not null not droppable,
L largeint not null not droppable,
M numeric(9,3) not null not droppable,
N decimal(18,9) default null,
primary key (K,L,M) )
attribute extent 256;

Create table T5 (O int default null,
P smallint default null,
Q largeint default null,
R numeric(9,3) not null not droppable primary key,
S decimal(18,9) default null,
T char(20) default null,
U float(52) default null,
V real default null,
W double precision default null,
X timestamp default null,
Y interval year to month default null ,
Z char (12) default null,
AA time default null)
attribute extent 256;

create index smint_idx on T1(B) attribute extent 256;
create index lint_idx on T2(E) attribute extent 256;
create index int_idx on T2(C) attribute extent 256;
create index num_idx on T3(I) attribute extent 256;
create index dem_idxT4 on T4(N) attribute extent 256;
create index int_idxT4 on T4(J) attribute extent 256;
create index dat_idxT5 on T5(T) attribute extent 256;
create index int_idxT5 on T5(O) attribute extent 256;
create index smint_idxT5 on T5(P) attribute extent 256;
create index lint_idxT5 on T5(Q) attribute extent 256;
create index dem_idxT5 on T5(S) attribute extent 256;
create index flo_idxT5 on T5(U) attribute extent 256;
create index rel_idxT5 on T5(V) attribute extent 256;
create index dou_idxT5 on T5(W) attribute extent 256;
create index tms_idxT5 on T5(X) attribute extent 256;
create index intv_idxT5 on T5(Y) attribute extent 256;
create index char_idxT5 on T5(Z) attribute extent 256;
create index tim_idxT5 on T5(AA) attribute extent 256;

insert into T1 values(2, 1);
insert into T1 values(4, 2);
insert into T1 values(6, 3);
insert into T1 values(8,4);
insert into T1 values(10,5);
insert into T1 values(12, -32768);
insert into T1(A) values (11);

insert into T2 values(2, 1,1);
insert into T2 values(4, 2,1);
insert into T2 values(6, 3,1);
insert into T2 values(8, 4,1);
insert into T2 values(10,5,1);
insert into T2 values(6, 6,1);
insert into T2 values(8, -32768 , -923720368547588);
insert into T2(C,D) values (4,14);

insert into T3 values(2, 1,1,1);
insert into T3 values(4, 2,2,1);
insert into T3 values(6, 3,3,1);
insert into T3 values(8, 4,4,1);
insert into T3 values(10, -32768 , -923720368547588, -99999.999);
insert into T3(F,G,H) values (12,5,5);

insert into T4 values(2, 1,1,1,0);
insert into T4 values(4, 2,2,2,0);
insert into T4 values(6, 3,3,3,0);
insert into T4 values(8, 4,4,4,0);
insert into T4 values(10, 5,5,5,0);
insert into T4 values(12, -32768 , -923720368547588, -99999.999, -999999.99999999);
insert into T4(J,K,L,M) values (14, 6,6,6);

drop schema mytest5 cascade;

-----------------------------------------------------------------------------------------------

Here is the execution output showing the errors:

>>set schema mytest5;

— SQL operation complete.
>>
>>Create table T1(A int not null not droppable primary key,
+>B smallint default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T2(C int default null,
+>D smallint not null not droppable primary key,
+>E largeint default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T3(F int default null,
+>G smallint default null,
+>H largeint not null not droppable primary key,
+>I numeric(9,3) default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T4(J int default null,
+>K smallint default null,
+>L largeint default null ,
+>M numeric(9,3) not null not droppable primary key,
+>N decimal(18,9) default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T5(O int default null,
+>P smallint default null,
+>Q largeint default null,
+>R numeric(9,3) not null not droppable primary key,
+>S decimal(18,9) default null,
+>T char(20) default null,
+>U float(52) default null,
+>V real default null,
+>W double precision default null,
+>X timestamp default null,
+>Y interval year to month default null ,
+>Z char (12) default null,
+>AA time default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>create index smint_idx on T1(B) attribute extent 256;

— SQL operation complete.
>>create index lint_idx on T2(E) attribute extent 256;

— SQL operation complete.
>>create index int_idx on T2(C) attribute extent 256;

— SQL operation complete.
>>create index num_idx on T3(I) attribute extent 256;

— SQL operation complete.
>>create index lint_idxT3 on T3(G) attribute extent 256;

— SQL operation complete.
>>create index int_idxT3 on T3(F) attribute extent 256;

— SQL operation complete.
>>create index dem_idxT4 on T4(N) attribute extent 256;

— SQL operation complete.
>>create index int_idxT4 on T4(J) attribute extent 256;

— SQL operation complete.
>>create index sint_idxT4 on T4(K) attribute extent 256;

— SQL operation complete.
>>create index lint_idxT4 on T4(L) attribute extent 256;

— SQL operation complete.
>>create index dat_idxT5 on T5(T) attribute extent 256;

— SQL operation complete.
>>create index int_idxT5 on T5(O) attribute extent 256;

— SQL operation complete.
>>create index smint_idxT5 on T5(P) attribute extent 256;

— SQL operation complete.
>>create index lint_idxT5 on T5(Q) attribute extent 256;
— SQL operation complete.
>>create index dem_idxT5 on T5(S) attribute extent 256;

— SQL operation complete.
>>create index flo_idxT5 on T5(U) attribute extent 256;

— SQL operation complete.
>>create index rel_idxT5 on T5(V) attribute extent 256;

— SQL operation complete.
>>create index dou_idxT5 on T5(W) attribute extent 256;

— SQL operation complete.
>>create index tms_idxT5 on T5(X) attribute extent 256;

— SQL operation complete.
>>create index intv_idxT5 on T5(Y) attribute extent 256;

— SQL operation complete.
>>create index char_idxT5 on T5(Z) attribute extent 256;

— SQL operation complete.
>>create index tim_idxT5 on T5(AA) attribute extent 256;

— SQL operation complete.
>>
>>insert into T1 values(2, 1);

— 1 row(s) inserted.
>>insert into T1 values(4, 2);

— 1 row(s) inserted.
>>insert into T1 values(6, 3);

— 1 row(s) inserted.
>>insert into T1 values(8,4);

— 1 row(s) inserted.
>>insert into T1 values(10,5);

— 1 row(s) inserted.
>>insert into T1 values(12, -32768);

— 1 row(s) inserted.
>>insert into T1(A) values (11);
— 1 row(s) inserted.
>>
>>insert into T2 values(2, 1,1);

— 1 row(s) inserted.
>>insert into T2 values(4, 2,1);

— 1 row(s) inserted.
>>insert into T2 values(6, 3,1);

— 1 row(s) inserted.
>>insert into T2 values(8, 4,1);

— 1 row(s) inserted.
>>insert into T2 values(10,5,1);

— 1 row(s) inserted.
>>insert into T2 values(6, 6,1);

— 1 row(s) inserted.
>>insert into T2 values(8, -32768 , -923720368547588);

— 1 row(s) inserted.
>>insert into T2(C,D) values (4,14);

— 1 row(s) inserted.
>>
>>insert into T3 values(2, 1,1,1);

— 1 row(s) inserted.
>>insert into T3 values(4, 2,2,1);

— 1 row(s) inserted.
>>insert into T3 values(6, 3,3,1);

— 1 row(s) inserted.
>>insert into T3 values(8, 4,4,1);

— 1 row(s) inserted.
>>insert into T3 values(10, -32768 , -923720368547588, -99999.999);

— 1 row(s) inserted.
>>insert into T3(F,G,H) values (12,5,5);

— 1 row(s) inserted.
>>
>>insert into T4 values(2, 1,1,1,0);

— 1 row(s) inserted.
>>insert into T4 values(4, 2,2,2,0);

— 1 row(s) inserted.
>>insert into T4 values(6, 3,3,3,0);

— 1 row(s) inserted.
>>insert into T4 values(8, 4,4,4,0);

— 1 row(s) inserted.
>>insert into T4 values(10, 5,5,5,0);

— 1 row(s) inserted.
>>insert into T4 values(12, -32768 , -923720368547588, -99999.999, -999999.99999
999);

— 1 row(s) inserted.
>>insert into T4(J,K,L,M) values (14, 6,6,6);

— 1 row(s) inserted.
>>
>>insert into T5 values(2,1,1,1,0, '1999-12-31', 1,1,1, TIMESTAMP'1999-01-01 00
:00:00.000000', INTERVAL '99-02'YEAR TO MONTH, '1',TIME '00:00:00');

— 1 row(s) inserted.
>>insert into T5 values(4, 2,1,2,0, '1990-12-31', 1,1,1, TIMESTAMP '1997-01-01
00:00:00.000000', INTERVAL '99-02'YEAR TO MONTH, '2', TIME '00:00:00');

— 1 row(s) inserted.
>>insert into T5 values(6, 3,1,3,0, '1991-12-31', 1,1,1, TIMESTAMP'1998-01-01 0
0:00:00.000000',INTERVAL '99-02'YEAR TO MONTH, '2', TIME '00:00:00');

— 1 row(s) inserted.
>>insert into T5 values(7, -32768 , -923720368547588, -99999.999, -999999.999999
99, '1991-12-31', 1,1,1, TIMESTAMP '1998-01-01 00:00:00.000000', INTERVAL '99-0
2'YEAR TO MONTH, '2', TIME '00:00:00');

— 1 row(s) inserted.
>>insert into T5(P,R) values (7, 7);

— 1 row(s) inserted.
>>insert into T5 values(8, -32768, -923720368547588, -99999.900, -999999.999990
00, '2006-06-15', 7.000000000001, -2.2250738585072014, -1.17549435e-38, TIMESTAM
P '2006-06-23 17:56:59.300439', INTERVAL '06-06'YEAR TO MONTH, '1', TIME '17:05:
45');

— 1 row(s) inserted.
>>insert into T5 values(9, 32767, 923720368547587, 999998.999, 9999999.999999
999, '2005-07-24', 6.000001, 4509.000000001, 3.40282347e+38, TIMESTAMP '2005-12
-24 09:21:11.234039', INTERVAL '06-07'YEAR TO MONTH, '12', TIME '07:35:54');

— 1 row(s) inserted.
>>insert into T5 values(10, 7892, 9809876586, 23990.78, 8769.1200454, '2000-
04-12', 11897.9998877656, 898889.00000997, 555.23, TIMESTAMP '1999-01-01 02:1
1:33.100439', INTERVAL '06-11' YEAR TO MONTH, '123', TIME '00:21:30');

— 1 row(s) inserted.
>>
>>drop table T1 cascade;

— SQL operation complete.
>>drop table T2 cascade;

— SQL operation complete.
>>drop table T3 cascade;

— SQL operation complete.
>>drop table T4 cascade;

— SQL operation complete.
>>drop table T5 cascade;

— SQL operation complete.
>>Create table T1(A int not null not droppable primary key,
+>B smallint default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T2(C int default null,
+>D smallint not null not droppable primary key,
+>E largeint default null)
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T3(F int not null not droppable ,
+>G smallint not null not droppable ,
+>H largeint not null not droppable ,
+>I numeric(9,3) default null,
+>primary key (F,G,H) )
+>attribute extent 256;

— SQL operation complete.
>>Create table T4 (J int default null,
+>K smallint not null not droppable,
+>L largeint not null not droppable,
+>M numeric(9,3) not null not droppable,
+>N decimal(18,9) default null,
+>primary key (K,L,M) )
+>attribute extent 256;

— SQL operation complete.
>>
>>Create table T5 (O int default null,
+>P smallint default null,
+>Q largeint default null,
+>R numeric(9,3) not null not droppable primary key,
+>S decimal(18,9) default null,
+>T char(20) default null,
+>U float(52) default null,
+>V real default null,
+>W double precision default null,
+>X timestamp default null,
+>Y interval year to month default null ,
+>Z char (12) default null,
+>AA time default null)
+>attribute extent 256;

— SQL operation complete.
>>create index smint_idx on T1(B) attribute extent 256;