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: | |
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
[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.