Issue Details (XML | Word | Printable)

Key: CORE-4673
Type: Bug Bug
Status: Resolved Resolved
Resolution: Fixed
Priority: Major Major
Assignee: Dmitry Yemanov
Reporter: Simeon Bodurov
Votes: 0
Watchers: 3
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Regression: computed index based on a computed column stores NULL for all its keys

Created: 28/Jan/15 12:49 PM   Updated: 29/Feb/16 01:31 PM
Component/s: Engine
Affects Version/s: 3.0 Alpha 1, 3.0 Alpha 2, 2.5.3, 3.0 Beta 1, 2.5.3 Update 1
Fix Version/s: 2.5.4, 3.0 Beta 2

Environment: Firebird 2.5.3 classic on Win 7 and Linux and Firebird 2.5.2 classic on Linux
Issue Links:
Relate

QA Status: Done successfully
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):
sql.ru/forum/actualutils.aspx?action=gotomsg&tid=945713&msg=12655568


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Dmitry Yemanov added a comment - 02/Feb/15 08:52 AM
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.