core/sql/regress/executor/TEST130

-- Test: TEST130 (Executor)
-- @@@ START COPYRIGHT @@@
--
-- Licensed to the Apache Software Foundation (ASF) under one
-- or more contributor license agreements.  See the NOTICE file
-- distributed with this work for additional information
-- regarding copyright ownership.  The ASF licenses this file
-- to you under the Apache License, Version 2.0 (the
-- "License"); you may not use this file except in compliance
-- with the License.  You may obtain a copy of the License at
--
--   http://www.apache.org/licenses/LICENSE-2.0
--
-- Unless required by applicable law or agreed to in writing,
-- software distributed under the License is distributed on an
-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
-- KIND, either express or implied.  See the License for the
-- specific language governing permissions and limitations
-- under the License.
--
-- @@@ END COPYRIGHT @@@
--
-- Functionality: LOBs
-- Expected files: EXPECTED130
-- Table created: 
-- Limitations: 
-- 
-- To Do:   
--      
-- Revision history:
--     (10/21/2014) - Created.
-- 




?section clnup
set schema trafodion.lob130;
drop table t130lob1 cascade;
drop table t130lob2 cascade;
drop table t130lob3 cascade;
drop table t130lob4 cascade;
drop table t130lob5 cascade;
drop table tlob130ext cascade;
drop table t130char cascade;
drop table t130var cascade;
drop external table if exists date_dim for hive.hive.date_dim;

?section setup
log LOG130 clear;
log;
drop schema trafodion.lob130 cascade;
log LOG130;
create schema trafodion.lob130;
set schema trafodion.lob130;
create table t130lob1 (c1 blob);
create table t130lob2 (c1 int not null, c2 blob , primary key (c1));
create table t130lob3 (c1 int not null,
c2 blob, c3 blob, primary key (c1));
create table t130lob4 (c1 int not null,
c2 char(10), c3 clob, primary key (c1));
create table t130char(c1 int not null, c2 char (20), primary key (c1));
create table t130var(c1 int not null, c2 varchar(100), primary key (c1));
create external table date_dim
  (d_date_sk int, d_date_id varchar(100 bytes) character set utf8, d_date date)
  for hive.hive.date_dim;

?section dml_insert
insert into t130lob1 values(NULL);
select * from t130lob1;

insert into t130lob2 values(1,stringtolob('inserted row11'));
insert into t130lob2 values(2,stringtolob('inserted row12'));
insert into t130lob2 values(3,stringtolob('inserted row13'));
insert into t130lob2 values(3,stringtolob('xxxx'));
select * from t130lob2;


select lobtostring(c2,10) from t130lob2;
select lobtostring(c2,2) from t130lob2;
select c1,lobtostring(c2,100) from t130lob2;

delete from t130lob2;
--test insert with param (assumes that caller will pass in string format.)
prepare s from insert into t130lob2 values (1, ?);
execute s using 'fgfgfhfhfhfhhfhfhfhjfkkfkffllflflfll';
select lobtostring(c2,50) from t130lob2;

--negative test for values tuple list
prepare s from insert into t130lob2 values (1,'gg'),(2,'hh');
prepare s from insert into t130lob2 values (1,stringtolob('hh')),(2,stringtolob('gg'));

--test update with param 
prepare s from update t130lob2 set c2=? where c1 =1;
execute s using 'xyxyxyxyxyxyx';
select lobtostring(c2,50) from t130lob2;

--test insert select from a source char column to lob
delete from t130lob2;
insert into t130char values (1,'fixed char 1'),(2,'fixed char 2'),(3,'fixed char 3');
insert into t130lob2(c1,c2) select c1,c2 from t130char;
select lobtostring(c2,50) from t130lob2;

--test insert select from a source varchar column to lob
delete from t130lob2;
insert into t130var values (1,'var char 1'),(2,'var char 22222222222222222222222'),(3,'var char 33333333333333333333333333333333333333333333333333333');
insert into t130lob2(c1,c2) select c1,c2 from t130var;
select lobtostring(c2,100) from t130lob2;

--insert select from a source lob column to a target varchar column.
delete from t130var;
insert into t130var select c1, lobtostring(c2,100) from t130lob2;
delete from t130var;
----negative case
insert into t130var select c1,c2 from t130lob2;
delete from t130var;
delete from t130lob2;

--test insert select from a source hive varchar column to lob
delete from t130lob2;
insert into t130lob2 select [first 10] d_date_sk,d_date_id from hive.hive.date_dim;
select lobtostring(c2,4) from t130lob2 order by c1;
delete from t130lob2;


--insert select from a source lob column to a target lob column
delete from t130lob3;
insert into t130lob2 values(1,stringtolob('inserted row11'));
insert into t130lob2 values(2,stringtolob('inserted row12'));
insert into t130lob2 values(3,stringtolob('inserted row13'));
insert into t130lob3 select c1,c2,c2 from t130lob2;
select c1, lobtostring(c2,100),lobtostring(c3,100) from t130lob3;
delete from t130lob3;
delete from t130lob2;




--test values clause
values((select * from t130lob1));

--test empty_blob(), empty_clob()
delete from t130lob2;
insert into t130lob2 values(0,stringtolob('inserted row10'));
insert into t130lob2 values (1, empty_blob());
select lobtostring(c2,10) from t130lob2;
delete from t130lob2;
insert into t130lob2 values(0,stringtolob('inserted row10'));
insert into t130lob2 values (1, empty_clob());
select lobtostring(c2,10) from t130lob2;
update t130lob2 set c2=stringtolob('inserted row11');
select lobtostring(c2,30) from t130lob2;
update t130lob2 set c2=empty_blob();
select lobtostring(c2,10) from t130lob2;
update t130lob2 set c2=stringtolob('inserted row11',append);
select lobtostring(c2,30) from t130lob2;
update t130lob2 set c2=stringtolob('more inserted row11',append);
select lobtostring(c2,50) from t130lob2;
update t130lob2 set c2=empty_blob();
select lobtostring(c2,10) from t130lob2;

?section dml_join
insert into t130lob3 values (1,stringtolob('inserted row21a'),stringtolob('inserted row21b'));
insert into t130lob3 values (2,stringtolob('inserted row22a'),stringtolob('inserted row22b'));
insert into t130lob3 values (3,stringtolob('inserted row23a'),stringtolob('inserted row23b'));
insert into t130lob3 values (4,stringtolob('inserted row24a'),stringtolob('inserted row24b'));

prepare s from 
select c1, lobtostring(c2,25), lobtostring(c3,25)  from t130lob3;



select lobtostring(t130lob2.c2,30) from t130lob2, t130lob3 where t130lob2.c1 = t130lob3.c1;

values (filetolob('./myfile.txt'));
values( stringtolob('xxxxxx'));

?section dml_update

update t130lob2 set c2=stringtolob('updated c2 in all rows');
select c1, lobtostring(c2,30) from t130lob2;

update t130lob2 set c2=stringtolob('updated row21a') where c1=1;
select c1, lobtostring(c2,30) from t130lob2;
select c1, lobtostring(c2,30) from t130lob2 where c1=1;


?section dml_update_append

update t130lob2 set c2=stringtolob('appended row21a',append) where c1=1;
select c1, lobtostring(c2,100) from t130lob2;
select c1, lobtostring(c2,100) from t130lob2 where c1=1;

update t130lob2 set c2=stringtolob(' appended c2 to all rows',append);
select c1, lobtostring(c2,60) from t130lob2;

?section dml_delete

delete from t130lob3 where c1=1;
select c1, lobtostring(c2,30), lobtostring(c3,30)  from t130lob3;

delete from t130lob2 ;
select * from t130lob2;

?section update_stats

-- positive tests; LOB columns should be silently skipped
update statistics for table t130lob1 on every column;

update statistics for table t130lob4 on c1 to c2;

-- negative tests; attempts to explicitly create stats on LOB
-- columns should fail with error 9246
update statistics for table t130lob4 on c3;

update statistics for table t130lob2 on (c1,c2);

update statistics for table t130lob4 on c2 to c3;

log;
obey TEST130(clnup);


sh echo "Test for file input and extract";
-- Test for local input

--cleanup -- comment the following lines out if you want to debug this test and save intermediate files.

?section lob_local_cleanup
log;
drop table tlob130txt1; 
drop table tlob130bin1;
drop table tlob130_limit50;
sh rm TMP130;
sh rm tlob130txt1;
sh rm tlob130_txt1.txt;
sh rm tlob130_deep.jpg;
sh rm tlob130_anoush.jpg;


?section lob_local_file
log LOG130;
--setup
sh echo "Test for file input and extract";
create table tlob130txt1 (c1 int not null, c2 clob, primary key (c1));
create table tlob130bin1 (c1 int not null, c2 blob, primary key (c1));
create table tlob130txt_limit50(c1 int not null, c2 clob(50), primary key (c1));
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/;

-- inserts
-- first line
insert into tlob130txt1 values (1, filetolob('lob_input_a1.txt'));

-- second line
insert into tlob130txt1 values (2, filetolob('lob_input_b1.txt'));

-- third line
insert into tlob130txt1 values (3, filetolob('lob_input_c1.txt'));

select lobtostring(c2, 40 ) from tlob130txt1;

--updates

--should update with full poem
update tlob130txt1 set c2=filetolob('lob_input_d1.txt', append) where c1 = 3;

select lobtostring(c2, 200 ) from tlob130txt1;

-- should see wrong text in the last few lines
update tlob130txt1 set c2=filetolob('lob_input_e1.txt') where c1 =3 ;

select lobtostring(c2, 200 ) from tlob130txt1;

--delete

-- go back to having just the first  line. 
delete from tlob130txt1 where c1 =2;
delete from tlob130txt1 where c1 =3;

--test limits

insert into tlob130txt_limit50 values(1,filetolob('lob_input_e1.txt'));
insert into tlob130bin_limit1K values(1,filetolob('anoush.jpg'));
--test extract 

log;
log TMP130 clear;
select c2 from tlob130txt1;
log;

log LOG130;
sh rm t130_extract_command;

sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_txt1.txt');/g" >> t130_extract_command;

obey t130_extract_command;

--binary input/update

insert into tlob130bin1 values (1 , filetolob('deep.jpg'));
--extract // should have a viewable picture file

log;
log TMP130 clear;
select c2 from tlob130bin1;
log;

log LOG130;
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;

update tlob130bin1 set c2=filetolob('anoush.jpg') ;

--extract into a different file // should have a different viewable picture.

log;
log TMP130 clear ;
select c2 from tlob130bin1;
log;

log LOG130;
sh rm t130_extract_command;
sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'tlob130_anoush.jpg');/g" >> t130_extract_command;

obey t130_extract_command;




-- Test for hdfs input
--cleanup -- comment the following lines out if you want to debug this test and save intermediate files.

?section lob_hdfs_cleanup
log;
drop table tlob130txt2; 
drop table tlob130bin2;
sh rm TMP130;
sh rm tlob130txt2;
sh rm tlob130_txt2.txt;
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_deep.jpg;
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_anoush.jpg;
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_txt2.txt;


?section lob_hdfs_file
log LOG130;
create table tlob130txt2 (c1 int not null, c2 clob, primary key (c1));
create table tlob130bin2 (c1 int not null, c2 blob, primary key (c1));

sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/lob_input_a1.txt;
sh regrhadoop.ksh fs -copyFromLocal lob_input_b1.txt /user/trafodion/lobs/lob_input_b1.txt;
sh regrhadoop.ksh fs -copyFromLocal lob_input_c1.txt /user/trafodion/lobs/lob_input_c1.txt;
sh regrhadoop.ksh fs -copyFromLocal lob_input_d1.txt /user/trafodion/lobs/lob_input_d1.txt;
sh regrhadoop.ksh fs -copyFromLocal lob_input_e1.txt /user/trafodion/lobs/lob_input_e1.txt;
sh regrhadoop.ksh fs -copyFromLocal deep.jpg /user/trafodion/lobs/deep.jpg;
sh regrhadoop.ksh fs -copyFromLocal anoush.jpg /user/trafodion/lobs/anoush.jpg;
-- the next one is a really long file name intended to test error message 8557
sh regrhadoop.ksh fs -copyFromLocal lob_input_a1.txt /user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt;
sh sleep(20);


insert into tlob130txt2 values (1, filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));

