Computed index regression
-------------------------
Key: CORE-4674
URL: http://tracker.firebirdsql.org/browse/CORE-4674
Project: Firebird Core
Issue Type: Bug
Affects Versions: 2.5.3
Environment: Firebird 2.5.3 classic on Win 7 and Linux and Firebird
2.5.2 classic on Linux
Reporter: Simeon Bodurov
Hello,
We upgardeed to Firebird 2.5.3 several months ago, but today somebody have
noticed that one of our reports have stopped working. It uses computed by index
on computed by field. The sql query worked fine on Firebird 2.5.2, but now on
Firebird 2.5.3 does not work. Recreation of index does not fix the problem and
it is repeatable, so I created show case of this regression:
CREATE TABLE TheTable (
ID BIGINT NOT NULL,
Moment COMPUTED BY (CAST('20' || SUBSTRING(ID FROM 1 FOR 2) || '-' ||
SUBSTRING(ID FROM 3 FOR 2) || '-' ||
SUBSTRING(ID FROM 5 FOR 2) || ' ' ||
SUBSTRING(ID FROM 7 FOR 2) || ':' ||
SUBSTRING(ID FROM 9 FOR 2) || ':' ||
SUBSTRING(ID FROM 11 FOR 2) AS
TIMESTAMP)),
Moment_TS COMPUTED BY (CAST(Moment AS TIMESTAMP)),
CONSTRAINT PK_TheTable PRIMARY KEY (ID)
);
-- crate the computed by index
CREATE INDEX IDX_THETABLE_MOMENT_TS ON TheTable COMPUTED BY (CAST(Moment AS
TIMESTAMP));
-- insert some test values
INSERT INTO TheTable(ID) VALUES (150125103035);
INSERT INTO TheTable(ID) VALUES (150126050607);
INSERT INTO TheTable(ID) VALUES (150127045021);
-- use index in select
SELECT *
FROM TheTable
WHERE Moment_TS BETWEEN '26.01.2015' AND '26.01.2015 23:59:59';
-- with PLAN (THETABLE INDEX (IDX_THETABLE_MOMENT_TS))
-- does not return anything on Firebird 2.5.3 clasic
-- index has statistics = 1 on Firebird 2.5.3 clasic !!!!!!!!
-- returns 1 row on Firebird 2.5.2 clasic
-- index has statistics = 0.33333 on Firebird 2.5.2 clasic
SELECT *
FROM TheTable
WHERE Moment_TS BETWEEN '26.01.2015' AND '26.01.2015 23:59:59'
PLAN (THETABLE NATURAL);
-- return 1 row on Firebird 2.5.3
-- return 1 row on Firebird 2.5.2
It seems that index is somehow broken on Firebird 2.5.3
The only purpose of this construction is to have nice select SQL on column with
TIMESTAMP type.
The work around in our case is to use the fastest and simple select
SELECT *
FROM TheTable
WHERE ID BETWEEN 150126000000 AND 150126999999
But I think that this regression can affect something else so I have reported
it.
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
