Issue Details (XML | Word | Printable)

Key: CORE-4740
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Pavel Zotov
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

SIMILAR TO with quantifier {n,} in the pattern: 1) fails on 2.5 ("Invalid pattern"), 2) strange result in 3.0

Created: 08/Apr/15 05:30 PM   Updated: 24/Jul/15 05:17 PM
Component/s: None
Affects Version/s: None
Fix Version/s: 3.0 Beta 2

QA Status: Done successfully
Test Details: See also CORE-3754, CORE-2006


 Description  « Hide
case-1: select iif( 'abcZ' similar to '[[:lower:]]{1,}Z', 1, 0) result from rdb$database; -- NB: letter 'Z' - in upper case
case-2: select iif( 'abcz' similar to '[[:lower:]]{1,}z', 1, 0) result from rdb$database; -- NB: letter 'Z' - in lower case, as all preceding ones

Result in WI-V2.5.4.26857 for both cases:
===
Statement failed, SQLSTATE = 42000
Invalid SIMILAR TO pattern
===


Result in WI-T3.0.0.31780:

SQL> select iif( 'abcZ' similar to '[[:lower:]]{1,}Z', 1, 0) result from rdb$database;

      RESULT
============
           1

SQL> select iif( 'abcz' similar to '[[:lower:]]{1,}z', 1, 0) result from rdb$database;

      RESULT
============
           0

Why second matching fails if we have three letters in lowercase left side from 'z' ?

If SIMILAR-engine count characters upto final one ('z') when it encounteres {1,} than why it does NOT so in the following cases (and these are also seems "invalid" for 2.5 as above):

SQL> select iif( 'abcz' similar to '[[:lower:]]*z', 1, 0) result from rdb$database;

      RESULT
============
           1

SQL> select iif( 'abcz' similar to '[[:lower:]]+z', 1, 0) result from rdb$database;

      RESULT
============
           1

-- ?


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 08/Apr/15 05:48 PM
It seems that something is broken now in WI-V2.5.4.26857.

