DB: firebird 2.5.4
I have 1 table, 2 string fields and 1 computed field:
name varchar 256
extension varchar 4
filename computed by name||extension
I want to search a filename in this table (case insensisive)
The query is
from files f
where upper(f.filename) = upper('test.txt')
This is working of course, and to speed up the query, I created a computed index on files on
CREATE INDEX test ON FILES COMPUTED BY (upper(filename));
Now, the same query doesn't work anymore ! It returns nothing. I tried an index on lower, but it doesn't work either.
wtf? Did I miss an option somewhere?
Best How To :
It seems firebird doesn't support computed indexes on computed fields. I replaced the computed field 'filename' with the regular fields 'name||extension' in index. It fixes the problem:
CREATE INDEX test ON FILES COMPUTED BY (upper(name||extension));
Bug report found here.
After trying to spot the problem, it appears my database has internal errors, due to the migration from an earlier version. Mark Rotteveel points me to the release notes where the problem is described:
I extracted the metadata and rebuild the whole database. This fixes the problem. Note that a backup and restore should work as well.
Thank you Mark.