Using computed superscript 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 find the file name in this table (case insensitive)

Inquiry

 Select * from files f where upper(f.filename) = upper('test.txt') 

This works, of course, and to speed up the request, I created a computed index for files on upper(filename)

 CREATE INDEX test ON FILES COMPUTED BY (upper(filename)); 

Now the same query no longer works! It does not return anything. I tried the index below, but it does not work either.

WTF? Have I missed an option somewhere?

Answer 1

It seems that firebird does not support computed indexes in computed fields. I replaced the calculated "filename" field with the regular "name || extension" fields in the index. It fixes the problem:

 CREATE INDEX test ON FILES COMPUTED BY (upper(name||extension)); 

An error report is found here .

[EDIT]

After trying to detect the problem, my database has internal errors due to migration from an earlier version. Mark Rottwevel points out the release notes describing the issue:

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

I extracted the metadata and rebuilt the entire database. This fixes the problem. Please note that backup and restore should also work.

Thanks Mark.