SIMILAR TO can raise 'INVALID PATTERN' when 1) connection charset = UTF8 and console is narrow one (dos866 or win1251) and 2) running two separate statements without COMMIT between them [CORE4749]

[firebird-automations]

Submitted by: @pavel-zotov

OS: Windows XP

Console charset: DOS866 Database charset: NONE Connection charset: UTF8 (should be dos866 but was occasionally selected = UTF8)

TEST-1: verify results of old good `SIMILAR TO` rather than `SUBSTRING( ... SIMILAR ... )`:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql.exe localhost/3333:e30 -ch utf8 Database: localhost/3333:e30 SQL> select iif( 'aaxyaaxyaaaaxyaaaxyaa' similar to '%(aaxy|aaax){2,}%', 1, 0 ) is_matching from rdb$database;

IS_MATCHING

       1

SQL> select iif( 'eiavieieav' similar to '%(ie){2,}%', 1, 0 ) is_matching from rdb$database;

IS_MATCHING

       1

// OK

TEST-2: verify results of `SUBSTRING( ... SIMILAR ...)` with the same arguments:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql.exe localhost/3333:e30 -ch utf8 Database: localhost/3333:e30 SQL> select substring( 'aaxyaaxyaaaaxyaaaxyaa' similar '%\"(aaxy|aaax){2,}\"%' escape '\' ) from rdb$database;

SUBSTRING

aaxyaaxy

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

SUBSTRING

Statement failed, SQLSTATE = 42000 Invalid SIMILAR TO pattern

TEST-3: same as test-2 but note that between two statements `COMMIT;` present:

C:\MIX\firebird\QA\fbt-repo\tmp>C:\MIX\firebird\fb30\isql.exe localhost/3333:e30 -ch utf8 Database: localhost/3333:e30 SQL> select substring( 'aaxyaaxyaaaaxyaaaxyaa' similar '%\"(aaxy|aaax){2,}\"%' escape '\' ) from rdb$database;

SUBSTRING

aaxyaaxy

SQL> commit; ------------------------------------ <<<<<<<<<<< !! <<<<<<<<<<<<<< SQL> SQL> select substring( 'eiavieieav' similar '%\"(ie){2,}\"%' escape '\' ) from rdb$database;

SUBSTRING

ieie

No error.

Q-1: If test-2 failed because of mismatch charset of console and connection then message should be more relevant (may be 'malformed string') - ? Q-2: Why issuing of `COMMIT;` in test-3 solved this trouble ?

[firebird-automations]

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).

[firebird-automations]

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.

[firebird-automations]

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

[firebird-automations]

Commented by: @asfernandes

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

[firebird-automations]

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

[firebird-automations]

Commented by: @asfernandes

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

[firebird-automations]

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.