http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/EXPECTED006 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED006 b/core/sql/regress/compGeneral/EXPECTED006 deleted file mode 100644 index 120b3c7..0000000 --- a/core/sql/regress/compGeneral/EXPECTED006 +++ /dev/null @@ -1,1235 +0,0 @@ ->>obey TEST006(create_tables); ->> ->>create table t006t1 (a varchar (200)); - ---- SQL operation complete. ->> ->>create table t006t2 (a int); - ---- SQL operation complete. ->> ->>create table t006t3 (a int not null); - ---- SQL operation complete. ->> ->>create table t006t4 (a char(10) not casespecific, b char(10)); - ---- SQL operation complete. ->> ->>create table t006t5 (a int not null, -+> b char(3) not null, -+> c date not null, -+> primary key(a)); - ---- SQL operation complete. ->> ->>create table t006t6 (a char(10) character set ucs2); - ---- SQL operation complete. ->> ->>create view t006t1_v as select a from t006t1; - ---- SQL operation complete. ->> ->>create table t006t7 (a float); - ---- SQL operation complete. ->> ->>create table t006t8 (a time); - ---- SQL operation complete. ->> ->>-- used for large scope rules ->>create table x1 (a int not null, b int not null primary key); - ---- SQL operation complete. ->>create table x2 (c int not null primary key, d int); - ---- SQL operation complete. ->>create table x3 (e int not null, f int) store by (e); - ---- SQL operation complete. ->> ->>insert into x1 values (1,1),(2,2); - ---- 2 row(s) inserted. ->>insert into x2 values (1,1),(2,2),(3,3),(4,4),(5,5); - ---- 5 row(s) inserted. ->> ->>create mv mvx refresh on request initialize on create as select a,sum(b) s_b -+>from x1 group by a; - ---- SQL operation complete. ->> ->>update statistics for table x1 on every column; - ---- SQL operation complete. ->>update statistics for table x2 on every column; - ---- SQL operation complete. ->>update statistics for table x3 on every column; - ---- SQL operation complete. ->> ->>obey TEST006(negative_tests); ->> ->>-- Error 2055 ->>control query default mp_subvolume 'raydddddd'; - -*** ERROR[2055] The specified value 'RAYDDDDDD' for DEFAULTS attribute MP_SUBVOLUME is not valid. - -*** ERROR[8822] The statement was not prepared. - ->>control query default mp_subvolume 'raydd~@'; - -*** ERROR[2055] The specified value 'RAYDD~@' for DEFAULTS attribute MP_SUBVOLUME is not valid. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 3233 ->>prepare xx from update with no rollback t006t4 set a = b; - -*** ERROR[3233] This type of UPDATE is not allowed when NO ROLLBACK transaction setting is in effect. Suggestion: Set ALLOW_RISKY_UPDATE_WITH_NO_ROLLBACK CQD to ON to allow UPDATE command with right-hand side SET clause consisting of columns. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 3416 ->>prepare xx from select coalesce(a, NULL) from t006t1; - -*** ERROR[3416] The last operand of function COALESCE must not be NULL. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4009 ->>prepare xx from select sum(max(a)) from t006t1; - -*** ERROR[4009] An aggregate is not allowed inside an aggregate function. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4022 ->>prepare xx from insert into t006t2(a, a) select a, a from t006t3; - -*** ERROR[4022] Target column A was specified more than once. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4026 ->>control query default COMP_BOOL_58 'ON'; - ---- SQL operation complete. ->>control query default MODE_SPECIAL_1 'ON'; - ---- SQL operation complete. ->>control query default R2_HALLOWEEN_SUPPORT 'ON'; - ---- SQL operation complete. ->>prepare s1 from insert into t006t3 select * from t006t3 where a in (select a from t006t3); - -*** ERROR[4026] Reading from and inserting into, or updating in, or deleting from the same table, CAT.SCH.T006T3, is not currently supported. - -*** ERROR[8822] The statement was not prepared. - ->>control query default R2_HALLOWEEN_SUPPORT reset; - ---- SQL operation complete. ->>control query default MODE_SPECIAL_1 reset; - ---- SQL operation complete. ->>control query default COMP_BOOL_58 reset; - ---- SQL operation complete. ->> ->>-- Error 4034 ->>prepare xx from select * from t006t1 where a = 1234 collate default; - -*** ERROR[4034] The operation (1234 COLLATE DEFAULT) is not allowed. - -*** ERROR[4073] The COLLATE clause may appear only after an expression of character data type, not NUMERIC. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4036 ->>prepare xx from select year(a) from t006t2; - -*** ERROR[4036] The source field of the EXTRACT function must be of DateTime or Interval type. - -*** ERROR[4062] The preceding error actually occurred in function YEAR. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4039 ->>prepare xx from insert into t006t3 values ('a'); - -*** ERROR[4039] Column A is of type INTEGER, incompatible with the value's type, CHAR(1). - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4043 ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1 where to_number(1112) = A; - -*** ERROR[4043] The operand of function TO_NUMBER must be character. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->>prepare xx from select tokenstr('a', a) from t006t2; - -*** ERROR[4043] The operand of function TOKENSTR must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select queryid_extract(a, b) from t006t5; - -*** ERROR[4043] The operand of function QUERYID_EXTRACT must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select os_userid(a) from t006t2; - -*** ERROR[4043] The operand of function OS_USERID must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select convertfromhex(a) from t006t2; - -*** ERROR[4043] The operand of function CONVERTFROMHEX must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select char_length(a) from t006t2; - -*** ERROR[4043] The operand of function CHARACTER_LENGTH, CHAR_LENGTH, or LENGTH must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select lower(a) from t006t2; - -*** ERROR[4043] The operand of function LOWER or LCASE must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select octet_length(a) from t006t2; - -*** ERROR[4043] The operand of function OCTET_LENGTH must be character. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4045 ->>prepare xx from select zeroifnull(a) from t006t1; - -*** ERROR[4045] The operand of function ZEROIFNULL must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select nullifzero(a) from t006t1; - -*** ERROR[4045] The operand of function NULLIFZERO must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select abs(a) from t006t1; - -*** ERROR[4045] The operand of function ABS must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select user(a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select ~a from t006t1; - -*** ERROR[4045] The operand of function BITNOT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select sin(a) from t006t1; - -*** ERROR[4045] The operand of function SIN must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A; - -*** ERROR[4045] The operand of function FORMAT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->> ->>-- Error 4046 ->>prepare xx from select ~(a) from t006t7; - -*** ERROR[4046] The operands of function BITNOT must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select bitextract(a, a, a) from t006t7; - -*** ERROR[4046] The operands of function BITEXTRACT must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select mod(a,a) from t006t1; - -*** ERROR[4046] The operands of function MOD must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select mod(a,a) from t006t7; - -*** ERROR[4046] The operands of function MOD must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select repeat('a', a) from t006t7; - -*** ERROR[4046] The operands of function REPEAT must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4047 ->>prepare xx from select round(3.224, 2.3) from t006t1; - -*** ERROR[4047] The operands of function ROUND must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select round(3.224, 3, 2.3) from t006t1; - -*** ERROR[4047] The operands of function ROUND must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select round(t006t2.a, 1.1) from t006t1, t006t2; - -*** ERROR[4047] The operands of function ROUND must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select ~(1.1) from t006t1; - -*** ERROR[4047] The operands of function BITNOT must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select bitextract(a, a, 1.1) from t006t2; - -*** ERROR[4047] The operands of function BITEXTRACT must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select mod(a,1.1) from t006t2; - -*** ERROR[4047] The operands of function MOD must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select repeat(a, 1.1) from t006t1; - -*** ERROR[4047] The operands of function REPEAT must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->> ->>control query default JDBC_PROCESS 'ON'; - ---- SQL operation complete. ->>prepare xx from select locate(a,a,1.1) from t006t1; - -*** ERROR[4047] The operands of function POSITION must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>control query default JDBC_PROCESS reset; - ---- SQL operation complete. ->> ->>prepare xx from select substring(a,1.1) from t006t1; - -*** ERROR[4047] The operands of function SUBSTRING must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select substring(a,1,1.1) from t006t1; - -*** ERROR[4047] The operands of function SUBSTRING must have a scale of 0. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4051 ->>prepare xx from select repeat(a, a) from t006t2; - -*** ERROR[4051] The first operand of function REPEAT must be character. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select substring(a,a) from t006t2; - -*** ERROR[4051] The first operand of function SUBSTRING must be character. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4052 ->>prepare xx from select a^b from t006t5; - -*** ERROR[4052] The second operand of function BITXOR must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select 1**a from t006t1; - -*** ERROR[4052] The second operand of function '**' must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select substring(a,a) from t006t1; - -*** ERROR[4052] The second operand of function SUBSTRING must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select round(t006t2.a, t006t1.a) from t006t1, t006t2; - -*** ERROR[4052] The second operand of function ROUND must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select repeat(a, a) from t006t1; - -*** ERROR[4052] The second operand of function REPEAT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select offset(a,a,a) from t006t1; - -*** ERROR[4052] The second operand of function OFFSET must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>control query default COMP_BOOL_58 'ON'; - ---- SQL operation complete. ->>control query default MODE_SPECIAL_1 'ON'; - ---- SQL operation complete. ->>prepare xx from select rows since(a = 11, a) from t006t1 sequence by a; - -*** ERROR[4052] The second operand of function ROWS SINCE must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_1 reset; - ---- SQL operation complete. ->>control query default COMP_BOOL_58 reset; - ---- SQL operation complete. ->> ->>-- Error 4053 ->>prepare xx from select bitextract(a, a, b) from t006t5; - -*** ERROR[4053] The third operand of function BITEXTRACT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select substring(a,1,a) from t006t1; - -*** ERROR[4053] The third operand of function SUBSTRING must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>control query default JDBC_PROCESS 'ON'; - ---- SQL operation complete. ->>prepare xx from select locate(a,a,a) from t006t1; - -*** ERROR[4053] The third operand of function POSITION must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>control query default JDBC_PROCESS reset; - ---- SQL operation complete. ->> ->>prepare xx from select insert(a, 1, 2.1, 3) from t006t2; - -*** ERROR[4053] The third operand of function INSERT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select round(t006t2.a, t006t2.a, t006t1.a) from t006t1, t006t2; - -*** ERROR[4053] The third operand of function ROUND must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select offset(a,1,a) from t006t1; - -*** ERROR[4053] The third operand of function OFFSET must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select movingcount(a, 1, a) from t006t1 sequence by a; - -*** ERROR[4053] The third operand of function MOVINGCOUNT must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4059 ->>prepare xx from select round(a) from t006t1; - -*** ERROR[4059] The first operand of function ROUND must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select trunc(a) from t006t1; - -*** ERROR[4059] The first operand of function TRUNC must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->>prepare xx from select a**a from t006t1; - -*** ERROR[4059] The first operand of function '**' must be numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4065 ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1 where to_date(A, '201005') = A; - -*** ERROR[4065] The format, "201005", specified in the TO_DATE function is not supported. - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A; - -*** ERROR[4065] The format, "YYYYMM", specified in the TO_DATE function is not supported. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->> ->>-- Error 4067 ->>prepare xx from select * from table (explain(NULL, 5)); - -*** ERROR[4067] The operands of function EXPLAIN must be character data types. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4068 ->>prepare xx from select convertfromhex(cast(a as char(1))) from t006t1; - -*** ERROR[4068] The operand of function CONVERTFROMHEX must contain an even number of characters. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4070 ->>prepare xx from select converttimestamp(a) from t006t1; - -*** ERROR[4070] The operand of function CONVERTTIMESTAMP must be exact numeric. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4071 ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1 where last_day(A) = A; - -*** ERROR[4071] The first operand of function LAST_DAY must be a datetime. - -*** ERROR[8822] The statement was not prepared. - ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->> ->>prepare xx from select juliantimestamp(a) from t006t1; - -*** ERROR[4071] The first operand of function JULIANTIMESTAMP must be a datetime. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4073 ->>prepare xx from select * from t006t1 where a = (select a from t006t3) collate default; - -*** ERROR[4034] The operation (SCALAR_AGGR(SCAN CAT.SCH.T006T3) COLLATE DEFAULT) is not allowed. - -*** ERROR[4073] The COLLATE clause may appear only after an expression of character data type, not INTEGER. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4078 ->>prepare xx from select variance(a, b) over (order by a) from t006t2; - -*** ERROR[4078] Function VARIANCE does not accept a weight operand. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4106 ->>prepare xx from select code_value(ascii(a)) from t006t6; - -*** ERROR[4106] The character set for the operand of function ASCII must be ISO88591. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4122 ->>prepare xx from insert into t006t3 values (NULL); - -*** ERROR[4122] NULL cannot be assigned to NOT NULL column CAT.SCH.T006T3.A. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4131 ->>alter table t006t1 add constraint t006t1_check check (t006t1.A = current_time); - -*** ERROR[4131] Current_time, current_date, or current_timestamp is not allowed in a check constraint. - -*** ERROR[1079] SQL was not able to prepare the statement. - ---- SQL operation failed with errors. ->> ->>-- Error 4132 ->>alter table t006t1 add constraint t006t1_check check (t006t1.A = current_user); - -*** ERROR[4132] Current_user, session_user, or system_user is not allowed in a check constraint. - -*** ERROR[1079] SQL was not able to prepare the statement. - ---- SQL operation failed with errors. ->> ->>-- Error 4185 ->>prepare xx from select sum(a) from t006t1 group by 1; - -*** ERROR[4185] Select list index is not allowed to be specified in the GROUP BY clause for this query. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4310 ->>prepare xx from select * from t006t1 where user(a) = a; - -*** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery. - -*** ERROR[4062] The preceding error actually occurred in function USER(CAT.SCH.T006T1.A). - -*** ERROR[8822] The statement was not prepared. - ->>prepare xx from select * from t006t1 where a = (select user(a) from t006t2); - -*** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery. - -*** ERROR[4062] The preceding error actually occurred in function USER(CAT.SCH.T006T2.A). - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4312 ->>prepare xx from update HP_SYSTEM_CATALOG.mxcs_schema.ASSOC2DS set assoc_id = 100; - -*** ERROR[4312] HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS is an MXCS metadata table and cannot be directly updated. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 4391 ->>prepare xx from select variance(a) over (order by ?p) from t006t2; - -*** ERROR[4391] Paramaters and outer references in the PARTITION BY or ORDER BY clause of a window function are not supported. - -*** ERROR[8822] The statement was not prepared. - ->> ->>-- Error 8403 ->>prepare xx from select substring(a,1,-1) from t006t1; - -*** ERROR[8403] The length argument of function SUBSTRING cannot be less than zero or greater than source string length. - -*** ERROR[8822] The statement was not prepared. - ->> ->>obey TEST006(positive_tests); ->> ->>-- Non-error coverage cases ->>prepare xx from select queryid_extract(a, b) from t006t4; - ---- SQL command prepared. ->>prepare xx from select round(3.224, 3, 2) from t006t1; - ---- SQL command prepared. ->>prepare xx from select user(a) from t006t1; - ---- SQL command prepared. ->> ->>control query default MODE_SPECIAL_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A; - ---- SQL command prepared. ->>control query default MODE_SPECIAL_3 reset; - ---- SQL operation complete. ->> ->>prepare xx from select current_running from t006t1; - ---- SQL command prepared. ->>prepare xx from select position(a in a) from t006t4; - ---- SQL command prepared. ->>prepare xx from select replace(a, a, a) from t006t4; - ---- SQL command prepared. ->> ->>control query default COMP_BOOL_58 'ON'; - ---- SQL operation complete. ->>control query default MODE_SPECIAL_1 'ON'; - ---- SQL operation complete. ->>prepare xx from select substring(a for 3) from t006t1; - ---- SQL command prepared. ->>prepare xx from select substring(a for 3) from t006t3; - ---- SQL command prepared. ->>prepare xx from select substring(c for 3) from t006t5; - ---- SQL command prepared. ->>prepare xx from select a from t006t1 group by 1 order by 1; - ---- SQL command prepared. ->>prepare xx from select a from t006t1 group by A order by A; - ---- SQL command prepared. ->>prepare xx from select * from t006t5 where c = 1231999; - ---- SQL command prepared. ->>prepare xx from select * from t006t5 where a = date'12/31/1999'; - ---- SQL command prepared. ->>prepare xx from select * from t006t5 where cast(a as interval day) = 1; - ---- SQL command prepared. ->>prepare xx from select * from t006t5 where 1 = interval '1' day; - ---- SQL command prepared. ->>control query default MODE_SPECIAL_1 reset; - ---- SQL operation complete. ->>control query default COMP_BOOL_58 reset; - ---- SQL operation complete. ->> ->>prepare xx from select date_trunc('YEAR',a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select date_trunc('YEAR',a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select date_trunc('month',a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select date_trunc('century',a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(year,a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(month,a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(hour,a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(minute,a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(second,a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select datediff(quarter,c, c) from t006t5; - ---- SQL command prepared. ->> ->>prepare xx from -+>select -+>decode(CAST (( a + 3) / 4 AS INT ) , -+>0,0.04, -+>1,0.04, -+>0.06) from t006t3; - ---- SQL command prepared. ->> ->>prepare xx from select count(*) from t006t1 where lpad(a,0,'') = 'aa'; - ---- SQL command prepared. ->>prepare xx from select count(*) from t006t1 where rpad(a,0,'') = 'aa'; - ---- SQL command prepared. ->> ->>prepare xx from select truncate(a, 2) from t006t3; - ---- SQL command prepared. ->>prepare xx from select os_userid(a) from t006t1; - ---- SQL command prepared. ->> ->>control query default ODBC_PROCESS 'ON'; - ---- SQL operation complete. ->>control query default COMP_BOOL_173 'ON'; - ---- SQL operation complete. ->>prepare xx from insert into t006t3 values (?p); - ---- SQL command prepared. ->>control query default ODBC_PROCESS reset; - ---- SQL operation complete. ->>control query default COMP_BOOL_173 reset; - ---- SQL operation complete. ->> ->>control query default INFER_CHARSET 'ON'; - ---- SQL operation complete. ->>prepare xx from select replace(a, a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select * from t006t1 where ?p like a; - ---- SQL command prepared. ->>prepare xx from select octet_length(a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select locate(a,a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select min(a) from t006t1; - ---- SQL command prepared. ->>control query default INFER_CHARSET reset; - ---- SQL operation complete. ->> ->>prepare xx from select lower(a) from t006t4; - ---- SQL command prepared. ->> ->>control query default PUBLIC_SCHEMA_NAME 'test_public_schema'; - ---- SQL operation complete. ->>create schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA; - ---- SQL operation complete. ->>Create table test_public_schema.t_pub1 (a int); - ---- SQL operation complete. ->>prepare xx from select * from t_pub1; - ---- SQL command prepared. ->>drop schema $$TEST_CATALOG$$.TEST_PUBLIC_SCHEMA cascade; - ---- SQL operation complete. ->>control query default PUBLIC_SCHEMA_NAME reset; - ---- SQL operation complete. ->> ->>control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW 'OFF'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1_v; - ---- SQL command prepared. ->>control query default ALLOW_ISOLATION_LEVEL_IN_CREATE_VIEW reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_192 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2 full outer join t006t3 on t006t2.a = t006t3.a; - ---- SQL command prepared. ->>control query default COMP_BOOL_192 reset; - ---- SQL operation complete. ->> ->>prepare xx from select * from table (explain(_UCS2'NULL', _UCS2's')); - ---- SQL command prepared. ->> ->>control query default COMP_BOOL_203 'ON'; - ---- SQL operation complete. ->>prepare xx from select rank(a) from t006t1 sequence by a; - ---- SQL command prepared. ->>control query default COMP_BOOL_203 reset; - ---- SQL operation complete. ->> ->>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a; - ---- SQL command prepared. ->>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset; - ---- SQL operation complete. ->> ->>control query default HIDE_INDEXES 'KEYINDEXES'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1; - ---- SQL command prepared. ->>control query default HIDE_INDEXES reset; - ---- SQL operation complete. ->> ->>prepare xx from select nullifzero(a) from t006t3; - ---- SQL command prepared. ->>prepare xx from select converttobits(a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select queryid_extract(a, a) from t006t1; - ---- SQL command prepared. ->>prepare xx from select tokenstr('a', a) from t006t1; - ---- SQL command prepared. ->> ->>prepare xx from select cast(?p as char(3) not null) from t006t1; - ---- SQL command prepared. ->> ->>control query default OVERRIDE_SYSKEY 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t7; - ---- SQL command prepared. ->>control query default OVERRIDE_SYSKEY reset; - ---- SQL operation complete. ->> ->>control query default VALIDATE_RFORK_REDEF_TS 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t7; - ---- SQL command prepared. ->>control query default VALIDATE_RFORK_REDEF_TS reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_108 'OFF'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3 where log(t006t2.a) = log(t006t3.a); - ---- SQL command prepared. ->>control query default COMP_BOOL_108 reset; - ---- SQL operation complete. ->> ->>prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where hour(t5_1.a) = minute(t5_2.a); - ---- SQL command prepared. ->>prepare xx from select * from t006t8 t5_1, t006t8 t5_2 where second(t5_1.a) = minute(t5_2.a); - ---- SQL command prepared. ->> ->>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a; - ---- SQL command prepared. ->>control query default MULTI_JOIN_CONSIDER_INITIAL_JOIN_ORDER reset; - ---- SQL operation complete. ->> ->>prepare xx from select * from t006t5 -+>where c >= current_date - interval '12' day and c < current_date; - ---- SQL command prepared. ->> ->>control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION '3'; - ---- SQL operation complete. ->>prepare xx from select * from t006t5 -+>where a > 15; - ---- SQL command prepared. ->>control query default HIST_OPTIMISTIC_CARD_OPTIMIZATION reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_42 'OFF'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3 where t006t2.a = t006t3.a; - ---- SQL command prepared. ->>control query default COMP_BOOL_42 reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_8 'ON'; - ---- SQL operation complete. ->>control query default ASM_PRECOMPUTE 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t1, t006t2, t006t3, t006t4, t006t5 where t006t2.a=t006t3.a and t006t1.a = t006t4.a and t006t3.a = t006t5.a and t006t5.b = t006t1.a; - ---- SQL command prepared. ->>control query default COMP_BOOL_8 reset; - ---- SQL operation complete. ->>control query default ASM_PRECOMPUTE reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_119 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3, t006t5 where t006t2.a=t006t3.a and t006t3.a = t006t5.a; - ---- SQL command prepared. ->>control query default COMP_BOOL_119 reset; - ---- SQL operation complete. ->> ->>control query default COMP_BOOL_25 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3, t006t5, t006t4 -+>where -+>t006t2.a=4 and t006t3.a = t006t5.a -+>or -+>t006t4.b=t006t5.b and t006t3.a = 5 -+>; - ---- SQL command prepared. ->>control query default COMP_BOOL_25 reset; - ---- SQL operation complete. ->> ->>control query default OPTIMIZER_HEURISTIC_3 'ON'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3, t006t5, t006t4 -+>where -+>t006t2.a=4 and t006t3.a = t006t5.a -+>or -+>t006t4.b=t006t5.b and t006t3.a = 5 -+>; - ---- SQL command prepared. ->>control query default OPTIMIZER_HEURISTIC_3 reset; - ---- SQL operation complete. ->> ->>control query default COMP_STRING_1 'T5'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2, t006t3, t006t5 as t5 where t006t2.a=t006t3.a and t006t3.a = t5.a; - ---- SQL command prepared. ->>control query default COMP_STRING_1 reset; - ---- SQL operation complete. ->> ->>-- coverage for Analyzer and largeScopeRules ->>obey TEST006(analyzer_tests); ->> ->>-- coverage for Analyzer.cpp method QueryAnalysis::newTableAnalysis ->>cqd COMP_BOOL_24 'ON'; - ---- SQL operation complete. ->>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a; - ---- SQL command prepared. ->>cqd COMP_BOOL_24 reset; - ---- SQL operation complete. ->> ->>-- coverage for Analyzer.cpp method QueryAnalysis::setHistogramsToDisplay ->>cqd HIST_ROOT_NODE '1,2,3,4'; - ---- SQL operation complete. ->>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a; - ---- SQL command prepared. ->>cqd HIST_ROOT_NODE reset; - ---- SQL operation complete. ->> ->>-- coverage for Analyzer.cpp method QueryAnalysis::computeTablesJoinedToLargestTable ->>cqd COMP_BOOL_119 'ON'; - ---- SQL operation complete. ->>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a; - ---- SQL command prepared. ->>cqd COMP_BOOL_119 reset; - ---- SQL operation complete. ->> ->>-- coverage for Analyzer.cpp method JBBSubsetAnalysis::isAStarPattern ->>cqd COMP_BOOL_12 'ON'; - ---- SQL operation complete. ->>prepare st1 from select a,sum(d) s_d from x1,x2 where b=c group by a; - ---- SQL command prepared. ->>cqd COMP_BOOL_12 reset; - ---- SQL operation complete. ->> ->>-- coverage for Analyzer.cpp method JBB::addJBBC ->>prepare st1 from select a,sum(b) s_b from x1 group by a; - ---- SQL command prepared. ->> ->>-- coverage for LargeScopeRules.cpp MJEnumRule::nextSubstitute ->>cqd COMP_INT_79 '2'; - ---- SQL operation complete. ->>prepare st1 from select a ,b from x1,x2,x3 where b=e; - ---- SQL command prepared. ->>cqd COMP_INT_79 reset; - ---- SQL operation complete. ->>cqd COMP_INT_51 '2'; - ---- SQL operation complete. ->>prepare st1 from select a ,b from x1,x2,x3 where b=e; - ---- SQL command prepared. ->>cqd COMP_INT_51 reset; - ---- SQL operation complete. ->> ->>obey TEST006(defaults_tests); ->> ->>-- The plan to support PUBLISHING_ROLES CQD has been ->>-- cancelled so the test cases are commented out. ->>--cqd PUBLISHING_ROLES 'a.b'; ->>--cqd PUBLISHING_ROLES reset; ->> ->>cqd POS 'off'; - ---- SQL operation complete. ->>cqd HIST_SCRATCH_VOL '\n.$a.b.c'; - -*** ERROR[2055] The specified value '\n.$a.b.c' for DEFAULTS attribute HIST_SCRATCH_VOL is not valid. - -*** ERROR[8822] The statement was not prepared. - ->>cqd HIST_SCRATCH_VOL reset; - ---- SQL operation complete. ->>cqd POS reset; - ---- SQL operation complete. ->> ->>cqd EID_TRACE_STR 'abc'; - ---- SQL operation complete. ->>cqd EID_TRACE_STR reset; - ---- SQL operation complete. ->> ->>cqd POS_ABSOLUTE_MAX_TABLE_SIZE '123,456,789'; - -*** ERROR[2055] The specified value '123,456,789' for DEFAULTS attribute POS_ABSOLUTE_MAX_TABLE_SIZE is not valid. - -*** ERROR[8822] The statement was not prepared. - ->>cqd POS_ABSOLUTE_MAX_TABLE_SIZE reset; - ---- SQL operation complete. ->> ->>cqd DEFAULT_SCHEMA_NAMETYPE 'user'; - ---- SQL operation complete. ->>cqd DEFAULT_SCHEMA_NAMETYPE reset; - ---- SQL operation complete. ->> ->>obey TEST006(misc_tests); ->> ->>-- Memory monitor infrastructure ->>control query default MEMORY_MONITOR 'ON'; - ---- SQL operation complete. ->>control query default MEMORY_MONITOR_LOG_INSTANTLY 'on'; - ---- SQL operation complete. ->>control query default MEMORY_MONITOR_LOGFILE 'mml.log'; - ---- SQL operation complete. ->>prepare xx from select * from t006t2; - ---- SQL command prepared. ->>control query default MEMORY_MONITOR 'OFF'; - ---- SQL operation complete. ->> ->>log;
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/EXPECTED006.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED006.SB b/core/sql/regress/compGeneral/EXPECTED006.SB index 3d1bf98..58b3b0c 100644 --- a/core/sql/regress/compGeneral/EXPECTED006.SB +++ b/core/sql/regress/compGeneral/EXPECTED006.SB @@ -60,9 +60,10 @@ >>create mv mvx refresh on request initialize on create as select a,sum(b) s_b +>from x1 group by a; -*** ERROR[1002] Catalog SEABASE does not exist or has not been registered on node \NSK. +*** ERROR[3131] The statement just entered is currently not supported. + +*** ERROR[8822] The statement was not prepared. ---- SQL operation failed with errors. >> >>update statistics for table x1 on every column; @@ -138,7 +139,7 @@ --- SQL operation complete. >>prepare s1 from insert into t006t3 select * from t006t3 where a in (select a >>from t006t3); -*** ERROR[4026] Reading from and inserting into, or updating in, or deleting from the same table, SEABASE.SCH.T006T3, is not currently supported. +*** ERROR[4026] Reading from and inserting into, or updating in, or deleting from the same table, TRAFODION.SCH.T006T3, is not currently supported. *** ERROR[8822] The statement was not prepared. @@ -275,16 +276,16 @@ *** ERROR[8822] The statement was not prepared. >> ->>control query default MODE_SPECIAL_3 'ON'; +>>control query default MODE_SPECIAL_4 'ON'; --- SQL operation complete. >>prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A; -*** ERROR[4045] The operand of function FORMAT must be numeric. +*** ERROR[4045] The operand of function TO_DATE must be numeric. *** ERROR[8822] The statement was not prepared. ->>control query default MODE_SPECIAL_3 reset; +>>control query default MODE_SPECIAL_4 reset; --- SQL operation complete. >> @@ -548,7 +549,7 @@ >> >>-- Error 4065 ->>control query default MODE_SPECIAL_3 'ON'; +>>control query default MODE_SPECIAL_4 'ON'; --- SQL operation complete. >>prepare xx from select * from t006t1 where to_date(A, '201005') = A; @@ -559,11 +560,11 @@ >>prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A; -*** ERROR[4065] The format, "YYYYMM", specified in the TO_DATE function is not supported. +*** ERROR[4041] Type DATE cannot be compared with type INTEGER. *** ERROR[8822] The statement was not prepared. ->>control query default MODE_SPECIAL_3 reset; +>>control query default MODE_SPECIAL_4 reset; --- SQL operation complete. >> @@ -615,7 +616,7 @@ >>-- Error 4073 >>prepare xx from select * from t006t1 where a = (select a from t006t3) >>collate default; -*** ERROR[4034] The operation (SCALAR_AGGR(SCAN SEABASE.SCH.T006T3) COLLATE DEFAULT) is not allowed. +*** ERROR[4034] The operation (SCALAR_AGGR(SCAN TRAFODION.SCH.T006T3) COLLATE DEFAULT) is not allowed. *** ERROR[4073] The COLLATE clause may appear only after an expression of character data type, not INTEGER. @@ -641,7 +642,7 @@ >>-- Error 4122 >>prepare xx from insert into t006t3 values (NULL); -*** ERROR[4122] NULL cannot be assigned to NOT NULL column SEABASE.SCH.T006T3.A. +*** ERROR[4122] NULL cannot be assigned to NOT NULL column TRAFODION.SCH.T006T3.A. *** ERROR[8822] The statement was not prepared. @@ -673,7 +674,7 @@ *** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery. -*** ERROR[4062] The preceding error actually occurred in function USER(SEABASE.SCH.T006T1.A). +*** ERROR[4062] The preceding error actually occurred in function USER(TRAFODION.SCH.T006T1.A). *** ERROR[8822] The statement was not prepared. @@ -681,7 +682,7 @@ *** ERROR[4310] USER(x) is currently supported only in the outermost SELECT list. For example, it cannot be a part of the subquery. -*** ERROR[4062] The preceding error actually occurred in function USER(SEABASE.SCH.T006T2.A). +*** ERROR[4062] The preceding error actually occurred in function USER(TRAFODION.SCH.T006T2.A). *** ERROR[8822] The statement was not prepared. @@ -689,7 +690,7 @@ >>-- Error 4312 >>prepare xx from update HP_SYSTEM_CATALOG.mxcs_schema.ASSOC2DS set assoc_id = >>100; -*** ERROR[4312] HP_SYSTEM_CATALOG.MXCS_SCHEMA.ASSOC2DS is an MXCS metadata table and cannot be directly updated. +*** ERROR[1002] Catalog HP_SYSTEM_CATALOG does not exist or has not been registered on node . *** ERROR[8822] The statement was not prepared. @@ -723,13 +724,13 @@ --- SQL command prepared. >> ->>control query default MODE_SPECIAL_3 'ON'; +>>control query default MODE_SPECIAL_4 'ON'; --- SQL operation complete. >>prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A; --- SQL command prepared. ->>control query default MODE_SPECIAL_3 reset; +>>control query default MODE_SPECIAL_4 reset; --- SQL operation complete. >> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/EXPECTED013.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED013.SB b/core/sql/regress/compGeneral/EXPECTED013.SB index 740cff2..97143c6 100644 --- a/core/sql/regress/compGeneral/EXPECTED013.SB +++ b/core/sql/regress/compGeneral/EXPECTED013.SB @@ -367,7 +367,55 @@ SCAN --- 2 row(s) inserted. >> +>>create table fksalta ( id int not null, PRIMARY KEY (id))SALT USING 4 PARTITIONS; + +--- SQL operation complete. +>> +>>CREATE TABLE fksaltb ( id int not null, val int, ++> PRIMARY KEY (id), ++> CONSTRAINT FKsalt FOREIGN KEY (val) REFERENCES fksalta (id)) ++> SALT USING 4 PARTITIONS; + +--- SQL operation complete. +>> +>>INSERT INTO fksalta VALUES ( 1 ); + +--- 1 row(s) inserted. +>> +>>INSERT INTO fksaltb VALUES ( 1, 1); + +--- 1 row(s) inserted. +>> +>>INSERT INTO fksaltb VALUES ( 2, 2); + +*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.FKSALT on table TRAFODION.SCH.FKSALTB. + +--- 0 row(s) inserted. +>> +>>CREATE TABLE fkdiva( store_id integer not null, ++> item_id integer not null, ++> sale_date date default date '2000-01-01' not null, ++> sale_amt numeric(10,2), ++> primary key (store_id, item_id, sale_date)) ++> division by (date_part('YEARMONTH', sale_date)); + +--- SQL operation complete. >> +>>create table fkdivb(c1 int not null, c2 int , c3 int, c4 date, primary key(c1), CONSTRAINT FKDIV FOREIGN KEY (c2,c3, c4) REFERENCES fkdiva (store_id,item_id, sale_date)); + +--- SQL operation complete. +>> +>>INSERT INTO fkdiva VALUES ( 1 , 1, date'2016-03-05', 10.2 ); + +--- 1 row(s) inserted. +>>INSERT INTO fkdivb VALUES ( 1 , 1, 1, date'2016-03-05'); + +--- 1 row(s) inserted. +>>INSERT INTO fkdivb VALUES ( 2 , 2, 1, date'2016-01-25'); + +*** ERROR[8103] The operation is prevented by referential integrity constraint TRAFODION.SCH.FKDIV on table TRAFODION.SCH.FKDIVB. + +--- 0 row(s) inserted. >> >>--- uk0 should be eliminated >>-- two table RI constraint join http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/EXPECTED042 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED042 b/core/sql/regress/compGeneral/EXPECTED042 index 74bb06b..55fb89e 100644 --- a/core/sql/regress/compGeneral/EXPECTED042 +++ b/core/sql/regress/compGeneral/EXPECTED042 @@ -2235,8 +2235,8 @@ NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS 314 0 3 1 314 2 1 0 422 2 1 1 - 471 0 2 3 - 471 1 2 3 + 487 0 2 3 + 487 1 2 3 492 0 2 1 492 1 2 1 619 0 1 14 @@ -2329,8 +2329,8 @@ NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS 314 0 3 1 314 2 1 0 422 2 1 1 - 471 0 2 3 - 471 1 2 3 + 487 0 2 3 + 487 1 2 3 492 0 2 1 492 1 2 1 619 0 1 14 http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/TEST006 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST006 b/core/sql/regress/compGeneral/TEST006 index 06a0ff4..e560192 100644 --- a/core/sql/regress/compGeneral/TEST006 +++ b/core/sql/regress/compGeneral/TEST006 @@ -159,9 +159,9 @@ prepare xx from select user(a) from t006t1; prepare xx from select ~a from t006t1; prepare xx from select sin(a) from t006t1; -control query default MODE_SPECIAL_3 'ON'; +control query default MODE_SPECIAL_4 'ON'; prepare xx from select * from t006t1 where to_date(A, '99:99:99:99') = A; -control query default MODE_SPECIAL_3 reset; +control query default MODE_SPECIAL_4 reset; -- Error 4046 prepare xx from select ~(a) from t006t7; @@ -233,10 +233,10 @@ control query default MODE_SPECIAL_3 reset; prepare xx from select a**a from t006t1; -- Error 4065 -control query default MODE_SPECIAL_3 'ON'; +control query default MODE_SPECIAL_4 'ON'; prepare xx from select * from t006t1 where to_date(A, '201005') = A; prepare xx from select * from t006t2 where to_date(A, 'YYYYMM') = A; -control query default MODE_SPECIAL_3 reset; +control query default MODE_SPECIAL_4 reset; -- Error 4067 prepare xx from select * from table (explain(NULL, 5)); @@ -295,9 +295,9 @@ prepare xx from select queryid_extract(a, b) from t006t4; prepare xx from select round(3.224, 3, 2) from t006t1; prepare xx from select user(a) from t006t1; -control query default MODE_SPECIAL_3 'ON'; +control query default MODE_SPECIAL_4 'ON'; prepare xx from select * from t006t2 where to_date(A, '99:99:99:99') = A; -control query default MODE_SPECIAL_3 reset; +control query default MODE_SPECIAL_4 reset; prepare xx from select current_running from t006t1; prepare xx from select position(a in a) from t006t4; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/compGeneral/TEST013 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/TEST013 b/core/sql/regress/compGeneral/TEST013 index ceca013..9e77bcf 100644 --- a/core/sql/regress/compGeneral/TEST013 +++ b/core/sql/regress/compGeneral/TEST013 @@ -167,7 +167,31 @@ create table t_013 ( a int not null, insert into t_013 values (1, 'abc', 'def'), (2, 'ghi', 'jkl'); +create table fksalta ( id int not null, PRIMARY KEY (id))SALT USING 4 PARTITIONS; +CREATE TABLE fksaltb ( id int not null, val int, + PRIMARY KEY (id), + CONSTRAINT FKsalt FOREIGN KEY (val) REFERENCES fksalta (id)) + SALT USING 4 PARTITIONS; + +INSERT INTO fksalta VALUES ( 1 ); + +INSERT INTO fksaltb VALUES ( 1, 1); + +INSERT INTO fksaltb VALUES ( 2, 2); + +CREATE TABLE fkdiva( store_id integer not null, + item_id integer not null, + sale_date date default date '2000-01-01' not null, + sale_amt numeric(10,2), + primary key (store_id, item_id, sale_date)) + division by (date_part('YEARMONTH', sale_date)); + +create table fkdivb(c1 int not null, c2 int , c3 int, c4 date, primary key(c1), CONSTRAINT FKDIV FOREIGN KEY (c2,c3, c4) REFERENCES fkdiva (store_id,item_id, sale_date)); + +INSERT INTO fkdiva VALUES ( 1 , 1, date'2016-03-05', 10.2 ); +INSERT INTO fkdivb VALUES ( 1 , 1, 1, date'2016-03-05'); +INSERT INTO fkdivb VALUES ( 2 , 2, 1, date'2016-01-25'); --- uk0 should be eliminated -- two table RI constraint join @@ -347,5 +371,9 @@ drop table uk1; drop table uk2; drop table uk3; drop table t_013; +drop table fksalta CASCADE; +drop table fksaltb CASCADE; +drop table fkdiva CASCADE; +drop table fkdivb CASCADE; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/EXPECTED001.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED001.SB b/core/sql/regress/core/EXPECTED001.SB index 2cbd15d..8870be4 100644 --- a/core/sql/regress/core/EXPECTED001.SB +++ b/core/sql/regress/core/EXPECTED001.SB @@ -13,11 +13,12 @@ >> >>invoke t001t1; --- Definition of Seabase table SEABASE.SCH.T001T1 --- Definition current Wed Jul 31 14:25:27 2013 +-- Definition of Trafodion table TRAFODION.SCH.T001T1 +-- Definition current Mon Mar 7 19:30:10 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL , C INT DEFAULT NULL @@ -30,11 +31,12 @@ >>#ifMX >>invoke $$TEST_SCHEMA$$.t001ut1; --- Definition of Seabase table SEABASE.SCH.T001UT1 --- Definition current Wed Jul 31 14:25:27 2013 +-- Definition of Trafodion table TRAFODION.SCH.T001UT1 +-- Definition current Mon Mar 7 19:30:10 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT DEFAULT NULL , C INT DEFAULT NULL @@ -184,19 +186,19 @@ A >> >>select b || NULL from t001t1; -*** ERROR[4097] A NULL operand is not allowed in function (SEABASE.SCH.T001T1.B || NULL). +*** ERROR[4097] A NULL operand is not allowed in function (TRAFODION.SCH.T001T1.B || NULL). *** ERROR[8822] The statement was not prepared. >>select NULL || b from t001t1; -*** ERROR[4097] A NULL operand is not allowed in function (NULL || SEABASE.SCH.T001T1.B). +*** ERROR[4097] A NULL operand is not allowed in function (NULL || TRAFODION.SCH.T001T1.B). *** ERROR[8822] The statement was not prepared. >>select a from t001t1 where c between 2 and null; -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.C BETWEEN 2 AND NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.C BETWEEN 2 AND NULL). *** ERROR[8822] The statement was not prepared. @@ -208,13 +210,13 @@ A >>select a from t001t1 where b like null; -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.B LIKE NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.B LIKE NULL). *** ERROR[8822] The statement was not prepared. >>select a from t001t1 where b like 'x' escape null; -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.B like 'x' escape NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.B like 'x' escape NULL). *** ERROR[8822] The statement was not prepared. @@ -261,7 +263,7 @@ A >>select DateFormat (null, usa) from t001t1; -*** ERROR[4097] A NULL operand is not allowed in function (DATEFORMAT(NULL)). +*** ERROR[4097] A NULL operand is not allowed in function (DATEFORMAT(NULL, USA)). *** ERROR[8822] The statement was not prepared. @@ -345,6 +347,18 @@ A *** ERROR[8822] The statement was not prepared. +>>select To_char (null, 'YYYYMMDD') from t001t1; + +*** ERROR[4097] A NULL operand is not allowed in function (TO_CHAR(NULL, 'YYYYMMDD')). + +*** ERROR[8822] The statement was not prepared. + +>>select To_date (null, 'YYYYMMDD') from t001t1; + +*** ERROR[4097] A NULL operand is not allowed in function (TO_DATE(NULL, 'YYYYMMDD')). + +*** ERROR[8822] The statement was not prepared. + >>select Trim (null) from t001t1; *** ERROR[4097] A NULL operand is not allowed in function TRIM, LTRIM or RTRIM. @@ -379,7 +393,7 @@ A >>select a from t001t1 where c in (1,null,2); -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.C = NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.C = NULL). *** ERROR[8822] The statement was not prepared. @@ -398,13 +412,13 @@ A >>select (case a when null then 1 else 2 end) from t001t1; -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.A = NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.A = NULL). *** ERROR[8822] The statement was not prepared. >>select (case a when 1 then 1 when null then 2 else 3 end) from t001t1; -*** ERROR[4099] A NULL operand is not allowed in predicate (SEABASE.SCH.T001T1.A = NULL). +*** ERROR[4099] A NULL operand is not allowed in predicate (TRAFODION.SCH.T001T1.A = NULL). *** ERROR[8822] The statement was not prepared. @@ -485,8 +499,8 @@ A B C D (EXPR) A B C D ----------- --------- ----------- ---- - 20 defg 30 wx 101 abc 20 xy + 20 defg 30 wx --- 2 row(s) selected. >> @@ -501,8 +515,8 @@ A B C D A B C D ----------- --------- ----------- ---- - 20 defg 30 wx 101 abc 20 xy + 20 defg 30 wx --- 2 row(s) selected. >> http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/EXPECTED037.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED037.SB b/core/sql/regress/core/EXPECTED037.SB index 144e600..809df01 100755 --- a/core/sql/regress/core/EXPECTED037.SB +++ b/core/sql/regress/core/EXPECTED037.SB @@ -75,10 +75,11 @@ >>invoke table_name; -- Definition of Trafodion table TRAFODION.SCH.TABLE_NAME --- Definition current Fri Aug 8 22:34:29 2014 +-- Definition current Thu Feb 25 06:24:08 2016 ( - ABSOLUTE INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , ABSOLUTE INT NO DEFAULT NOT NULL NOT DROPPABLE , ACCESS INT NO DEFAULT NOT NULL NOT DROPPABLE , ANSIVARCHAR INT NO DEFAULT NOT NULL NOT DROPPABLE , ASCENDING INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -223,10 +224,11 @@ >>invoke system_name; -- Definition of Trafodion table TRAFODION.SCH.SYSTEM_NAME --- Definition current Fri Aug 8 22:34:33 2014 +-- Definition current Thu Feb 25 06:24:10 2016 ( - LARGEINT INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , LARGEINT INT NO DEFAULT NOT NULL NOT DROPPABLE , LEADING_PRECISION INT NO DEFAULT NOT NULL NOT DROPPABLE , LENGTH INT NO DEFAULT NOT NULL NOT DROPPABLE , LINE_NUMBER INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -648,7 +650,7 @@ SELECT GENERAL GENERAL from (values(0)) GENERAL(GENERAL); *** ERROR[15001] A syntax error occurred at or before: SELECT IDENTITY IDENTITY from (values(0)) IDENTITY(IDENTITY); - ^ (24 characters from start of SQL statement) + ^ (15 characters from start of SQL statement) *** ERROR[8822] The statement was not prepared. @@ -1010,7 +1012,7 @@ SELECT ROUTINE ROUTINE from (values(0)) ROUTINE(ROUTINE); *** ERROR[15001] A syntax error occurred at or before: SELECT ROW ROW from (values(0)) ROW(ROW); - ^ (14 characters from start of SQL statement) + ^ (10 characters from start of SQL statement) *** ERROR[8822] The statement was not prepared. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/EXPECTED056.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED056.SB b/core/sql/regress/core/EXPECTED056.SB index c1621af..4eb2f50 100755 --- a/core/sql/regress/core/EXPECTED056.SB +++ b/core/sql/regress/core/EXPECTED056.SB @@ -2026,9 +2026,6 @@ T056T56_COL T056T56_COL2 T056T56_COL3 T056T56_COL4 --- 1 row(s) selected. >> ->>cqd hbase_filter_preds 'on' ; - ---- SQL operation complete. >>create table t056t57 (a1 numeric(2,2) signed default 0 not null); --- SQL operation complete. @@ -2153,9 +2150,6 @@ A1 .00 --- 1 row(s) selected. ->>cqd hbase_filter_preds '2' ; - ---- SQL operation complete. >> >>obey TEST056(tests4); >>obey test056(test4OLTops); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/EXPECTED061.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED061.SB b/core/sql/regress/core/EXPECTED061.SB index 7d026dd..f25fb2a 100755 --- a/core/sql/regress/core/EXPECTED061.SB +++ b/core/sql/regress/core/EXPECTED061.SB @@ -10,10 +10,11 @@ >>invoke t2421; -- Definition of Trafodion table TRAFODION.SCH.T2421 --- Definition current Sun Aug 17 13:04:04 2014 +-- Definition current Thu Feb 25 06:40:41 2016 ( - A DECIMAL(1, 0) UNSIGNED DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A DECIMAL(1, 0) UNSIGNED DEFAULT NULL , B DECIMAL(1, 1) UNSIGNED DEFAULT NULL , C DECIMAL(18, 8) DEFAULT NULL , D DECIMAL(2, 1) DEFAULT NULL @@ -47,10 +48,11 @@ A B C D E F H >>invoke t101t2; -- Definition of Trafodion table TRAFODION.SCH.T101T2 --- Definition current Sun Aug 17 13:04:07 2014 +-- Definition current Thu Feb 25 06:40:43 2016 ( - C01 CHAR(2) CHARACTER SET ISO88591 COLLATE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , C01 CHAR(2) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL , C02 CHAR(2) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL @@ -103,13 +105,14 @@ ab 4 abcdefghijklmnopqrstu 1 >>invoke t104t2; -- Definition of Trafodion table TRAFODION.SCH.T104T2 --- Definition current Sun Aug 17 13:04:09 2014 +-- Definition current Thu Feb 25 06:40:45 2016 ( C CHAR(15) CHARACTER SET ISO88591 COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE , D INT NO DEFAULT NOT NULL NOT DROPPABLE ) + PRIMARY KEY (C ASC, D ASC) --- SQL operation complete. >>showddl t104t2; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/EXPECTED116 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED116 b/core/sql/regress/core/EXPECTED116 index 42092e2..1128c8e 100644 --- a/core/sql/regress/core/EXPECTED116 +++ b/core/sql/regress/core/EXPECTED116 @@ -335,13 +335,17 @@ A1 C1 B1 +> c varchar(4) character set utf8 not null); --- SQL operation complete. +>>create view t116v1 as select * from t116t1; + +--- SQL operation complete. >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:04:39 2016 +-- Definition current Sun Mar 13 00:13:59 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE , B VARCHAR(2) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL , C VARCHAR(4 CHARS) CHARACTER SET UTF8 @@ -352,16 +356,46 @@ A1 C1 B1 >>begin work; --- SQL operation complete. +>>invoke t116t1; + +-- Definition of Trafodion table TRAFODION.SCH.T116T1 +-- Definition current Sun Mar 13 00:13:59 2016 + + ( + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(2) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:00 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(2) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >>alter table t116t1 alter column b varchar(3); --- SQL operation complete. >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:04:40 2016 +-- Definition current Sun Mar 13 00:14:10 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL /*altered_col*/ , C VARCHAR(4 CHARS) CHARACTER SET UTF8 @@ -369,16 +403,31 @@ A1 C1 B1 ) --- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:10 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >>commit work; --- SQL operation complete. >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:04:51 2016 +-- Definition current Sun Mar 13 00:14:14 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL /*altered_col*/ , C VARCHAR(4 CHARS) CHARACTER SET UTF8 @@ -386,20 +435,64 @@ A1 C1 B1 ) --- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:15 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >> >>begin work; --- SQL operation complete. +>>invoke t116t1; + +-- Definition of Trafodion table TRAFODION.SCH.T116T1 +-- Definition current Sun Mar 13 00:14:15 2016 + + ( + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL /*altered_col*/ + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:15 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >>alter table t116t1 alter column b varchar(4); --- SQL operation complete. >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:04:52 2016 +-- Definition current Sun Mar 13 00:14:25 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE , B VARCHAR(4) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL /*altered_col*/ , C VARCHAR(4 CHARS) CHARACTER SET UTF8 @@ -407,6 +500,20 @@ A1 C1 B1 ) --- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:25 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(4) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >>rollback work; --- SQL operation complete. @@ -414,10 +521,11 @@ A1 C1 B1 >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:05:01 2016 +-- Definition current Sun Mar 13 00:14:30 2016 ( - A INT NO DEFAULT NOT NULL NOT DROPPABLE + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT NO DEFAULT NOT NULL NOT DROPPABLE , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL /*altered_col*/ , C VARCHAR(4 CHARS) CHARACTER SET UTF8 @@ -425,9 +533,23 @@ A1 C1 B1 ) --- SQL operation complete. +>>invoke t116v1; + +-- Definition of Trafodion view TRAFODION.SCH.T116V1 +-- Definition current Sun Mar 13 00:14:30 2016 + + ( + A INT NO DEFAULT NOT NULL NOT DROPPABLE + , B VARCHAR(3) CHARACTER SET ISO88591 COLLATE + DEFAULT DEFAULT NULL + , C VARCHAR(4 CHARS) CHARACTER SET UTF8 + COLLATE DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE + ) + +--- SQL operation complete. >> >>-- concurrent ddl operations ->>drop table if exists t116t1; +>>drop table if exists t116t1 cascade; --- SQL operation complete. >>begin work; @@ -472,10 +594,11 @@ End of MXCI Session >>invoke t116t1; -- Definition of Trafodion table TRAFODION.SCH.T116T1 --- Definition current Mon Feb 15 16:05:22 2016 +-- Definition current Sun Mar 13 00:14:46 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL ) --- SQL operation complete. http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/TEST001 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST001 b/core/sql/regress/core/TEST001 index 023ac31..cd0ec0a 100755 --- a/core/sql/regress/core/TEST001 +++ b/core/sql/regress/core/TEST001 @@ -111,6 +111,8 @@ select Position (b in null) from t001t1; select rtrim (null) from t001t1; select Substring (null from 2) from t001t1; select Substring (b from null) from t001t1; +select To_char (null, 'YYYYMMDD') from t001t1; +select To_date (null, 'YYYYMMDD') from t001t1; select Trim (null) from t001t1; select Upper (null) from t001t1; select Upshift (null) from t001t1; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/TEST029 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST029 b/core/sql/regress/core/TEST029 index adfc463..7601251 100755 --- a/core/sql/regress/core/TEST029 +++ b/core/sql/regress/core/TEST029 @@ -39,7 +39,6 @@ obey TEST029(ddbMX); drop table MT; create table MT(a int); -- empty table, for NULL data source #ifMX -cqd hbase_filter_preds 'on' ; log LOG029 clear; obey TEST029(tests); obey TEST029(ddbMXMP); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/TEST056 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST056 b/core/sql/regress/core/TEST056 index 41b8e40..8629a17 100755 --- a/core/sql/regress/core/TEST056 +++ b/core/sql/regress/core/TEST056 @@ -807,7 +807,6 @@ alter table t056t56 add column t056t56_col3 pic 9 default +00; alter table t056t56 add column t056t56_col4 decimal default 0; select * from t056t56; -cqd hbase_filter_preds 'on' ; create table t056t57 (a1 numeric(2,2) signed default 0 not null); showddl t056t57; insert into t056t57 default values; @@ -832,7 +831,6 @@ create table t056t61 (a1 numeric(2,2) default -0.0 not null); showddl t056t61; insert into t056t61 default values; select * from t056t61; -cqd hbase_filter_preds '2' ; ?section test4ddl ------------------------------------------------ http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/core/TEST116 ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/TEST116 b/core/sql/regress/core/TEST116 index 9d04754..663a593 100755 --- a/core/sql/regress/core/TEST116 +++ b/core/sql/regress/core/TEST116 @@ -143,22 +143,31 @@ insert into t116t3 values(2,2,'b'); drop table if exists t116t1; create table t116t1(a int not null, b varchar(2), c varchar(4) character set utf8 not null); +create view t116v1 as select * from t116t1; invoke t116t1; begin work; +invoke t116t1; +invoke t116v1; alter table t116t1 alter column b varchar(3); invoke t116t1; +invoke t116v1; commit work; invoke t116t1; +invoke t116v1; begin work; +invoke t116t1; +invoke t116v1; alter table t116t1 alter column b varchar(4); invoke t116t1; +invoke t116v1; rollback work; invoke t116t1; +invoke t116v1; -- concurrent ddl operations -drop table if exists t116t1; +drop table if exists t116t1 cascade; begin work; create table t116t1 (a int); http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/executor/EXPECTED001 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED001 b/core/sql/regress/executor/EXPECTED001 index cc27d67..26e1f4e 100755 --- a/core/sql/regress/executor/EXPECTED001 +++ b/core/sql/regress/executor/EXPECTED001 @@ -32,11 +32,12 @@ >> >>invoke t001t1; --- Definition of table CAT.SCH.T001T1 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001T1 +-- Definition current Thu Feb 25 07:44:34 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B CHAR(9) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL , C INT DEFAULT NULL @@ -47,13 +48,13 @@ --- SQL operation complete. >>invoke t001tn; -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. --- SQL operation failed with errors. >>invoke t001t2; --- Definition of table CAT.SCH.T001T2 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001T2 +-- Definition current Thu Feb 25 07:44:36 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -63,12 +64,13 @@ , D CHAR(4) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>invoke t001t3; --- Definition of table CAT.SCH.T001T3 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001T3 +-- Definition current Thu Feb 25 07:44:38 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -78,17 +80,19 @@ , D CHAR(4) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL ) + PRIMARY KEY (A ASC, B ASC) --- SQL operation complete. >> >>#ifMX >>invoke $$TEST_SCHEMA$$.t001ut1; --- Definition of table CAT.SCH.T001UT1 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001UT1 +-- Definition current Thu Feb 25 07:44:38 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B CHAR(9) CHARACTER SET UCS2 COLLATE DEFAULT DEFAULT NULL , C INT DEFAULT NULL @@ -99,8 +103,8 @@ --- SQL operation complete. >>invoke $$TEST_SCHEMA_NAME$$.t001ut2; --- Definition of table CAT.SCH.T001UT2 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001UT2 +-- Definition current Thu Feb 25 07:44:39 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -110,12 +114,13 @@ , D CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>invoke t001ut3; --- Definition of table CAT.SCH.T001UT3 --- Definition current Tue Aug 10 22:34:12 2010 +-- Definition of Trafodion table TRAFODION.SCH.T001UT3 +-- Definition current Thu Feb 25 07:44:40 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -125,6 +130,7 @@ , D CHAR(4) CHARACTER SET UCS2 COLLATE DEFAULT DEFAULT NULL ) + PRIMARY KEY (A ASC, B ASC) --- SQL operation complete. >>#ifMX @@ -173,8 +179,8 @@ A B C D A B C D ----------- --------- ----------- ---- - 20 defg 30 wx 10 abc 20 xy + 20 defg 30 wx --- 2 row(s) selected. >>select * from t001t3; @@ -202,8 +208,8 @@ A B C D A B C D ----------- ------------------ ----------- -------- - 20 defg 30 wx 10 abc 20 xy + 20 defg 30 wx --- 2 row(s) selected. >>select * from t001ut3; @@ -211,8 +217,8 @@ A B C D A B C D ----------- ------------------ ----------- -------- - 20 defg 30 wx 10 abc 20 xy + 20 defg 30 wx --- 2 row(s) selected. >>#ifMX @@ -396,10 +402,10 @@ A D A B C D A B C D ----------- ------------------ ----------- -------- ----------- ------------------ ----------- -------- - 10 abc 20 xy 20 defg 30 wx 10 abc 20 xy 10 abc 20 xy - 20 defg 30 wx 20 defg 30 wx + 10 abc 20 xy 20 defg 30 wx 20 defg 30 wx 10 abc 20 xy + 20 defg 30 wx 20 defg 30 wx --- 4 row(s) selected. >>#ifMX @@ -554,14 +560,14 @@ select a from t001t1 where b like _SJIS'sj'; >> >>select b from t001tn UNION select d from t001tn; -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. >> -- not comparable >>select b from t001tn UNION ALL select d from t001tn; -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -569,7 +575,7 @@ select a from t001t1 where b like _SJIS'sj'; >>select * from ( +> select b from t001tn UNION ALL select d from t001tn) u(b) where b='x'; -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -577,7 +583,7 @@ select a from t001t1 where b like _SJIS'sj'; >>select * from ( +> select b from t001tn UNION ALL select d from t001tn) u(b) where b=(select b from t001tn); -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -586,7 +592,7 @@ select a from t001t1 where b like _SJIS'sj'; *** WARNING[3169] SJIS is not a known collation. -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -595,7 +601,7 @@ select a from t001t1 where b like _SJIS'sj'; *** WARNING[3169] SJIS is not a known collation. -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -605,7 +611,7 @@ select a from t001t1 where b like _SJIS'sj'; *** WARNING[3169] SJIS is not a known collation. -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -613,7 +619,7 @@ select a from t001t1 where b like _SJIS'sj'; *** WARNING[3169] XYZW is not a known collation. -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -622,7 +628,7 @@ select a from t001t1 where b like _SJIS'sj'; *** WARNING[3169] XYZW is not a known collation. -*** ERROR[4082] Object CAT.SCH.T001TN does not exist or is inaccessible. +*** ERROR[4082] Object TRAFODION.SCH.T001TN does not exist or is inaccessible. *** ERROR[8822] The statement was not prepared. @@ -663,8 +669,8 @@ A A A A ----------- ----------- - 10 20 10 10 + 10 20 20 10 --- 3 row(s) selected. @@ -675,8 +681,8 @@ A A A A ----------- ----------- - 10 20 10 10 + 10 20 20 10 --- 3 row(s) selected. @@ -696,10 +702,10 @@ A A A B C D A B C D ----------- --------- ----------- ---- ----------- --------- ----------- ---- - 10 abc 20 xy 20 defg 30 wx 10 abc 20 xy 10 abc 20 xy - 20 defg 30 wx 20 defg 30 wx + 10 abc 20 xy 20 defg 30 wx 20 defg 30 wx 10 abc 20 xy + 20 defg 30 wx 20 defg 30 wx --- 4 row(s) selected. >> @@ -737,8 +743,8 @@ A A B ----------- --------- - 20 defg 10 abc + 20 defg --- 2 row(s) selected. >> @@ -766,8 +772,8 @@ A A ----------- - 20 10 + 20 --- 2 row(s) selected. >>select x.a, x.b from (select a, b, c from t001ut1 natural join t001ut2) x >>join t001ut3 @@ -886,8 +892,8 @@ A B C D A B C D ----------- --------- ----------- ---- - 20 gfe 31 wx 10 abc 20 xy + 20 gfe 31 wx --- 2 row(s) selected. >> @@ -914,8 +920,8 @@ A B C D A B C D ----------- ------------------ ----------- -------- - 20 gfe 31 wx 10 abc 20 xy + 20 gfe 31 wx --- 2 row(s) selected. >>#ifMX http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/681cad66/core/sql/regress/executor/EXPECTED013.SB ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED013.SB b/core/sql/regress/executor/EXPECTED013.SB index 808f9ef..b83960c 100644 --- a/core/sql/regress/executor/EXPECTED013.SB +++ b/core/sql/regress/executor/EXPECTED013.SB @@ -21,10 +21,11 @@ >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:35:15 2016 +-- Definition current Sat Mar 12 07:39:38 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL ) --- SQL operation complete. @@ -49,12 +50,13 @@ A >>invoke t013t1; -- Definition of Trafodion volatile table T013T1 --- Definition current Sun Feb 21 07:35:27 2016 +-- Definition current Sat Mar 12 07:39:53 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE , B INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>insert into t013t1 values (1,2); @@ -111,10 +113,11 @@ A >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:36:26 2016 +-- Definition current Sat Mar 12 07:40:59 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL ) --- SQL operation complete. @@ -136,13 +139,14 @@ A >>invoke t013t1; -- Definition of Trafodion volatile table T013T1 --- Definition current Sun Feb 21 07:36:38 2016 +-- Definition current Sat Mar 12 07:41:18 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE , B INT DEFAULT NULL , C INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>insert into t013t1 values (1,2,3); @@ -175,10 +179,11 @@ A B C >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:36:52 2016 +-- Definition current Sat Mar 12 07:41:32 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL ) --- SQL operation complete. @@ -197,7 +202,7 @@ A >>invoke t013t1; -- Definition of Trafodion volatile table T013T1 --- Definition current Sun Feb 21 07:36:58 2016 +-- Definition current Sat Mar 12 07:41:38 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE @@ -205,6 +210,7 @@ A , C INT DEFAULT NULL , D INT DEFAULT NULL ) + PRIMARY KEY (A ASC) --- SQL operation complete. >>insert into t013t1 values (1,2,3,4); @@ -335,7 +341,7 @@ CREATE VIEW TRAFODION.T013_SCH.TEMPV AS >>-- this will return an error, no permanent table exists >>alter table t013t2 add column z int default 0 not null; -*** ERROR[4082] Object TRAFODION.T013_SCH.T013T2 does not exist or is inaccessible. +*** ERROR[1146] Object TRAFODION.T013_SCH.T013T2 could not be altered because it either does not exist or is not a table. --- SQL operation failed with errors. >> @@ -449,10 +455,11 @@ CONTROL QUERY DEFAULT >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:37:55 2016 +-- Definition current Sat Mar 12 07:42:40 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , Z INT DEFAULT 0 NOT NULL NOT DROPPABLE /*added_col*/ ) @@ -469,10 +476,11 @@ CONTROL QUERY DEFAULT >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:38:00 2016 +-- Definition current Sat Mar 12 07:42:49 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , Z INT DEFAULT 0 NOT NULL NOT DROPPABLE /*added_col*/ ) @@ -560,10 +568,11 @@ control query shape nested_join(anything,anything); >>invoke t013t3; -- Definition of Trafodion table TRAFODION.T013SCH1.T013T3 --- Definition current Sun Feb 21 07:38:45 2016 +-- Definition current Sat Mar 12 07:43:35 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B INT DEFAULT NULL ) @@ -577,11 +586,12 @@ control query shape nested_join(anything,anything); >>invoke t013t3; -- Definition of Trafodion volatile table T013T3 --- Definition current Sun Feb 21 07:38:50 2016 +-- Definition current Sat Mar 12 07:43:40 2016 ( A INT NO DEFAULT NOT NULL NOT DROPPABLE ) + PRIMARY KEY (A ASC) --- SQL operation complete. >> @@ -592,10 +602,11 @@ control query shape nested_join(anything,anything); >>invoke t013t3; -- Definition of Trafodion table TRAFODION.T013SCH1.T013T3 --- Definition current Sun Feb 21 07:39:03 2016 +-- Definition current Sat Mar 12 07:43:52 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B INT DEFAULT NULL ) @@ -657,10 +668,11 @@ CREATE VOLATILE TABLE T013T3 >>invoke t013t3; -- Definition of Trafodion table TRAFODION.T013SCH1.T013T3 --- Definition current Sun Feb 21 07:39:46 2016 +-- Definition current Sat Mar 12 07:44:35 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , B INT DEFAULT NULL ) @@ -1278,10 +1290,11 @@ CREATE VOLATILE TABLE T013T1 >>invoke t013t1; -- Definition of Trafodion table TRAFODION.T013_SCH.T013T1 --- Definition current Sun Feb 21 07:46:01 2016 +-- Definition current Sat Mar 12 07:51:06 2016 ( - A INT DEFAULT NULL + SYSKEY LARGEINT NO DEFAULT NOT NULL NOT DROPPABLE + , A INT DEFAULT NULL , Z INT DEFAULT 0 NOT NULL NOT DROPPABLE /*added_col*/ )
