[MDEV-14700] ALTER TABLE fails with bogus "BLOB/TEXT column '(null)' can't have a default value" Created: 2017-12-18  Updated: 2022-01-06  Resolved: 2022-01-06

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 10.2
Fix Version/s: 10.2.24, 10.3.14

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-10134 Add full support for DEFAULT Closed

 Description   

Support for default values for blobs was added in scope of MDEV-10134. So now this works:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (i INT);
 
ALTER TABLE t1 ADD COLUMN b1 BLOB NULL;
 
ALTER TABLE t1 ALTER COLUMN b1 SET DEFAULT NULL;


MariaDB [test]> ALTER TABLE t1 ALTER COLUMN b1 SET DEFAULT NULL;
 
Query OK, 0 rows affected (0.00 sec)
 
Records: 0  Duplicates: 0  Warnings: 0

But this does not work:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (i INT);
 
ALTER TABLE t1 ADD COLUMN b2 BLOB NULL, ALTER COLUMN b2 SET DEFAULT NULL;


MariaDB [test]> ALTER TABLE t1 ADD COLUMN b2 BLOB NULL, ALTER COLUMN b2 SET DEFAULT NULL;
 
ERROR 1101 (42000): BLOB/TEXT column '(null)' can't have a default value



 Comments   
Comment by Elena Stepanova [ 2022-01-06 ]

It was fixed in 10.2 by/together with this merge:

commit f2a0c758da3d9f0fa42c96114b453cf4835bcbab (HEAD)
 
Merge: fc168c3a5e5 d0116e10a5d
 
Author: Sergei Golubchik
 
Date:   Fri Mar 29 10:58:20 2019 +0100
 
 
 
    Merge branch '10.1' into 10.2

Apparently by this change, probably on top of the merge:

--- a/sql/sql_table.cc
 
+++ b/sql/sql_table.cc
 
@@ -7873,11 +7873,6 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
 
     }
 
     if (alter)
 
     {
 
-      if (def->sql_type == MYSQL_TYPE_BLOB)
 
-      {
 
-        my_error(ER_BLOB_CANT_HAVE_DEFAULT, MYF(0), def->change);
 
-        goto err;
 
-      }
 
       if ((def->default_value= alter->default_value)) // Use new default
 
         def->flags&= ~NO_DEFAULT_VALUE_FLAG;
 
       else

Generated at Thu Feb 08 08:15:36 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.