ALTER TABLE col SET NOT NULL fails with 'unimplemented at or near "null"'

https://github.com/ory/hydra oauth server is designed to work with postgres and mysql.

I tried to use it with cockroach and it fails to setup a database tables. It uses https://github.com/rubenv/sql-migrate to do that.

The SQL that fails is

ERROR:  unimplemented at or near "null"
DETAIL:  source SQL:
ALTER TABLE hydra.hydra_client ALTER COLUMN request_uris SET NOT NULL;

                                                                 ^

Build Tag:    v2.0.3
Build Time:   2018/06/18 16:11:33
Distribution: CCL
Platform:     linux amd64 (x86_64-unknown-linux-gnu)
Go Version:   go1.10
C Compiler:   gcc 6.3.0
Build SHA-1:  91715a9a95edbe716912173204fa4c0fc6724457
Build Type:   release 

The table that is altered is

CREATE TABLE hydra.hydra_client (
    id STRING(255) PRIMARY KEY,
    client_name STRING NOT NULL,
    client_secret STRING NOT NULL,
    redirect_uris STRING NOT NULL,
    grant_types STRING NOT NULL,
    response_types STRING NOT NULL,
    scope STRING NOT NULL,
    owner STRING NOT NULL,
    policy_uri STRING NOT NULL,
    tos_uri STRING NOT NULL,
    client_uri STRING NOT NULL,
    logo_uri STRING NOT NULL,
    contacts STRING NOT NULL,
    public BOOL NOT NULL,
    client_secret_expires_at INTEGER NOT NULL DEFAULT 0:::INT,
    sector_identifier_uri STRING,
    jwks STRING,
    jwks_uri STRING,
    request_uris STRING,
    token_endpoint_auth_method STRING(25) NOT NULL DEFAULT '':::STRING,
    request_object_signing_alg STRING(10) NOT NULL DEFAULT '':::STRING,
    userinfo_signed_response_alg STRING(10) NOT NULL DEFAULT '':::STRING
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX primary ON hydra.hydra_client(id);

Is it expected that ALTER TABLE hydra.hydra_client ALTER COLUMN request_uris SET NOT NULL; to fail or I should file a bug ?

Thanks

https://www.cockroachlabs.com/docs/stable/not-null.html

I see SET NOT NULL is not allowed

Hi @artem,

Yes, it seems that in 2.0, it is not possible to add the NOT NULL constraint to an existing column.

I think this functionality might be coming in 2.1, based on this issue, but I’m not certain. @Bob or @awoods187, do you have insights?

Best,
Jesse