http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/EXPECTED130 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130 index bf7d45f..a119aed 100644 --- a/core/sql/regress/executor/EXPECTED130 +++ b/core/sql/regress/executor/EXPECTED130 @@ -53,9 +53,9 @@ C1 C1 C2 ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------- - 1 LOBH00000200010669695009493408479919669695009498814985418212329581527003582020"TRAFODION"."LOB130" - 2 LOBH00000200010669695009493408479919669695009499452607918212329581533443121020"TRAFODION"."LOB130" - 3 LOBH00000200010669695009493408479919669695009500088291418212329581539790325020"TRAFODION"."LOB130" + 1 LOBH0000000200010321792085830706042419321792085835428245518212331057036978909020"TRAFODION"."LOB130" + 2 LOBH0000000200010321792085830706042419321792085835962263118212331057042314939020"TRAFODION"."LOB130" + 3 LOBH0000000200010321792085830706042419321792085836459216918212331057047401716020"TRAFODION"."LOB130" --- 3 row(s) selected. >> @@ -278,7 +278,8 @@ C1 (EXPR) (EXPR) >>sh cp $scriptsdir/executor/deep.jpg $rundir/executor/; >>sh cp $scriptsdir/executor/anoush.jpg $rundir/executor/; >>sh cp $scriptsdir/executor/lob_input_* $rundir/executor/; ->>sh cp $scriptsdir/executor/TEST130_argfile $rundir/executor/; +>>sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/; +>> >>-- inserts >>-- first line >>insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt')); @@ -386,7 +387,7 @@ And the dish ran away with the fork ! >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'tlob130_txt1.txt');/g" >> t130_extract_command; >> >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493412345019669695009536909968118212329581908062051020"TRAFODION"."LOB130" ' , 'tlob130_txt1.txt'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830709525319321792085869850100918212331057381219508020"TRAFODION"."LOB130" ' , 'tlob130_txt1.txt'); Success. Targetfile :tlob130_txt1.txt Length : 19 --- SQL operation complete. @@ -402,7 +403,7 @@ Success. Targetfile :tlob130_txt1.txt Length : 19 >>sh rm t130_extract_command; >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'tlob130_deep.jpg');/g" >> t130_extract_command; >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130" ' , 'tlob130_deep.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130" ' , 'tlob130_deep.jpg'); Success. Targetfile :tlob130_deep.jpg Length : 159018 --- SQL operation complete. @@ -418,7 +419,7 @@ Success. Targetfile :tlob130_deep.jpg Length : 159018 >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'tlob130_anoush.jpg');/g" >> t130_extract_command; >> >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130" ' , 'tlob130_anoush.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130" ' , 'tlob130_anoush.jpg'); Success. Targetfile :tlob130_anoush.jpg Length : 230150 --- SQL operation complete. @@ -538,7 +539,7 @@ And the dish ran away with the fork ! >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'hdfs:\/\/\/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command; >> >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493413536419669695009549202243618212329582030992428020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_txt2.txt'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830710639319321792085881807198418212331057500820343020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_txt2.txt'); Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19 --- SQL operation complete. @@ -554,7 +555,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_txt2.txt Length : 19 >>sh rm t130_extract_command; >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'hdfs:\/\/\/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command; >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493413599619669695009555279429118212329582091774912020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_deep.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830710684919321792085887364039618212331057556357004020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_deep.jpg'); Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018 --- SQL operation complete. @@ -570,7 +571,7 @@ Success. Targetfile :hdfs:///lobs/tlob130_deep.jpg Length : 159018 >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'hdfs:\/\/\/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command; >> >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_anoush.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_anoush.jpg'); Success. Targetfile :hdfs:///lobs/tlob130_anoush.jpg Length : 230150 --- SQL operation complete. @@ -589,7 +590,7 @@ Column Name : c2 Input a filename to extract to : Output File Name : lobc2out.jpg Extracting lob handle for column c2... -LOB handle for c2: LOBH00000200010669695009493412404519669695009543226997218212329581971119639020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010321792085830709573619321792085875915579518212331057441900379020"TRAFODION"."LOB130" Extracting LOB data length for the above handle... LOB data length :230150 Extracting lob data into file in chunks of 1000 ... @@ -868,7 +869,7 @@ And the dish ran away with the spoon. >>sh rm t130_extract_command; >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'tlob130_deep2.jpg');/g" >> t130_extract_command; >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200020669695009493415412319669695009566298720718212329582202018367020"TRAFODION"."LOB130" ' , 'tlob130_deep2.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200020321792085830712377119321792085897256767518212331057655277665020"TRAFODION"."LOB130" ' , 'tlob130_deep2.jpg'); Success. Targetfile :tlob130_deep2.jpg Length : 159018 --- SQL operation complete. @@ -877,7 +878,7 @@ Success. Targetfile :tlob130_deep2.jpg Length : 159018 >>sh rm t130_extract_command; >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'hdfs:\/\/\/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command; >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200030669695009493415412319669695009566813234018212329582207185399020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_anoush2.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200030321792085830712377119321792085897715067718212331057660007758020"TRAFODION"."LOB130" ' , 'hdfs:///lobs/tlob130_anoush2.jpg'); Success. Targetfile :hdfs:///lobs/tlob130_anoush2.jpg Length : 230150 --- SQL operation complete. @@ -904,7 +905,7 @@ Hey diddle diddle, >>sh rm t130_extract_command; >>sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , >>'tlob130_anoush3.jpg',create,truncate);/g" >> t130_extract_command; >>obey t130_extract_command; ->>extract lobtofile(LOB 'LOBH00000200030669695009493415804519669695009570711099518212329582246227037020"TRAFODION"."LOB130" ' , 'tlob130_anoush3.jpg',create,truncate); +>>extract lobtofile(LOB 'LOBH0000000200030321792085830712722219321792085901159787918212331057694392521020"TRAFODION"."LOB130" ' , 'tlob130_anoush3.jpg',create,truncate); Success. Targetfile :tlob130_anoush3.jpg Length : 230150 --- SQL operation complete. @@ -994,6 +995,42 @@ C1 (EXPR) (EXPR) >>create table tlob130gt (c1 int not null, c2 blob, c3 clob, c4 blob, primary >>key (c1)); --- SQL operation complete. +>>create table tlob130gt2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); + +--- SQL operation complete. +>>get lob stats for table tlob130gt2; + + +Lob Information for table: "TRAFODION".LOB130.TLOB130GT2 +========================= + + ColumnName : C2 + Lob Location : /lobs + LOB Data File: LOBP_03217920858307150891_0001 + LOB EOD : 0 + LOB Used Len : 0 + ColumnName : C3 + Lob Location : /lobs + LOB Data File: LOBP_03217920858307150891_0002 + LOB EOD : 0 + LOB Used Len : 0 + ColumnName : C4 + Lob Location : External HDFS Location + LOB Data File: External HDFS File + LOB EOD : 0 + LOB Used Len : 0 + +--- SQL operation complete. +>>select * from table(lob stats(tlob130gt2)); + +CATALOG_NAME SCHEMA_NAME OBJECT_NAME COLUMN_NAME LOB_LOCATION LOB_DATA_FILE LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------- + +TRAFODION LOB130 TLOB130GT2 C2 /lobs LOBP_03217920858307150891_0001 0 0 +TRAFODION LOB130 TLOB130GT2 C3 /lobs LOBP_03217920858307150891_0002 0 0 +TRAFODION LOB130 TLOB130GT2 C4 External HDFS Location External HDFS File 0 0 + +--- 3 row(s) selected. >>insert into tlob130gt values (1, stringtolob('xxxx'), stringtolob('yyyy'), >>stringtolob('zzzzzzzzzzzzzz')); --- 1 row(s) inserted. @@ -1011,17 +1048,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT ColumnName : C2 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0001 + LOB Data File: LOBP_03217920858307149714_0001 LOB EOD : 15 LOB Used Len : 15 ColumnName : C3 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0002 + LOB Data File: LOBP_03217920858307149714_0002 LOB EOD : 15 LOB Used Len : 15 ColumnName : C4 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0003 + LOB Data File: LOBP_03217920858307149714_0003 LOB EOD : 45 LOB Used Len : 45 @@ -1031,9 +1068,9 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT CATALOG_NAME SCHEMA_NAME OBJECT_NAME COLUMN_NAME LOB_LOCATION LOB_DATA_FILE LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------- -TRAFODION LOB130 TLOB130GT C2 /lobs LOBP_06696950094934183379_0001 15 15 -TRAFODION LOB130 TLOB130GT C3 /lobs LOBP_06696950094934183379_0002 15 15 -TRAFODION LOB130 TLOB130GT C4 /lobs LOBP_06696950094934183379_0003 45 45 +TRAFODION LOB130 TLOB130GT C2 /lobs LOBP_03217920858307149714_0001 15 15 +TRAFODION LOB130 TLOB130GT C3 /lobs LOBP_03217920858307149714_0002 15 15 +TRAFODION LOB130 TLOB130GT C4 /lobs LOBP_03217920858307149714_0003 45 45 --- 3 row(s) selected. >>delete from tlob130gt where c1=2; @@ -1050,17 +1087,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT ColumnName : C2 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0001 + LOB Data File: LOBP_03217920858307149714_0001 LOB EOD : 30 LOB Used Len : 25 ColumnName : C3 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0002 + LOB Data File: LOBP_03217920858307149714_0002 LOB EOD : 31 LOB Used Len : 26 ColumnName : C4 Lob Location : /lobs - LOB Data File: LOBP_06696950094934183379_0003 + LOB Data File: LOBP_03217920858307149714_0003 LOB EOD : 71 LOB Used Len : 56 @@ -1070,11 +1107,209 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT CATALOG_NAME SCHEMA_NAME OBJECT_NAME COLUMN_NAME LOB_LOCATION LOB_DATA_FILE LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------- -TRAFODION LOB130 TLOB130GT C2 /lobs LOBP_06696950094934183379_0001 30 25 -TRAFODION LOB130 TLOB130GT C3 /lobs LOBP_06696950094934183379_0002 31 26 -TRAFODION LOB130 TLOB130GT C4 /lobs LOBP_06696950094934183379_0003 71 56 +TRAFODION LOB130 TLOB130GT C2 /lobs LOBP_03217920858307149714_0001 30 25 +TRAFODION LOB130 TLOB130GT C3 /lobs LOBP_03217920858307149714_0002 31 26 +TRAFODION LOB130 TLOB130GT C4 /lobs LOBP_03217920858307149714_0003 71 56 + +--- 3 row(s) selected. +>> +>>?section lob_external +>>--test external lobs +>>create table tlob130ext (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); + +--- SQL operation complete. +>>insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); + +--- 1 row(s) inserted. +>>insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///lobs/lob_input_b1.txt'),externaltolob('hdfs:///lobs/lob_input_b1.txt')); + +--- 1 row(s) inserted. +>>insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///lobs/lob_input_c1.txt'),externaltolob('hdfs:///lobs/lob_input_c1.txt')); + +--- 1 row(s) inserted. +>>--negative cases +>>insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); + +*** ERROR[1432] Input LOB type 8 does not match column's storage type : 2 Column name : C2 . + +*** ERROR[8822] The statement was not prepared. + +>>insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),filetolob('hdfs:///lobs/lob_input_a1.txt')); + +*** ERROR[1432] Input LOB type 2 does not match column's storage type : 8 Column name : C4 . + +*** ERROR[8822] The statement was not prepared. + +>>update tlob130ext set c4=stringtolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1; + +*** ERROR[1432] Input LOB type 2 does not match column's storage type : 8 Column name : C4 . + +*** ERROR[8822] The statement was not prepared. + +>>update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1; + +*** ERROR[15001] A syntax error occurred at or before: +update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_a1.txt', append) + ^ (79 characters from start of SQL statement) + +*** ERROR[8822] The statement was not prepared. + +>>update tlob130ext set c3=externaltolob('hdfs:///lobs/lob_input_b1.txt') where c1=1; + +*** ERROR[1432] Input LOB type 8 does not match column's storage type : 2 Column name : C3 . + +*** ERROR[8822] The statement was not prepared. + +>>update tlob130ext set c4=stringtolob('cannot allow this') where c1=1; + +*** ERROR[1432] Input LOB type 2 does not match column's storage type : 8 Column name : C4 . + +*** ERROR[8822] The statement was not prepared. + +>>delete from tlob130ext where c1=1; + +--- 1 row(s) deleted. +>>insert into tlob130ext values(1, stringtolob('first lob'),externaltolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); + +*** ERROR[1432] Input LOB type 8 does not match column's storage type : 2 Column name : C3 . + +*** ERROR[8822] The statement was not prepared. + +>>insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); + +--- 1 row(s) inserted. +>> +>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; + +(EXPR) (EXPR) (EXPR) +-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- + +first lob Hey diddle diddle, + Hey diddle diddle, + +second lob The cat and the fiddle, + The cat and the fiddle, + +third lob The cow jumped over the moon. + The cow jumped over the moon. + + +--- 3 row(s) selected. +>>update tlob130ext set c3=stringtolob('can allow this') where c1=1; + +--- 1 row(s) updated. +>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; + +(EXPR) (EXPR) (EXPR) +-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- + +first lob can allow this Hey diddle diddle, + +second lob The cat and the fiddle, + The cat and the fiddle, + +third lob The cow jumped over the moon. + The cow jumped over the moon. + + +--- 3 row(s) selected. +>> +>> +>> +>>update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_d1.txt') where c1=2; + +--- 1 row(s) updated. +>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; + +(EXPR) (EXPR) (EXPR) +-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- + +first lob can allow this Hey diddle diddle, + +second lob The cat and the fiddle, + The little dog laughed, +To see such sport, + +And th +third lob The cow jumped over the moon. + The cow jumped over the moon. + + +--- 3 row(s) selected. +>> +>> +>>update tlob130ext set c2=filetolob('hdfs:///lobs/lob_input_b1.txt') where c1=2; + +--- 1 row(s) updated. +>>select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext; + +(EXPR) (EXPR) (EXPR) +-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- + +first lob can allow this can allow this +The cat and the fiddle, + The cat and the fiddle, + The cat and the fiddle, + +third lob The cow jumped over the moon. + The cow jumped over the moon. + + +--- 3 row(s) selected. +>>get lob stats for table tlob130ext; + + +Lob Information for table: "TRAFODION".LOB130.TLOB130EXT +========================= + + ColumnName : C2 + Lob Location : /lobs + LOB Data File: LOBP_03217920858307159876_0001 + LOB EOD : 61 + LOB Used Len : 42 + ColumnName : C3 + Lob Location : /lobs + LOB Data File: LOBP_03217920858307159876_0002 + LOB EOD : 106 + LOB Used Len : 68 + ColumnName : C4 + Lob Location : External HDFS Location + LOB Data File: External HDFS File + LOB EOD : 0 + LOB Used Len : 0 + +--- SQL operation complete. +>>select * from table(lob stats(tlob130ext)); + +CATALOG_NAME SCHEMA_NAME OBJECT_NAME COLUMN_NAME LOB_LOCATION LOB_DATA_FILE LOB_DATA_FILE_SIZE_EOD LOB_DATA_FILE_SIZE_USED +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ----------------------- + +TRAFODION LOB130 TLOB130EXT C2 /lobs LOBP_03217920858307159876_0001 61 42 +TRAFODION LOB130 TLOB130EXT C3 /lobs LOBP_03217920858307159876_0002 106 68 +TRAFODION LOB130 TLOB130EXT C4 External HDFS Location External HDFS File 0 0 --- 3 row(s) selected. +>>sh clitestdriver 2 < TEST130_argfile2 2>&1 | tee -a LOG130; +************************************* +Blob test extract to file in chunks +************************************ +Extract from a lob column in a lob table + +Input lob table name : +Table name : TRAFODION.LOB130.tlob130ext +Input lob column name to extract from : +Column Name : c4 +Input a filename to extract to : +Output File Name : lobc4ext.txt +Extracting lob handle for column c4... +LOB handle for c4: LOBH0000000800030321792085830715987619321792085938826265518212331058071128297020"TRAFODION"."LOB130" +Extracting LOB data length for the above handle... +LOB data length :19 +Extracting lob data into file in chunks of 1000 ... +Wrote 19 bytes to file : lobc4ext.txt +>>sh cat lobc4ext.txt |tee -a LOG130; +Hey diddle diddle, +>> >> >>-- test to ensure all lob dependent tables and schemas containing lob tables >>-- get dropped cleanly. @@ -1101,9 +1336,9 @@ TRAFODION Tables in Schema TRAFODION.LOBSCH ================================= -LOBDescChunks__06696950094934193691_0001 -LOBDescHandle__06696950094934193691_0001 -LOBMD__06696950094934193691 +LOBDescChunks__03217920858307180754_0001 +LOBDescHandle__03217920858307180754_0001 +LOBMD__03217920858307180754 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS TLOB130TS2
http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/TEST130 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST130 b/core/sql/regress/executor/TEST130 index 48da1ed..2f10aed 100755 --- a/core/sql/regress/executor/TEST130 +++ b/core/sql/regress/executor/TEST130 @@ -42,7 +42,7 @@ drop table t130lob1 cascade; drop table t130lob2 cascade; drop table t130lob3 cascade; drop table t130lob4 cascade; - +drop table tlob130ext cascade; ?section setup log LOG130 clear; @@ -167,7 +167,8 @@ create table tlob130bin_limit1K(c1 int not null, c2 blob(1 K), primary key (c1)) sh cp $scriptsdir/executor/deep.jpg $rundir/executor/; sh cp $scriptsdir/executor/anoush.jpg $rundir/executor/; sh cp $scriptsdir/executor/lob_input_* $rundir/executor/; -sh cp $scriptsdir/executor/TEST130_argfile $rundir/executor/; +sh cp $scriptsdir/executor/TEST130_argfile* $rundir/executor/; + -- inserts -- first line insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt')); @@ -448,6 +449,9 @@ drop table tlob130gt; ?section lob_get_test log LOG130; create table tlob130gt (c1 int not null, c2 blob, c3 clob, c4 blob, primary key (c1)); +create table tlob130gt2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); +get lob stats for table tlob130gt2; +select * from table(lob stats(tlob130gt2)); insert into tlob130gt values (1, stringtolob('xxxx'), stringtolob('yyyy'), stringtolob('zzzzzzzzzzzzzz')); insert into tlob130gt values (2, stringtolob('xxxxx'), stringtolob('yyyyy'), stringtolob('zzzzzzzzzzzzzzz')); insert into tlob130gt values (3, stringtolob('xxxxxx'), stringtolob('yyyyyy'), stringtolob('zzzzzzzzzzzzzzzz')); @@ -458,6 +462,41 @@ insert into tlob130gt values (2, stringtolob('xxxxxxxxxxxxxxx'), stringtolob('yy get lob stats for table tlob130gt; select * from table(lob stats(tlob130gt)); +?section lob_external +--test external lobs +create table tlob130ext (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1)); +insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); +insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///lobs/lob_input_b1.txt'),externaltolob('hdfs:///lobs/lob_input_b1.txt')); +insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///lobs/lob_input_c1.txt'),externaltolob('hdfs:///lobs/lob_input_c1.txt')); +--negative cases +insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); +insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),filetolob('hdfs:///lobs/lob_input_a1.txt')); +update tlob130ext set c4=stringtolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1; +update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_a1.txt', append) where c1=1; +update tlob130ext set c3=externaltolob('hdfs:///lobs/lob_input_b1.txt') where c1=1; +update tlob130ext set c4=stringtolob('cannot allow this') where c1=1; +delete from tlob130ext where c1=1; +insert into tlob130ext values(1, stringtolob('first lob'),externaltolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); +insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///lobs/lob_input_a1.txt'),externaltolob('hdfs:///lobs/lob_input_a1.txt')); + +select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; +update tlob130ext set c3=stringtolob('can allow this') where c1=1; +select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; + + + +update tlob130ext set c4=externaltolob('hdfs:///lobs/lob_input_d1.txt') where c1=2; +select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c4,50) from tlob130ext; + + +update tlob130ext set c2=filetolob('hdfs:///lobs/lob_input_b1.txt') where c1=2; +select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext; +get lob stats for table tlob130ext; +select * from table(lob stats(tlob130ext)); +sh clitestdriver 2 < TEST130_argfile2 2>&1 | tee -a LOG130; +sh cat lobc4ext.txt |tee -a LOG130; + + -- test to ensure all lob dependent tables and schemas containing lob tables -- get dropped cleanly. @@ -482,6 +521,11 @@ set schema trafodion.lob130; log; sh rm t130_*; sh regrhadoop.ksh fs -rm /lobs/tlob130* +sh regrhadoop.ksh fs -rm /lobs/lobinput_a1.txt +sh regrhadoop.ksh fs -rm /lobs/lobinput_b1.txt +sh regrhadoop.ksh fs -rm /lobs/lobinput_c1.txt +sh regrhadoop.ksh fs -rm /lobs/lobinput_d1.txt +sh regrhadoop.ksh fs -rm /lobs/lobinput_e1.txt sh rm lobinput_*; sh rm deep.jpg; sh rm anoush.jpg; @@ -500,9 +544,11 @@ drop table tlob130bin3; drop table tlob130bt; drop table tlob130txt2; drop table tlob130bin2; +drop table tlob130ext; sh rm TMP130; sh rm tlob130txt2; sh rm lobc2out.jpg; +sh rm lobc4ext.txt; sh rm TEST130_argfile; drop table tlob130gc; drop table tlob130gt; http://git-wip-us.apache.org/repos/asf/incubator-trafodion/blob/5a3f2024/core/sql/regress/executor/TEST130_argfile2 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST130_argfile2 b/core/sql/regress/executor/TEST130_argfile2 index f123030..181570c 100644 --- a/core/sql/regress/executor/TEST130_argfile2 +++ b/core/sql/regress/executor/TEST130_argfile2 @@ -1,3 +1,3 @@ TRAFODION.LOB130.tlob130ext c4 -lobc4ext.jpg +lobc4ext.txt
