MARIADB - Can SQL convert NULL to an empty string if no default value is provided?

Question

I have a table that has nullable columns:

+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| id    | int(11)      | YES  |     | NULL    |       |
| name  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+

I insert a row with name set to NULL;

INSERT INTO some_table (id, name) VALUES (1, NULL);
Query OK, 1 row affected (0.02 sec)

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.01 sec)

If I alter the table's name column to be not-nullable it apparently converts NULL to an empty string:

ALTER TABLE some_table CHANGE COLUMN name name VARCHAR(255) NOT NULL;
Query OK, 1 row affected, 1 warning (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 1

SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 |      |
+------+------+
1 row in set (0.02 sec)

At this point I would expect an exception to be raised telling me that I have NULL in my dataset and I can not set the column name to NOT NULL.

Is this a configurable option in SQL/MariaDB? Why is NULL being converted to an empty string?

There is a warning being invoked when altering the table:

SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1265 | Data truncated for column 'name' at row 1 |
+---------+------+-------------------------------------------+
1 row in set (0.01 sec)

Version:

SELECT version();
+----------------+
| version()      |
+----------------+
| 5.5.62-MariaDB |
+----------------+
1 row in set (0.02 sec)

"Can SQL convert NULL to an empty string if no default value is provided?" Sounds not logical as NULL is not the same as " " in SQL... — Raymond Nijland
– Raymond Nijland, Commented Sep 4, 2019 at 13:04
@RaymondNijland In fact empty string is '' (no space), and in Oracle empty string and NULL are the same thing. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 4, 2019 at 13:05
@TimBiegeleisen i meant '' not the double qoute and without space.. But then still '' should/could not be considerd the same as NULL in SQL standards pretty sure most RDMS agree on this also.. " In fact empty string is '' (no space), and in Oracle empty string and NULL are the same thing. " So it seams Oracle database extended the standard to allow this — Raymond Nijland
– Raymond Nijland, Commented Sep 4, 2019 at 13:13
That Oracle owns MySQL makes me wonder if that has something to do with NULL being coerced to empty string. — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 4, 2019 at 13:18

Tim Biegeleisen · Accepted Answer · 2019-09-04 13:09:11Z

3

Apparently, from the documentation for ALTER TABLE, enabling strict mode would prevent your alter statement from succeeding:

This conversion may result in alteration of data. For example, if you shorten a string column, values may be truncated. To prevent the operation from succeeding if conversions to the new data type would result in loss of data, enable strict SQL mode before using ALTER TABLE.

One way to enable strict mode from within MySQL:

SET GLOBAL sql_mode='STRICT_TRANS_TABLES';

See here for other options.

answered Sep 4, 2019 at 13:09

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Raymond Nijland Over a year ago

@harpocrates i am pretty sure that query disables all active sql_modes at server configuration level? A better and safer query to run would be SET GLOBAL sql_mode = CONCAT(@@sql_mode, ',STRICT_TRANS_TABLES') ? But i am already to late it seams...

Erik Nellessen · Accepted Answer · 2019-09-04 13:12:43Z

Using 10.3.15-MariaDB-1 on Debian Buster, I cannot reproduce the problem:

MariaDB [foo]> CREATE TABLE some_table(id int(11), name varchar(255));
Query OK, 0 rows affected (0.009 sec)

MariaDB [foo]> INSERT INTO some_table (id, name) VALUES (1, NULL);
Query OK, 1 row affected (0.003 sec)

MariaDB [foo]> SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.000 sec)

MariaDB [foo]> ALTER TABLE some_table CHANGE COLUMN name name VARCHAR(255) NOT NULL;
ERROR 1265 (01000): Data truncated for column 'name' at row 1
MariaDB [foo]> SELECT * FROM some_table;
+------+------+
| id   | name |
+------+------+
|    1 | NULL |
+------+------+
1 row in set (0.000 sec)

MariaDB [foo]> SELECT version();
+-------------------+
| version()         |
+-------------------+
| 10.3.15-MariaDB-1 |
+-------------------+
1 row in set (0.000 sec)

If possible, I suggest you update your MariaDB version. It seems very old to me.

Collectives™ on Stack Overflow

MARIADB - Can SQL convert NULL to an empty string if no default value is provided?

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related