Commented by: Sean Leyne (seanleyne)

There are several aspects of this case which trouble me:

1- The OS is XP. I realize that this OS is commonly used in some markets, but IMO the project should only be working on/testing against OS which are still supported by MS. Would like to see this case reproduced with later OS.

2- It seems inconsistent for us to be concerned about such an extreme example where their is no attempt at consistent charset environment (i.e. the charset most have some consistency).

Commented by: @asfernandes

Pavel, please replace your two:

select substring( 'eiavieieav' similar '%\"(ie){2,}\"%' escape '\' ) from rdb$database;

by:

select cast('eiavieieav' as varchar(10) character set octets), cast('%\"(ie){2,}\"%' as varchar(14) character set octets) from rdb$database;

And put here the results.

Commented by: @pavel-zotov

Console charset = win1251.

I do the following:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql.exe localhost/3330:e30 -ch utf8
Database: localhost/3330:e30
SQL> select cast('eiavieieav' as varchar(10) character set octets), cast('%\"(ie){2,}\"%' as varchar(14) character set octets) from rdb
$database;

CAST CAST
==================== ============================
65696176696569656176 255C22286965297B322C7D5C2225

-- PS. Check database default charset:
SQL> set list on;
SQL> select RDB$CHARACTER_SET_NAME from rdb$database;

RDB$CHARACTER_SET_NAME UTF8

Commented by: @asfernandes

Same as for me. And I cannot reproduce the "Invalid SIMILAR TO pattern" error.

Commented by: @pavel-zotov

> I cannot reproduce the "Invalid SIMILAR TO pattern" error.

I still can, do it running TEST-2 from start post, and also trying like this:

C:\MIX\firebird\QA\fbt-repo\tmp>isql E30.fdb -n -ch utf8
Database: E30.fdb
SQL> select substring( cast('aaxyaaxyaaaaxyaaaxyaa' as varchar(50) character set octets) similar '%\"(aaxy|aaax){2,}\"%' escape '\' )
from rdb$database;

SUBSTRING

Statement failed, SQLSTATE = 42000
Invalid SIMILAR TO pattern

SQL> select substring( cast('aaxyaaxyaaaaxyaaaxyaa' as varchar(50) character set octets) similar cast('%\"(aaxy|aaax){2,}\"%' as varch
ar(50) character set octets) escape '\' ) from rdb$database;

SUBSTRING

Statement failed, SQLSTATE = 42000
Invalid SIMILAR TO pattern

Commented by: @asfernandes

What if you save this command to a file encoded in DOS866 and load it with "input" in isql?

Commented by: @pavel-zotov

Adriano,

I've done following:

CHECK-1.

1) created SQL script "tmp-c4749-dos866.sql" with following lines:

-- set names dos866; -- works FINE
-- set names utf8; -- leads to "Invalid SIMILAR TO pattern"
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\tmp-c4749-dos866.fdb' default character set utf8;
commit;
select substring( 'aaxyaaxyaaaaxyaaaxyaa' similar '%\"(aaxy|aaax){2,}\"%' escape '\' ) from rdb$database;
select substring( 'eiavieieav' similar '%\"(ie){2,}\"%' escape '\' ) from rdb$database;

2) run cmd.exe and verified result of CHCP command - it is 866 on my machine.

3.1) opened file tmp-c4749-dos866.sql and UNCOMMENTED line #⁠1: `set names dos866;`
3.2) run in cmd.exe:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql -q -i tmp-c4749-dos866.sql

Result: works fine.

Output:
SUBSTRING

aaxyaaxy

SUBSTRING

ieie

4.1) opened file tmp-c4749-dos866.sql, restored comment to line #⁠1 and UNCOMMENTED line #⁠2: `set names utf8;`
4.2) repeated ISQL with this file - it FAILS:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql -q -i tmp-c4749-dos866.sql

SUBSTRING

aaxyaaxy

SUBSTRING

Statement failed, SQLSTATE = 42000
Invalid SIMILAR TO pattern
After line 4 in file tmp-c4749-dos866.sql

CHECK-2.

1) created SQL script "tmp-c4749-dos866.sql" with following lines:

-- set names dos866; -- works FINE
-- set names utf8; -- leads to "Invalid SIMILAR TO pattern"
create database 'localhost/3333:C:\MIX\firebird\QA\fbt-repo\tmp\tmp-c4749-dos866.fdb' default character set utf8;
commit;

select
substring( cast('aaxyaaxyaaaaxyaaaxyaa' as varchar(50) character set octets)
similar
'%\"(aaxy|aaax){2,}\"%' escape '\'
)
from rdb$database;

select
substring(
cast('aaxyaaxyaaaaxyaaaxyaa' as varchar(50) character set octets)
similar
cast('%\"(aaxy|aaax){2,}\"%' as varchar(50) character set octets) escape '\'
)
from rdb$database;

Then I've repeated steps 2..4 from previous check.

Result: BOTH character set specified in SET NAMES statement failed - not only UTF8 but also DOS866.