Issue Details (XML | Word | Printable)

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

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

Very poor performance of SIMILAR TO on some arguments + unable to disconnect via DELETE FROM MON$ATTACHMENTS

Created: 29/May/12 06:18 PM   Updated: 08/Sep/19 07:35 AM
Component/s: None
Affects Version/s: None
Fix Version/s: 4.0 Beta 2

Issue Links:
Relate
 

QA Status: Done successfully


 Description  « Hide
C:\MIX\firebird\fb25>bin\isql -n -c 2048 localhost/3050:C:\MIX\firebird\fb25\T1.FDB
Database: localhost/3050:C:\MIX\firebird\fb25\T1.FDB
SQL> show version;
ISQL Version: WI-V2.5.2.26482 Firebird 2.5
Server version:
Firebird/x86/Windows NT (access method), version "WI-V2.5.2.26482 Firebird 2.5"
Firebird/x86/Windows NT (remote server), version "WI-V2.5.2.26482 Firebird 2.5/tcp (csprog)/P12"
Firebird/x86/Windows NT (remote interface), version "WI-V2.5.2.26482 Firebird 2.5/tcp (csprog)/P12"
on disk structure version 11.2

SQL> select iif('sdf--asdf_sd.dsfsfh-.sdjskdjfh-_.' SIMILAR TO '(([[:ALNUM:]\_\-])+.?)+([[:ALNUM:]\_\-])+' escape '\', 1, 0) from rdb$database;

-- This query will run about 9 minutes.
Furthermore,
1) it can not be interrupted in another ('monitoring') windows if I issue there: DELETE FROM MON$ATTACHMENTS WHERE ...; COMMIT;
2) CPU is loaded about 100%
3) There is no ability to connect to any database on that machine untill this "strange query" will finish.


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Pavel Zotov added a comment - 27/Jun/18 07:08 AM
One more sample.

set list on;
set stat on;
 
select 1 from rdb$database where
'
group by
MATCODE,
MATNAME,
NOTE,
GROUPCODE,
STATE,
MATPROD,
MATFULL,
KOLVO,
EDIZM,
MATGRPCOD,
CODE,
TEXTTOHIST,
INV_ROOMDOC,
INV_ROOM,
PARENTCODE,
INVIS
'
    similar to
'%group[[:WHITESPACE:]]+by[[:WHITESPACE:]]+([[:ALNUM:]]|_)+([[:WHITESPACE:]]*,[[:WHITESPACE:]]*[[:ALNUM:]]+){12,}[[:WHITESPACE:]]*%'
;


NOTE on this part of pattern:
([[:ALNUM:]]|_)+

It is underscore character that is "reason" of very poor performance.
Yes, i understand that escape character should be placed leftside of "_"; but one may to forgot to do this and result will be very bad.