Database created in Firebird 2.5.1 may cause the "IS NULL" predicate evaluated differently if used with Firebird 2.5.0 [CORE3853]

[firebird-automations]

Submitted by: Richard Stekly (rs.)

create or restore with recreate database in 251.
MY_TABLE - MY_FLD Varchar(31)
insert one row where MY_FLD is null
//-------
use this database in 250 engine
insert one row where MY_FLD is null
//------
select * from MY_TABLE where MY_FLD is null
result set contain only one row, written by the same version of engine.

bug is fixed after backup-restore with recreate

I mean ODS version shoud be different in this case, not 11.2 - 11.2, I do'nt have any other version protection.

Commits: 161386e cf6e3d0

[firebird-automations]

Commented by: @dyemanov

A complete reproducible test case is required.

[firebird-automations]

Commented by: @dyemanov

Am I correct assuming that there must be a compound index involving a few fields one of those is MY_FLD? And that without such a condition, the issue doesn't manifest itself?

[firebird-automations]

Commented by: Richard Stekly (rs.)

Thanks for response, I'm sorry, I'm too busy now. I'll prepare better test-case at evening. I cannot post original database, because customers data ... I have to prepare another one.
MY_TABLE = templates, MY_FIELD= special :

CREATE TABLE TEMPLATES(
SOL_ID Smallint NOT NULL,
TEMPLATE_ID Integer NOT NULL,
LAST_UPDATE Timestamp NOT NULL,
LAST_UPDATE_UN Varchar(31) NOT NULL,
NAME Varchar(64) NOT NULL,
EXTENSION Char(4),
TEMPLATE Blob sub_type 0,
SPECIAL Varchar(31),
ATTRIBUTES Integer,
FLAG_DB Smallint NOT NULL,
CAPTION Varchar(64) NOT NULL,
CATHEGORY Varchar(250),
PRODUCTID Varchar(60) NOT NULL,
LANGID Varchar(10) NOT NULL,
TVERSION Varchar(10) NOT NULL,
ICON_ID Integer,
ASOL_ID Smallint,
ACLASS_ID Smallint,
DESCRIPTION Blob sub_type 0,
CONSTRAINT PK_TEMPLATES PRIMARY KEY (SOL_ID,TEMPLATE_ID),
CONSTRAINT IDX_TEMPLATES_SNE UNIQUE (SOL_ID,NAME,EXTENSION)
);
ALTER TABLE TEMPLATES ADD CONSTRAINT FK_TEMPLATES_CLASSES
FOREIGN KEY (ASOL_ID,ACLASS_ID) REFERENCES CLASSES (SOL_ID,CLASS_ID) ON UPDATE CASCADE ON DELETE CASCADE;
ALTER TABLE TEMPLATES ADD CONSTRAINT FK_TEMPLATES_ICONS
FOREIGN KEY (ICON_ID) REFERENCES ICONS (ICON_ID) ON UPDATE CASCADE ON DELETE NO ACTION;
ALTER TABLE TEMPLATES ADD CONSTRAINT FK_TEMPLATES_SOLUTIONS
FOREIGN KEY (SOL_ID) REFERENCES SOLUTIONS (SOL_ID) ON UPDATE CASCADE ON DELETE CASCADE;
CREATE INDEX IDX_TEMPLATES_SES ON TEMPLATES (SOL_ID,EXTENSION,SPECIAL);
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TEMPLATES TO ROLE DBADMIN WITH GRANT OPTION;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TEMPLATES TO ROLE DESIGN;
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TEMPLATES TO ROLE POWER;
GRANT SELECT
ON TEMPLATES TO "PUBLIC";
GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE
ON TEMPLATES TO SYSDBA WITH GRANT OPTION;

+ some triggers for last_update etc.

[firebird-automations]

Modified by: @dyemanov

summary: difference between 250 and 251 for "is null", but ODS is same => Database created in Firebird 2.5.1 may cause the "IS NULL" predicate evaluated differently if used with Firebird 2.5.0

Version: 2.5.0 [ 10221 ] =>

[firebird-automations]

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.2 [ 10450 ]

assignee: Dmitry Yemanov [ dimitr ]

[firebird-automations]

Modified by: @pcisar

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

[firebird-automations]

Modified by: @pavel-zotov

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

QA Status: No test