Bug #91105 Empty value in NOT NULL column with geometric types
Submitted: 1 Jun 2018 16:09 Modified: 4 Jun 2018 16:30
Reporter: David Ducos Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.5/5.6 OS:Any
Assigned to: CPU Architecture:Any

[1 Jun 2018 16:09] David Ducos
Description:
In 5.6 it is possible to have empty values on POINT type in not null columns but in 5.7, it is not possible. 

This could end up in problems during migrations. is any workaround?

How to repeat:
In 5.6
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test_table (id int primary key);
Query OK, 0 rows affected (0.05 sec)

mysql> insert into test_table values (1);
Query OK, 1 row affected (0.03 sec)

mysql> alter table test_table add column (point_val point not null);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from test_table;
+----+-----------+
| id | point_val |
+----+-----------+
|  1 |           |
+----+-----------+
1 row in set (0.00 sec)

In 5.7 and later:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table test_table (id int primary key);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test_table values (1);
Query OK, 1 row affected (0.01 sec)

mysql> alter table test_table add column (point_val point not null);
ERROR 1138 (22004): Invalid use of NULL value
[2 Jun 2018 0:27] MySQL Verification Team
Thank you for the bug report, issue noticed on 5.5 and 5.6 version

Your MySQL connection id is 1
Server version: 5.5.62 Source distribution 2018-MAY-31

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.5 > use test
Database changed
mysql 5.5 > create table test_table (id int primary key);
Query OK, 0 rows affected (0,10 sec)

mysql 5.5 > insert into test_table values (1);
Query OK, 1 row affected (0,04 sec)

mysql 5.5 > alter table test_table add column (point_val point not null);
Query OK, 1 row affected (0,25 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql 5.5 > select * from test_table;
+----+-----------+
| id | point_val |
+----+-----------+
|  1 |           |
+----+-----------+
1 row in set (0,01 sec)

-------------------------------------------------------------------------------------
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.42 Source distribution 2018-MAY-31

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.6 > use test
Database changed
mysql 5.6 > create table test_table (id int primary key);
Query OK, 0 rows affected (0,37 sec)

mysql 5.6 > insert into test_table values (1);
Query OK, 1 row affected (0,05 sec)

mysql 5.6 > alter table test_table add column (point_val point not null);
Query OK, 0 rows affected (0,82 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql 5.6 > select * from test_table;
+----+-----------+
| id | point_val |
+----+-----------+
|  1 |           |
+----+-----------+
1 row in set (0,00 sec)
---------------------------------------------------------------------------------
Your MySQL connection id is 3
Server version: 5.7.24 Source distribution 2018-MAY-31

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > create table test_table (id int primary key);
Query OK, 0 rows affected (0,45 sec)

mysql 5.7 > insert into test_table values (1);
Query OK, 1 row affected (0,05 sec)

mysql 5.7 > alter table test_table add column (point_val point not null);
ERROR 1138 (22004): Invalid use of NULL value
mysql 5.7 >
[4 Jun 2018 13:30] Norvald Ryeng
Posted by developer:
 
The 5.6 behavior is a bug which was fixed in 5.7. I'm marking this a duplicate of that bug report.

POINT NOT NULL columns can only contain valid POINT values. The empty string is not a valid geometry value.

In order to store the fact that there is no point value for that row, the column should either not be NOT NULL, or be of type GEOMETRY and use GEOMETRYCOLLECTION() as the empty value.
[4 Jun 2018 16:30] MySQL Verification Team
Duplicate of https://bugs.mysql.com/bug.php?id=73871 as defined by developer.