Dropping default value definitions from a table does not work as expected

[monetdb-team]

Date: 2014-09-19 15:50:03 +0200
From: Martin van Dinther <<martin.van.dinther>>
To: SQL devs <>
Version: 11.17.21 (Jan2014-SP3)
CC: @njnes

Last updated: 2014-10-31 14:14:41 +0100

Comment 20167

Date: 2014-09-19 15:50:03 +0200
From: Martin van Dinther <<martin.van.dinther>>

User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:32.0) Gecko/20100101 Firefox/32.0
Build Identifier:

The dropped default value definition seem to return after another columns default definition in the same table is dropped.
See steps to reproduce.

Reproducible: Always

Steps to Reproduce:

1. Start mserver5 (MonetDB 5 server v11.17.21 "Jan2014-SP3")
2. Start mclient
3. Run SQL cammnds:
   create table def_test (c1 integer default 5, c2 text default 'initial_default');
   insert into def_test default values;
   select * from def_test;

alter table def_test alter column c1 drop default;
insert into def_test default values;
select * from def_test;

alter table def_test alter column c2 drop default;
insert into def_test default values;
select * from def_test;

alter table def_test alter column c1 drop default;
insert into def_test default values;
select * from def_test;

alter table def_test alter column c2 drop default;
insert into def_test default values;
select * from def_test;

Actual Results:

bash-4.2$ mclient
Welcome to mclient, the MonetDB/SQL interactive terminal (unreleased)
Database: MonetDB v11.17.21 (Jan2014-SP3), 'demo'
Type \q to quit, ? for a list of available commands
auto commit mode: on
sql>create table def_test (
more>c1integer default 5,
more>c2text default 'initial_default'
more>);
syntax error, unexpected DEFAULT in: "create table def_test (
c1integer default"
sql>
sql>create table def_test (c1 integer default 5, c2 text default 'initial_default');
operation successful (85.583ms)
sql>insert into def_test default values;
1 affected rows (13.651ms)
sql>select * from def_test;
+------+-----------------+
| c1 | c2 |
+======+=================+
| 5 | initial_default |
+------+-----------------+
1 tuple (1.138ms)
sql>alter table def_test alter column c1 drop default;
operation successful (8.304ms)
sql>insert into def_test default values;
1 affected rows (2.722ms)
sql>select * from def_test;
+------+-----------------+
| c1 | c2 |
+======+=================+
| 5 | initial_default |
| null | initial_default |
+------+-----------------+
2 tuples (3.419ms)
sql>
sql>alter table def_test alter column c2 drop default;
operation successful (4.511ms)
sql>insert into def_test default values;
1 affected rows (2.518ms)
sql>select * from def_test;
+------+-----------------+
| c1 | c2 |
+======+=================+
| 5 | initial_default |
| null | initial_default |
| 5 | null |
+------+-----------------+
3 tuples (3.396ms)
sql>alter table def_test alter column c1 drop default;
operation successful (4.528ms)
sql>insert into def_test default values;
1 affected rows (3.879ms)
sql>select * from def_test;
+------+-----------------+
| c1 | c2 |
+======+=================+
| 5 | initial_default |
| null | initial_default |
| 5 | null |
| null | initial_default |
+------+-----------------+
4 tuples (1.086ms)
sql>alter table def_test alter column c2 drop default;
operation successful (3.957ms)
sql>insert into def_test default values;
1 affected rows (2.518ms)
sql>select * from def_test;
+------+-----------------+
| c1 | c2 |
+======+=================+
| 5 | initial_default |
| null | initial_default |
| 5 | null |
| null | initial_default |
| 5 | null |
+------+-----------------+
5 tuples (1.141ms)
sql>

Expected Results:

The default definition of c1 should not reappear after running
alter table def_test alter column c2 drop default;

and the default definition of c2 should not reappear after running
alter table def_test alter column c1 drop default;

Running
alter table def_test alter column c1 drop default;
and
alter table def_test alter column c2 drop default;
the second time should result in an error.

The last 3 inserted records are also wrong. They should have null values.

Comment 20184

Date: 2014-09-21 18:48:55 +0200
From: @njnes

fixed bug in book keeping of the default string

Comment 20387

Date: 2014-10-31 14:14:41 +0100
From: @sjoerdmullender

Oct2014 has been released.