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

NOT-NULL-column can be used as primary key and filled with NULL-values [CORE4070] #4398

Closed
firebird-automations opened this issue Mar 27, 2013 · 8 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Thomas Beckmann (thbeckmann)

I guess, this should not be possible:

SQL> show version;
ISQL Version: WI-T3.0.0.30287 Firebird 3.0 Unstable
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.30287 Firebird 3.0 Unstable"
on disk structure version 12.0
SQL> create table TEST$DATABASE (UID char(16) character set OCTETS collate OCTETS);
SQL> alter table TEST$DATABASE add primary key (UID);
SQL> commit;
SQL> insert into TEST$DATABASE (UID) values (null);
SQL> insert into TEST$DATABASE (UID) values (null);
SQL> commit;
SQL> show table TEST$DATABASE;
UID CHAR(16) CHARACTER SET OCTETS Nullable
CONSTRAINT INTEG_21:
Primary key (UID)
SQL> select * from TEST$DATABASE;

UID

<null>
<null>

Commits: d7c58ad FirebirdSQL/fbt-repository@f1e1187

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Thomas Beckmann (thbeckmann)

Hm, I've some problems understanding this: Why does the server complain about the NULL-value even though it has been deleted and does not show up?

SQL> set;
Print statistics: OFF
Echo commands: OFF
List format: OFF
Show Row Count: OFF
Select maxrows limit: 0
Autocommit DDL: OFF
Access Plan: OFF
Access Plan only: OFF
Display BLOB type: 1
Column headings: ON
Terminator: ;
Time: OFF
Warnings: ON
Bail on error: OFF
SQL> show version;
ISQL Version: WI-T3.0.0.30287 Firebird 3.0 Unstable
Server version:
Firebird/Windows/Intel/i386 (access method), version "WI-T3.0.0.30287 Firebird 3.0 Unstable"
on disk structure version 12.0
SQL> create table TEST$DATABASE (UID char(16) character set OCTETS collate OCTETS);
SQL> commit;
SQL> insert into TEST$DATABASE (UID) values (null);
SQL> commit;
SQL> alter table TEST$DATABASE add primary key (UID);
SQL> commit;
Statement failed, SQLSTATE = 23000
validation error for column UID, value "*** null ***"
SQL> delete from TEST$DATABASE;
SQL> commit;
Statement failed, SQLSTATE = 23000
validation error for column UID, value "*** null ***"
SQL> insert into TEST$DATABASE (UID) values (gen_uuid());
SQL> commit;
Statement failed, SQLSTATE = 23000
validation error for column UID, value "*** null ***"
SQL> show table TEST$DATABASE;
UID CHAR(16) CHARACTER SET OCTETS Nullable
SQL> select * from TEST$DATABASE;

UID

908BCF2F81144D3A94F482170C1FE013

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

The affending row should be not only deleted but also garbage collected.

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Fix Version: 3.0 Initial [ 10301 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

Version: 3.0 Initial [ 10301 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Version: 3.0 Alpha 1 [ 10331 ] =>

Fix Version: 3.0 Initial [ 10301 ] =>

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

@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: Done successfully

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