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
Bug: Reverse like-search with '%' not working [CORE5929] #6186
Comments
Commented by: Rajko Thon (rthon) Sample-Database to illustrate the behaviour |
Modified by: Rajko Thon (rthon)Attachment: k4-test.7z [ 13303 ] |
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 To illustrate the behaviour I created a small sample database. 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%' Since a search-term usually has the letters in normal order, we reverse them. 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 => 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: To illustrate the behaviour I created a small sample database. One table: SYS_WORT. Indices on: 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%' Since a search-term usually has the letters in normal order, we reverse them. 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? |
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: To illustrate the behaviour I created a small sample database. One table: SYS_WORT. Indices on: 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%' Since a search-term usually has the letters in normal order, we reverse them. 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: To illustrate the behaviour I created a small sample database. One table: SYS_WORT. Indices on: 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) 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%' ? 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). But LIKE again does not use the index IX1. So it seems it is not possible to reverse search with LIKE and '%' and using the index at the same time? |
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. |
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. |
Commented by: Sean Leyne (seanleyne) If the reverse() function was defined as "deterministic" the engine would use an index for the query, correct? |
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. |
Commented by: Rajko Thon (rthon) Thanks for your quick responses, guys! So this is a behaviour, not a bug. I almost guessed it. select * This variant finds everything and uses the indices on top. 1) A notable observation. If the query is phrased as below using LIKEs only select * 2) A question @adriano: I guess STARTING will always use the index, even when Thanks for your time! |
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?
The text was updated successfully, but these errors were encountered: