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

Wrong result of WHERE predicate when it contains NULL IS NOT DISTINCT FROM (select min(NULL) from ...) [CORE4366] #4688

Closed
firebird-automations opened this issue Mar 15, 2014 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Is related to QA596

LI-T3.0.0.30967 Firebird 3.0 Alpha 2

This is the content of first 10 rows in my RDB$FIELDS table, select only field "rdb$field_name":
SQL> select rdb$field_name from rdb$fields where null is NOT distinct from null rows 10;

RDB$FIELD_NAME

RDB$VIEW_CONTEXT
RDB$CONTEXT_NAME
RDB$DESCRIPTION
RDB$EDIT_STRING
RDB$FIELD_ID
RDB$FIELD_NAME
RDB$SYSTEM_FLAG
RDB$SYSTEM_NULLFLAG
RDB$INDEX_ID
RDB$INDEX_NAME

When I try to add the following WHERE condition result becames wrong, no matter of what table is inside subquery that aggregates MIN(null):

SQL> select rdb$field_name from rdb$fields where null is NOT distinct from (select min(null) from rdb$fields) rows 10;

RDB$FIELD_NAME

RDB$VIEW_CONTEXT

SQL> select rdb$field_name from rdb$fields where null is NOT distinct from (select min(null) from rdb$types) rows 10;

RDB$FIELD_NAME

RDB$VIEW_CONTEXT

PS. Compare with LI-V2.5.3.26744 Firebird 2.5: both variants select 10 rows, which is OK:

SQL> select rdb$field_name from rdb$fields where null is NOT distinct from null rows 10;

RDB$FIELD_NAME

RDB$VIEW_CONTEXT
RDB$CONTEXT_NAME
RDB$DESCRIPTION
RDB$EDIT_STRING
RDB$FIELD_ID
RDB$FIELD_NAME
RDB$SYSTEM_FLAG
RDB$INDEX_ID
RDB$INDEX_NAME
RDB$FIELD_LENGTH

SQL> select rdb$field_name from rdb$fields where null is NOT distinct from (select min(null) from rdb$fields) rows 10;

RDB$FIELD_NAME

RDB$VIEW_CONTEXT
RDB$CONTEXT_NAME
RDB$DESCRIPTION
RDB$EDIT_STRING
RDB$FIELD_ID
RDB$FIELD_NAME
RDB$SYSTEM_FLAG
RDB$INDEX_ID
RDB$INDEX_NAME
RDB$FIELD_LENGTH

Commits: c0361bc FirebirdSQL/fbt-repository@89836a0

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

assignee: Dmitry Yemanov [ dimitr ]

summary: wrong result of WHERE predicat when it contains NULL IS NOT DISTINCT FROM (select min(NULL) from ...) => Wrong result of WHERE predicate when it contains NULL IS NOT DISTINCT FROM (select min(NULL) from ...)

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Beta 1 [ 10332 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

Link: This issue is related to QA596 [ QA596 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Commented by: @pcisar

Test created.

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

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