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-issue-importer opened this issue Apr 8, 2015 · 15 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Apr 8, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 8, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 18, 2015

Commented by: @asfernandes

2.5 problem: It should be LOWER, not lower.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 18, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 18, 2015

Commented by: @asfernandes

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

About the wrong result, I'm investigating.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 19, 2015

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 20, 2015

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 21, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

Modified by: @pavel-zotov

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

resolution: Fixed [ 1 ] =>

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented May 30, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 21, 2015

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-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 21, 2015

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 30, 2015

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jun 30, 2015

Modified by: @pavel-zotov

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

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Jul 24, 2015

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