Friday, November 3, 2017, 3:12:37 AM, Rustam wrote:

> I got an error executing SELECT query with LIKE predicate in case of UTF8 database,
> non-UTF8 connection charset and national characters in LIKE
> argument without mask symbols '%' and '_'

> Error is

> "arithmetic exception, numeric overflow, or string truncation
> -string right truncation"

> 1) Create database with default character set = 'UTF8'
> 2) Connect to DB with ANSI character set, e.g. 'WIN1251'


> isql -ch WIN1251

> 3) Execute SELECT query on any table with VARCHAR fields of any length
> with LIKE predicate on VARCHAR field, that meet following criterias:
> - argument not contain mask symbols '%' and '_'
> - argument length greater then 1 symbol
> - argument contain national symbols

> EXAMPLE:

> Change console window font to Lucida Console (to allow cp1251 characters in console)

> Start ISQL:
> isql -ch WIN1251

> create database 'c:\databases\bug.fdb' user 'SYSDBA' password 'masterkey'
> default character set UTF8 collation UNICODE_CI_AI;


> create table TABLE1 (
> FIELD1 varchar(50)
>
> insert into TABLE1 (FIELD1) values ('абвгде'); -- WIN1251 characters string

'абвгде' is transliterated to charset UTF8.

> This queries work fine:

> select * from TABLE1 where FIELD1 like 'abc%';
> select * from TABLE1 where FIELD1 like 'abc';
> select * from TABLE1 where FIELD1 like 'абв%';

As expected. Client's WIN1251 input is transliterated to UTF8.

> This query fails:

> select * from TABLE1 where FIELD1 like 'абв';

The "bug" here is that the message for all string input errors is the
same. Transliteration of the input argument in the failing query
would occur as in your third query above if the LIKE argument was
valid. IMHO, it should give an invalid DSQL error, not that generic
string error.

> In last 2 queries in LIKE argument used string with national
> characters (in this case - WIN1251)

The difference between the two results confirms that transliteration
is healthy when you provide a valid argument for LIKE, i.e., provide a
wildcard character.

If you wanted to pass a query like that, you should use STARTING
[WITH]:

select * from TABLE1 where FIELD1 STARTING WITH 'абв';

Helen