[
https://issues.apache.org/jira/browse/TRAFODION-795?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14705215#comment-14705215
]
Roberta Marton commented on TRAFODION-795:
------------------------------------------
When indexes are removed as part of drop table, cache entries are not being
deleted from the compiler process associated with the session.
When create index ndx1 with UID 001 is performed, no entries are stored in the
embedded compiler cache for the index – just the table. The compiler process is
called to compile statements for index maintenance. Index maintenance treats
indexes as special tables so both the table and index is stored in the
compiler's NATable cache.
The index is dropped as part of drop table – the index entry does not exist in
embedded compiler cache so SetSecInvalidKeys is not invoked.
In the same session, the table and index is recreated - again only the table
entry is stored in the embedded compilers cache. when the create index is
performed, an index maintenance query is executed that gets sent to the
compiler process (instead of the embedded compiler) which now uses an invalid
cache – problems occur.
The first index drop is not cleaning up cache simply because it is never loaded
into embedded compiler's cache, so the call to removNACache is not doing any
work.
The difference between when these queries worked and when they failed is how
the drop table code gets its list of indexes.
The old code would get the list of indexes from the NATable structure – could
be using a stale index list
The new code does a metadata read.
The code was changed to gather a list of SQL_QIKEY's for each index on the
table. This list is sent to SetSecInvalidKeys to remove the index entries from
all caches which includes the current session's compiler process(es).
QA - verified on the v1209_0830 build installed on a cluster that this problem
has been fixed
> 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)
