Issue Details (XML | Word | Printable)

Key: CORE-2833
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Blocker Blocker
Assignee: Dmitry Yemanov
Reporter: Frank Schlottmann-Goedde
Votes: 0
Watchers: 1
Operations

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

Changing data that affects an expression index that contains references to null date fields fails

Created: 28/Jan/10 03:16 PM   Updated: 04/Feb/11 11:44 AM
Component/s: Engine
Affects Version/s: 2.1.0, 2.5 Alpha 1, 2.1.1, 2.1.2, 2.5 Beta 1, 2.5 Beta 2, 2.1.3, 3.0 Initial, 2.5 RC1, 2.5 RC2
Fix Version/s: 2.5 RC3, 2.1.4, 3.0 Alpha 1

Time Tracking:
Not Specified

Planning Status: Unspecified


 Description  « Hide
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 (new.id is null) then
    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

















 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Frank Schlottmann-Goedde added a comment - 17/Feb/10 03:04 PM
In RC2 even the creation of the index fails with
Statement failed, SQLSTATE = 08006
connection lost to database


Frank Schlottmann-Goedde added a comment - 17/Feb/10 03:06 PM
This bug stops our efforts to test FB2.5 in semi-production environments.


Dmitry Yemanov added a comment - 17/Feb/10 03:31 PM
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? ;-)

Frank Schlottmann-Goedde added a comment - 17/Feb/10 04:06 PM
No idea :-)

I will forward your question to the original developer.

Apparently this thing worked as expected in FB 2.0


Frank Schlottmann-Goedde added a comment - 17/Feb/10 04:11 PM
and anyway, the server shouldn't die if someone tries to create a strange index.

Dmitry Yemanov added a comment - 17/Feb/10 04:18 PM
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.

Frank Schlottmann-Goedde added a comment - 18/Feb/10 07:54 AM
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.