You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "ASF GitHub Bot (JIRA)" <ji...@apache.org> on 2016/09/14 18:06:20 UTC
[jira] [Commented] (TRAFODION-2220) INC UPD STATS: update stats
returns 8102 unique constraint error
[ https://issues.apache.org/jira/browse/TRAFODION-2220?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15491080#comment-15491080 ]
ASF GitHub Bot commented on TRAFODION-2220:
-------------------------------------------
GitHub user DaveBirdsall opened a pull request:
https://github.com/apache/incubator-trafodion/pull/705
[TRAFODION-2220] Change handling of manual persistent sample tables
Changed the handling of persistent sample tables (that is, those created by UPDATE STATISTICS ... CREATE SAMPLE) to be the same as those created using the PERSISTENT keyword (that is, those created by UPDATE STATISTICS ... SAMPLE RANDOM PERSISTENT). This is a simpler model; now these tables are used interchangeably.
As before, we still limit a table to having at most one persistent sample table.
Contrary to before, the behavior when creating a persistent sample table (via either syntax) when one already exists has been changed. Before we would silently drop the earlier table; now the UPDATE STATISTICS statement will fail with error 9251. This is better behavior, because it prevents a user from accidentally destroying an existing persistent sample. The user has to explicitly remove it instead.
So, in the test script for this JIRA, the last UPDATE STATISTICS command will now fail with error 9251 (rather than the mysterious error 8102).
You can merge this pull request into a Git repository by running:
$ git pull https://github.com/DaveBirdsall/incubator-trafodion Trafodion2220
Alternatively you can review and apply these changes as the patch at:
https://github.com/apache/incubator-trafodion/pull/705.patch
To close this pull request, make a commit to your master/trunk branch
with (at least) the following in the commit message:
This closes #705
----
commit b3e01ba3ea9ca964ef4086b516b89eb2bf2d43cb
Author: Dave Birdsall <db...@apache.org>
Date: 2016-09-14T17:57:36Z
[TRAFODION-2220] Change handling of manual persistent sample tables
----
> INC UPD STATS: update stats returns 8102 unique constraint error
> ----------------------------------------------------------------
>
> Key: TRAFODION-2220
> URL: https://issues.apache.org/jira/browse/TRAFODION-2220
> Project: Apache Trafodion
> Issue Type: Bug
> Components: sql-cmp
> Affects Versions: 2.1-incubating
> Reporter: David Wayne Birdsall
> Assignee: David Wayne Birdsall
>
> The following sequence of statements returns a 8102 error indicating unique constraint violation. I was expecting the persistent sample table to be dropped and replaced by the new one.
> >>create schema mysch2;
> --- SQL operation complete.
> >>set schema mysch2;
> --- 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.
> >>update statistics for table myFullTable create sample random 100 percent;
> *** ERROR[9200] UPDATE STATISTICS for table TRAFODION.MYSCH2.MYFULLTABLE encountered an error (8102) from statement Process_Query.
> *** ERROR[8102] The operation is prevented by a unique constraint.
> --- SQL operation failed with errors.
> >>
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)