`SIMILAR TO` with constant pattern using ‘|’, ‘*’, ‘?’ or ‘{0,N}’ doesn't work as expected

[eFKaey]

We want to migrate from Firebird 2.5 to a higher version. During my compatibility tests with Firebird 5.0, I encountered a SELECT query that produced different results compared to earlier versions (2.5 / 4.0). To pinpoint the discrepancies, I created small test databases for each Firebird version.

A SELECT query using LIKE yields consistent results across versions:

SELECT a.TESTCOLUMN1, a.ID
FROM TESTTABLE a
WHERE a.TESTCOLUMN1 LIKE '7264%'

Results:

FB 2.5 / 4.0	FB 5.0
72644	72644
72649	72649

However, when using SIMILAR TO, the results differ:

SELECT a.TESTCOLUMN1, a.ID
FROM TESTTABLE a
WHERE a.TESTCOLUMN1 SIMILAR TO '72649|72644'

Results:

FB 2.5 / 4.0	FB 5.0
72644	72649
72649

It appears that in Firebird 5.0, a SELECT query with SIMILAR TO only considers the first search parameter:

SELECT a.TESTCOLUMN1, a.ID
FROM TESTTABLE a
WHERE a.TESTCOLUMN1 SIMILAR TO '72644|72649'

Results:

FB 2.5 / 4.0	FB 5.0
72644	72644
72649

[asfernandes]

This was being caused by wrong optimization implemented in https://github.com/FirebirdSQL/firebird/issues/6873 It's caused not only with |, but also with *, ? and {0,N}.