You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
There's also a related but very old bug (not a regression): expression index contains all NULLs if created as: computed (<computed column>), i.e. without any expression around the computed column (like CAST in this ticket). Now it's fixed as well.
Submitted by: Simeon Bodurov (simeon.bodurov)
Is related to QA616
Relate to CORE5118
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.
Commits: 6addf91 d772560 a212d55 1cb23c7 FirebirdSQL/fbt-repository@6a6c373 FirebirdSQL/fbt-repository@43b0dc8 FirebirdSQL/fbt-repository@55ebe0f FirebirdSQL/fbt-repository@86483ee
====== Test Details ======
Added 2nd COMPUTED_BY index WITHOUT any expression,
see dimitr's issue in the ticket, 02/Feb/15 08:52 AM.
See also several samples (rus):
http://sql.ru/forum/actualutils.aspx?action=gotomsg&tid=945713&msg=12655568
The text was updated successfully, but these errors were encountered: