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: FirebirdSQL/firebird@161386ee2fbd7a9427e369457845ea84074d899b FirebirdSQL/firebird@cf6e3d09690ab6384b7fa2d83bfe9c721a325976

[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