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:18:12 UTC
[jira] [Created] (TRAFODION-795) LP Bug: 1396746 - Min() and max()
returned incorrect results with a dropped index
Alice Chen created TRAFODION-795:
------------------------------------
Summary: LP Bug: 1396746 - Min() and max() returned incorrect results with a dropped index
Key: TRAFODION-795
URL: https://issues.apache.org/jira/browse/TRAFODION-795
Project: Apache Trafodion
Issue Type: Bug
Components: sql-exe
Reporter: Weishiun Tsai
Assignee: Roberta Marton
Priority: Blocker
When a table was dropped and recreated with the same name, min() and max() returned incorrect results on the 2nd table. This seems to be related to an index on the 1st table that should have been dropped. If the 1st table was created without that index, these functions returned correct results on the 2nd table.
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. Since this is a data correctness issue and a regression, this case is marked as Critical.
-----------------------------------------------------------------------------------------------------------------
Here is the entire script to reproduce this problem:
set schema mytest;
create table table1
(
Int_1 INT SIGNED not null not droppable,
Large_2 LARGEINT not null,
primary key(Int_1)
);
create index idx1 on table1 (Large_2 desc, Int_1);
insert into table1 values (1, 1804250150),(2, 939828307);
drop table table1 cascade;
create table table1
(
Int_1 INT SIGNED not null not droppable,
Large_2 LARGEINT,
primary key(Int_1)
);
create index idx1 on table1 (Large_2 desc, Int_1);
create index idx2 on table1 (Int_1, Large_2);
insert into table1 values (3, -2115140520),(4, 2104744432);
prepare XX from select min(Large_2) from table1;
explain options 'f' XX;
execute XX;
prepare XX from select max(Large_2) from table1;
explain options 'f' XX;
execute XX;
drop schema mytest cascade;
-----------------------------------------------------------------------------------------------------------------
Here is the execution output showing that the last 2 select statements with min(Large_2) and max(Large_2) returned incorrect results.
>>set schema mytest;
--- SQL operation complete.
>>
>>create table table1
+>(
+>Int_1 INT SIGNED not null not droppable,
+>Large_2 LARGEINT not null,
+>primary key(Int_1)
+>);
--- SQL operation complete.
>>
>>create index idx1 on table1 (Large_2 desc, Int_1);
--- SQL operation complete.
>>
>>insert into table1 values (1, 1804250150),(2, 939828307);
--- 2 row(s) inserted.
>>
>>drop table table1 cascade;
--- SQL operation complete.
>>
>>create table table1
+>(
+>Int_1 INT SIGNED not null not droppable,
+>Large_2 LARGEINT,
+>primary key(Int_1)
+>);
--- SQL operation complete.
>>
>>create index idx1 on table1 (Large_2 desc, Int_1);
--- SQL operation complete.
>>create index idx2 on table1 (Int_1, Large_2);
--- SQL operation complete.
>>
>>insert into table1 values (3, -2115140520),(4, 2104744432);
--- 2 row(s) inserted.
>>
>>prepare XX from select min(Large_2) from table1;
--- SQL command prepared.
>>explain options 'f' XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+000
1 . 2 sort_scalar_aggr 1.00E+000
. . 1 trafodion_index_scan TABLE1 1.00E+002
--- SQL operation complete.
>>execute XX;
(EXPR)
--------------------
?
--- 1 row(s) selected.
>>
>>prepare XX from select max(Large_2) from table1;
--- SQL command prepared.
>>explain options 'f' XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
3 . 4 root 1.00E+000
2 . 3 shortcut_scalar_aggr 1.00E+000
1 . 2 firstn 1.00E+000
. . 1 trafodion_index_scan TABLE1 9.89E+001
--- SQL operation complete.
>>execute XX;
(EXPR)
--------------------
?
--- 1 row(s) selected.
>>
>>drop schema mytest cascade;
--- SQL operation complete.
-----------------------------------------------------------------------------------------------------------------
Here is the execution output showing that if the 1st table was created without that index, the last 2 select statements with min(Large_2) and max(Large_2) returned correct results.
>>set schema mytest;
--- SQL operation complete.
>>
>>create table table1
+>(
+>Int_1 INT SIGNED not null not droppable,
+>Large_2 LARGEINT not null,
+>primary key(Int_1)
+>);
--- SQL operation complete.
>>
>>-- create index idx1 on table1 (Large_2 desc, Int_1);
>>
>>insert into table1 values (1, 1804250150),(2, 939828307);
--- 2 row(s) inserted.
>>
>>drop table table1 cascade;
--- SQL operation complete.
>>
>>create table table1
+>(
+>Int_1 INT SIGNED not null not droppable,
+>Large_2 LARGEINT,
+>primary key(Int_1)
+>);
--- SQL operation complete.
>>
>>create index idx1 on table1 (Large_2 desc, Int_1);
--- SQL operation complete.
>>create index idx2 on table1 (Int_1, Large_2);
--- SQL operation complete.
>>
>>insert into table1 values (3, -2115140520),(4, 2104744432);
--- 2 row(s) inserted.
>>
>>prepare XX from select min(Large_2) from table1;
--- SQL command prepared.
>>explain options 'f' XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
2 . 3 root 1.00E+000
1 . 2 sort_scalar_aggr 1.00E+000
. . 1 trafodion_index_scan TABLE1 1.00E+002
--- SQL operation complete.
>>execute XX;
(EXPR)
--------------------
-2115140520
--- 1 row(s) selected.
>>
>>prepare XX from select max(Large_2) from table1;
--- SQL command prepared.
>>explain options 'f' XX;
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
3 . 4 root 1.00E+000
2 . 3 shortcut_scalar_aggr 1.00E+000
1 . 2 firstn 1.00E+000
. . 1 trafodion_index_scan TABLE1 9.89E+001
--- SQL operation complete.
>>execute XX;
(EXPR)
--------------------
2104744432
--- 1 row(s) selected.
>>
>>drop schema mytest cascade;
--- SQL operation complete.
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)