[
https://issues.apache.org/jira/browse/TRAFODION-304?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14708675#comment-14708675
]
Atanu Mishra commented on TRAFODION-304:
----------------------------------------
Anoop Sharma (anoop-sharma) wrote on 2014-05-28: #2
Download full text (5.6 KiB)
Did not see this error.
In the test run, did the target table myb2ul04 exist before the
create...as...select stmt was issued?
[...]
Read more...
ying-wen ku (ying-wen-ku) wrote on 2014-05-28: RE: [Bug 1323864] Re: create
table as 106 column table return error 4023 #3
Download full text (24.3 KiB)
I don't recall. But mostly like it did exist.
But table was dropped before create table as command.
Thanks,
ying
-----Original Message-----
From: <email address hidden> [mailto:<email address hidden>] On Behalf Of
Sharma, Anoop
Sent: Wednesday, May 28, 2014 9:19 AM
To: Ku, Ying-Wen
Subject: [Bug 1323864] Re: create table as 106 column table return error 4023
Did not see this error.
In the test run, did the target table myb2ul04 exist before the
create...as...select stmt was issued?
[...]
Stacey Johnson (sjohnson-w) on 2014-06-10
information type: Proprietary → Public
Anoop Sharma (anoop-sharma) on 2014-06-27
Changed in trafodion:
status: New → In Progress
Anoop Sharma (anoop-sharma) on 2014-07-11
Changed in trafodion:
status: In Progress → Fix Committed
Alice Chen (alchen) on 2014-10-15
Changed in trafodion:
milestone: none → r0.8
status: Fix Committed → Fix Released
> LP Bug: 1323864 - create table as 106 column table return error 4023
> --------------------------------------------------------------------
>
> Key: TRAFODION-304
> URL: https://issues.apache.org/jira/browse/TRAFODION-304
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Apache Trafodion
> Assignee: Anoop Sharma
> Priority: Critical
> Fix For: 0.8 (pre-incubation)
>
>
> 1. table g_sqldopt.b2uwl04 has 106 columns
> 2. create table as ... return error 4023
> SQL>create table myb2ul04 store by (SDEC9_UNIQ)
> as (select * from g_sqldopt.b2uwl04);
> *** ERROR[4023] The degree of each row value constructor (106) must equal the
> degree of the target table column list (80). [2014-05-27 15:06:37]
> SQL>select * from myb2ul04;
> *** ERROR[4082] Object TRAFODION.USR.MYB2UL04 does not exist or is
> inaccessible. [2014-05-27 15:06:37]
> create schema g_sqldopt;
> set schema g_sqldopt;
> Create Table b2uwl04 (
> sbin0_4 Integer default 3 not null,
> time0_uniq Time not null,
> varchar0_uniq VarChar(8) no default not null,
> sdec0_100 Decimal(9) no default not null,
> int0_dTOf6_4 Interval day to second(6) not null,
> ts1_n100 Timestamp
> heading 'ts1_n100 allowing nulls',
> sdec1_20 Decimal(5) no default not null,
> int1_yTOm_n100 Interval year(1) to month no default,
> double1_2 Double Precision not null,
> udec1_nuniq Decimal(4) unsigned ,
> char2_2 Character(2) not null,
> sbin2_nuniq Largeint ,
> sdec2_500 Decimal(9) signed no default not null,
> date2_uniq Date not null,
> int2_dTOf6_n2 Interval day to second(6) no default,
> real2_500 Real not null,
> real3_n1000 Real ,
> int3_yTOm_4 Interval year(1) to month no default not null,
> date3_n2000 Date no default,
> udec3_n100 Decimal(9) unsigned ,
> ubin3_n2000 Numeric(4) unsigned ,
> char3_4 Character(8) no default not null,
> sdec4_n20 Decimal(4) no default,
> int4_yTOm_uniq Interval year(5) to month not null,
> sbin4_n1000 Smallint ,
> time4_1000 Time no default not null,
> char4_n10 Character(8) no default,
> real4_2000 Real not null,
> char5_n20 Character(8) ,
> sdec5_10 Decimal(9) signed no default not null,
> ubin5_n500 Numeric(9) unsigned no default,
> real5_uniq Real not null,
> dt5_yTOmin_n500 Timestamp(0) ,
> int5_hTOs_500 Interval hour to second(0) no default not null,
> int6_dTOf6_nuniq Interval day to second(6) no default,
> sbin6_nuniq Largeint no default,
> double6_n2 Float(23) ,
> sdec6_4 Decimal(4) signed no default not null,
> char6_n100 Character(8) no default,
> date6_100 Date not null,
> time7_uniq Time not null,
> sbin7_n20 Smallint no default,
> char7_500 Character(8) no default not null,
> int7_hTOs_nuniq Interval hour(2) to second(0) ,
> udec7_n10 Decimal(4) unsigned ,
> real7_n4 Real ,
> ubin8_10 Numeric(4) unsigned not null,
> int8_y_n1000 Interval year(3) ,
> date8_10 Date no default not null,
> char8_n1000 Character(8) no default,
> double8_n10 Double Precision no default,
> sdec8_4 Decimal(9) unsigned not null,
> sdec9_uniq Decimal(18) signed no default not null,
> real9_n20 Real ,
> time9_n4 Time ,
> char9_100 Character(2) no default not null,
> int9_dTOf6_2000 Interval day to second(6) no default not null,
> ubin9_n4 Numeric(9) unsigned no default,
> ubin10_n2 Numeric(4) unsigned no default,
> char10_nuniq Character(8) ,
> int10_d_uniq Interval day(6) not null,
> ts10_n2 Timestamp ,
> real10_100 Real not null,
> udec10_uniq Decimal(9) unsigned no default not null,
> udec11_2000 Decimal(9) unsigned no default not null,
> int11_h_n10 Interval hour(1) no default,
> sbin11_100 Integer not null,
> time11_20 Time not null,
> char11_uniq Character(8) not null,
> double11_n100 Double Precision ,
> real12_n20 Real ,
> ubin12_2 Numeric(4) unsigned no default not null,
> dt12_mTOh_1000 Timestamp(0) no default not null,
> sdec12_n1000 Decimal(18) signed no default,
> char12_n2000 Character(8) no default,
> int12_yTOm_100 Interval year to month not null,
> int13_yTOm_n1000 Interval year to month ,
> udec13_500 Decimal(9) unsigned no default not null,
> sbin13_n100 PIC S9(8)V9 COMP no default,
> ts13_uniq Timestamp not null,
> char13_1000 Character(8) not null,
> real13_n1000 Real ,
> sbin14_1000 Integer no default not null,
> double14_nuniq Float(23) no default,
> udec14_100 Decimal(4) unsigned not null,
> char14_n500 Character(8) ,
> int14_d_500 Interval day(3) no default not null,
> ts14_n100 Timestamp no default,
> dt15_mTOh_n100 Timestamp(0) no default,
> double15_uniq Double Precision not null,
> sbinneg15_nuniq Largeint ,
> sdecneg15_100 Decimal(9) signed no default not null,
> int15_dTOf6_n100 Interval day to second(6) no default,
> char15_100 Character(8) not null,
> dt16_m_n10 Date ,
> int16_h_20 Interval hour no default not null,
> ubin16_n10 Numeric(4) unsigned no default,
> sdec16_uniq Decimal(18) signed not null,
> char16_n20 Character(5) , -- len = 2,4
> real16_10 Real no default not null,
> int17_y_n10 Interval year(1) no default,
> dt17_yTOmin_uniq Timestamp(0) not null,
> real17_n100 Real ,
> sbin17_uniq Largeint no default not null,
> -- range: 0-149999
> sdec17_nuniq Decimal(18) no default,
> char17_2 Character(8) not null,
> primary key ( sdec9_uniq ASC,
> sdec0_100 DESC,
> sdec1_20 ASC
> )
> )
> store by primary key;
> insert into b2uwl04 values (
> 3,
> time '00:14:10',
> 'EKAALAAC',
> 88,
> interval '0 00:00:03.375000' day to second(6),
> timestamp '2100-01-01 00:00:56.250000',
> 12,
> interval '7-04' year(1) to month,
> 1.0001,
> 850,
> 'AA',
> 4652,
> 152,
> date '2112-09-27',
> interval '0 00:00:00.000000' day to second(6),
> 10.052,
> 101.88,
> interval '0-00' year(1) to month,
> date '2101-08-12',
> 88,
> 588,
> 'AAAAAAAA',
> 3,
> interval '151-11' year(5) to month,
> 823,
> time '00:13:43',
> 'BDAAAAAA',
> 10223.0,
> 'CAAAAAAA',
> 0,
> 350,
> 1.085,
> timestamp '2100-01-01 05:50:00',
> interval '0:05:50' hour to second(0),
> interval '0 01:27:13.500000' day to second(6),
> 4652,
> 1.0,
> 0,
> 'ACAAAAAA',
> date '2100-02-22',
> time '00:43:08',
> 8,
> 'FEAAFAAA',
> interval '0:43:08' hour(2) to second(0),
> 8,
> 1.0,
> 3,
> interval '823' year(3),
> date '2100-01-04',
> 'DIAADAAA',
> 1.0003,
> 3,
> 2372,
> 10.0,
> time '00:00:02',
> 'CA',
> interval '0 00:15:56.250000' day to second(6),
> 2,
> 0,
> 'EKAALAAC',
> interval '4652' day(6),
> timestamp '2100-01-01 00:00:00.000000',
> 100.02,
> 4652,
> 588,
> interval '8' hour(1),
> 88,
> time '00:00:08',
> 'FDAABAAD',
> 1001.3000000000003,
> 1.0003,
> 1,
> timestamp '2000-02-04 07:00:00',
> 823,
> 'DIAADAAB',
> interval '1-11' year to month,
> interval '70-10' year to month,
> 350,
> 5.0,
> timestamp '2100-01-01 00:15:56.250000',
> 'DDAAFAAA',
> 10050.0,
> 652,
> 10652.000000000004,
> 52,
> 'EEAAIAAA',
> interval '152' day(3),
> timestamp '2100-01-01 00:00:58.500000',
> timestamp '2000-01-04 16:00:00',
> 105.88000000000002,
> -2588,
> -88,
> interval '0 00:01:39.000000' day to second(6),
> 'ANAAAAAA',
> date '2000-04-01',
> interval '3' hour,
> 3,
> 1823,
> 'BD',
> 1.0003,
> interval '0' year(1),
> timestamp '2100-01-01 14:10:00',
> 100.0,
> 77041,
> 850,
> 'AAAAAAAA'
> );
> insert into b2uwl04 values (
> 1,
> time '01:05:08',
> 'AEAAFAAE',
> 88,
> interval '0 00:00:01.125000' day to second(6),
> timestamp '2100-01-01 00:00:09.000000',
> 14,
> interval '7-04' year(1) to month,
> 1.0001,
> 3908,
> 'AA',
> 4074,
> 74,
> date '2111-02-27',
> interval '0 00:00:00.000000' day to second(6),
> 1.0074,
> 101.88,
> interval '0-00' year(1) to month,
> date '2101-08-12',
> 88,
> 588,
> 'AAAAAAAA',
> 17,
> interval '408-01' year(5) to month,
> 897,
> time '00:14:57',
> 'BCAAAAAA',
> 100.97,
> 'ADAAAAAA',
> 8,
> 408,
> 1090.8,
> timestamp '2100-01-01 06:48:00',
> interval '0:06:48' hour to second(0),
> interval '0 01:16:23.250000' day to second(6),
> 4074,
> 1.0,
> 2,
> 'CYAAAAAA',
> date '2100-03-16',
> time '00:09:48',
> 8,
> 'AEAADAAA',
> interval '0:09:48' hour(2) to second(0),
> 8,
> 1.0,
> 7,
> interval '897' year(3),
> date '2100-01-08',
> 'ECAAJAAA',
> 10.002,
> 1,
> 1553,
> 1.0008,
> time '00:00:00',
> 'AI',
> interval '0 00:35:46.500000' day to second(6),
> 0,
> 0,
> 'AEAAFAAE',
> interval '4074' day(6),
> timestamp '2100-01-01 00:00:00.000000',
> 100.24,
> 4074,
> 588,
> interval '8' hour(1),
> 88,
> time '00:00:08',
> 'AFAADAAD',
> 1001.3000000000003,
> 10.007,
> 1,
> timestamp '2000-02-07 09:00:00',
> 897,
> 'ECAAJAAB',
> interval '8-01' year to month,
> interval '75-08' year to month,
> 408,
> 0.8,
> timestamp '2100-01-01 01:13:16.500000',
> 'CDAAIAAA',
> 10108.0,
> 74,
> 10074.000000000002,
> 74,
> 'ACAAGAAA',
> interval '74' day(3),
> timestamp '2100-01-01 00:01:23.250000',
> timestamp '2000-01-04 16:00:00',
> 1.0588,
> -588,
> -88,
> interval '0 00:01:39.000000' day to second(6),
> 'ANAAAAAA',
> date '2000-08-01',
> interval '17' hour,
> 7,
> 4897,
> 'BCAA',
> 10.002,
> interval '8' year(1),
> timestamp '2100-01-03 17:08:00',
> 1.0008,
> 43570,
> 3908,
> 'AAAAAAAA'
> );
> insert into b2uwl04 values (
> 1,
> time '00:56:32',
> 'EAAAJAAB',
> 88,
> interval '0 00:00:01.125000' day to second(6),
> timestamp '2100-01-01 00:01:43.500000',
> 6,
> interval '7-04' year(1) to month,
> 1.0001,
> 3392,
> 'AA',
> 1166,
> 166,
> date '2103-03-13',
> interval '0 00:00:00.000000' day to second(6),
> 10.066,
> 101.88,
> interval '0-00' year(1) to month,
> date '2101-08-12',
> 88,
> 588,
> 'AAAAAAAA',
> 13,
> interval '126-01' year(5) to month,
> 513,
> time '00:08:33',
> 'BDAAAAAA',
> 1031.3,
> 'ACAAAAAA',
> 2,
> 392,
> 1039.2,
> timestamp '2100-01-01 06:32:00',
> interval '0:06:32' hour to second(0),
> interval '0 00:21:51.750000' day to second(6),
> 1166,
> 1.0,
> 2,
> 'CQAAAAAA',
> date '2100-03-08',
> time '01:16:28',
> 8,
> 'DEAAHAAA',
> interval '1:16:28' hour(2) to second(0),
> 8,
> 1.0,
> 3,
> interval '513' year(3),
> date '2100-01-04',
> 'BGAAFAAA',
> 1.0003,
> 1,
> 194,
> 10.002,
> time '00:00:00',
> 'AR',
> interval '0 00:26:06.000000' day to second(6),
> 0,
> 0,
> 'EAAAJAAB',
> interval '1166' day(6),
> timestamp '2100-01-01 00:00:00.000000',
> 100.16,
> 1166,
> 588,
> interval '8' hour(1),
> 88,
> time '00:00:08',
> 'DBAAMAAD',
> 1001.3000000000003,
> 10.003,
> 1,
> timestamp '2000-01-22 09:00:00',
> 513,
> 'BGAAFAAB',
> interval '1-01' year to month,
> interval '32-08' year to month,
> 392,
> 9.2,
> timestamp '2100-01-01 01:03:36.000000',
> 'EEAAMAAA',
> 10.192,
> 166,
> 10.166,
> 66,
> 'EGAAFAAA',
> interval '166' day(3),
> timestamp '2100-01-01 00:01:14.250000',
> timestamp '2000-01-04 16:00:00',
> 10588.000000000004,
> -4588,
> -88,
> interval '0 00:01:39.000000' day to second(6),
> 'ANAAAAAA',
> date '2000-04-01',
> interval '13' hour,
> 3,
> 1513,
> 'BD',
> 1.0003,
> interval '2' year(1),
> timestamp '2100-01-03 08:32:00',
> 1001.7,
> 144407,
> 3392,
> 'AAAAAAAA'
> );
> -- test scripts
> log t1log clear;
> set schema usr;
> drop table myb2ul04 cascade;
> showddl g_sqldopt.b2uwl04;
> create table myb2ul04 store by (SDEC9_UNIQ)
> as (select * from g_sqldopt.b2uwl04);
> select * from myb2ul04;
> log off;
> exit;
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
