|
SET sql_mode='STRICT_ALL_TABLES,STRICT_TRANS_TABLES';
|
CREATE OR REPLACE TABLE t1 AS SELECT
|
0xFFFFFFFFFFFFFFFF+0 AS c1,
|
FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,
|
CEILING(0xFFFFFFFFFFFFFFFF) AS c3,
|
ROUND(0xFFFFFFFFFFFFFFFF) AS c4,
|
TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5;
|
ERROR 1264 (22003): Out of range value for column 'c4' at row
|
Looks bad. No error expected.
SET sql_mode='';
|
CREATE OR REPLACE TABLE t1 AS SELECT
|
0xFFFFFFFFFFFFFFFF+0 AS c1,
|
FLOOR(0xFFFFFFFFFFFFFFFF) AS c2,
|
CEILING(0xFFFFFFFFFFFFFFFF) AS c3,
|
ROUND(0xFFFFFFFFFFFFFFFF) AS c4,
|
TRUNCATE(0xFFFFFFFFFFFFFFFF,0) AS c5;
|
Query OK, 1 row affected, 2 warnings (0.022 sec)
|
Records: 1 Duplicates: 0 Warnings: 2
|
Ok. With a loose mode it worked, but with warnings. Warnings are not expected.
*************************** 1. row ***************************
|
c1: 18446744073709551615
|
c2: 9223372036854775808
|
c3: 9223372036854775808
|
c4: 100000000000000000
|
c5: 100000000000000000
|
1 row in set (0.001 sec)
|
The result for all columns is wrong. Expect 18446744073709551615 for all columns.
+-------+-----------------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+-------+-----------------------+------+-----+---------+-------+
|
| c1 | bigint(21) unsigned | NO | | NULL | |
|
| c2 | bigint(10) unsigned | NO | | NULL | |
|
| c3 | bigint(10) unsigned | NO | | NULL | |
|
| c4 | double(17,0) unsigned | NO | | NULL | |
|
| c5 | double(17,0) unsigned | NO | | NULL | |
|
+-------+-----------------------+------+-----+---------+-------+
|
The data types for all columns except c1 are wrong. Expect BIGINT(21) UNSIGNED for all columns.
|