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
Comments
Commented by: Frank Schlottmann-Goedde (fsg) In RC2 even the creation of the index fails with |
Commented by: Frank Schlottmann-Goedde (fsg) This bug stops our efforts to test FB2.5 in semi-production environments. |
Modified by: Frank Schlottmann-Goedde (fsg)priority: Major [ 3 ] => Blocker [ 1 ] Version: 2.5 RC2 [ 10372 ] |
Modified by: @dyemanovassignee: Dmitry Yemanov [ dimitr ] |
Modified by: @dyemanovstatus: Open [ 1 ] => In Progress [ 3 ] |
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? ;-) |
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 |
Commented by: Frank Schlottmann-Goedde (fsg) and anyway, the server shouldn't die if someone tries to create a strange index. |
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. |
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. |
Modified by: @dyemanovstatus: In Progress [ 3 ] => Open [ 1 ] |
Modified by: @dyemanovVersion: 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 ] |
Modified by: @dyemanovsummary: 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 |
Modified by: @dyemanovstatus: 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 ] |
Modified by: @pcisarstatus: Resolved [ 5 ] => Closed [ 6 ] |
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;
============ ============ =========== ============ ===========
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;
============ ============ =========== ============ ===========
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
The text was updated successfully, but these errors were encountered: