DOUBLE

USAGE

DETAILS

SYNONYMS

The following are synonyms for DOUBLE:

  • DOUBLE PRECISION

  • FLOAT8

  • REAL

SCHEMA

PARAMETERS

SKYSQL

PRIVILEGES

EXAMPLES

SIGNED and UNSIGNED

The DOUBLE data type may be SIGNED (allowing negative values) or UNSIGNED (not allowing negative values).

Example of DOUBLE SIGNED (SIGNED is 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.141592653589793 |     3.14 |  3.1415926535897930000 |
| Series      |    1234.567890123 |  1234.57 |  1.2345678901230000000 |
| Negative    |   -1234.567890123 | -1234.57 | -1.2345678901230000000 |
| 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.141592653589793 |    3.14 |  3.1415926535897930000 |
| Series      |    1234.567890123 | 1234.57 |  1.2345678901230000000 |
| 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. When SQL_MODE is strict (the default) an out-of-range value generates an error and the operation fails. If strict mode is not in effect, the value is rounded to the nearest valid value and a warning is generated (which might be hidden, depending on your warning settings).

A value whose significant digits must be rounded to fit only generates a warning note about data truncation, since it is only an out-of-range value if the rounding causes the value to overflow. A somewhat strange exception happens when the decimal places are 16 digits or larger: at that point the value can round up to be one digit larger than you would expect to be accepted, but only for the next larger power of 10. For instance, a FLOAT(17,16) should max out at 9.9999999999999999, but that value rounds up to being equivalent to 10 (and 11 overflows).

Note that since a DOUBLE stores the largest and smallest possible numbers that the server can represent, you will get an error trying to specify a number so large or small that an INSERT would get an error/warning trying to process it.

TRUNCATE double_signed_example;

-- Disable strict mode
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);
Warning (Code 1264): Out of range value for column 'sz6_2' at row 4
Warning (Code 1264): Out of range value for column 'sz6_2' at row 5
Warning (Code 1264): Out of range value for column 'sz6_2' at row 7
Warning (Code 1264): Out of range value for column 'sz6_2' at row 8
Warning (Code 1264): Out of range value for column 'sz20_19' at row 11
Warning (Code 1264): Out of range value for column 'sz20_19' at row 14
INSERT INTO double_signed_example VALUES
  ('Impossible', 1.798e308, NULL, NULL);
ERROR 1367 (22007): Illegal double '1.798e308' value found during parsing
SELECT * FROM double_signed_example;
+-------------------+-------------------------+----------+-------------------------+
| description       | example                 | sz6_2    | sz20_19                 |
+-------------------+-------------------------+----------+-------------------------+
| OK                |  1.7976931348623157e308 |     NULL |                    NULL |
| OK                | -1.7976931348623157e308 |     NULL |                    NULL |
| OK                |                    NULL |  9999.99 |                    NULL |
| Rounded overflow  |                    NULL |  9999.99 |                    NULL |
| Overflow          |                    NULL |  9999.99 |                    NULL |
| OK                |                    NULL | -9999.99 |                    NULL |
| Rounded underflow |                    NULL | -9999.99 |                    NULL |
| Underflow         |                    NULL | -9999.99 |                    NULL |
| OK                |                    NULL |     NULL |  10.0000000000000000000 |
| Rounded OK        |                    NULL |     NULL |  10.0000000000000000000 |
| Overflow          |                    NULL |     NULL |  10.0000000000000000000 |
| OK                |                    NULL |     NULL | -10.0000000000000000000 |
| Rounded OK        |                    NULL |     NULL | -10.0000000000000000000 |
| Underflow         |                    NULL |     NULL | -10.0000000000000000000 |
+-------------------+-------------------------+----------+-------------------------+
TRUNCATE double_unsigned_example;

-- Disable strict mode
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);
Warning (Code 1264): Out of range value for column 'example' at row 2
INSERT INTO double_signed_example VALUES
  ('Impossible', 1.798e308, NULL, NULL);
ERROR 1367 (22007): Illegal double '1.798e308' value found during parsing
SELECT * FROM double_unsigned_example;
+-------------+------------------------+-------+---------+
| description | example                | sz6_2 | sz20_19 |
+-------------+------------------------+-------+---------+
| OK          | 1.7976931348623157e308 |  NULL |    NULL |
| Underflow   |                      0 |  NULL |    NULL |
+-------------+------------------------+-------+---------+

DOUBLE ZEROFILL

A special type of DOUBLE UNSIGNED is DOUBLE ZEROFILL, which pads out the values with leading zeros in SELECT results. The number of leading zeros are just enough to pad the field out to the length of the type's field size (counting the decimal place), but the zeros are not included in an expression result or in a UNION SELECT column.

Using DOUBLE ZEROFILL works the same way as DOUBLE UNSIGNED for most operations except a simple SELECT. For example, with the following test table setup:

CREATE TABLE double_zerofill_example (
  description VARCHAR(20),
  example DOUBLE ZEROFILL,
  sz6_2 DOUBLE(6,2) ZEROFILL,
  sz20_19 DOUBLE(20,19) ZEROFILL
);
SET @pi = 3.1415926535897932384626433832795;

INSERT INTO double_zerofill_example VALUES
  ('Pi', @pi, @pi, @pi),
  ('Series', 1234.567890123, 1234.567890123, 1.234567890123),
  ('Various', 1234567890, 9999.99, 9.9999999999999999999);
SELECT * FROM double_zerofill_example;
+-------------+------------------------+---------+------------------------+
| description | example                | sz6_2   | sz20_19                |
+-------------+------------------------+---------+------------------------+
| Pi          | 000003.141592653589793 |  003.14 |  3.1415926535897930000 |
| Series      | 000000001234.567890123 | 1234.57 |  1.2345678901230000000 |
| Various     | 0000000000001234567890 | 9999.99 | 10.0000000000000000000 |
+-------------+------------------------+---------+------------------------+

ERROR HANDLING

FEATURE INTERACTION

RESPONSES

DIAGNOSIS

ISO 9075:2016

CHANGE HISTORY

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.6 Community

  • Present starting in MariaDB Community Server 10.6.0.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.5 Community

  • Present starting in MariaDB Community Server 10.5.0.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

10.4 Community

  • Present starting in MariaDB Community Server 10.4.0.

10.3 Enterprise

  • Present starting in MariaDB Enterprise Server 10.3.16-1.

10.3 Community

  • Present starting in MariaDB Community Server 10.3.0.

10.2 Enterprise

  • Present starting in MariaDB Enterprise Server 10.2.25-1.

10.2 Community

  • Present starting in MariaDB Community Server 10.2.0.

Release Series

History

23.08 Enterprise

  • Present starting in MariaDB Enterprise Server 23.08.0.

23.07 Enterprise

  • Present starting in MariaDB Enterprise Server 23.07.0.

10.6 Enterprise

  • Present starting in MariaDB Enterprise Server 10.6.4-1.

10.5 Enterprise

  • Present starting in MariaDB Enterprise Server 10.5.3-1.

10.4 Enterprise

  • Present starting in MariaDB Enterprise Server 10.4.6-1.

EXTERNAL REFERENCES