[MDEV-12860] Out-of-range error on CREATE..SELECT with a view using MAX and EXTRACT(MINUTE_MICROSECOND..) - 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: OTHER
Labels:
- datatype

Description

I run this script:

SET sql_mode=STRICT_ALL_TABLES;

DROP TABLE IF EXISTS t1;

CREATE TABLE t1 (

  id bigint(11) NOT NULL PRIMARY KEY,

  dt datetime(6)

);

INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');

CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;

DESCRIBE v1;

SELECT * FROM v1;

It correctly reports that the data type for EXTRACT(MINUTE_MICROSECOND) is BIGINT:

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

| Field | Type       | Null | Key | Default | Extra |

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

| dt2   | bigint(11) | YES  |     | NULL    |       |

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

and returns the correct value which is outside of the signed 32-bit range:

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

| dt2        |

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

| 2233123456 |

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

Now I want to create a table as follows:

CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1;

It unexpectedly returns an error:

ERROR 1264 (22003): Out of range value for column 'MAX(dt2)' at row 2

Adding LIMIT 0 shows that it erroneously creates an INT rather than BIGINT column:

CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1 LIMIT 0;

DESCRIBE t2;

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

| Field    | Type    | Null | Key | Default | Extra |

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

| MAX(dt2) | int(11) | YES  |     | NULL    |       |

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

The problem is in Item_sum::create_tmp_field:

  Field *create_tmp_field(bool group, TABLE *table)

    return Item::create_tmp_field(group, table, MY_INT32_NUM_DECIMAL_DIGITS);

It creates BIGINT only if max_char_length() is greater than MY_INT32_NUM_DECIMAL_DIGITS, i.e. starting from 12.

Attachments

Issue Links

blocks

MDEV-4912 Data type plugin API version 1

Closed

relates to

MDEV-12858 Out-of-range error for CREATE..SELECT unsigned_int_column+1

Closed

MDEV-12859 Out-of-range error for CREATE..SELECT @a:=EXTRACT(MINUTE_MICROSECOND FROM..)

Closed

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-05-22 04:43

Alexander Barkov made changes - 2017-05-22 04:43

Field	Original Value	New Value
Component/s		OTHER [ 10125 ]

Alexander Barkov made changes - 2017-05-22 04:43

Labels

datatype

Alexander Barkov made changes - 2017-05-22 04:44

Link

This issue blocks ~~MDEV-4912~~ [ ~~MDEV-4912~~ ]

Alexander Barkov made changes - 2017-05-22 04:44

Link

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

Alexander Barkov made changes - 2017-05-22 04:44

Link

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

Alexander Barkov made changes - 2017-05-22 04:45

Description

I run this script:
{code:sql}
SET sql_mode=STRICT_ALL_TABLES;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id bigint(11) NOT NULL PRIMARY KEY,
  dt datetime(6)
);
INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');
CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;
DESCRIBE v1;
SELECT * FROM v1;
{code}
It correctly reports that the data type for {{EXTRACT(MINUTE_MICROSECOND)}} is {{BIGINT}}:
{noformat}
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| dt2 | bigint(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
{noformat}
and returns the correct value which is outside of the 32-bit range:
{noformat}
+------------+
| dt2 |
+------------+
| 2233123456 |
+------------+
{noformat}

Now I want to create a table as follows:
{code:sql}
CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1;
{code}
It unexpectedly returns an error:
{noformat}
ERROR 1264 (22003): Out of range value for column 'MAX(dt2)' at row 2
{noformat}

Adding {{LIMIT 0}} shows that it erroneously creates an {{INT}} rather than {{BIGINT}} column:
{code:sql}
CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1 LIMIT 0;
DESCRIBE t2;
{code}
{noformat}
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| MAX(dt2) | int(11) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
{noformat}

The problem is in {{Item_sum::create_tmp_field}}:
{code:cpp}
  Field *create_tmp_field(bool group, TABLE *table)
  {
    return Item::create_tmp_field(group, table, MY_INT32_NUM_DECIMAL_DIGITS);
  }
{code}
It creates {{BIGINT}} only if {{max_char_length()}} is greater than {{MY_INT32_NUM_DECIMAL_DIGITS}}, i.e. starting from {{12}}.

I run this script:
{code:sql}
SET sql_mode=STRICT_ALL_TABLES;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
  id bigint(11) NOT NULL PRIMARY KEY,
  dt datetime(6)
);
INSERT INTO t1 VALUES (1,'2001-01-01 11:22:33.123456');
CREATE OR REPLACE VIEW v1 AS SELECT EXTRACT(MINUTE_MICROSECOND FROM dt) AS dt2 FROM t1;
DESCRIBE v1;
SELECT * FROM v1;
{code}
It correctly reports that the data type for {{EXTRACT(MINUTE_MICROSECOND)}} is {{BIGINT}}:
{noformat}
+-------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| dt2 | bigint(11) | YES | | NULL | |
+-------+------------+------+-----+---------+-------+
{noformat}
and returns the correct value which is outside of the signed 32-bit range:
{noformat}
+------------+
| dt2 |
+------------+
| 2233123456 |
+------------+
{noformat}

Now I want to create a table as follows:
{code:sql}
CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1;
{code}
It unexpectedly returns an error:
{noformat}
ERROR 1264 (22003): Out of range value for column 'MAX(dt2)' at row 2
{noformat}

Adding {{LIMIT 0}} shows that it erroneously creates an {{INT}} rather than {{BIGINT}} column:
{code:sql}
CREATE OR REPLACE TABLE t2 AS SELECT MAX(dt2) FROM v1 LIMIT 0;
DESCRIBE t2;
{code}
{noformat}
+----------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------+------+-----+---------+-------+
| MAX(dt2) | int(11) | YES | | NULL | |
+----------+---------+------+-----+---------+-------+
{noformat}

The problem is in {{Item_sum::create_tmp_field}}:
{code:cpp}
  Field *create_tmp_field(bool group, TABLE *table)
  {
    return Item::create_tmp_field(group, table, MY_INT32_NUM_DECIMAL_DIGITS);
  }
{code}
It creates {{BIGINT}} only if {{max_char_length()}} is greater than {{MY_INT32_NUM_DECIMAL_DIGITS}}, i.e. starting from {{12}}.

Alexander Barkov made changes - 2017-05-22 05:24

Status

Open [ 1 ]

In Progress [ 3 ]

Alexander Barkov made changes - 2017-05-22 06:35

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

Alexander Barkov added a comment - 2017-05-22 06:35

Pushed to bb-10.2-ext

Alexander Barkov added a comment - 2017-05-22 06:35 Pushed to bb-10.2-ext

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

Workflow

MariaDB v3 [ 80872 ]

MariaDB v4 [ 152190 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2017-05-22 04:43

Updated:: 2017-05-22 06:35

Resolved:: 2017-05-22 06:35

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