Skip to content
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

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

Closed
firebird-automations opened this issue May 24, 2012 · 7 comments

Comments

@firebird-automations
Copy link
Collaborator

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
Copy link
Collaborator Author

Commented by: @dyemanov

A complete reproducible test case is required.

@firebird-automations
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

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
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 2.5.2 [ 10450 ]

assignee: Dmitry Yemanov [ dimitr ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants