http://git-wip-us.apache.org/repos/asf/trafodion/blob/43984c46/core/sql/regress/compGeneral/EXPECTED042 ---------------------------------------------------------------------- diff --git a/core/sql/regress/compGeneral/EXPECTED042 b/core/sql/regress/compGeneral/EXPECTED042 index e766c48..1cd8b5f 100644 --- a/core/sql/regress/compGeneral/EXPECTED042 +++ b/core/sql/regress/compGeneral/EXPECTED042 @@ -1,9 +1,9 @@ >>showstats for table t042_orderline on ol_o_id detail; Detailed Histogram data for Table TRAFODION.ORDERENTRY.T042_ORDERLINE -Table ID: 1700229370398217975 +Table ID: 8379103414832633643 -Hist ID: 767276153 +Hist ID: 1428267625 Column(s): OL_O_ID Total Rows: 10 Total UEC: 4 @@ -1725,10 +1725,6 @@ SQL query=select '0123456789' from t042_t1; HQC key=SELECT #NP# FROM T042_T1 ; HQC::AddEntry(): passed -SQL query=select '' from t042_t1; -HQC key=SELECT #NP# FROM T042_T1 ; - -HQC::AddEntry(): passed SQL query=select b from t042_t1 where a like 'BOO_%'; HQC key=SELECT B FROM T042_T1 WHERE A LIKE #NP# ; @@ -1763,18 +1759,18 @@ HQC key=SELECT T . VARCHAR0_UNIQ AS T_VARCHAR0_UNIQ , T . CHAR2_2 AS T_CHAR2_2 , >>-- test compile time >> >>sh more /proc/loadavg | cut -d' ' -f 1-3 | sed -e 's/^/System load: /' >> >>LOG042; -System load: 0.37 0.36 0.74 +System load: 1.39 2.74 2.71 >>sh grep "model name" /proc/cpuinfo | head -1 | cut -d '@' -f 2 | sed -e >>'s/^/CPU frequency: /' >> LOG042; -CPU frequency: 2.40GHz +CPU frequency: model name : Intel Core Processor (Haswell) >>set statistics on; >>prepare xx from select * from t042_orderline where ol_o_id = 1 ; --- SQL command prepared. -Start Time 2017/01/31 17:26:48.806392 -End Time 2017/01/31 17:26:48.808005 -Elapsed Time 00:00:00.001613 -Compile Time 00:00:00.001613 +Start Time 2018/03/22 01:04:54.921227 +End Time 2018/03/22 01:04:54.924930 +Elapsed Time 00:00:00.003703 +Compile Time 00:00:00.003703 Execution Time 00:00:00.000000 @@ -1805,10 +1801,10 @@ HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; --- SQL command prepared. -Start Time 2017/01/31 17:26:48.970491 -End Time 2017/01/31 17:26:48.972007 -Elapsed Time 00:00:00.001516 -Compile Time 00:00:00.001516 +Start Time 2018/03/22 01:04:55.115150 +End Time 2018/03/22 01:04:55.117539 +Elapsed Time 00:00:00.002389 +Compile Time 00:00:00.002389 Execution Time 00:00:00.000000 @@ -1841,7 +1837,7 @@ HQC key=SELECT * FROM T042_ORDERLINE WHERE OL_O_ID = #NP# ; NUM_ENTRIES TEXT_ENTRIES NUM_PLANS ----------- ------------ ---------- - 65 10 65 + 64 9 64 --- 1 row(s) selected. >>select num_entries, text_entries, num_plans from table(querycache('meta', >>'local')); @@ -1849,7 +1845,7 @@ NUM_ENTRIES TEXT_ENTRIES NUM_PLANS NUM_ENTRIES TEXT_ENTRIES NUM_PLANS ----------- ------------ ---------- - 31 0 31 + 22 0 22 2 0 2 --- 2 row(s) selected. @@ -1861,8 +1857,8 @@ NUM_ENTRIES TEXT_ENTRIES NUM_PLANS NUM_ENTRIES TEXT_ENTRIES NUM_PLANS ----------- ------------ ---------- - 65 10 65 - 31 0 31 + 64 9 64 + 22 0 22 2 0 2 --- 3 row(s) selected. @@ -1875,8 +1871,6 @@ NUM_ENTRIES TEXT_ENTRIES NUM_PLANS NUM_HITS NUM_PARAMS (EXPR) (EXPR) ---------- ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - 0 0 ====QUERY: select '' from t042_t1; - 0 0 ====QUERY: select '' from t042_t1; 0 0 ====QUERY: select '0123456789' from t042_t1; 0 0 ====QUERY: select * from t042_BTA1P006; 0 0 ====QUERY: select * from t042_ORDERLINE where OL_DIST_INFO like 'DoIQ%'; @@ -1951,48 +1945,39 @@ NUM_HITS NUM_PARAMS (EXPR) (EXPR) 1 9 ====QUERY: select bitand(1,2), bitor(0,1), bitxor(0,0),bitnot(0), abs(-1) from t042_ORDERLINE; 5 1 ====QUERY: select * from t042_orderline where ol_o_id = 1 ; ---- 75 row(s) selected. +--- 73 row(s) selected. >>select num_hits, num_params, '====QUERY:', cast(substring(text, 1, 200) as >>char(200 bytes) character set utf8) +> from table(querycacheentries('meta', 'local')) order by 1,2,4; NUM_HITS NUM_PARAMS (EXPR) (EXPR) ---------- ---------- ---------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - 0 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( - 0 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( - 0 1 ====QUERY: SELECT OBJECT_UID, OBJECT_NAME, OBJECT_TYPE, GRANTEE_ID, GRANTEE_NAME, GRANTEE_TYPE, GRANTOR_ID, GRANTOR_NAME, GRANTOR_TYPE, PRIVILEGES_BITMAP, GRANTABLE_BITMAP FROM TRAFODION."_PRIVMGR_MD_".OBJECT_PR - 0 1 ====QUERY: SELECT object_uid,object_name,grantee_id,grantee_name,grantor_id,grantor_name,column_number,privileges_bitmap,grantable_bitmap FROM TRAFODION."_PRIVMGR_MD_".COLUMN_PRIVILEGES where object_uid = 170022 - 0 1 ====QUERY: select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 1700229370398217975 for read committed access - 0 1 ====QUERY: select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 1700229370398217975 for read committed access - 0 1 ====QUERY: select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob - 0 1 ====QUERY: select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob - 0 2 ====QUERY: select O.catalog_name, O.schema_name, O.object_name, I.keytag, I.is_unique, I.is_explicit, I.key_colcount, I.nonkey_colcount, T.num_salt_partns, T.row_format, I.index_uid from TRAFODION."_MD_".INDEXES + 0 1 ====QUERY: select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 8379103414832633643 for read committed access 0 2 ====QUERY: select column_name, column_number, column_class, fs_data_type, column_size, column_precision, column_scale, datetime_start_field, datetime_end_field, trim(is_upshifted), column_flags, nullable, trim(c - 0 2 ====QUERY: select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398218011 for read committed access order by keyseq_number - 0 2 ====QUERY: select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398222005 for read committed access order by keyseq_number - 0 3 ====QUERY: select O.object_name, C.constraint_type, C.col_count, C.constraint_uid, C.enforced, C.flags from TRAFODION."_MD_".OBJECTS O, TRAFODION."_MD_".TABLE_CONSTRAINTS C where O.catalog_name = 'TRAFODION' and - 0 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398217975 and nonkeycol = 0 for read committed access order - 0 4 ====QUERY: select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'OBJECTS' and object_type = 'BT'; - 0 4 ====QUERY: select object_uid, object_type from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'DESCRIBE__' and object_type = ' ' - 0 5 ====QUERY: select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_ORDERLINE' and object_type = - 1 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( - 1 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( - 1 1 ====QUERY: SELECT OBJECT_UID, OBJECT_NAME, OBJECT_TYPE, GRANTEE_ID, GRANTEE_NAME, GRANTEE_TYPE, GRANTOR_ID, GRANTOR_NAME, GRANTOR_TYPE, PRIVILEGES_BITMAP, GRANTABLE_BITMAP FROM TRAFODION."_PRIVMGR_MD_".OBJECT_PR - 1 1 ====QUERY: SELECT object_uid,object_name,grantee_id,grantee_name,grantor_id,grantor_name,column_number,privileges_bitmap,grantable_bitmap FROM TRAFODION."_PRIVMGR_MD_".COLUMN_PRIVILEGES where object_uid = 170022 - 1 1 ====QUERY: select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 1700229370398220983 for read committed access - 1 1 ====QUERY: select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 1700229370398220983 for read committed access - 1 2 ====QUERY: select O.catalog_name, O.schema_name, O.object_name, I.keytag, I.is_unique, I.is_explicit, I.key_colcount, I.nonkey_colcount, T.num_salt_partns, T.row_format, I.index_uid from TRAFODION."_MD_".INDEXES + 0 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 8379103414832633643 and nonkeycol = 0 for read committed access order + 0 4 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'DESCRIBE__' and obje + 0 4 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'OBJECTS' and object_type = + 0 5 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_ORDERLINE' and + 1 1 ====QUERY: select is_audited, num_salt_partns, row_format, flags from TRAFODION."_MD_".TABLES where table_uid = 8379103414832637810 for read committed access + 1 1 ====QUERY: select trim(O.catalog_name || '.' || '"' || O.schema_name || '"' || '.' || '"' || O.object_name || '"' ) constr_name, trim(O2.catalog_name || '.' || '"' || O2.schema_name || '"' || '.' || '"' || O2.ob 1 2 ====QUERY: select column_name, column_number, column_class, fs_data_type, column_size, column_precision, column_scale, datetime_start_field, datetime_end_field, trim(is_upshifted), column_flags, nullable, trim(c - 1 3 ====QUERY: select O.object_name, C.constraint_type, C.col_count, C.constraint_uid, C.enforced, C.flags from TRAFODION."_MD_".OBJECTS O, TRAFODION."_MD_".TABLE_CONSTRAINTS C where O.catalog_name = 'TRAFODION' and - 1 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 1700229370398220983 and nonkeycol = 0 for read committed access order - 1 5 ====QUERY: select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_T1' and object_type = 'BT' + 1 2 ====QUERY: select column_name, column_number, keyseq_number, ordering , cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 8379103414832633687 for read committed access order by keyseq_number + 1 3 ====QUERY: select column_name, column_number, keyseq_number, ordering, cast(0 as int not null) from TRAFODION."_MD_".KEYS where object_uid = 8379103414832637810 and nonkeycol = 0 for read committed access order + 1 5 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = 'ORDERENTRY' and object_name = 'T042_T1' and object_ + 2 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( + 2 1 ====QUERY: SELECT COMPONENT_UID, OPERATION_CODE, GRANTEE_ID, GRANTOR_ID, GRANTEE_NAME, GRANTOR_NAME, GRANT_DEPTH FROM TRAFODION."_PRIVMGR_MD_".COMPONENT_PRIVILEGES WHERE COMPONENT_UID = 1 AND OPERATION_CODE IN ( 2 1 ====QUERY: SELECT HISTOGRAM_ID, COLUMN_NUMBER, COLCOUNT, INTERVAL_COUNT, ROWCOUNT, TOTAL_UEC, JULIANTIMESTAMP(STATS_TIME), LOW_VALUE, HIGH_VALUE, JULIANTIMESTAMP(READ_TIME), READ_COUNT, SAMPLE_SECS, COL_SECS, SA 2 1 ====QUERY: SELECT HISTOGRAM_ID, INTERVAL_NUMBER, INTERVAL_ROWCOUNT, INTERVAL_UEC, INTERVAL_BOUNDARY, CAST(STD_DEV_OF_FREQ AS DOUBLE PRECISION), V1, V2, V5 FROM TRAFODION.ORDERENTRY.SB_HISTOGRAM_INTERVALS WHERE T - 2 3 ====QUERY: select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 1700229370398217975 and text_type = 2 and sub_id = 0 for read committed access order by seq_num - 3 3 ====QUERY: select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 1700229370398220983 and text_type = 2 and sub_id = 0 for read committed access order by seq_num - 12 4 ====QUERY: select object_uid, object_owner, schema_owner, flags from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'TABLES' and object_type = 'BT'; - ---- 33 row(s) selected. + 2 1 ====QUERY: SELECT OBJECT_UID, OBJECT_NAME, OBJECT_TYPE, GRANTEE_ID, GRANTEE_NAME, GRANTEE_TYPE, GRANTOR_ID, GRANTOR_NAME, GRANTOR_TYPE, PRIVILEGES_BITMAP, GRANTABLE_BITMAP FROM TRAFODION."_PRIVMGR_MD_".OBJECT_PR + 2 1 ====QUERY: SELECT object_uid,object_name,grantee_id,grantee_name,grantor_id,grantor_name,column_number,privileges_bitmap,grantable_bitmap FROM TRAFODION."_PRIVMGR_MD_".COLUMN_PRIVILEGES where object_uid = 837910 + 2 1 ====QUERY: select check_option, is_updatable, is_insertable from TRAFODION."_MD_".VIEWS where view_uid = 8379103414832633643 for read committed access + 2 2 ====QUERY: select O.catalog_name, O.schema_name, O.object_name, I.keytag, I.is_unique, I.is_explicit, I.key_colcount, I.nonkey_colcount, T.num_salt_partns, T.row_format, I.index_uid from TRAFODION."_MD_".INDEXES + 2 3 ====QUERY: select O.object_name, C.constraint_type, C.col_count, C.constraint_uid, C.enforced, C.flags from TRAFODION."_MD_".OBJECTS O, TRAFODION."_MD_".TABLE_CONSTRAINTS C where O.catalog_name = 'TRAFODION' and + 2 3 ====QUERY: select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 8379103414832633643 and text_type = 2 and sub_id = 0 for read committed access order by seq_num + 3 3 ====QUERY: select octet_length(text), text from TRAFODION."_MD_".TEXT where text_uid = 8379103414832637810 and text_type = 2 and sub_id = 0 for read committed access order by seq_num + 12 4 ====QUERY: select object_uid, object_owner, schema_owner, flags, create_time from TRAFODION."_MD_".OBJECTS where catalog_name = 'TRAFODION' and schema_name = '_MD_' and object_name = 'TABLES' and object_type = + +--- 24 row(s) selected. >>select num_hits, num_params, '====QUERY:', cast(substring(text, 1, 200) as >>char(200 bytes) character set utf8) +> from table(querycacheentries('ustats', 'local')) order by 1,2,4; @@ -2002,7 +1987,7 @@ NUM_HITS NUM_PARAMS (EXPR) (EXPR) (EXPR) -------------------- - 108 + 97 --- 1 row(s) selected. >>-- should be the sum of the user, meta and ustats caches @@ -2014,7 +1999,7 @@ NUM_HITS NUM_PARAMS (EXPR) (EXPR) NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS ---------- ---------- ---------------------- ---------------------- - 37 60 5 211 + 37 59 5 211 --- 1 row(s) selected. >>select * from table(hybridquerycache('meta', 'local')); @@ -2022,7 +2007,7 @@ NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS ---------- ---------- ---------------------- ---------------------- - 29 31 5 211 + 21 22 5 211 2 2 5 211 --- 2 row(s) selected. @@ -2034,8 +2019,8 @@ NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS ---------- ---------- ---------------------- ---------------------- - 37 60 5 211 - 29 31 5 211 + 37 59 5 211 + 21 22 5 211 2 2 5 211 --- 3 row(s) selected. @@ -2063,7 +2048,6 @@ NUM_HITS NUM_PLITERALS NUM_NPLITERALS 0 0 1 0 0 1 0 0 1 - 0 0 1 0 0 2 0 0 2 0 0 2 @@ -2106,25 +2090,14 @@ NUM_HITS NUM_PLITERALS NUM_NPLITERALS 1 4 0 5 1 0 ---- 60 row(s) selected. +--- 59 row(s) selected. >>select num_hits, num_PLiterals, num_NPLiterals from >>table(hybridquerycacheentries('meta', 'local')) order by 1, 2, 3; NUM_HITS NUM_PLITERALS NUM_NPLITERALS ---------- ------------- -------------- 0 1 0 - 0 1 0 - 0 1 0 - 0 1 0 - 0 1 10 - 0 1 10 - 0 1 14 - 0 1 14 - 0 2 1 - 0 2 1 0 2 1 - 0 2 3 - 0 3 1 0 3 1 0 3 1 0 4 0 @@ -2133,20 +2106,22 @@ NUM_HITS NUM_PLITERALS NUM_NPLITERALS 0 5 0 0 5 0 1 1 0 - 1 1 0 - 1 1 0 - 1 1 0 - 1 1 10 - 1 1 10 + 1 1 14 + 1 2 1 1 2 1 - 1 2 3 1 3 1 2 1 0 + 2 1 0 + 2 1 0 + 2 1 0 2 1 1 + 2 1 10 + 2 1 10 + 2 2 3 2 3 0 3 3 0 ---- 33 row(s) selected. +--- 24 row(s) selected. >>select num_hits, num_PLiterals, num_NPLiterals from >>table(hybridquerycacheentries('ustats', 'local')) order by 1, 2, 3; --- 0 row(s) selected. @@ -2155,7 +2130,7 @@ NUM_HITS NUM_PLITERALS NUM_NPLITERALS (EXPR) -------------------- - 93 + 83 --- 1 row(s) selected. >>-- should be the sum of the user, meta and ustats caches @@ -2243,7 +2218,7 @@ book row2 NUM_ENTRIES TEXT_ENTRIES NUM_PLANS ----------- ------------ ---------- - 4 4 4 + 3 3 3 0 0 0 0 0 0 @@ -2253,7 +2228,7 @@ NUM_ENTRIES TEXT_ENTRIES NUM_PLANS NUM_HKEYS NUM_SKEYS NUM_MAX_VALUES_PER_KEY NUM_HASH_TABLE_BUCKETS ---------- ---------- ---------------------- ---------------------- - 2 3 5 211 + 2 2 5 211 0 0 5 211 0 0 5 211
http://git-wip-us.apache.org/repos/asf/trafodion/blob/43984c46/core/sql/regress/core/EXPECTED038.LINUX ---------------------------------------------------------------------- diff --git a/core/sql/regress/core/EXPECTED038.LINUX b/core/sql/regress/core/EXPECTED038.LINUX index 3d86052..d317748 100644 --- a/core/sql/regress/core/EXPECTED038.LINUX +++ b/core/sql/regress/core/EXPECTED038.LINUX @@ -127,10 +127,12 @@ EMPNUM FIRSTNAME LASTNAME AGE SALARY >>select * from T038edat +> where {fn locate('ma', {fn concat(first_name, last_name)}, 1)} > 0; -*** ERROR[3131] The statement just entered is currently not supported. +EMPNUM FIRST_NAME LAST_NAME +----------- -------------------- -------------------- -*** ERROR[8822] The statement was not prepared. + 300 ARUN Thomas +--- 1 row(s) selected. >> >>select {fn lcase(first_name)} from T038edat; @@ -243,10 +245,10 @@ ORDER_NUM ORDER_DATE ORDER_TIME ORDER_QTY ----------- ---------- ---------- ----------- 100 1997-01-30 13:40:05 1000 - 200 2017-06-05 21:43:42 99 + 200 2018-03-08 04:11:27 99 300 1996-08-10 10:20:10 6000 - 400 1997-05-12 21:43:42 99 - 500 2017-06-05 21:43:42 99 + 400 1997-05-12 04:11:27 99 + 500 2018-03-08 04:11:27 99 --- 5 row(s) selected. >> @@ -264,7 +266,7 @@ ORDER_NUM ORDER_DATE ORDER_TIME ORDER_QTY T038PART_NUM ORDER_TIME ------------ -------------------------- - 600 2017-06-05 21:43:45.699265 + 600 2018-03-08 04:11:32.295835 --- 1 row(s) selected. >> @@ -661,8 +663,8 @@ GANESAN DEV YOW EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ - 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 100 RAO 6500 6500 QA DENNIS ? ? ? + 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 --- 3 row(s) selected. @@ -674,8 +676,8 @@ EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ - 100 RAO 6500 6500 QA DENNIS ? ? ? 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 + 100 RAO 6500 6500 QA DENNIS ? ? ? 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 --- 3 row(s) selected. @@ -703,8 +705,8 @@ EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ 100 RAO 6500 6500 QA DENNIS ? ? ? - 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 + 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 --- 3 row(s) selected. >> @@ -730,9 +732,9 @@ EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ + 100 RAO 6500 6500 QA DENNIS ? ? ? 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 - 100 RAO 6500 6500 QA DENNIS ? ? ? --- 3 row(s) selected. >>SELECT * @@ -743,9 +745,9 @@ EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME EMPNUM EMPNAME DEPTNUM DEPTNUM DEPTNAME MANAGER UNITNUM DEPTNUM REVENVUE ----------- -------------------- ----------- ----------- -------------------- -------------------- ----------- ----------- ------------ - 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 - 100 RAO 6500 6500 QA DENNIS ? ? ? 100 GANESAN 6400 6400 DEV YOW 101 6400 10000.000 + 100 RAO 6500 6500 QA DENNIS ? ? ? + 100 BHAVE 6400 6400 DEV YOW 101 6400 10000.000 --- 3 row(s) selected. >> http://git-wip-us.apache.org/repos/asf/trafodion/blob/43984c46/core/sql/regress/executor/EXPECTED012 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED012 b/core/sql/regress/executor/EXPECTED012 index a15082d..26730d5 100755 --- a/core/sql/regress/executor/EXPECTED012 +++ b/core/sql/regress/executor/EXPECTED012 @@ -899,7 +899,6 @@ A B C >>-- * select * from t012t1 where position('b' in b) < 3; >>-- * select * from t012t1 where position('b' in b) <= 2; >> ->>?ifMX >>select b, position(N'ab' in b) from t012ut1; B (EXPR) @@ -1032,7 +1031,138 @@ A B C 40 test trim on non-space --- 3 row(s) selected. ->>?ifMX +>> +>>-- test INSTR function +>>select instr('heelplo', 'l', 2, 2) from dual; + +(EXPR) +---------- + + 6 + +--- 1 row(s) selected. +>>select instr('heelplo', 'l', 2) from dual; + +(EXPR) +---------- + + 4 + +--- 1 row(s) selected. +>>select instr('heelplo', 'l', 1) from dual; + +(EXPR) +---------- + + 4 + +--- 1 row(s) selected. +>>select instr('heelplo', 'l', 5) from dual; + +(EXPR) +---------- + + 6 + +--- 1 row(s) selected. +>>select instr('heelplo', 'l', 10) from dual; + +(EXPR) +---------- + + 0 + +--- 1 row(s) selected. +>>SELECT INSTR('CORPORATE FLOOR','OR', 3, 1) FROM DUAL; + +(EXPR) +---------- + + 5 + +--- 1 row(s) selected. +>>SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) FROM DUAL; + +(EXPR) +---------- + + 14 + +--- 1 row(s) selected. +>>SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM DUAL; + +*** ERROR[1572] START POSITION value cannot be a negative number for INSTR function. + +--- 0 row(s) selected. +>>SELECT INSTR('CORPORATE FLOOR','OR', 3, -2) FROM DUAL; + +*** ERROR[1572] OCCURRENCE value cannot be a negative number for INSTR function. + +--- 0 row(s) selected. +>> +>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2, 2) from dual; + +(EXPR) +---------- + + 6 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2) from dual; + +(EXPR) +---------- + + 4 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 1) from dual; + +(EXPR) +---------- + + 4 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 5) from dual; + +(EXPR) +---------- + + 6 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 10) from dual; + +(EXPR) +---------- + + 0 + +--- 1 row(s) selected. +>> +>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 1) from dual; + +(EXPR) +---------- + + 5 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 2) from dual; + +(EXPR) +---------- + + 14 + +--- 1 row(s) selected. +>>select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', -3, 2) from dual; + +*** ERROR[1572] START POSITION value cannot be a negative number for INSTR function. + +--- 0 row(s) selected. +>> >> >>-- testing SUBSTRING function >>select b,substring(b from 2 for 1) as u,char_length(substring(b from 2 for >>1)) len from t012t1; @@ -1925,7 +2055,6 @@ A B C --- 1 row(s) selected. >> ->>?ifMX >>select b||b,char_length(b||b) from t012ut1; (EXPR) (EXPR) @@ -2132,7 +2261,6 @@ A B C 30 abc def --- 1 row(s) selected. ->>?ifMX >> >>-- test BIT functions >>select bitand(1,1), bitor(1,1), bitxor(1,1), bitnot(1) from (values(1)) x(a); @@ -2629,6 +2757,465 @@ C --- 9 row(s) selected. >> +>>-- various operations involving number datatypes and string functions. +>>select '1 ' || 12.34e3 from dual; + +(EXPR) +--------------------------- + +1 1.23400000000000000E+004 + +--- 1 row(s) selected. +>>select 12 || '1 ' || 12.34e3 from dual; + +(EXPR) +------------------------------- + +121 1.23400000000000000E+004 + +--- 1 row(s) selected. +>>select concat(_ucs2'2233', 12.34e3) from dual; + +(EXPR) +---------------------------------------------------------- + +22331.23400000000000000E+004 + +--- 1 row(s) selected. +>>select case when 1 = 1 then 12.3e34 else '1' end from dual; + +(EXPR) +------------------------- + +1.23000000000000000E+035 + +--- 1 row(s) selected. +>> +>>drop table if exists t012t4; + +--- SQL operation complete. +>> +>>create table t012t4 ( ++>c1 float, ++>c2 numeric, ++>c3 numeric(128), ++>c4 numeric(10,5), ++>c5 decimal, ++>c6 decimal(18), ++>c7 decimal(10,5) ++>); + +--- SQL operation complete. +>>insert into t012t4 values (1.23, 1.23, 1.23, 1.23, 1.23, 1.23, 1.23); + +--- 1 row(s) inserted. +>>select * from t012t4; + +C1 C2 C3 C4 C5 C6 C7 +------------------------- ----------- --------------------------------------------------------------------------------------------------------------------------------- --------------------- ---------- ------------------- ------------ + + 1.23000000000000000E+000 1 1 1.23000 1 1 1.23000 + +--- 1 row(s) selected. +>> +>>select CONCAT(c1,'ZZZ') from t012t4; + +(EXPR) +---------------------------- + +1.23000000000000000E+000ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c2,'ZZZ') from t012t4; + +(EXPR) +-------------- + +1ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c3,'ZZZ') from t012t4; + +(EXPR) +------------------------------------------------------------------------------------------------------------------------------------ + +1ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c4,'ZZZ') from t012t4; + +(EXPR) +------------------------ + +1.23000ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c5,'ZZZ') from t012t4; + +(EXPR) +------------- + +1ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c6,'ZZZ') from t012t4; + +(EXPR) +---------------------- + +1ZZZ + +--- 1 row(s) selected. +>>select CONCAT(c7,'ZZZ') from t012t4; + +(EXPR) +--------------- + +1.23000ZZZ + +--- 1 row(s) selected. +>> +>>select LPAD(c1,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>>select LPAD(c2,2,'ZZZ') from t012t4; + +(EXPR) +------ + +Z1 + +--- 1 row(s) selected. +>>select LPAD(c3,2,'ZZZ') from t012t4; + +(EXPR) +------ + +Z1 + +--- 1 row(s) selected. +>>select LPAD(c4,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>>select LPAD(c5,2,'ZZZ') from t012t4; + +(EXPR) +------ + +Z1 + +--- 1 row(s) selected. +>>select LPAD(c6,2,'ZZZ') from t012t4; + +(EXPR) +------ + +Z1 + +--- 1 row(s) selected. +>>select LPAD(c7,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>> +>>select LTRIM(c1) from t012t4; + +(EXPR) +------------------------- + +1.23000000000000000E+000 + +--- 1 row(s) selected. +>>select LTRIM(c2) from t012t4; + +(EXPR) +----------- + +1 + +--- 1 row(s) selected. +>>select LTRIM(c3) from t012t4; + +(EXPR) +--------------------------------------------------------------------------------------------------------------------------------- + +1 + +--- 1 row(s) selected. +>>select LTRIM(c4) from t012t4; + +(EXPR) +--------------------- + +1.23000 + +--- 1 row(s) selected. +>>select LTRIM(c5) from t012t4; + +(EXPR) +---------- + +1 + +--- 1 row(s) selected. +>>select LTRIM(c6) from t012t4; + +(EXPR) +------------------- + +1 + +--- 1 row(s) selected. +>>select LTRIM(c7) from t012t4; + +(EXPR) +------------ + +1.23000 + +--- 1 row(s) selected. +>> +>>select OCTET_LENGTH(c1) from t012t4; + +(EXPR) +---------- + + 24 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c2) from t012t4; + +(EXPR) +---------- + + 1 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c3) from t012t4; + +(EXPR) +---------- + + 1 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c4) from t012t4; + +(EXPR) +---------- + + 7 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c5) from t012t4; + +(EXPR) +---------- + + 1 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c6) from t012t4; + +(EXPR) +---------- + + 1 + +--- 1 row(s) selected. +>>select OCTET_LENGTH(c7) from t012t4; + +(EXPR) +---------- + + 7 + +--- 1 row(s) selected. +>> +>>select RPAD(c1,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>>select RPAD(c2,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1Z + +--- 1 row(s) selected. +>>select RPAD(c3,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1Z + +--- 1 row(s) selected. +>>select RPAD(c4,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>>select RPAD(c5,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1Z + +--- 1 row(s) selected. +>>select RPAD(c6,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1Z + +--- 1 row(s) selected. +>>select RPAD(c7,2,'ZZZ') from t012t4; + +(EXPR) +------ + +1. + +--- 1 row(s) selected. +>> +>>select RTRIM(c1) from t012t4; + +(EXPR) +------------------------- + +1.23000000000000000E+000 + +--- 1 row(s) selected. +>>select RTRIM(c2) from t012t4; + +(EXPR) +----------- + +1 + +--- 1 row(s) selected. +>>select RTRIM(c3) from t012t4; + +(EXPR) +--------------------------------------------------------------------------------------------------------------------------------- + +1 + +--- 1 row(s) selected. +>>select RTRIM(c4) from t012t4; + +(EXPR) +--------------------- + +1.23000 + +--- 1 row(s) selected. +>>select RTRIM(c5) from t012t4; + +(EXPR) +---------- + +1 + +--- 1 row(s) selected. +>>select RTRIM(c6) from t012t4; + +(EXPR) +------------------- + +1 + +--- 1 row(s) selected. +>>select RTRIM(c7) from t012t4; + +(EXPR) +------------ + +1.23000 + +--- 1 row(s) selected. +>> +>>select TRIM(c1) from t012t4; + +(EXPR) +------------------------- + +1.23000000000000000E+000 + +--- 1 row(s) selected. +>>select TRIM(c2) from t012t4; + +(EXPR) +----------- + +1 + +--- 1 row(s) selected. +>>select TRIM(c3) from t012t4; + +(EXPR) +--------------------------------------------------------------------------------------------------------------------------------- + +1 + +--- 1 row(s) selected. +>>select TRIM(c4) from t012t4; + +(EXPR) +--------------------- + +1.23000 + +--- 1 row(s) selected. +>>select TRIM(c5) from t012t4; + +(EXPR) +---------- + +1 + +--- 1 row(s) selected. +>>select TRIM(c6) from t012t4; + +(EXPR) +------------------- + +1 + +--- 1 row(s) selected. +>>select TRIM(c7) from t012t4; + +(EXPR) +------------ + +1.23000 + +--- 1 row(s) selected. >> >>-- negative BIT function tests >>select bitand(1, 1.0) from (values(1)) x(a); @@ -2733,5 +3320,8 @@ select bitnot(1,1) from (values(1)) x(a); >>drop table t012t3; --- SQL operation complete. +>>drop table t012t4; + +--- SQL operation complete. >> >>log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/43984c46/core/sql/regress/executor/TEST012 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST012 b/core/sql/regress/executor/TEST012 index c5e734e..d376350 100755 --- a/core/sql/regress/executor/TEST012 +++ b/core/sql/regress/executor/TEST012 @@ -44,6 +44,7 @@ drop table t012t1 cascade; drop table t012ut1 cascade; ?ifMX drop table t012t3 cascade; +drop table t012t4 cascade; ?section crdb log LOG012 clear; @@ -217,7 +218,6 @@ select * from t012t1 where position('b' in b) >= 2; -- * select * from t012t1 where position('b' in b) < 3; -- * select * from t012t1 where position('b' in b) <= 2; -?ifMX select b, position(N'ab' in b) from t012ut1; select b, position(N'xy' in b) from t012ut1; select b, position(N'ef' in b) from t012ut1; @@ -231,7 +231,28 @@ select * from t012ut1 where position(N'b' in b) > 1; select * from t012ut1 where position(N'b' in b) >= 2; select * from t012ut1 where position(N'b' in b) < 3; select * from t012ut1 where position(N'b' in b) <= 2; -?ifMX + +-- test INSTR function +select instr('heelplo', 'l', 2, 2) from dual; +select instr('heelplo', 'l', 2) from dual; +select instr('heelplo', 'l', 1) from dual; +select instr('heelplo', 'l', 5) from dual; +select instr('heelplo', 'l', 10) from dual; +SELECT INSTR('CORPORATE FLOOR','OR', 3, 1) FROM DUAL; +SELECT INSTR('CORPORATE FLOOR','OR', 3, 2) FROM DUAL; +SELECT INSTR('CORPORATE FLOOR','OR', -3, 2) FROM DUAL; +SELECT INSTR('CORPORATE FLOOR','OR', 3, -2) FROM DUAL; + +select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2, 2) from dual; +select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 2) from dual; +select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 1) from dual; +select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 5) from dual; +select instr(_ucs2 x'c231 c232 c232 c233 c234 c233 c235', _ucs2 x'c233', 10) from dual; + +select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 1) from dual; +select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', 3, 2) from dual; +select instr(_ucs2 x'c231 c232 c233 c234 c232 c233 c235 c236 c237 c331 c332 c333 c232 c232 c233', _ucs2 x'c232 c233', -3, 2) from dual; + -- testing SUBSTRING function select b,substring(b from 2 for 1) as u,char_length(substring(b from 2 for 1)) len from t012t1; @@ -341,7 +362,6 @@ select trim(b)||trim(c),char_length(trim(b)||trim(c)) from t012t1; select trim(b)||trim(c)||trim(b),char_length(trim(b)||trim(c)||trim(b)) from t012t1; select * from t012t1 where trim(b)||trim(c) = 'abcdef'; -?ifMX select b||b,char_length(b||b) from t012ut1; select b||trim(b),char_length(b||trim(b)) from t012ut1; select trim(b)||trim(b),char_length(trim(b)||trim(b)) from t012ut1; @@ -361,7 +381,6 @@ select b||c,char_length(b||c) from t012ut1; select trim(b)||trim(c),char_length(trim(b)||trim(c)) from t012ut1; select trim(b)||trim(c)||trim(b),char_length(trim(b)||trim(c)||trim(b)) from t012ut1; select * from t012ut1 where trim(b)||trim(c) = N'abcdef'; -?ifMX -- test BIT functions select bitand(1,1), bitor(1,1), bitxor(1,1), bitnot(1) from (values(1)) x(a); @@ -456,6 +475,81 @@ select bitand(a,a) + bitor(a,a) + bitxor(a,a) from t012t3; select converttobits(bitand(a,a) + bitor(a,a) + bitxor(a,a)) from t012t3; select converttobits(bitnot(bitand(a,a) + bitor(a,a) + bitxor(a,a))) from t012t3; +-- various operations involving number datatypes and string functions. +select '1 ' || 12.34e3 from dual; +select 12 || '1 ' || 12.34e3 from dual; +select concat(_ucs2'2233', 12.34e3) from dual; +select case when 1 = 1 then 12.3e34 else '1' end from dual; + +drop table if exists t012t4; + +create table t012t4 ( +c1 float, +c2 numeric, +c3 numeric(128), +c4 numeric(10,5), +c5 decimal, +c6 decimal(18), +c7 decimal(10,5) +); +insert into t012t4 values (1.23, 1.23, 1.23, 1.23, 1.23, 1.23, 1.23); +select * from t012t4; + +select CONCAT(c1,'ZZZ') from t012t4; +select CONCAT(c2,'ZZZ') from t012t4; +select CONCAT(c3,'ZZZ') from t012t4; +select CONCAT(c4,'ZZZ') from t012t4; +select CONCAT(c5,'ZZZ') from t012t4; +select CONCAT(c6,'ZZZ') from t012t4; +select CONCAT(c7,'ZZZ') from t012t4; + +select LPAD(c1,2,'ZZZ') from t012t4; +select LPAD(c2,2,'ZZZ') from t012t4; +select LPAD(c3,2,'ZZZ') from t012t4; +select LPAD(c4,2,'ZZZ') from t012t4; +select LPAD(c5,2,'ZZZ') from t012t4; +select LPAD(c6,2,'ZZZ') from t012t4; +select LPAD(c7,2,'ZZZ') from t012t4; + +select LTRIM(c1) from t012t4; +select LTRIM(c2) from t012t4; +select LTRIM(c3) from t012t4; +select LTRIM(c4) from t012t4; +select LTRIM(c5) from t012t4; +select LTRIM(c6) from t012t4; +select LTRIM(c7) from t012t4; + +select OCTET_LENGTH(c1) from t012t4; +select OCTET_LENGTH(c2) from t012t4; +select OCTET_LENGTH(c3) from t012t4; +select OCTET_LENGTH(c4) from t012t4; +select OCTET_LENGTH(c5) from t012t4; +select OCTET_LENGTH(c6) from t012t4; +select OCTET_LENGTH(c7) from t012t4; + +select RPAD(c1,2,'ZZZ') from t012t4; +select RPAD(c2,2,'ZZZ') from t012t4; +select RPAD(c3,2,'ZZZ') from t012t4; +select RPAD(c4,2,'ZZZ') from t012t4; +select RPAD(c5,2,'ZZZ') from t012t4; +select RPAD(c6,2,'ZZZ') from t012t4; +select RPAD(c7,2,'ZZZ') from t012t4; + +select RTRIM(c1) from t012t4; +select RTRIM(c2) from t012t4; +select RTRIM(c3) from t012t4; +select RTRIM(c4) from t012t4; +select RTRIM(c5) from t012t4; +select RTRIM(c6) from t012t4; +select RTRIM(c7) from t012t4; + +select TRIM(c1) from t012t4; +select TRIM(c2) from t012t4; +select TRIM(c3) from t012t4; +select TRIM(c4) from t012t4; +select TRIM(c5) from t012t4; +select TRIM(c6) from t012t4; +select TRIM(c7) from t012t4; -- negative BIT function tests select bitand(1, 1.0) from (values(1)) x(a); @@ -522,6 +616,7 @@ drop table t012ut1; ?ifMX drop table t012t3; +drop table t012t4; log; http://git-wip-us.apache.org/repos/asf/trafodion/blob/43984c46/core/sql/regress/hive/EXPECTED005 ---------------------------------------------------------------------- diff --git a/core/sql/regress/hive/EXPECTED005 b/core/sql/regress/hive/EXPECTED005 index 493fe4e..6521790 100644 --- a/core/sql/regress/hive/EXPECTED005 +++ b/core/sql/regress/hive/EXPECTED005 @@ -72,7 +72,7 @@ *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978885, failedModTS = 1499978946, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763081810, failedModTS = 1521763182817, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -108,7 +108,7 @@ Y 9525 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978885, failedModTS = 1499978946, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763081810, failedModTS = 1521763182817, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -177,7 +177,7 @@ Y 9525 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978950, failedModTS = 1499978958, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763188079, failedModTS = 1521763198786, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable A ------------------------- @@ -227,7 +227,7 @@ xyz *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978946, failedModTS = 1499978976, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763182817, failedModTS = 1521763227396, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -242,7 +242,7 @@ Y 18984 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978946, failedModTS = 1499978976, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763182817, failedModTS = 1521763227396, failedLoc = hdfs://localhost:24200/user/trafodion/hive/exttables/customer_ddl C_PREFERRED_CUST_FLAG (EXPR) ------------------------- -------------------- @@ -298,7 +298,7 @@ Y 18984 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499978973, failedModTS = 1499978982, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763223181, failedModTS = 1521763236557, failedLoc = hdfs://localhost:24200/user/hive/warehouse/newtable A B ----------- ------------------------- @@ -456,10 +456,10 @@ ID CHAPTER ENGLISH TRANSLATOR >> >>select * from tbl_type; -TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 ----- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- ----------- ---------- ----------- ------------------------------- +TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 +---- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- -------------------- ------------ -------------------- ------------------------------- - 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 + 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 --- 1 row(s) selected. >>insert into tbl_type_temp select * from tbl_type; @@ -467,10 +467,10 @@ TINT SM I BIG STR F --- 1 row(s) inserted. >>select * from tbl_type_temp; -TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 ----- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- ----------- ---------- ----------- ------------------------------- +TINT SM I BIG STR F D T DT VC D10 D18 D181 D30 +---- ------ ----------- -------------------- ------------------------- --------------- ------------------------- -------------------------- ---------- ---------------------------------------- -------------------- ------------ -------------------- ------------------------------- - 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 + 101 202 203 204 two hundred 2.0000000E+002 2.00000000000000000E+002 2022-02-02 22:22:22.222222 2022-02-02 varchar 1234567890 123456.11 12345 11111111111111111111111111111 --- 1 row(s) selected. >> @@ -617,18 +617,18 @@ C1 C2 C3 C4 C --- 1 row(s) selected. >>load with continue on error into trafodion.seabase.traf_tbl_bad select * >>from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2017-07-13 20:50:54.956 -Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:54.975 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.018 -Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:54.975 +Task: CLEANUP Status: Started Time: 2018-03-23 00:02:16.72820 +Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:16.87954 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015 +Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:16.88001 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:55.307 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.332 -Task: COMPLETION Status: Started Time: 2017-07-13 20:50:55.307 +Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:16.505744 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.418 +Task: COMPLETION Status: Started Time: 2018-03-23 00:02:16.505804 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:55.655 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.348 +Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:17.132726 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.627 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -644,19 +644,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.348 --- 3 row(s) deleted. >>load with log error rows into trafodion.seabase.traf_tbl_bad select * from >>tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2017-07-13 20:50:56.697 -Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:56.705 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009 - Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205056 -Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:56.705 +Task: CLEANUP Status: Started Time: 2018-03-23 00:02:18.222742 +Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:18.238737 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.016 + Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000218 +Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:18.238844 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:57.153 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.448 -Task: COMPLETION Status: Started Time: 2017-07-13 20:50:57.153 +Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:18.652144 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.413 +Task: COMPLETION Status: Started Time: 2018-03-23 00:02:18.652182 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:57.695 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.542 +Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:19.268334 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.616 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -669,19 +669,19 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.542 --- 1 row(s) selected. >>load with log error rows to '/user/trafodion/bulkload/logs/TEST005' into >>trafodion.seabase.traf_tbl_bad select * from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2017-07-13 20:50:58.677 -Task: CLEANUP Status: Ended Time: 2017-07-13 20:50:58.685 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.008 - Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205058 -Task: LOADING DATA Status: Started Time: 2017-07-13 20:50:58.685 +Task: CLEANUP Status: Started Time: 2018-03-23 00:02:20.268608 +Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:20.278195 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.010 + Logging Location: /user/trafodion/bulkload/logs/TEST005/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000220 +Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:20.278249 Rows Processed: 8 Error Rows: 5 -Task: LOADING DATA Status: Ended Time: 2017-07-13 20:50:59.124 -Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.439 -Task: COMPLETION Status: Started Time: 2017-07-13 20:50:59.124 +Task: LOADING DATA Status: Ended Time: 2018-03-23 00:02:20.743348 +Task: LOADING DATA Status: Ended Elapsed Time: 00:00:00.465 +Task: COMPLETION Status: Started Time: 2018-03-23 00:02:20.743404 Rows Loaded: 3 -Task: COMPLETION Status: Ended Time: 2017-07-13 20:50:59.453 -Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.329 +Task: COMPLETION Status: Ended Time: 2018-03-23 00:02:21.350084 +Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.607 --- 3 row(s) loaded. >>select count(*) from trafodion.seabase.traf_tbl_bad; @@ -697,10 +697,10 @@ Task: COMPLETION Status: Ended Elapsed Time: 00:00:00.329 --- 6 row(s) deleted. >>load with stop after 3 error rows into trafodion.seabase.traf_tbl_bad select >>* from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2017-07-13 20:51:00.456 -Task: CLEANUP Status: Ended Time: 2017-07-13 20:51:00.475 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.018 -Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:00.475 +Task: CLEANUP Status: Started Time: 2018-03-23 00:02:22.398357 +Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:22.409109 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.011 +Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:22.409145 *** ERROR[8113] The maximum number of error rows is exceeded. @@ -715,11 +715,11 @@ Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:00.475 --- 1 row(s) selected. >>load with log error rows, stop after 3 error rows into >>trafodion.seabase.traf_tbl_bad select * from tbl_bad; Task: LOAD Status: Started Object: TRAFODION.SEABASE.TRAF_TBL_BAD -Task: CLEANUP Status: Started Time: 2017-07-13 20:51:01.786 -Task: CLEANUP Status: Ended Time: 2017-07-13 20:51:01.795 -Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.009 - Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20170713_205101 -Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:01.795 +Task: CLEANUP Status: Started Time: 2018-03-23 00:02:23.773372 +Task: CLEANUP Status: Ended Time: 2018-03-23 00:02:23.788799 +Task: CLEANUP Status: Ended Elapsed Time: 00:00:00.015 + Logging Location: /user/trafodion/bulkload/logs/ERR_TRAFODION.SEABASE.TRAF_TBL_BAD_20180323_000223 +Task: LOADING DATA Status: Started Time: 2018-03-23 00:02:23.788861 *** ERROR[8113] The maximum number of error rows is exceeded. @@ -807,7 +807,7 @@ Task: LOADING DATA Status: Started Time: 2017-07-13 20:51:01.795 *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979080, failedModTS = 1499979093, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763369777, failedModTS = 1521763389102, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive A ----------- @@ -842,7 +842,7 @@ A *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979096, failedModTS = 1499979110, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763391527, failedModTS = 1521763411540, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive A ----------- @@ -869,7 +869,7 @@ A B *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979110, failedModTS = 1499979116, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763411540, failedModTS = 1521763418274, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive --- 0 row(s) selected. >>insert into hive.hive.thive values (10, 20); @@ -893,7 +893,7 @@ A B *** WARNING[8597] Statement was automatically retried 1 time(s). Delay before each retry was 0 seconds. See next entry for the error that caused this retry. -*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1499979118, failedModTS = 1499979120, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive +*** WARNING[8436] Mismatch detected between compiletime and runtime hive table definitions. DataModMismatchDetails: compiledModTS = 1521763422992, failedModTS = 1521763425613, failedLoc = hdfs://localhost:24200/user/hive/warehouse/thive --- 0 row(s) selected. >> @@ -984,7 +984,7 @@ t005part.a t005part.b t005part.c >>invoke hive.hive.thive_insert_smallint; -- Definition of hive table THIVE_INSERT_SMALLINT --- Definition current Thu Jul 13 20:54:19 2017 +-- Definition current Fri Mar 23 00:07:13 2018 ( A SMALLINT @@ -1091,7 +1091,7 @@ A >>invoke hive.hive.thive_insert_varchar; -- Definition of hive table THIVE_INSERT_VARCHAR --- Definition current Thu Jul 13 20:54:28 2017 +-- Definition current Fri Mar 23 00:07:23 2018 ( A VARCHAR(1 CHAR) CHARACTER SET UTF8 COLLATE
