|
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).
|
|
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.
|
|
A similar problem:
I start mysql --column-type-info test and run this query:
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.
|