Using computed upper index in firebird to compare case insensitive strings

Question

DB: firebird 2.5.4

I have 1 table, 2 string fields and 1 computed field:

Files
 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

Select * 
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 upper(filename)

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?

Answer 1

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.

[EDIT]

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:

http://www.firebirdsql.org/file/documentation/release_notes/html/rlsnotes254.html#notes-253

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.