Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28918

Implicit cast from INET6 UNSIGNED works differently on UPDATE vs ALTER

Details

    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 |
      		 
      +------+

      Attachments

        Issue Links

          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-29159 Patch for MDEV-28918 introduces more inconsistency than it solves, breaks usability

          • Blocker - Blocks development and/or testing work, production could not run.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-28963 Incompatible data type assignment through SP vars is not consistent with columns

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment -

            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 |
            		 
            +------+

            bar Alexander Barkov added a comment - 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 | +------+

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.