Skip to content

NULLS NOT DISTINCT constraint changed after column dropped #4036

@kacperkasztelanic

Description

@kacperkasztelanic

Let's say a new instance of the latest (2.2.224) version of H2 is run via:
java -jar h2-2.2.224.jar
once the h2-console is reached and the following sample table is created:

create table some_table
(
    id int                not null primary key,
    a  int                not null,
    b  varchar(30),
    c  varchar(20),
    constraint some_table_constraint unique nulls not distinct (a, b)
);

the query:

select NULLS_DISTINCT 
from information_schema.indexes 
where table_name='SOME_TABLE' and index_name like 'SOME_TABLE_CONSTRAINT%';

returns NO (as expected).

However, once the following statement is executed
alter table some_table drop column c;
the previously mentioned select query
returns YES (surprisingly).

The NULLS_DISTINCT property value is somehow lost in the process and considering that the dropped column c was not a part of the constraint it is a bug - a rather hard-to-find and dangerous (if H2 was used on the prod environment) one.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions