Repository: trafodion Updated Branches: refs/heads/master fb3a5e4bb -> bd886c121
http://git-wip-us.apache.org/repos/asf/trafodion/blob/a96968e4/core/sql/regress/executor/EXPECTED130 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/EXPECTED130 b/core/sql/regress/executor/EXPECTED130 index 4ef2b51..5d274b6 100644 --- a/core/sql/regress/executor/EXPECTED130 +++ b/core/sql/regress/executor/EXPECTED130 @@ -63,9 +63,9 @@ C1 C1 C2 ----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------- - 1 LOBH0000000200010684167783903023229919684167783929468270818212379187803875502020"TRAFODION"."LOB130" - 2 LOBH0000000200010684167783903023229919684167783930244221218212379187811728769020"TRAFODION"."LOB130" - 3 LOBH0000000200010684167783903023229919684167783931095112818212379187819814558020"TRAFODION"."LOB130" + 1 LOBH0000000200010599162824891000214319599162824908088805718212384027254904057020"TRAFODION"."LOB130" + 2 LOBH0000000200010599162824891000214319599162824908906372018212384027264411730020"TRAFODION"."LOB130" + 3 LOBH0000000200010599162824891000214319599162824909696994518212384027272355484020"TRAFODION"."LOB130" --- 3 row(s) selected. >> @@ -668,7 +668,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 'LOBH0000000200010684167783903032825819684167784024315676018212379188752592782020"TRAFODION"."LOB130" ' , 'tlob130_txt1.txt'); +>>extract lobtofile(LOB 'LOBH0000000200010599162824891010592019599162825008788792718212384028263167191020"TRAFODION"."LOB130" ' , 'tlob130_txt1.txt'); Success. Targetfile :tlob130_txt1.txt Length : 19 --- SQL operation complete. @@ -684,7 +684,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 'LOBH0000000200010684167783903033003019684167784032532137618212379188834776548020"TRAFODION"."LOB130" ' , 'tlob130_deep.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010599162824891010783319599162825017613184818212384028351739389020"TRAFODION"."LOB130" ' , 'tlob130_deep.jpg'); Success. Targetfile :tlob130_deep.jpg Length : 159018 --- SQL operation complete. @@ -700,7 +700,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 'LOBH0000000200010684167783903033003019684167784032532137618212379188834776548020"TRAFODION"."LOB130" ' , 'tlob130_anoush.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200010599162824891010783319599162825017613184818212384028351739389020"TRAFODION"."LOB130" ' , 'tlob130_anoush.jpg'); Success. Targetfile :tlob130_anoush.jpg Length : 230150 --- SQL operation complete. @@ -861,7 +861,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: LOBH0000000200010684167783903033003019684167784032532137618212379188834776548020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010599162824891010783319599162825017613184818212384028351739389020"TRAFODION"."LOB130" Extracting LOB data length for the above handle... LOB data length :230150 Extracting lob data into file in chunks ... @@ -935,7 +935,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 'LOBH0000000200020684167783903037314519684167784064680849818212379189156209716020"TRAFODION"."LOB130" ' , 'tlob130_deep2.jpg'); +>>extract lobtofile(LOB 'LOBH0000000200020599162824891015449419599162825052851369318212384028704041429020"TRAFODION"."LOB130" ' , 'tlob130_deep2.jpg'); Success. Targetfile :tlob130_deep2.jpg Length : 159018 --- SQL operation complete. @@ -967,7 +967,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 'LOBH0000000200030684167783903037826119684167784070505285418212379189214460936020"TRAFODION"."LOB130" ' , 'tlob130_anoush3.jpg',create,truncate); +>>extract lobtofile(LOB 'LOBH0000000200030599162824891016047719599162825059292319718212384028768498788020"TRAFODION"."LOB130" ' , 'tlob130_anoush3.jpg',create,truncate); Success. Targetfile :tlob130_anoush3.jpg Length : 230150 --- SQL operation complete. @@ -1068,12 +1068,12 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT2 ColumnName : C2 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030414947_0001 + LOB Data File: LOBP_05991628248910199914_0001 LOB EOD : 0 LOB Used Len : 0 ColumnName : C3 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030414947_0002 + LOB Data File: LOBP_05991628248910199914_0002 LOB EOD : 0 LOB Used Len : 0 ColumnName : C4 @@ -1088,8 +1088,8 @@ Lob Information for table: "TRAFODION".LOB130.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 /user/trafodion/lobs LOBP_06841677839030414947_0001 0 0 -TRAFODION LOB130 TLOB130GT2 C3 /user/trafodion/lobs LOBP_06841677839030414947_0002 0 0 +TRAFODION LOB130 TLOB130GT2 C2 /user/trafodion/lobs LOBP_05991628248910199914_0001 0 0 +TRAFODION LOB130 TLOB130GT2 C3 /user/trafodion/lobs LOBP_05991628248910199914_0002 0 0 TRAFODION LOB130 TLOB130GT2 C4 External HDFS Location External HDFS File 0 0 --- 3 row(s) selected. @@ -1110,17 +1110,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT ColumnName : C2 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0001 + LOB Data File: LOBP_05991628248910195746_0001 LOB EOD : 15 LOB Used Len : 15 ColumnName : C3 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0002 + LOB Data File: LOBP_05991628248910195746_0002 LOB EOD : 15 LOB Used Len : 15 ColumnName : C4 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0003 + LOB Data File: LOBP_05991628248910195746_0003 LOB EOD : 45 LOB Used Len : 45 @@ -1130,9 +1130,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 /user/trafodion/lobs LOBP_06841677839030411144_0001 15 15 -TRAFODION LOB130 TLOB130GT C3 /user/trafodion/lobs LOBP_06841677839030411144_0002 15 15 -TRAFODION LOB130 TLOB130GT C4 /user/trafodion/lobs LOBP_06841677839030411144_0003 45 45 +TRAFODION LOB130 TLOB130GT C2 /user/trafodion/lobs LOBP_05991628248910195746_0001 15 15 +TRAFODION LOB130 TLOB130GT C3 /user/trafodion/lobs LOBP_05991628248910195746_0002 15 15 +TRAFODION LOB130 TLOB130GT C4 /user/trafodion/lobs LOBP_05991628248910195746_0003 45 45 --- 3 row(s) selected. >>delete from tlob130gt where c1=2; @@ -1149,17 +1149,17 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130GT ColumnName : C2 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0001 + LOB Data File: LOBP_05991628248910195746_0001 LOB EOD : 30 LOB Used Len : 25 ColumnName : C3 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0002 + LOB Data File: LOBP_05991628248910195746_0002 LOB EOD : 31 LOB Used Len : 26 ColumnName : C4 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030411144_0003 + LOB Data File: LOBP_05991628248910195746_0003 LOB EOD : 71 LOB Used Len : 56 @@ -1169,9 +1169,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 /user/trafodion/lobs LOBP_06841677839030411144_0001 30 25 -TRAFODION LOB130 TLOB130GT C3 /user/trafodion/lobs LOBP_06841677839030411144_0002 31 26 -TRAFODION LOB130 TLOB130GT C4 /user/trafodion/lobs LOBP_06841677839030411144_0003 71 56 +TRAFODION LOB130 TLOB130GT C2 /user/trafodion/lobs LOBP_05991628248910195746_0001 30 25 +TRAFODION LOB130 TLOB130GT C3 /user/trafodion/lobs LOBP_05991628248910195746_0002 31 26 +TRAFODION LOB130 TLOB130GT C4 /user/trafodion/lobs LOBP_05991628248910195746_0003 71 56 --- 3 row(s) selected. >> @@ -1197,6 +1197,26 @@ TRAFODION --- 1 row(s) inserted. >> +>> +>>log; +>>sh rm t130_extract_command; +>>sh grep "^LOBH" TMP130 | sed "s/^/extract filename(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; +>>obey t130_extract_command; +>>extract filename(LOB 'LOBH0000000200020599162824891021556919599162825127466931518212384029450160806020"TRAFODION"."LOB130" '); + LOB filename : /user/trafodion/lobs/LOBP_05991628248910215569_0002 + +--- SQL operation complete. +>>sh rm t130_extract_command; +>>sh grep "^LOBH" TMP130 | sed "s/^/extract offset(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; +>>obey t130_extract_command; +>>extract offset(LOB 'LOBH0000000200020599162824891021556919599162825127466931518212384029450160806020"TRAFODION"."LOB130" '); + LOB Offset : 43 + +--- SQL operation complete. +>> +>> +>> +>> >>--negative cases >>insert into tlob130ext values(2, externaltolob('first lob'), >>filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); @@ -1342,12 +1362,12 @@ Lob Information for table: "TRAFODION".LOB130.TLOB130EXT ColumnName : C2 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030429611_0001 + LOB Data File: LOBP_05991628248910215569_0001 LOB EOD : 70 LOB Used Len : 42 ColumnName : C3 Lob Location : /user/trafodion/lobs - LOB Data File: LOBP_06841677839030429611_0002 + LOB Data File: LOBP_05991628248910215569_0002 LOB EOD : 125 LOB Used Len : 68 ColumnName : C4 @@ -1362,8 +1382,8 @@ Lob Information for table: "TRAFODION".LOB130.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 /user/trafodion/lobs LOBP_06841677839030429611_0001 70 42 -TRAFODION LOB130 TLOB130EXT C3 /user/trafodion/lobs LOBP_06841677839030429611_0002 125 68 +TRAFODION LOB130 TLOB130EXT C2 /user/trafodion/lobs LOBP_05991628248910215569_0001 70 42 +TRAFODION LOB130 TLOB130EXT C3 /user/trafodion/lobs LOBP_05991628248910215569_0002 125 68 TRAFODION LOB130 TLOB130EXT C4 External HDFS Location External HDFS File 0 0 --- 3 row(s) selected. @@ -1380,7 +1400,7 @@ Column Name : c4 Input a filename to extract to : Output File Name : lobc4ext.txt Extracting lob handle for column c4... -LOB handle for c4: LOBH0000000800030684167783903042961119684167784136680234118212379189876220716020"TRAFODION"."LOB130" +LOB handle for c4: LOBH0000000800030599162824891021556919599162825132831553518212384029503873802020"TRAFODION"."LOB130" Extracting LOB data length for the above handle... LOB data length :19 Extracting lob data into file in chunks ... @@ -1446,7 +1466,7 @@ Table name : TRAFODION.LOB130.t130lob5 Input lob column name to get handle from : Column Name : c2 Extracting lob handle for column c2... -LOB handle for c2: LOBH0000000200010684167783903048606519684167784176681773818212379190276169590020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010599162824891027798519599162825175307181118212384029928539205020"TRAFODION"."LOB130" >>select lobtostring(c2,20) from t130lob5; (EXPR) @@ -1463,7 +1483,7 @@ Table name : TRAFODION.LOB130.t130lob5 Input lob column name to get handle from : Column Name : c2 Extracting lob handle for column c2... -LOB handle for c2: LOBH0000000200010684167783903048606519684167784176681773818212379190276169590020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010599162824891027798519599162825175307181118212384029928539205020"TRAFODION"."LOB130" >>select lobtostring(c2,40) from t130lob5; (EXPR) @@ -1480,7 +1500,7 @@ Table name : TRAFODION.LOB130.t130lob5 Input lob column name to get handle from : Column Name : c2 Extracting lob handle for column c2... -LOB handle for c2: LOBH0000000200010684167783903048606519684167784176681773818212379190276169590020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010599162824891027798519599162825175307181118212384029928539205020"TRAFODION"."LOB130" >>select lobtostring(c2,20) from t130lob5; (EXPR) @@ -1497,7 +1517,7 @@ Table name : TRAFODION.LOB130.t130lob5 Input lob column name to get handle from : Column Name : c2 Extracting lob handle for column c2... -LOB handle for c2: LOBH0000000200010684167783903048606519684167784176681773818212379190276169590020"TRAFODION"."LOB130" +LOB handle for c2: LOBH0000000200010599162824891027798519599162825175307181118212384029928539205020"TRAFODION"."LOB130" >>select lobtostring(c2,40) from t130lob5; (EXPR) @@ -1524,7 +1544,7 @@ zzzzzzzzzzzzzzzzzzzz C2 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------- -LOBH0000000200010684167783903042961119684167784191698308218212379190426424539020"TRAFODION"."LOB130" +LOBH0000000200010599162824891021556919599162825191632813218212384030091687654020"TRAFODION"."LOB130" --- 1 row(s) selected. >>-- following should return error since only external lobs will be allowed @@ -1567,9 +1587,9 @@ LOBH0000000200010684167783903042961119684167784191698308218212379190426424539020 Tables in Schema TRAFODION.LOBSCH ================================= -LOBDescChunks__06841677839030508209_0001 -LOBDescHandle__06841677839030508209_0001 -LOBMD__06841677839030508209 +LOBDescChunks__05991628248910301630_0001 +LOBDescHandle__05991628248910301630_0001 +LOBMD__05991628248910301630 SB_HISTOGRAMS SB_HISTOGRAM_INTERVALS SB_PERSISTENT_SAMPLES http://git-wip-us.apache.org/repos/asf/trafodion/blob/a96968e4/core/sql/regress/executor/TEST130 ---------------------------------------------------------------------- diff --git a/core/sql/regress/executor/TEST130 b/core/sql/regress/executor/TEST130 index 38214c6..05e235f 100755 --- a/core/sql/regress/executor/TEST130 +++ b/core/sql/regress/executor/TEST130 @@ -559,6 +559,23 @@ insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///us insert into tlob130ext values(2, stringtolob('second lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt')); insert into tlob130ext values(3, stringtolob('third lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt')); + +log; +log TMP130 clear; +select c3 from tlob130ext where c1=3; +log; + +log LOG130; +sh rm t130_extract_command; +sh grep "^LOBH" TMP130 | sed "s/^/extract filename(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; +obey t130_extract_command; +sh rm t130_extract_command; +sh grep "^LOBH" TMP130 | sed "s/^/extract offset(LOB '/g" | sed "s/$/');/g" >> t130_extract_command; +obey t130_extract_command; + + + + --negative cases insert into tlob130ext values(2, externaltolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt')); insert into tlob130ext values(3, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));
