[
https://issues.apache.org/jira/browse/TRAFODION-2832?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16282594#comment-16282594
]
Sandhya Sundaresan commented on TRAFODION-2832:
-----------------------------------------------
The hang happens during prepare at compile time.
The cause for the hang is that the check for the multiple tuple list was in an
endless loop since it expected a LOB function operator directly i the tuple
list and didn't hndle any other cases. Now that we allow plain char/varchar
types in the values clause and convert them in the binder phase internally,
this check had to be changed to be don e after the assign bind 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)
