DOUBLE
This page is part of MariaDB's Documentation.
The parent of this page is: Data Types for MariaDB Xpand
Topics on this page:
Overview
Double-precision floating-point number.
USAGE
DOUBLE[(<precision>[,<scale>])] [SIGNED | UNSIGNED] [ZEROFILL]
DETAILS
SYNONYMS
The following are synonyms for DOUBLE:
DOUBLE PRECISION
REAL
EXAMPLES
SIGNED and UNSIGNED
The DOUBLE data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).
Example of DOUBLE SIGNED (the default):
CREATE TABLE double_signed_example (
description VARCHAR(20),
example DOUBLE,
sz6_2 DOUBLE(6,2),
sz20_19 DOUBLE(20,19) SIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO double_signed_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Negative', -1234.567890123, -1234.567890123, -1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM double_signed_example;
+-------------+-----------------+----------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+-----------------+----------+------------------------+
| Pi | 3.1415926535898 | 3.14 | 3.1415926535897931160 |
| Series | 1234.567890123 | 1234.57 | 1.2345678901229999447 |
| Negative | -1234.567890123 | -1234.57 | -1.2345678901229999447 |
| Various | 1234567890 | 9999.99 | 10.0000000000000000000 |
+-------------+-----------------+----------+------------------------+
Example of DOUBLE UNSIGNED:
CREATE TABLE double_unsigned_example (
description VARCHAR(20),
example DOUBLE UNSIGNED,
sz6_2 DOUBLE(6,2) UNSIGNED,
sz20_19 DOUBLE(20,19) UNSIGNED
);
SET @pi = 3.1415926535897932384626433832795;
INSERT INTO double_unsigned_example VALUES
('Pi', @pi, @pi, @pi),
('Series', 1234.567890123, 1234.567890123, 1.234567890123),
('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM double_unsigned_example;
+-------------+-----------------+---------+------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------+-----------------+---------+------------------------+
| Pi | 3.1415926535898 | 3.14 | 3.1415926535897931160 |
| Series | 1234.567890123 | 1234.57 | 1.2345678901229999447 |
| Various | 1234567890 | 9999.99 | 10.0000000000000000000 |
+-------------+-----------------+---------+------------------------+
Out-of-Range
A value is considered "out-of-range" when it is too small or too large to be stored in a data type. The size specified when creating the column is the limit for what values can be represented. The limits can also vary based on your hardware and operating system. The value is rounded to the nearest valid value.
TRUNCATE double_signed_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO double_signed_example VALUES
('OK', 1.7976931348623157e308, NULL, NULL),
('OK', -1.7976931348623157e308, NULL, NULL),
('OK', NULL, 9999.99, NULL),
('Rounded overflow', NULL, 9999.995, NULL),
('Overflow', NULL, 10000, NULL),
('OK', NULL, -9999.99, NULL),
('Rounded underflow', NULL, -9999.995, NULL),
('Underflow', NULL, -10000, NULL),
('OK', NULL, NULL, 9.9999999999999999),
('Rounded OK', NULL, NULL, 9.99999999999999995),
('Overflow', NULL, NULL, 11),
('OK', NULL, NULL, -9.9999999999999999),
('Rounded OK', NULL, NULL, -9.99999999999999995),
('Underflow', NULL, NULL, -11);
INSERT INTO double_signed_example VALUES
('Impossible', 1.798e308, NULL, NULL);
ERROR 1367 (HY000): [32773] Illegal value for type: illegal floating-point constant '1.798e308'
SELECT * FROM double_signed_example;
+-------------------+-----------------------+-----------+-------------------------+
| description | example | sz6_2 | sz20_19 |
+-------------------+-----------------------+-----------+-------------------------+
| OK | 1.7976931348623e+308 | NULL | NULL |
| OK | -1.7976931348623e+308 | NULL | NULL |
| OK | NULL | 9999.99 | NULL |
| Rounded overflow | NULL | 10000.00 | NULL |
| Overflow | NULL | 10000.00 | NULL |
| OK | NULL | -9999.99 | NULL |
| Rounded underflow | NULL | -10000.00 | NULL |
| Underflow | NULL | -10000.00 | NULL |
| OK | NULL | NULL | 10.0000000000000000000 |
| Rounded OK | NULL | NULL | 10.0000000000000000000 |
| Overflow | NULL | NULL | 11.0000000000000000000 |
| OK | NULL | NULL | -10.0000000000000000000 |
| Rounded OK | NULL | NULL | -10.0000000000000000000 |
| Underflow | NULL | NULL | -11.0000000000000000000 |
+-------------------+-----------------------+-----------+-------------------------+
TRUNCATE double_unsigned_example;
-- Disable strict mode or the inserts will fail
SET sql_mode=(SELECT REPLACE(@@sql_mode, 'STRICT_TRANS_TABLES', ''));
INSERT INTO double_unsigned_example VALUES
('OK', 1.7976931348623157e308, NULL, NULL),
('Underflow', -1, NULL, NULL);
INSERT INTO double_signed_example VALUES
('Impossible', 1.798e308, NULL, NULL);
ERROR 1367 (HY000): [32773] Illegal value for type: illegal floating-point constant '1.798e308'
SELECT * FROM double_unsigned_example;
+-------------+----------------------+-------+---------+
| description | example | sz6_2 | sz20_19 |
+-------------+----------------------+-------+---------+
| OK | 1.7976931348623e+308 | NULL | NULL |
| Underflow | 0 | NULL | NULL |
+-------------+----------------------+-------+---------+
DOUBLE ZEROFILL
While Xpand accepts the DOUBLE ZEROFILL type on table create, the qualifier is ignored and silently dropped:
CREATE TABLE double_zerofill_example (
example DOUBLE ZEROFILL
);
SHOW CREATE TABLE double_zerofill_example\G
*************************** 1. row ***************************
Table: double_zerofill_example
Create Table: CREATE TABLE `double_zerofill_example` (
`example` double
) CHARACTER SET utf8 /*$ SLICES=3 */