Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
firebird-automations opened this issue Apr 8, 2015 · 15 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

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

-- ?

Commits: c8e7c92 003be80 FirebirdSQL/fbt-repository@8818460 FirebirdSQL/fbt-repository@d0c123a

====== Test Details ======

See also CORE3754, CORE2006

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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 CORE2952,
-- and corresponding QAtest 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
-[

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

2.5 problem: It should be LOWER, not lower.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

For uppercase/lowercase, please search the bug tracker. Hint: CORE2952

About the wrong result, I'm investigating.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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 ?

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Reopened [ 4 ]

resolution: Fixed [ 1 ] =>

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @asfernandes

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

status: Reopened [ 4 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

Test Details: See also CORE3754, CORE2006

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants