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

Copying from INT/DOUBLE to ENUM is inconsistent

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL)
    • 10.2.0
    • OTHER
    • None

    Description

      Copying from DOUBLE to ENUM in INSERT..SELECT

      DROP TABLE IF EXISTS t1,t2;
      		 
      CREATE TABLE t1 (a ENUM('9e200','9e100'));
      		 
      CREATE TABLE t2 (a DOUBLE);
      		 
      INSERT INTO t2 VALUES ('9e100');
      		 
      INSERT INTO t1 SELECT * FROM t2;
      		 
      SELECT * FROM t1;

      returns

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

      Copying from DOUBLE to ENUM in ALTER

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a DOUBLE);
      		 
      INSERT INTO t1 VALUES (9e100);
      		 
      ALTER TABLE t1 MODIFY a ENUM('9e200','9e100');
      		 
      SELECT *FROM t1;

      returns

      +-------+
      		 
      | a     |
      		 
      +-------+
      		 
      | 9e100 |
      		 
      +-------+

      Attachments

        Issue Links

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment -

            The same problem is observed when copying from INT to ENUM.

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (a ENUM('200','100'));
            		 
            CREATE TABLE t2 (a DOUBLE);
            		 
            INSERT INTO t2 VALUES ('100');
            		 
            INSERT INTO t1 SELECT * FROM t2;
            		 
            SELECT * FROM t1;

            returns

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

            while

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a INT);
            		 
            INSERT INTO t1 VALUES ('200');
            		 
            ALTER TABLE t1 MODIFY a ENUM('200','100');
            		 
            SELECT *FROM t1;

            returns

            +------+
            		 
            | a    |
            		 
            +------+
            		 
            | 200  |
            		 
            +------+

            bar Alexander Barkov added a comment - The same problem is observed when copying from INT to ENUM. DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a ENUM('200','100')); CREATE TABLE t2 (a DOUBLE); INSERT INTO t2 VALUES ('100'); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; returns +------+ | a | +------+ | | +------+ while DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES ('200'); ALTER TABLE t1 MODIFY a ENUM('200','100'); SELECT *FROM t1; returns +------+ | a | +------+ | 200 | +------+
            bar Alexander Barkov added a comment - - edited

            The same problem is observed when copying from YEAR to ENUM.

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (a ENUM('2001','2002'));
            		 
            CREATE TABLE t2 (a YEAR);
            		 
            INSERT INTO t2 VALUES ('2001');
            		 
            INSERT INTO t1 SELECT * FROM t2;
            		 
            SELECT * FROM t1;

            returns

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

            so does

            DROP TABLE IF EXISTS t1,t2;
            		 
            CREATE TABLE t1 (a ENUM('2001','2002'));
            		 
            CREATE TABLE t2 (a YEAR);
            		 
            INSERT INTO t2 VALUES ('2001');
            		 
            INSERT INTO t1 SELECT * FROM t2;
            		 
            SELECT * FROM t1;

            while

            DROP TABLE IF EXISTS t1;
            		 
            CREATE TABLE t1 (a YEAR);
            		 
            INSERT INTO t1 VALUES ('2001');
            		 
            ALTER TABLE t1 MODIFY a ENUM('2001','2002');
            		 
            SELECT *FROM t1;

            returns

            +------+
            		 
            | a    |
            		 
            +------+
            		 
            | 2001 |
            		 
            +------+

            bar Alexander Barkov added a comment - - edited The same problem is observed when copying from YEAR to ENUM. DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a ENUM('2001','2002')); CREATE TABLE t2 (a YEAR); INSERT INTO t2 VALUES ('2001'); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; returns +------+ | a | +------+ | | +------+ so does DROP TABLE IF EXISTS t1,t2; CREATE TABLE t1 (a ENUM('2001','2002')); CREATE TABLE t2 (a YEAR); INSERT INTO t2 VALUES ('2001'); INSERT INTO t1 SELECT * FROM t2; SELECT * FROM t1; while DROP TABLE IF EXISTS t1; CREATE TABLE t1 (a YEAR); INSERT INTO t1 VALUES ('2001'); ALTER TABLE t1 MODIFY a ENUM('2001','2002'); SELECT *FROM t1; returns +------+ | a | +------+ | 2001 | +------+

            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.