You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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:
SELECTa.TESTCOLUMN1, a.IDFROM TESTTABLE a
WHEREa.TESTCOLUMN1LIKE'7264%'
Results:
FB 2.5 / 4.0
FB 5.0
72644
72644
72649
72649
However, when using SIMILAR TO, the results differ:
SELECTa.TESTCOLUMN1, a.IDFROM TESTTABLE a
WHEREa.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:
SELECTa.TESTCOLUMN1, a.IDFROM TESTTABLE a
WHEREa.TESTCOLUMN1 SIMILAR TO '72644|72649'
Results:
FB 2.5 / 4.0
FB 5.0
72644
72644
72649
The text was updated successfully, but these errors were encountered:
This was being caused by wrong optimization implemented in #6873
It's caused not only with |, but also with *, ? and {0,N}.
asfernandes
changed the title
FB 5.0: SIMILAR TO with the OR operator ‘|’ doesn't work as expectedSIMILAR TO with constant pattern using ‘|’, ‘*’, ‘?’ or ‘{0,N}’ doesn't work as expected
Apr 12, 2024
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 usingLIKE
yields consistent results across versions:Results:
However, when using
SIMILAR TO
, the results differ:Results:
It appears that in Firebird 5.0, a
SELECT
query withSIMILAR TO
only considers the first search parameter:Results:
The text was updated successfully, but these errors were encountered: