shaoyong.li created TRAFODION-3099:
--------------------------------------
Summary: Prepare execute fails to handle N'<string>',
_iso88591'<string>', _utf8'<string>' for a nchar column
Key: TRAFODION-3099
URL: https://issues.apache.org/jira/browse/TRAFODION-3099
Project: Apache Trafodion
Issue Type: Bug
Reporter: shaoyong.li
Assignee: shaoyong.li
As shown below, for a nchar column c1 with a value of N'ABCD' in the table t, a
direct select from t with predicates c1=N'ABCD', c1='ABCD', c1=_iso88591'ABCD',
and c1=_utf8'ABCD' all are able to find the match. However, with a prepared
query xx executed using a parameter ? in sqlci and trafci operator have failed.
>From sqlci:
>>drop table if exists t;
--- SQL operation complete.
>>create table t (c1 nchar(10));
--- SQL operation complete.
>>insert into t values (N'ABCD');
--- 1 row(s) inserted.
>>select * from t;
C1
--------------------
ABCD
--- 1 row(s) selected.
>>select * from t where c1=N'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>select * from t where c1='ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>select * from t where c1=_iso88591'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>select * from t where c1=_utf8'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>
>>prepare xx from select * from t where c1=?;
--- SQL command prepared.
>>execute xx using N'ABCD';
--- 0 row(s) selected.
>>execute xx using 'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>execute xx using _iso88591'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
>>execute xx using _utf8'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
From trafci:
trafci sees even more failures:
$ trafci
Welcome to EsgynDB Command Interface
Copyright (c) 2015-2018 Esgyn Corporation
User Name: db__root
Host Name/IP Address: localhost:23400
Connected to EsgynDB Advanced
SQL>obey mytest.sql;
SQL>drop table if exists t;
--- SQL operation complete.
SQL>create table t (c1 nchar(10));
--- SQL operation complete.
SQL>insert into t values (N'ABCD');
--- 1 row(s) inserted.
SQL>select * from t;
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>select * from t where c1=N'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>select * from t where c1='ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>select * from t where c1=_iso88591'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>select * from t where c1=_utf8'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>prepare xx from select * from t where c1=?;
--- SQL command prepared.
SQL>execute xx using N'ABCD';
--- 0 row(s) selected.
SQL>execute xx using 'ABCD';
C1
--------------------
ABCD
--- 1 row(s) selected.
SQL>execute xx using _iso88591'ABCD';
*** ERROR[29183] Invalid Parameter Value: CHAR input data is longer than the
length for column: 0
SQL>execute xx using _utf8'ABCD';
*** ERROR[29183] Invalid Parameter Value: CHAR input data is longer than the
length for column: 0
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
