[MDEV-12769] Arithmetic operators with temporal types create excessive column types - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: 10.3.1
Component/s: Temporal Types
Labels:
None

Description

CREATE OR REPLACE TABLE t1 AS SELECT

  TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,

  TIME'10:20:30.123'+0 AS c2,

  DATE'2001-01-01'+0 AS c3;

SHOW CREATE TABLE t1;

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Table | Create Table                                                                                                                                                                                    |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| t1    | CREATE TABLE `t1` (

  `c1` decimal(24,3) NOT NULL DEFAULT '0.000',

  `c2` decimal(15,3) NOT NULL DEFAULT '0.000',

  `c3` bigint(12) NOT NULL DEFAULT '0'

) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The above types are excessive. This happens because Item::decimal_precision() treats temporal types as usual strings and uses max_length to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).

The correct integer part precision would be:

14 for DATETIME and TIMESTAMP (YYYYMMDDhhmmss)
7 for TIME (hhhmmss)
8 for DATE (YYYYMMDD)

Plus adds one digit to the integer part.

The expected result for the above query would be:

15 integer part digits for c1, which gives decimal(18,3)
8 integer part digits for c2, which gives decimal(11,3)
9 integer part digits for c3, which should give int(9), but DATE expression also do not set unsigned_flag properly, so int(10) would be ok until the unsigned_flag is fixed.

Attachments

Issue Links

blocks

MDEV-4912 Data type plugin API version 1

Closed

relates to

MDEV-12770 Add Type_handler::decimal_precision()

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-05-10 03:32

Alexander Barkov made changes - 2017-05-10 03:32

Field	Original Value	New Value
Link		This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov made changes - 2017-05-10 03:34

Description

{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT
  TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,
  TIME'10:20:30.123'+0 AS c2,
  DATE'2001-01-01'+0 AS c3;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `c1` decimal(24,3) NOT NULL DEFAULT '0.000',
  `c2` decimal(15,3) NOT NULL DEFAULT '0.000',
  `c3` bigint(12) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{noformat}

The above types are excessive. This happens because {{Item::decimal_precision()}} treats temporal types as usual strings and uses {{max_length}} to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).

The correct {{decimal_precision()}} would be:
- 14 for {{DATETIME}} and {{TIMESTAMP}} ({{YYYYMMDDhhmmss}})
- 7 for {{TIME}} ({{hhhmmss}})
- 8 for {{DATE}} ({{YYYYMMDD}})

Plus adds one digit to the integer part.

The expected result for the above query would be:
- 15 integer part digits for {{c1}}, which gives {{decimal(18,3)}}
- 8 integer part digits for {{c2}}, which gives {{decimal(11,3}}}}
- 9 integer part digits for {{c3}}, which should give {{int(9)}}, but {{DATE}} expression also do not set {{unsigned_flag}} properly, so {{int(10)}} would be ok until the {{unsigned_flag}} is fixed.

{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT
  TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,
  TIME'10:20:30.123'+0 AS c2,
  DATE'2001-01-01'+0 AS c3;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `c1` decimal(24,3) NOT NULL DEFAULT '0.000',
  `c2` decimal(15,3) NOT NULL DEFAULT '0.000',
  `c3` bigint(12) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{noformat}

The above types are excessive. This happens because {{Item::decimal_precision()}} treats temporal types as usual strings and uses {{max_length}} to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).

The correct integer part precision would be:
- 14 for {{DATETIME}} and {{TIMESTAMP}} ({{YYYYMMDDhhmmss}})
- 7 for {{TIME}} ({{hhhmmss}})
- 8 for {{DATE}} ({{YYYYMMDD}})

Plus adds one digit to the integer part.

The expected result for the above query would be:
- 15 integer part digits for {{c1}}, which gives {{decimal(18,3)}}
- 8 integer part digits for {{c2}}, which gives {{decimal(11,3}}}}
- 9 integer part digits for {{c3}}, which should give {{int(9)}}, but {{DATE}} expression also do not set {{unsigned_flag}} properly, so {{int(10)}} would be ok until the {{unsigned_flag}} is fixed.

Alexander Barkov made changes - 2017-05-10 03:34

Description

{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT
  TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,
  TIME'10:20:30.123'+0 AS c2,
  DATE'2001-01-01'+0 AS c3;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `c1` decimal(24,3) NOT NULL DEFAULT '0.000',
  `c2` decimal(15,3) NOT NULL DEFAULT '0.000',
  `c3` bigint(12) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{noformat}

The above types are excessive. This happens because {{Item::decimal_precision()}} treats temporal types as usual strings and uses {{max_length}} to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).

The correct integer part precision would be:
- 14 for {{DATETIME}} and {{TIMESTAMP}} ({{YYYYMMDDhhmmss}})
- 7 for {{TIME}} ({{hhhmmss}})
- 8 for {{DATE}} ({{YYYYMMDD}})

Plus adds one digit to the integer part.

The expected result for the above query would be:
- 15 integer part digits for {{c1}}, which gives {{decimal(18,3)}}
- 8 integer part digits for {{c2}}, which gives {{decimal(11,3}}}}
- 9 integer part digits for {{c3}}, which should give {{int(9)}}, but {{DATE}} expression also do not set {{unsigned_flag}} properly, so {{int(10)}} would be ok until the {{unsigned_flag}} is fixed.

{code:sql}
CREATE OR REPLACE TABLE t1 AS SELECT
  TIMESTAMP'2004-01-01 10:20:30.123'+0 AS c1,
  TIME'10:20:30.123'+0 AS c2,
  DATE'2001-01-01'+0 AS c3;
SHOW CREATE TABLE t1;
{code}
{noformat}
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
  `c1` decimal(24,3) NOT NULL DEFAULT '0.000',
  `c2` decimal(15,3) NOT NULL DEFAULT '0.000',
  `c3` bigint(12) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
{noformat}

The above types are excessive. This happens because {{Item::decimal_precision()}} treats temporal types as usual strings and uses {{max_length}} to calculate precision. This is wrong, because it does not exclude the formatting characters (dashes and semicolons).

The correct integer part precision would be:
- 14 for {{DATETIME}} and {{TIMESTAMP}} ({{YYYYMMDDhhmmss}})
- 7 for {{TIME}} ({{hhhmmss}})
- 8 for {{DATE}} ({{YYYYMMDD}})

Plus adds one digit to the integer part.

The expected result for the above query would be:
- 15 integer part digits for {{c1}}, which gives {{decimal(18,3)}}
- 8 integer part digits for {{c2}}, which gives {{decimal(11,3)}}
- 9 integer part digits for {{c3}}, which should give {{int(9)}}, but {{DATE}} expression also do not set {{unsigned_flag}} properly, so {{int(10)}} would be ok until the {{unsigned_flag}} is fixed.

Alexander Barkov made changes - 2017-05-10 03:36

Link

This issue relates to ~~MDEV-12770~~ [ ~~MDEV-12770~~ ]

Alexander Barkov made changes - 2017-05-10 04:32

Fix Version/s		10.3.1 [ 22532 ]
Fix Version/s	10.3 [ 22126 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

Alexander Barkov added a comment - 2017-05-10 04:32

The patch for ~~MDEV-12770~~ fixed this problem.

Alexander Barkov added a comment - 2017-05-10 04:32 The patch for MDEV-12770 fixed this problem.

Sergei Golubchik made changes - 2021-12-06 21:44

Workflow

MariaDB v3 [ 80704 ]

MariaDB v4 [ 152139 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-05-10 03:32

Updated:: 2017-05-10 04:32

Resolved:: 2017-05-10 04:32

Git Integration

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

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration