Bug: Reverse like-search with '%' not working
---------------------------------------------
Key: CORE-5929
URL: http://tracker.firebirdsql.org/browse/CORE-5929
Project: Firebird Core
Issue Type: Bug
Affects Versions: 3.0.2
Environment: Windows 64Bit
Reporter: Rajko Thon
BUG: Reverse like-search with '%' not working
I implemented a table containing words and tried to implement a kind of
full-text-search-solution similar to that of Jirí Cincura:
"https://www.tabsoverspaces.com/233577-poor-mans-full-text-using-psql-only-on-firebird";.
Unfortunately, there seems to be a bug if you search with like and '%'.
To illustrate the behaviour I created a small sample database.
One table: SYS_WORT.
Two important columns: WORT and WORT_REV, the latter one containing the
reversed word.
Indices on: Index
SYS_WORT.WORT UC1 unique constraint
SYS_WORT.WORT IX1 additional function based index on
REVERSE(WORT)
SYS_WORT.WORT_REV IX2 index
So the first query demonstrates the behaviour hoped for.
It gets three of the four rows by using indices UC1(WORT) and IX2(WORT_REV)
select w.*, char_length(w.wort) len from sys_wort w where wort like
'heis%en%' or wort_rev like 'greb%sie%'
Since a search-term usually has the letters in normal order, we reverse them.
But this one does not work as expected and does also not use any indices.
So is reverse('%eis%berg') != 'greb%sie%' ?
select * from sys_wort where wort like 'Heis%en%' or wort like
reverse('%eis%berg')
You can find endings of words using index IX2 although, without LIKE and any
'%', but with STARTING.
select * from sys_wort where wort_rev starting reverse('berg')
You can also find the same using the additional index IX1 on REVERSE(WORT).
select * from sys_wort where reverse(wort) starting reverse('berg')
But like again does not use the index IX1.
select * from sys_wort where reverse(wort) like reverse('%berg')
So it seems it is not possible to reverse search with like and '%' and
using the index at the same time?
Seems like an oversight to me.
Bug?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
