'Malformed string' or 'Cannot transliterate character between character sets' with non ASCII char

[Fab8573]

Hello,`

Issue: "Malformed String" with Non-ASCII Characters in Numeric or Timestamp Fields on Firebird Database

This issue occurs in any version of Firebird when using non-ASCII characters in a numeric or timestamp field.

It can be easily reproduced using the sample EMPLOYEE.FDB database :

SELECT * FROM EMPLOYEE r where hire_date like '%€%'

• If the database charset is NONE: The query results in a 'Malformed String' error.
• If the database charset is UTF8: The query results in an 'Arithmetic exception, numeric overflow, or string truncation: Cannot transliterate character between character sets' error.

The same problem occurs with any non-ASCII character for example '£', 'é' or '²' etc...

However, using a standard ASCII string works fine:

SELECT * FROM EMPLOYEE r where hire_date like '%ytrytrytyn$trbtrytr$$$ybrtjury%'
=> Work fine

[aafemt]

Database charset in this case is irrelevant. Strings are converted using connection charset. What was connection charset in your examples?

[Fab8573]

With default EMPLOYEE.FDB
if Connection Charset = NONE error is 'Malformed String'
if Connection Charset = UTF8 or Charset = WIN1252 error is 'Arithmetic exception, numeric overflow, or string truncation: Cannot transliterate character between character sets'

Same problem if EMPLOYEE.FDB is recreated with UTF8 or WIN1252 character sets. Error will be 'Arithmetic exception, numeric overflow, or string truncation: Cannot transliterate character between character sets'

Problem is : In any case Firebird try do an ASCII conversion for non alphanumeric fields comparison. Why ?

[madorin]

I looked into this on v5.0-release (5.0.5 debug build) and master.

Cause. For LIKE, CONTAINING, STARTING WITH and SIMILAR TO, ComparativeBoolNode::stringBoolean() takes the text type of the operation from the first operand. When that operand isn't a string (DATE, TIMESTAMP, INTEGER, NUMERIC, BOOLEAN, ...), v3.0 to v5.0 get it from INTL_TEXT_TYPE(), which returns ttype_ascii for non-text types. The pattern is then converted to ASCII, so any non-ASCII character fails before the matching starts:

• connection charset NONE: the NONE -> ASCII well-formedness check gives Malformed string;
• UTF8, WIN1252, ...: Cannot transliterate character between character sets.

As @aafemt said, the database charset doesn't matter: the literal has the connection charset. The text form of a date or a number is ASCII only, so the right answer here is simply FALSE.

Smaller repro, no table needed:

select count(*) from rdb$database where current_date like '%é%';
select count(*) from rdb$database where rdb$relation_id like '%é%';

Both fail on 3.0.14 and 5.0.x with any connection charset. 2.5 fails only with a charset other than NONE.

master already returns FALSE here. In c2413fb (CSetId/TTypeId refactoring) INTL_TEXT_TYPE(*desc1) became desc1->getTextType(), which returns CS_NONE for non-text types, so the pattern converts without an error. (DATE/TIME/TIMESTAMP/DOUBLE operands of these operators currently have another problem in master: #9186.)

PR #9184 backports that one-line change to v5.0-release. I checked it with LIKE, NOT LIKE, CONTAINING, STARTING WITH and SIMILAR TO on DATE, TIME, TIMESTAMP, INTEGER, NUMERIC, DOUBLE PRECISION, DECFLOAT, INT128, BOOLEAN and DB_KEY, with connection charsets NONE, UTF8 and WIN1252. Non-ASCII patterns now return FALSE (NOT LIKE returns TRUE), and ASCII patterns (d like '2024%', ts like '%10:00%', n like '3.1%', ...) give the same results as before. The commit also cherry-picks cleanly onto v4.0-release (not built there).

One thing the change doesn't cover: a multibyte ESCAPE character with a non-text operand (d like '2024é%' escape 'é' with UTF8) is still rejected, now as Invalid ESCAPE sequence, because the escape is converted to NONE. Before the change it failed with the transliteration error.

Workaround for older versions: cast the operand to a string first, e.g. cast(hire_date as varchar(30)) like '%€%'.