Bug: Reverse like-search with '%' not working [CORE5929]

[firebird-automations]

Submitted by: Rajko Thon (rthon)

Attachments:
k4-test.7z

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.
SELECT w.*, char_length(w.wort) len FROM sys_wort w WHERE wort LIKE 'heis%en%' OR wort_rev LIKE 'greb%sie%'

It gets three of the four rows by using indices UC1(WORT) and IX2(WORT_REV)

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.
SELECT * FROM sys_wort WHERE wort LIKE 'Heis%en%' OR wort LIKE reverse('%eis%berg')

So is reverse('%eis%berg') != 'greb%sie%' ?

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?

[firebird-automations]

Commented by: Rajko Thon (rthon)

Sample-Database to illustrate the behaviour

[firebird-automations]

Modified by: Rajko Thon (rthon)

Attachment: k4-test.7z [ 13303 ]

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: 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?

=>

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?

[firebird-automations]

Modified by: Sean Leyne (seanleyne)

description: 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?

=>

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.
SELECT w.*, char_length(w.wort) len FROM sys_wort w WHERE wort LIKE 'heis%en%' OR wort_rev LIKE 'greb%sie%'

It gets three of the four rows by using indices UC1(WORT) and IX2(WORT_REV)

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.
SELECT * FROM sys_wort WHERE wort LIKE 'Heis%en%' OR wort LIKE reverse('%eis%berg')

So is reverse('%eis%berg') != 'greb%sie%' ?

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?

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

The LIKE operand cannot use indexes for 'ending in' conditions ( '%berg' is ending in)

Further, indexes are not used for "not equal" (<> and !=) conditions.

[firebird-automations]

Commented by: @asfernandes

LIKE uses indices only when you pass a literal pattern. A variable or function call will not make it use the index.

What the engine does when it founds a X LIKE 'literal%...' is kind of transform it to (X STARTING WITH 'literal' AND X LIKE 'literal%...'), so the index (used in STARTING) works.

[firebird-automations]

Commented by: Sean Leyne (seanleyne)

@adriano,

If the reverse() function was defined as "deterministic" the engine would use an index for the query, correct?

[firebird-automations]

Commented by: @asfernandes

Not sure it's simple, as it may return a string starting with '%' with the original code does not make to use index.

So optimizer would need to call the function (at prepare time), and I think that is not an allowed thing.

[firebird-automations]

Commented by: Rajko Thon (rthon)

Thanks for your quick responses, guys!
That really helped.

So this is a behaviour, not a bug. I almost guessed it.
Considering the explanation of the workings of LIKE and STARTING I can rephrase
my query like that:

select *
from sys_wort
where wort like 'heis%en%'
or (
reverse(wort) starting reverse('berg')
and reverse(wort) like reverse('%eis%berg')
)

This variant finds everything and uses the indices on top.
I can work with that.

1) A notable observation. If the query is phrased as below using LIKEs only
it finds everything but NO index is used. Not even for the first condition, so
no transformation in this case.

select *
from sys_wort
where wort like 'heis%en%'
or reverse(wort) like reverse('%eis%berg')

2) A question @adriano: I guess STARTING will always use the index, even when
I put it inside a procedure and provide the search-strings as parameters?

Thanks for your time!