[MDEV-28918] Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER Created: 2022-06-21  Updated: 2023-11-23  Resolved: 2022-06-27

Status: Closed
Project: MariaDB Server
Component/s: Data types, Plugin - INET6
Affects Version/s: 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.7.5, 10.8.4, 10.9.2, 10.10.0

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-29159 Patch for MDEV-28918 introduces more ... Closed
Relates
relates to MDEV-28963 Incompatible data type assignment thr... Closed

 Description    

SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a INET6, b INT UNSIGNED);
 
INSERT INTO t1 VALUES (NULL, 0x61);
 
UPDATE t1 SET a=b;
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+---------+------+-----------------------------------------------------------------+
 
| Level   | Code | Message                                                         |
 
+---------+------+-----------------------------------------------------------------+
 
| Warning | 1292 | Incorrect inet6 value: '97' for column `test`.`t1`.`a` at row 1 |
 
+---------+------+-----------------------------------------------------------------+


+------+------+
 
| a    | b    |
 
+------+------+
 
| ::   |   97 |
 
+------+------+


SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a INT UNSIGNED);
 
INSERT INTO t1 VALUES (0x61);
 
ALTER TABLE t1 MODIFY a INET6;
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+---------+------+-----------------------------------------------------------------+
 
| Level   | Code | Message                                                         |
 
+---------+------+-----------------------------------------------------------------+
 
| Warning | 1292 | Incorrect inet6 value: '97' for column `test`.`t1`.`a` at row 1 |
 
+---------+------+-----------------------------------------------------------------+


+------+
 
| a    |
 
+------+
 
| NULL |
 
+------+



 Comments   
Comment by Alexander Barkov [ 2022-06-21 ]

The problem is also repeatable with the UUID data type (starting from 10.7 when UUID was added).

SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a UUID, b INT UNSIGNED);
 
INSERT INTO t1 VALUES (NULL, 0x61);
 
UPDATE t1 SET a=b;
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+---------+------+----------------------------------------------------------------+
 
| Level   | Code | Message                                                        |
 
+---------+------+----------------------------------------------------------------+
 
| Warning | 1292 | Incorrect uuid value: '97' for column `test`.`t1`.`a` at row 1 |
 
+---------+------+----------------------------------------------------------------+


+--------------------------------------+------+
 
| a                                    | b    |
 
+--------------------------------------+------+
 
| 00000000-0000-0000-0000-000000000000 |   97 |
 
+--------------------------------------+------+


SET sql_mode='';
 
CREATE OR REPLACE TABLE t1 (a INT UNSIGNED);
 
INSERT INTO t1 VALUES (0x61);
 
ALTER TABLE t1 MODIFY a UUID;
 
SHOW WARNINGS;
 
SELECT * FROM t1;


+---------+------+----------------------------------------------------------------+
 
| Level   | Code | Message                                                        |
 
+---------+------+----------------------------------------------------------------+
 
| Warning | 1292 | Incorrect uuid value: '97' for column `test`.`t1`.`a` at row 1 |
 
+---------+------+----------------------------------------------------------------+


+------+
 
| a    |
 
+------+
 
| NULL |
 
+------+

Generated at Thu Feb 08 10:04:27 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.