-- second line
insert into tlob130txt2 values (2, filetolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt'));

-- third line
insert into tlob130txt2 values (3, filetolob('hdfs:///user/trafodion/lobs/lob_input_c1.txt'));
select lobtostring(c2, 40 ) from tlob130txt2;

--updates

--should update with full poem
update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_d1.txt', append) where c1 = 3;
select lobtostring(c2, 200 ) from tlob130txt2;

-- should see wrong text in the last few lines
update tlob130txt2 set c2=filetolob('hdfs:///user/trafodion/lobs/lob_input_e1.txt') where c1 =3 ;
select lobtostring(c2, 200 ) from tlob130txt2;

--delete

 
-- go back to having just the first  line. 
delete from tlob130txt2 where c1 =2;
delete from tlob130txt2 where c1 =3;
-- test extract 

log;
log TMP130 clear;
select c2 from tlob130txt2;
log;

log LOG130;
sh rm t130_extract_command;

sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_txt2.txt');/g" >> t130_extract_command;
obey t130_extract_command;

--binary input/update

insert into tlob130bin2 values (1 , filetolob('deep.jpg'));
--extract // should have a viewable picture file

log;
log TMP130 clear ;
select c2 from tlob130bin2;
log;

log LOG130;
sh rm t130_extract_command;
sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_deep.jpg');/g" >> t130_extract_command;
obey t130_extract_command;

update tlob130bin2 set c2=filetolob('anoush.jpg') ;

--extract into a different file // should have a different viewable picture.

log;
log TMP130 clear;
select c2 from tlob130bin1;
log;

log LOG130;
sh rm t130_extract_command;
sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_anoush.jpg');/g" >> t130_extract_command;

obey t130_extract_command;


sh clitestdriver 2 < TEST130_argfile 2>&1 | tee -a LOG130;



------------------------------------------------------------------------------

?section lob_misc_cleanup
log;
drop table tlob130txt3;
drop table tlob130bin3;
drop table tlob130bt;
sh rm tlob130_deep2.jpg;
sh rm tlob130_txt1.txt;
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130_anoush2.jpg;
sh rm tlob130_anoush3.jpg;


?section lob_miscfile_test
log LOG130;
-- 2 clob columns
create table tlob130txt3 (c1 int not null, c2 int, c3 clob, c4 clob, primary key (c1));
insert into tlob130txt3 values (1, 1,filetolob('lob_input_a1.txt'), filetolob('lob_input_b1.txt'));
insert into tlob130txt3 values (2, 2,filetolob('lob_input_c1.txt'), filetolob('lob_input_d1.txt'));
select lobtostring(c3,100), lobtostring(c4,100)from tlob130txt3;

-- 2 blob columns
create table tlob130bin3 (c1 int not null, c2 int, c3 blob, c4 blob, primary key (c1));
insert into tlob130bin3 values (1, 1, filetolob('deep.jpg'), filetolob('anoush.jpg'));

log;
log TMP130 clear;
select c3 from tlob130bin3;
log;

log LOG130;
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;

log;
log TMP130 clear;
select c4 from tlob130bin3;
log;

log LOG130;
sh rm t130_extract_command;
sh grep "^LOBH" TMP130 | sed "s/^/extract lobtofile(LOB '/g" | sed "s/$/' , 'hdfs:\/\/\/user\/trafodion\/lobs\/tlob130_anoush2.jpg');/g" >> t130_extract_command;
obey t130_extract_command;

-- combination blob and clob columns
create table tlob130bt (c1 int not null, c2 int, c3 blob, c4 clob, primary key (c1));
insert into tlob130bt values (1,1, filetolob('lob_input_a1.txt'), filetolob('anoush.jpg'));

select lobtostring(c3,40) from tlob130bt;

log;
log TMP130 clear;
select c4 from tlob130bt;
log;

log LOG130;
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;

--test alter 
alter table tlob130bt add column c5 blob;
--should show default value NULL
select c5 from tlob130bt; 

?section lob_gc_cleanup
log;
drop table tlob130gc;

?section lob_gc_test
log LOG130;
create table tlob130gc (c1 int not null, c2 blob, c3 blob, primary key (c1));
--insert a few rows
insert into tlob130gc values (1, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (2, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (3, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (4, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (5, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (6, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (7, stringtolob('aaaa'), stringtolob('bbbbb'));
insert into tlob130gc values (8, stringtolob('aaaa'), stringtolob('bbbbb'));
--create holes in the lob data file
delete from tlob130gc where c1=2;
delete from tlob130gc where c1=6;
update tlob130gc set c2=stringtolob('xxxx')where c1=7;
--check contents of table
select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc;
cqd LOB_GC_LIMIT_SIZE '0';
--this next insert should trigger a GC
insert into tlob130gc values(9,stringtolob('aaaa'), stringtolob('bbbbb'));
--check contents of table. Should have one extra row compared to above select
select c1,lobtostring(c2,10),lobtostring(c3,10) from tlob130gc;
cqd LOB_GC_LIMIT_SIZE reset;

?section lob_get_cleanup
log;
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'));
get lob stats for table tlob130gt;
select * from table(lob stats(tlob130gt));
delete from tlob130gt where c1=2;
insert into tlob130gt values (2, stringtolob('xxxxxxxxxxxxxxx'), stringtolob('yyyyyyyyyyyyyyyy'), stringtolob('zzzzzzzzzzzzzzzzzzzzzzzzzz'));
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));

create table tlob130ext2 (c1 int not null, c2 blob, c3 clob, c4 blob storage 'external', primary key (c1));
create table tlob130_not_external (c1 int not null , c2 blob, c3 blob, c4 blob, primary key (c1));
insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));
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 name(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'));
update tlob130ext set c4=stringtolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1;
update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt', append) where c1=1;
update tlob130ext set c3=externaltolob('hdfs:///user/trafodion/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:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));
insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),externaltolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'));
-- the next one should see error 8557
insert into tlob130ext values(1, stringtolob('first lob'), filetolob('hdfs:///user/trafodion/lobs/lob_input_a1.txt'),
externaltolob('hdfs:///user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.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:///user/trafodion/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:///user/trafodion/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;

--insert select from a source external lob column to a target external lob column
select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext;
insert into tlob130ext2 select * from tlob130ext;
select lobtostring(c2,50),lobtostring(c3,50),lobtostring(c3,50) from tlob130ext2;
----negative test
insert into tlob130_not_external select * from tlob130ext; 

?section update_lob_handle
-- test lob update via "update lob" command
log;
drop table t130lob5;
create table t130lob5 (c1 int not null, c2 blob , primary key (c1));
log LOG130;
delete from t130lob5;
insert into t130lob5 values (1,empty_blob());
sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130;
select lobtostring(c2,20) from t130lob5;
sh clitestdriver 7 < TEST130_argfile3 2>&1 | tee -a LOG130;
select lobtostring(c2,40) from t130lob5;
sh clitestdriver 8 < TEST130_argfile3 2>&1 | tee -a LOG130;
select lobtostring(c2,20) from t130lob5;
sh clitestdriver 6 < TEST130_argfile3 2>&1 | tee -a LOG130;
select lobtostring(c2,40) from t130lob5;
delete from t130lob5;


-- test_empty_blob() with external tables
delete from tlob130ext;
-- following empty_blob will be of type 'external'
insert into tlob130ext values(1,empty_blob(),empty_clob(),empty_blob());
select c2 from tlob130ext;
-- following should return error since only external lobs will be allowed
update tlob130ext set c4=stringtolob('gggg'); 
-- following should work. 
update tlob130ext set c4=externaltolob('hdfs:///user/trafodion/lobs/lob_input_b1.txt');
delete from tlob130ext;

-- test to ensure all lob dependent tables and schemas containing lob tables
-- get dropped cleanly.

?section lob_drop_table_schema
log;
cleanup schema trafodion.lobsch;
log LOG130;
create schema trafodion.lobsch;
set schema trafodion.lobsch;
create table tlob130ts1 (c1 int not null, c2 blob, primary key (c1));
create table tlob130ts2 (c1 int not null, c2 blob, primary key (c1));
drop table tlob130ts1;
get tables;
drop schema trafodion.lobsch cascade;
select OBJECT_NAME,OBJECT_TYPE from TRAFODION."_MD_".OBJECTS where catalog_name='TRAFODION' AND schema_name='LOBSCH';
--go back to the schema used for the rest of the tests.
set schema trafodion.lob130;


-- cleanup
?section lob_general_cleanup
log;
sh rm t130_*;
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/tlob130*
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_a1.txt
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_b1.txt
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_c1.txt
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_d1.txt
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/lobinput_e1.txt
sh regrhadoop.ksh fs -rm /user/trafodion/lobs/reallyLongDirectoryName0123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789/lob_input_a1012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890123456789.txt;
sh rm lobinput_*;
sh rm deep.jpg;
sh rm anoush.jpg;
drop table tlob130txt1; 
drop table tlob130bin1;
drop table tlob130txt_limit50;
drop table tlob130bin_limit1K;
sh rm TMP130;
sh rm tlob130txt1;

sh rm tlob130_txt*.txt;
sh rm tlob130_deep*.jpg;
sh rm tlob130_anoush*.jpg;
drop table tlob130txt3;
drop table tlob130bin3;
drop table tlob130bt;
drop table tlob130txt2;
drop table tlob130bin2; 
drop table tlob130ext;
drop table tlob130ext2;
sh rm TMP130;
sh rm tlob130txt2;
sh rm lobc2out.jpg;
sh rm lobc4ext.txt;
sh rm TEST130_argfile*;
drop table tlob130gc;
drop table tlob130gt;
drop table tlob130t5;
drop table tlob130_not_external;
drop external table if exists date_dim for hive.hive.date_dim;
drop schema trafodion.lob130 cascade;


exit;