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

Firebird Core

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

Created: 24/May/12 06:38 AM Updated: 23/Apr/13 01:53 PM

Component/s:

Engine, GBAK

Affects Version/s:

2.5.1

Fix Version/s:

2.5.2

Time Tracking:

Not Specified

Environment:

Win Vista SP2, Win7, Classic builds

Planning Status:

Unspecified

Description

« Hide

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.

All

Comments

Work Log

Change History

Version Control

Subversion Commits

Sort Order:

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 24/May/12 06:44 AM

A complete reproducible test case is required.

[ Permalink | « Hide ]

Dmitry Yemanov added a comment - 24/May/12 09:31 AM

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?

[ Permalink | « Hide ]

Richard Stekly added a comment - 24/May/12 10:07 AM

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.

[ Show » ]

Richard Stekly added a comment - 24/May/12 10:07 AM 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.