These is examples from doc ( http://www.firebirdsql.org/refdocs/langrefupd25-similar-to.html ):

SQL> select iif( 'Erdbeere' similar to '[[:ALPHA:]]', 1, 0 ) r from rdb$database; -- OK

           R
============
           0


-- The following FAILS, but character classes must be case-insensitive since fixed CORE-2952,
-- and corresponding QA-test currently passed OK:

SQL> select iif( 'Erdbeere' similar to '[[:alpha:]]', 1, 0 ) r from rdb$database;

           R
============
Statement failed, SQLSTATE = 42000
Invalid SIMILAR TO pattern

-----------------------------------------------------------------------------------------------------------

-- This also FAILS despite that http://www.firebirdsql.org/refdocs/langrefupd25-similar-to.html DOES contain sample
-- where character class is used WITHOUT apostrophes (though there is NO such example in the doc\sql.extensions\README.similar_to.txt ):

SQL> select iif( 'Erdbeere' similar to [[:ALPHA:]], 1, 0 ) r from rdb$database;
Statement failed, SQLSTATE = 42000
Dynamic SQL Error
-SQL error code = -104
-Token unknown - line 1, column 35
-[

Adriano dos Santos Fernandes added a comment - 18/May/15 03:19 PM
2.5 problem: It should be LOWER, not lower.

Pavel Zotov added a comment - 18/May/15 03:43 PM - edited
You're right, it was my mistake applying pattern in lower-case for 2.5.

But what about 3.0 (tested on WI-T3.0.0.31840 and LI-T3.0.0.31827) ?

select iif( 'abcz' similar to '[[:LOWER:]]{1,}z', 1, 0) result from rdb$database; -- NB: letter 'Z' - in lower case, as all preceding ones

      RESULT
============
           0

select iif( 'abcz' similar to '[[:lower:]]{1,}z', 1, 0) result from rdb$database; -- NB: letter 'Z' - in lower case, as all preceding ones

      RESULT
============
           0

FB 2.5 returns 1 for the first statement (and 'invalid pattern' for 2nd but it must be so).

Adriano dos Santos Fernandes added a comment - 18/May/15 03:52 PM
For uppercase/lowercase, please search the bug tracker. Hint: CORE-2952

About the wrong result, I'm investigating.

Pavel Zotov added a comment - 21/May/15 12:02 PM - edited
It seems that fix ( http://sourceforge.net/p/firebird/code/61585 ) isn`t completed:

set list on;
select iif('aggagg' similar to '([[:LOWER:]]{1}gg){2,}', 1, 0) result from rdb$database;


On WI-V2.5.5.26871: RESULT 1

On WI-T3.0.0.31844: RESULT 0


PS. And this:

select iif('aggagg' similar to '([[:LOWER:]]{1,}gg){2,}', 1, 0) result from rdb$database;

-- works OK on 3.0

The only difference is in the comma inside quantifier after [[:LOWER:]] class:
was: {1}
now: {1,}

But text 'agg' has EXACT ONE character leftside of 'gg', so why we can`t specify this by {1} -- i.e. *without* comma ?

Pavel Zotov added a comment - 30/May/15 11:35 AM
Ticket should be reopened. Following query:
===
with
d(txt,ptn) as(
  select
    'aggagg', '(a{1}gg){2}' from rdb$database union all select
    'aggagg', '(a{0,}gg){2}' from rdb$database union all select
    'aggagg', '(a{1}__){2}' from rdb$database union all select
    'aggagg', '(a{1}__){1,}' from rdb$database union all select
    'aggagg', '(a{1}[b-z]{2}){2}' from rdb$database union all select
    'XabaXa', '([X](a|b){1,3}){2}' from rdb$database union all select
    'XabaXaba', '([X](a|b){3}){2}' from rdb$database union all select
    'XabaX', '([X](a|b){0,3}){1,}' from rdb$database union all select
    'XabaX', '([X](a|b){0,3}){2}' from rdb$database union all select
    'XaX', '([X](a){0,1}){2}' from rdb$database union all select
    'XaXa', '([X](a){1}){2}' from rdb$database union all select
    'XaXa', '([X]a{1}){2}' from rdb$database
)
select txt, ptn, case when trim(txt) similar to trim(ptn) then 1 else 0 end is_match
from d;
===

-- shows totally different results in:

1. FB2.5:
========
TXT PTN IS_MATCH
aggagg (a{1}gg){2} 1
aggagg (a{0,}gg){2} 1
aggagg (a{1}__){2} 1
aggagg (a{1}__){1,} 1
aggagg (a{1}[b-z]{2}){2} 1
XabaXa ([X](a|b){1,3}){2} 0
XabaXaba ([X](a|b){3}){2} 1
XabaX ([X](a|b){0,3}){1,} 0
XabaX ([X](a|b){0,3}){2} 0
XaX ([X](a){0,1}){2} 0
XaXa ([X](a){1}){2} 1
XaXa ([X]a{1}){2} 1

2. FB 3.0:
=======
TXT PTN IS_MATCH
aggagg (a{1}gg){2} 0
aggagg (a{0,}gg){2} 0
aggagg (a{1}__){2} 0
aggagg (a{1}__){1,} 1
aggagg (a{1}[b-z]{2}){2} 0
XabaXa ([X](a|b){1,3}){2} 0
XabaXaba ([X](a|b){3}){2} 0
XabaX ([X](a|b){0,3}){1,} 0
XabaX ([X](a|b){0,3}){2} 0
XaX ([X](a){0,1}){2} 0
XaXa ([X](a){1}){2} 0
XaXa ([X]a{1}){2} 0

3. Postgresql 9.4.2:
================
TXT PTN IS_MATCH_PG_94
aggagg (a{1}gg){2} 1
aggagg (a{0,}gg){2} 1
aggagg (a{1}__){2} 1
aggagg (a{1}__){1,} 1
aggagg (a{1}[b-z]{2}){2} 1
XabaXa ([X](a|b){1,3}){2} 1
XabaXaba ([X](a|b){3}){2} 1
XabaX ([X](a|b){0,3}){1,} 1
XabaX ([X](a|b){0,3}){2} 1
XaX ([X](a){0,1}){2} 1
XaXa ([X](a){1}){2} 1
XaXa ([X]a{1}){2} 1


Pavel Zotov added a comment - 30/May/15 12:23 PM
PS.

select iif('aggagg' similar to '(a{0,}%){2}', 1, 0) result from rdb$database;

-- leads to (both in FB 2.5 & 3.0):
===
Invalid String.
Invalid SIMILAR TO pattern.
===

But runs OK in PG 9.4.2 and returns 1.

Adriano dos Santos Fernandes added a comment - 21/Jun/15 03:57 PM
select iif('aggagg' similar to '(a{0,}%){2}', 1, 0) result from rdb$database;

is not supported by our engine and will not be fixed in this ticket.

The others cases are fixed.