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

CREATE..SELECT with DIV creates columns of a wrong data type

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3(EOL)
    • 10.4(EOL)
    • Data types
    • None

    Description

      SET sql_mode='';
      SELECT 1e14 DIV 2 AS c1, '' DIV 2 AS c2;
      CREATE OR REPLACE TABLE t1 AS SELECT 1e14 DIV 2 AS c1, '' DIV 2 AS c2;
      SELECT * FROM t1;
      SHOW CREATE TABLE t1;
      

      +----------------+------+
      | c1             | c2   |
      +----------------+------+
      | 50000000000000 |    0 |
      +----------------+------+
      

      +------------+------+
      | c1         | c2   |
      +------------+------+
      | 2147483647 |    0 |
      +------------+------+
      

      +-------+-------------------------------------------------------------------------------------------------------------------+
      | Table | Create Table                                                                                                      |
      +-------+-------------------------------------------------------------------------------------------------------------------+
      | t1    | CREATE TABLE `t1` (
        `c1` int(4) DEFAULT NULL,
        `c2` int(0) DEFAULT NULL
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
      +-------+-------------------------------------------------------------------------------------------------------------------+
      

      Notice:

      • The value of c1 in the second result is wrong. It should be 50000000000000.
      • The data type for t1.c1 is wrong. It should be approximately bigint(14).
      • The data type for t1.c2 is wrong. It should be at least int(1).

      Attachments

        Issue Links

          Activity

            A similar problem is repeatable in:

            SET sql_mode='';
            CREATE OR REPLACE TABLE t1 AS SELECT 0x1000 DIV 1 AS c1;
            SELECT * FROM t1;
            SHOW CREATE TABLE t1;
            

            +------+
            | c1   |
            +------+
            | 4096 |
            +------+
            

            +-------+------------------------------------------------------------------------------------------------+
            | Table | Create Table                                                                                   |
            +-------+------------------------------------------------------------------------------------------------+
            | t1    | CREATE TABLE `t1` (
              `c1` int(2) unsigned DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+------------------------------------------------------------------------------------------------+
            

            Notice:

            • The data type for t1.c1 is wrong. It should be at least int(4).
            bar Alexander Barkov added a comment - A similar problem is repeatable in: SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 AS SELECT 0x1000 DIV 1 AS c1; SELECT * FROM t1; SHOW CREATE TABLE t1; +------+ | c1 | +------+ | 4096 | +------+ +-------+------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `c1` int(2) unsigned DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------+ Notice: The data type for t1.c1 is wrong. It should be at least int(4) .

            A similar problem is repeatable in this script:

            SET sql_mode='';
            CREATE OR REPLACE TABLE t1 AS SELECT 1 DIV 0.01 AS c;
            SELECT * FROM t1; SHOW CREATE TABLE t1;
            

            +------+
            | c    |
            +------+
            |  100 |
            +------+
            

            +-------+--------------------------------------------------------------------------------------+
            | Table | Create Table                                                                         |
            +-------+--------------------------------------------------------------------------------------+
            | t1    | CREATE TABLE `t1` (
              `c` int(1) DEFAULT NULL
            ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
            +-------+--------------------------------------------------------------------------------------+
            

            Notice:

            • The data type for t1.c is wrong. It should be at least int(3).
            bar Alexander Barkov added a comment - A similar problem is repeatable in this script: SET sql_mode= '' ; CREATE OR REPLACE TABLE t1 AS SELECT 1 DIV 0.01 AS c; SELECT * FROM t1; SHOW CREATE TABLE t1; +------+ | c | +------+ | 100 | +------+ +-------+--------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `c` int(1) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------+ Notice: The data type for t1.c is wrong. It should be at least int(3) .

            A related problem:

            CREATE TABLE t1 (a INT UNSIGNED, b INT);
            INSERT INTO t1 VALUES (1,-1);
            SELECT a DIV b FROM t1;
            

            ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`test`.`t1`.`a` DIV `test`.`t1`.`b`'
            

            Looks wrong. The expected result is to return -1.

            bar Alexander Barkov added a comment - A related problem: CREATE TABLE t1 (a INT UNSIGNED, b INT ); INSERT INTO t1 VALUES (1,-1); SELECT a DIV b FROM t1; ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '`test`.`t1`.`a` DIV `test`.`t1`.`b`' Looks wrong. The expected result is to return -1 .
            bar Alexander Barkov added a comment - - edited

            A similar problem:

            I start mysql --column-type-info test and run this query:

            SELECT 1 DIV '10e-15';
            

            Field   1:  `1 DIV '10e-15'`
            Catalog:    `def`
            Database:   ``
            Table:      ``
            Org_table:  ``
            Type:       LONG
            Collation:  binary (63)
            Length:     1
            Max_length: 15
            Decimals:   0
            Flags:      BINARY NUM 
             
            +-----------------+
            | 1 DIV '10e-15'  |
            +-----------------+
            | 100000000000000 |
            +-----------------+
            

            Notice, the data type LONG is wrong. It does not fit the result. If I now try to create a table field from the same expression, it fails:

            CREATE OR REPLACE TABLE t1 AS SELECT 1 DIV '10e-15' AS c1;
            

            ERROR 1264 (22003): Out of range value for column '1 DIV '10e-15'' at row 1
            

            It's expected to create a LONGLONG column.

            bar Alexander Barkov added a comment - - edited A similar problem: I start mysql --column-type-info test and run this query: SELECT 1 DIV '10e-15' ; Field 1: `1 DIV '10e-15'` Catalog: `def` Database: `` Table: `` Org_table: `` Type: LONG Collation: binary (63) Length: 1 Max_length: 15 Decimals: 0 Flags: BINARY NUM   +-----------------+ | 1 DIV '10e-15' | +-----------------+ | 100000000000000 | +-----------------+ Notice, the data type LONG is wrong. It does not fit the result. If I now try to create a table field from the same expression, it fails: CREATE OR REPLACE TABLE t1 AS SELECT 1 DIV '10e-15' AS c1; ERROR 1264 (22003): Out of range value for column '1 DIV '10e-15'' at row 1 It's expected to create a LONGLONG column.

            A similar problem:

            CREATE OR REPLACE TABLE t1 AS SELECT TIME'00:00:01.000001' DIV 1 AS c1;
            DESC t1;
            

            +-------+------------+------+-----+---------+-------+
            | Field | Type       | Null | Key | Default | Extra |
            +-------+------------+------+-----+---------+-------+
            | c1    | bigint(17) | YES  |     | NULL    |       |
            +-------+------------+------+-----+---------+-------+
            

            The data type looks too excessive. Maximum possible length should be 7 digits for hhhmmss, plus optional sign.

            bar Alexander Barkov added a comment - A similar problem: CREATE OR REPLACE TABLE t1 AS SELECT TIME '00:00:01.000001' DIV 1 AS c1; DESC t1; +-------+------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------------+------+-----+---------+-------+ | c1 | bigint(17) | YES | | NULL | | +-------+------------+------+-----+---------+-------+ The data type looks too excessive. Maximum possible length should be 7 digits for hhhmmss, plus optional sign.

            People

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

              Dates

                Created:
                Updated:

                Git Integration

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