You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Roberta Marton (JIRA)" <ji...@apache.org> on 2015/08/20 18:27:45 UTC
[jira] [Commented] (TRAFODION-795) LP Bug: 1396746 - Min() and
max() returned incorrect results with a dropped index
[ 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)