Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Regression: computed index based on a computed column stores NULL for all its keys [CORE4673] #4983

Closed
firebird-automations opened this issue Jan 28, 2015 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Beta 1 [ 10332 ]

Version: 3.0 Alpha 2 [ 10560 ]

Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 2.5.3 Update 1 [ 10650 ]

Component: Engine [ 10000 ]

summary: Computed index regression => Regression: computed index based on a computed column stores NULL for all its keys

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 2.5.4 [ 10585 ]

Fix Version: 3.0 Beta 2 [ 10586 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

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.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA616 [ QA616 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Link: This issue relate to CORE5118 [ CORE5118 ]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment