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

Changing data that affects an expression index that contains references to null date fields fails [CORE2833] #3219

Closed
firebird-automations opened this issue Jan 28, 2010 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Frank Schlottmann-Goedde (fsg)

Changing data that affects an expression index that contains references to null date fields fails in Firebird 2.5
Here is an isql script to show the effect:
-------
create database 'bugtest.fdb';
show version;
CREATE GENERATOR GEN_POLICEN_ORDER_ID;
CREATE TABLE POLICEN_ORDER (
ID INTEGER NOT NULL,
VSTATUS INTEGER,
ABLAUF DATE,
VSTORNO INTEGER,
STORNO DATE
);
ALTER TABLE POLICEN_ORDER ADD CONSTRAINT PK_NEW_TABLE PRIMARY KEY (ID);

SET TERM ^ ;
CREATE OR ALTER TRIGGER POLICEN_ORDER_BI FOR POLICEN_ORDER
ACTIVE BEFORE INSERT POSITION 0
as
begin
if (http://new.id is null) then
http://new.id = gen_id(gen_policen_order_id,1);
end
^
SET TERM ; ^

-- insert some data with null dates

INSERT INTO POLICEN_ORDER (ID, VSTATUS, ABLAUF, VSTORNO, STORNO)
VALUES (2, 1, NULL, NULL, NULL);
INSERT INTO POLICEN_ORDER (ID, VSTATUS, ABLAUF, VSTORNO, STORNO)
VALUES (3, 2, NULL, NULL, NULL);
INSERT INTO POLICEN_ORDER (ID, VSTATUS, ABLAUF, VSTORNO, STORNO)
VALUES (4, 3, NULL, NULL, NULL);

COMMIT;

-- now let's create an obscure index

CREATE INDEX IDX_POLICEN_ORDER_BIT_VSTATUS ON POLICEN_ORDER COMPUTED BY (case
when POLICEN_ORDER.VSTATUS=1 then 64 -- Anbahnung
when POLICEN_ORDER.VSTATUS=7 then 32 -- DA
when POLICEN_ORDER.VSTATUS=8 then 16 -- Angebot
when POLICEN_ORDER.VSTATUS=0 then 8 -- Vertrag
when POLICEN_ORDER.VSTATUS=3 then 8 -- Vertrag
when POLICEN_ORDER.VSTATUS=4 then 8 -- Vertrag
when POLICEN_ORDER.VSTATUS=5 then 8 -- Vertrag
when POLICEN_ORDER.VSTATUS=6 then 8 -- Vertrag
when POLICEN_ORDER.VSTATUS=2 then 4 -- Fremdvertrag
else 0
end +
case when coalesce(POLICEN_ORDER.ABLAUF, current_date+1)<=current_date or
POLICEN_ORDER.VSTORNO=1 and coalesce(POLICEN_ORDER.STORNO,
current_date+1)<=current_date then 2 else 0 end);
-- and do some work with the data that will fail miserably in 2.5

update POLICEN_ORDER
set VSTATUS = 8
where (ID = 2);
commit;

-- this works in 2.0

select * from policen_order;

update POLICEN_ORDER
set VSTATUS = 2
where (ID = 2);
commit;

exit;
--------

Here is what happens in 2.0:

-------
....
update POLICEN_ORDER
set VSTATUS = 8
where (ID = 2);
commit;

-- this works in 2.0

select * from policen_order;

      ID      VSTATUS      ABLAUF      VSTORNO      STORNO 

============ ============ =========== ============ ===========
2 8 <null> <null> <null>
3 2 <null> <null> <null>
4 3 <null> <null> <null>
-----

and this is from 2.5

------
.....
update POLICEN_ORDER
set VSTATUS = 8
where (ID = 2);
Statement failed, SQLSTATE = 22008
value exceeds the range for valid dates
After line 52 in file bugtest.sql
commit;

-- this works in 2.1

select * from policen_order;

      ID      VSTATUS      ABLAUF      VSTORNO      STORNO 

============ ============ =========== ============ ===========
2 1 <null> <null> <null>
3 2 <null> <null> <null>
4 3 <null> <null> <null>

update POLICEN_ORDER
set VSTATUS = 2
where (ID = 2);
Statement failed, SQLSTATE = 22008
value exceeds the range for valid dates
After line 62 in file bugtest.sql
commit;

exit;

hth

fsg

Commits: 42fbd26 7522c38 767a819

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

In RC2 even the creation of the index fails with
Statement failed, SQLSTATE = 08006
connection lost to database

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

This bug stops our efforts to test FB2.5 in semi-production environments.

@firebird-automations
Copy link
Collaborator Author

Modified by: Frank Schlottmann-Goedde (fsg)

priority: Major [ 3 ] => Blocker [ 1 ]

Version: 2.5 RC2 [ 10372 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

How is this index expected to work, provided that is uses CURRENT_DATE? Are you going to compare date of the index creation with the runtime date in a hope to find a match? ;-)

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

No idea :-)

I will forward your question to the original developer.

Apparently this thing worked as expected in FB 2.0

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

and anyway, the server shouldn't die if someone tries to create a strange index.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Sure, and I hope to have a fix by tomorrow. This just looks like using RANDOM in the index, i.e. it could return unexpected results under some conditions.

@firebird-automations
Copy link
Collaborator Author

Commented by: Frank Schlottmann-Goedde (fsg)

As the databases in question go through a nightly backup and restore cycle, the index usually is correct for the actual day.

the last part calculates whether a given policy is still active and sets the corresponding bit in the bitmap.

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

status: In Progress [ 3 ] => Open [ 1 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 Initial [ 10301 ]

Version: 2.1.3 [ 10302 ]

Version: 2.5 Beta 2 [ 10300 ]

Version: 2.5 Beta 1 [ 10251 ]

Version: 2.1.2 [ 10270 ]

Version: 2.1.1 [ 10223 ]

Version: 2.5 Alpha 1 [ 10224 ]

Version: 2.1.0 [ 10041 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

summary: Changing data that affects an expression index that contains references to null date fields fails in Firebird 2.5 => Changing data that affects an expression index that contains references to null date fields fails

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5 RC3 [ 10381 ]

Fix Version: 2.1.4 [ 10361 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Closed [ 6 ] => Closed [ 6 ]

QA Status: Done successfully

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