[#MDEV-26075] alter column drop default syntax creates inconsistent table behavior

Running the command "alter column drop default" creates an inconsistent table behavior where the column appears to not have a default but insertion without a value specified for this column succeeds.

How to reproduce:

Use the following table. For this example I'm using schema 'test':

CREATE TABLE `example_table` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

The default allows insertion without specifying a value for 'at', as expected:

insert example_table () values ();

Query OK, 1 row affected (0.003 sec)

select * from example_table;

+----+---------------------+

| id | at                  |

+----+---------------------+

|  1 | 2021-07-01 19:53:40 |

+----+---------------------+

1 row in set (0.000 sec)

select default(at) from example_table;

+---------------------+

| default(at)         |

+---------------------+

| 2021-07-01 19:53:40 |

+---------------------+

1 row in set (0.000 sec)

Now run an alter table statement as follows:

alter table test.example_table alter column at drop default;

Using the default() statement makes it appear that this removed the specified default:

select default(at) from example_table;

ERROR 1364 (HY000): Field 'at' doesn't have a default value

However, the create table statement still shows the default, and it's still possible to insert into example_table without providing a value for the 'at' column:

show create table example_table\G

*************************** 1. row ***************************

       Table: example_table

Create Table: CREATE TABLE `example_table` (

  `id` bigint(20) NOT NULL AUTO_INCREMENT,

  `at` datetime NOT NULL DEFAULT current_timestamp(),

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

#Based on the result of the default() query I would expect the at column definition to be "`at` datetime NOT NULL,"

insert example_table () values ();

Query OK, 1 row affected (0.003 sec)

# Based on the result of the default() query I would expect this to fail with "ERROR 1364 (HY000): Field 'at' doesn't have a default value"

select * from example_table;

+----+---------------------+

| id | at                  |

+----+---------------------+

|  1 | 2021-07-01 19:53:40 |

|  2 | 2021-07-01 19:57:31 |

+----+---------------------+

2 rows in set (0.000 sec)

# Based on the result of the default() query I would expect the insert to fail and for this to still have 1 row

From the documentation it's not clear to me if perhaps the 'drop default' is expected to revert to the 'initial default' for the column data type, which would still be current_timestamp(), instead of removing the default and putting the column definition to "`at` datetime NOT NULL". The current behavior, however, seems to be a combination of the two where the default() command behaves as if the column definition is "`at` datetime NOT NULL", but otherwise the table behaves as if it still has the default as current_timestamp().

[MDEV-26075] alter column drop default syntax creates inconsistent table behavior Created: 2021-07-01 Updated: 2021-07-17
Status:	Open
Project:	MariaDB Server
Component/s:	None
Affects Version/s:	10.5.5
Fix Version/s:	10.5, 10.6

[MDEV-26075] alter column drop default syntax creates inconsistent table behavior Created: 2021-07-01 Updated: 2021-07-17