[
https://issues.apache.org/jira/browse/TRAFODION-2832?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16324358#comment-16324358
]
Sandhya Sundaresan commented on TRAFODION-2832:
-----------------------------------------------
Adding another fix to this. In case of certain plans wehre the binder didn't
get a chance to bind the tuple list values, the check added in binder didn't
work. We need an additional check in the generator where we bind the Assign
nodes again.
The case that didn't work :
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');
So it happens if there is a syskey and a lob column. The binding of the assign
nodes gets pushed to the codegen phase.
> LOB: Insert hangs with a mutli-row insertion that requires internal
> stringtolob() conversion
> --------------------------------------------------------------------------------------------
>
> Key: TRAFODION-2832
> URL: https://issues.apache.org/jira/browse/TRAFODION-2832
> Project: Apache Trafodion
> Issue Type: Bug
> Reporter: Sandhya Sundaresan
> Assignee: Sandhya Sundaresan
>
> 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;
--
This message was sent by Atlassian JIRA
(v6.4.14#64029)
