Issue Details (XML | Word | Printable)

Key: CORE-4070
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Critical Critical
Assignee: Adriano dos Santos Fernandes
Reporter: Thomas Beckmann
Votes: 0
Watchers: 0
Operations

If you were logged in you would be able to see more operations.
Firebird Core

NOT-NULL-column can be used as primary key and filled with NULL-values

Created: 27/Mar/13 12:41 PM   Updated: 29/May/15 01:07 PM
Component/s: Engine
Affects Version/s: 3.0 Initial
Fix Version/s: 3.0 Alpha 1

Environment: Windows 7 64, ISQL, Version: WI-T3.0.0.30287 Firebird 3.0 Unstable

QA Status: Done successfully


 Description  « Hide
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>

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Thomas Beckmann added a comment - 27/Mar/13 01:12 PM
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

Dmitry Yemanov added a comment - 27/Mar/13 02:00 PM
The affending row should be not only deleted but also garbage collected.