Bug #97212 Cannot insert default value after dropping default

• Submitted: 14 Oct 2019 9:44
• Modified: 14 Oct 2019 10:08
• Reporter: Lukas Eder
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 8.0.17
• OS: Linux (Docker)
• CPU Architecture: x86

[14 Oct 2019 9:44] Lukas Eder

Description:
When creating a table with column defaults, and then dropping one of the defaults, it is no longer possible to insert a default value in that column, as if there was a not null constraint

How to repeat:
create table t(
  i int null default 1,
  j int null default 1
);

alter table t alter j drop default;

-- This produces [1364] [HY000]: Field 'j' doesn't have a default value
insert into t (i)
values (2);

-- But this shows, that it should work:
select column_name, column_default, is_nullable
from information_schema.columns
where lower(table_name) = 't';

-- COLUMN_NAME|COLUMN_DEFAULT|IS_NULLABLE|
-- -----------|--------------|-----------|
-- i          |1             |YES        |
-- j          |              |YES        |

-- A workaround is to set the default to null, instead
alter table t alter j set default null;

drop table t;

[14 Oct 2019 10:08] MySQL Verification Team

Hello Lukas Eder,

Thank you for the report.
Imho this looks like duplicate of Bug #81010, please see Bug #81010

regards,
Umesh