[TRAFODION-3099] Prepare execute fails to handle N'<string>', _iso88591'<string>', _utf8'<string>' for a nchar column

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Assignee: shaoyong.li
• Reporter: shaoyong.li
• Created: 07/Jun/18 08:02
• Updated: 28/Jun/18 16:13

Description

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

 

Issue Links

• links to: GitHub Pull Request #1623