[jira] [Resolved] (TRAFODION-2187) UPD STATS: drop schema fails with error 1069 after using persistent sample

["David Wayne Birdsall (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/TRAFODION-2187?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

David Wayne Birdsall resolved TRAFODION-2187.
---------------------------------------------
       Resolution: Fixed
    Fix Version/s: 2.1-incubating

> UPD STATS: drop schema fails with error 1069 after using persistent sample
> --------------------------------------------------------------------------
>
>                 Key: TRAFODION-2187
>                 URL: https://issues.apache.org/jira/browse/TRAFODION-2187
>             Project: Apache Trafodion
>          Issue Type: Bug
>          Components: sql-cmp
>    Affects Versions: 2.1-incubating
>         Environment: All
>            Reporter: David Wayne Birdsall
>            Assignee: David Wayne Birdsall
>             Fix For: 2.1-incubating
>
>
> If one does an UPDATE STATS using a persistent sample table, then DROP SCHEMA will fail with error 1069. For example:
> >>create schema mysch3;
> --- SQL operation complete.
> >>set schema mysch3;
> --- SQL operation complete.
> >>
> >>create table myFullTable (
> +>c_char char(50),
> +>c_char_upshift char(50) upshift,
> +>c_char_not_casespecific char(50) not casespecific,
> +>c_char_varying char varying(50),
> +>c_char_varying_upshift char varying(50) upshift,
> +>c_char_varying_not_casespecific char varying(50) not casespecific,
> +>c_varchar varchar(50),
> +>c_varchar_upshift varchar(50) upshift,
> +>c_varchar_not_casespecific varchar(50) not casespecific,
> +>c_nchar nchar(50),
> +>c_nchar_upshift nchar(50) upshift,
> +>c_nchar_not_casespecific nchar(50) not casespecific,
> +>c_nchar_varying nchar varying(50),
> +>c_nchar_varying_upshift nchar varying(50) upshift,
> +>c_nchar_varying_not_casespecific nchar varying(50) not casespecific,
> +>c_numeric numeric(9,2),
> +>c_numeric_unsigned numeric(9,2) unsigned,
> +>c_decimal decimal(9,2),
> +>c_decimal_unsigned decimal(9,2) unsigned,
> +>c_integer integer,
> +>c_integer_unsigned integer unsigned,
> +>c_largeint largeint,
> +>c_smallint smallint,
> +>c_smallint_unsigned smallint unsigned,
> +>c_float float(10),
> +>c_real real,
> +>c_double_precision double precision,
> +>c_date date,
> +>c_time time, -- default is (0)
> +>c_time5 time(5),
> +>c_timestamp timestamp, -- default is (6)
> +>c_timestamp5 timestamp(5),
> +>c_interval interval year to month,
> +>c_clob clob,
> +>c_blob blob
> +>);
> --- SQL operation complete.
> >>
> >>insert into myFullTable values
> +>('CHAR_1', 'char_1', 'char_1', 'CHARVAR_1', 'charvar_1', 'charvar_1', 'VARCHAR_1', 'varchar_1', 'varchar_1', 'NCHAR_1', 'nchar_1', 'nchar_1', 'NCHARVAR_1', 'ncharvar_1', 'ncharvar_1', -1, 1, -1.11, 1.11, -1, 1, -1, -1, 1, -1.11, -1.11, -1.11, date '2001-01-01', time '01:01:01', time '01:01:01.12345', timestamp '2001-01-01 01:01:01.123456', timestamp '2001-01-01 01:01:01.12345', interval '01-01' year to month, 'clob_1', 'blob_1'
> +>),
> +>('CHAR_2', 'char_2', 'char_2', 'CHARVAR_2', 'charvar_2', 'charvar_2', 'VARCHAR_2', 'varchar_2', 'varchar_2', 'NCHAR_2', 'nchar_2', 'nchar_2', 'NCHARVAR_2', 'ncharvar_2', 'ncharvar_2', -2, 2, -2.22, 2.22, -2, 2, -2, -2, 2, -2.22, -2.22, -2.22, date '2002-02-02', time '02:02:02', time '02:02:02.12345', timestamp '2002-02-02 02:02:02.123456', timestamp '2002-02-02 02:02:02.12345', interval '02-02' year to month, 'clob_2', 'blob_2'
> +>),
> +>('CHAR_3', 'char_3', 'char_3', 'CHARVAR_3', 'charvar_3', 'charvar_3', 'VARCHAR_3', 'varchar_3', 'varchar_3', 'NCHAR_3', 'nchar_3', 'nchar_3', 'NCHARVAR_3', 'ncharvar_3', 'ncharvar_3', -3, 3, -3.33, 3.33, -3, 3, -3, -3, 3, -3.33, -3.33, -3.33, date '2003-03-03', time '03:03:03', time '03:03:03.12345', timestamp '2003-03-03 03:03:03.123456', timestamp '2003-03-03 03:03:03.12345', interval '03-03' year to month, 'clob_3', 'blob_3'
> +>),
> +>('CHAR_4', 'char_4', 'char_4', 'CHARVAR_4', 'charvar_4', 'charvar_4', 'VARCHAR_4', 'varchar_4', 'varchar_4', 'NCHAR_4', 'nchar_4', 'nchar_4', 'NCHARVAR_4', 'ncharvar_4', 'ncharvar_4', -4, 4, -4.44, 4.44, -4, 4, -4, -4, 4, -4.44, -4.44, -4.44, date '2004-04-04', time '04:04:04', time '04:04:04.12345', timestamp '2004-04-04 04:04:04.123456', timestamp '2004-04-04 04:04:04.12345', interval '04-04' year to month, 'clob_4', 'blob_4'
> +>),
> +>(null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null
> +>)
> +>;
> --- 5 row(s) inserted.
> >>
> >>update statistics for table myFullTable on every column sample random 50 percent persistent;
> --- SQL operation complete.
> >>
> >>drop schema mysch3 cascade;
> *** ERROR[1069] Schema TRAFODION.MYSCH3 could not be dropped. Reason: schema MYSCH3 is not empty. ObjectsInSchema: MYFULLTABLE TRAF_SAMPLE_3002318618490724872_1471889356_69617
> *** ERROR[1073] Schema TRAFODION.MYSCH3 was partially dropped, call CLEANUP SCHEMA to remove remaining entries.
> --- SQL operation failed with errors.



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