You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@trafodion.apache.org by an...@apache.org on 2018/04/05 00:09:58 UTC
[2/4] trafodion git commit: various fixes
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