[TRAFODION-2832] LOB: Insert hangs with a mutli-row insertion that requires internal stringtolob() conversion

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Assignee: Sandhya Sundaresan
• Reporter: Sandhya Sundaresan
• Created: 07/Dec/17 21:49
• Updated: 05/Sep/18 21:40
• Resolved: 27/Mar/18 17:39

Description

The LOB feature allows the user to specify a char string as the insert value. EsgynDB will automatically convert it into a LOB datatype, just as if the user has specified stringtolob() explicitly. The following execution output shows that both insert..values('1') and insert..values(stringtolob('2')) work fine.

The design has prohibited the user from using stringtolob() to insert multiple rows at the same time. It's a 4483 error as shown by insert..values (stringtolob('3')),(stringtolob('4')). However, if the user doesn't specify stringtolob(), the multi-row insertion insert..values('5'),('6') doesn't return such an error. It simply hangs, presumably because it tries to invoke stringtolob() underneath and has run into the situation that the 4483 error is designed to prevent. It probably needs to return the same error to avoid such a hang problem.
>>cqd TRAF_BLOB_AS_VARCHAR 'OFF';

— SQL operation complete.
>>cqd TRAF_CLOB_AS_VARCHAR 'OFF';

— SQL operation complete.
>>
>>drop table if exists t cascade;

— SQL operation complete.
>>
>>create table t (a clob);

— SQL operation complete.
>>insert into t values ('1');

— 1 row(s) inserted.
>>insert into t values (stringtolob('2'));

— 1 row(s) inserted.
>>select cast(lobtostring(a) as char(5)) from t;

(EXPR)
------

1
2

— 2 row(s) selected.
>>insert into t values (stringtolob('3')),(stringtolob('4'));

• • • ERROR[4483] This LOB conversion function is not allowed in the VALUES clause with multiple input value rows. Use it with a single value row.

• • • ERROR[8822] The statement was not prepared.
      >>insert into t values ('5'),('6');

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

To reproduce :
cqd TRAF_BLOB_AS_VARCHAR 'OFF';
cqd TRAF_CLOB_AS_VARCHAR 'OFF';

drop table if exists t cascade;

create table t (a clob);
insert into t values ('1');
insert into t values (stringtolob('2'));
select cast(lobtostring(a) as char(5)) from t;
insert into t values (stringtolob('3')),(stringtolob('4'));
insert into t values ('5'),('6');

drop table t cascade;

Issue Links

• links to: GitHub Pull Request #1330