[MDEV-17318] CAST(LEAST(zero_date,non_zero_date) AS numeric_data_type) returns a wrong result - Jira

Details

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

Description

SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';

SELECT

 LEAST('0000-00-00',DATE'2001-01-01') AS c0,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint,

 CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint;

*************************** 1. row ***************************

      c0: NULL

  string: NULL

    date: NULL

datetime: NULL

    time: 00:00:00

      dc: 0

     dbl: 0

    sint: 0

    uint: 0

The expected result is to return NULL for all columns.

Attachments

Issue Links

blocks

MDEV-17317 Add THD* parameter into Item::get_date() and stricter data type control to "fuzzydate"

Closed

relates to

MDEV-17330 Wrong result for 0 + LEAST(TIME'-10:00:00',TIME'10:00:00')

Closed

MDEV-16991 Rounding vs truncation for TIME, DATETIME, TIMESTAMP

Closed

MDEV-17329 Inconsistency of NULL date value conversion to zero date in comparison context

Open

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2018-09-28 12:11

Alexander Barkov made changes - 2018-09-28 12:11

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

Alexander Barkov made changes - 2018-09-29 12:15

Link

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

Alexander Barkov made changes - 2018-09-29 18:25

Description

{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
LEAST('0000-00-00',DATE'2001-01-01') AS c0,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint;
{code}
{noformat}
*************************** 1. row ***************************
      c0: NULL
  string: NULL
    date: NULL
datetime: NULL
    time: 00:00:00
      dc: 0
     dbl: 0
    sint: 0
    uint: 0
{noformat}

The expected result is to return NULL for all columns.

{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
LEAST('0000-00-00',DATE'2001-01-01') AS c0,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint,https://jira.mariadb.org/browse/MDEV-17318#
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint;
{code}
{noformat}
*************************** 1. row ***************************
      c0: NULL
  string: NULL
    date: NULL
datetime: NULL
    time: 00:00:00
      dc: 0
     dbl: 0
    sint: 0
    uint: 0
{noformat}

The expected result is to return NULL for all columns.

Alexander Barkov made changes - 2018-09-29 18:26

Description

{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT
LEAST('0000-00-00',DATE'2001-01-01') AS c0,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS CHAR) AS string,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATE) AS date,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DATETIME) AS datetime,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS TIME) AS time,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DECIMAL) AS dc,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS DOUBLE) AS dbl,
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS SIGNED) AS sint,https://jira.mariadb.org/browse/MDEV-17318#
CAST(LEAST('0000-00-00',DATE'2001-01-01') AS UNSIGNED) AS uint;
{code}
{noformat}
*************************** 1. row ***************************
      c0: NULL
  string: NULL
    date: NULL
datetime: NULL
    time: 00:00:00
      dc: 0
     dbl: 0
    sint: 0
    uint: 0
{noformat}

The expected result is to return NULL for all columns.

Alexander Barkov made changes - 2018-09-30 12:02

Link

This issue relates to MDEV-17329 [ MDEV-17329 ]

Alexander Barkov made changes - 2018-09-30 12:03

Comment

[ A similar problem is repeatable with the DATE function.
Note, with NO_ZERO_DATE, zero date literals are even not accepted:
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE'0000-00-00' < DATE'2001-01-01';
{code}
{noformat}
ERROR 1525 (HY000): Incorrect DATE value: '0000-00-00'
{noformat}
Looks fine so far.

DATE() as a function returns NULL when not in comparison context, but not-null in comparison context.
{code:sql}
SET sql_mode='NO_ZERO_DATE,NO_ZERO_IN_DATE';
SELECT DATE('0000-00-00'),DATE('0000-00-00') < DATE'2001-01-01';
{code}
{noformat}
+--------------------+---------------------------------------+
| DATE('0000-00-00') | DATE('0000-00-00') < DATE'2001-01-01' |
+--------------------+---------------------------------------+
| NULL | 1 |
+--------------------+---------------------------------------+
{noformat}
The second column should also return NULL.
]

Alexander Barkov made changes - 2018-09-30 13:14

Link

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

Alexander Barkov made changes - 2018-09-30 13:25

issue.field.resolutiondate

2018-09-30 13:25:26.0

2018-09-30 13:25:26.023

Alexander Barkov made changes - 2018-09-30 13:25

Component/s		Data types [ 13906 ]
Component/s		Temporal Types [ 11000 ]
Fix Version/s		10.4.0 [ 23115 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

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

Workflow

MariaDB v3 [ 89810 ]

MariaDB v4 [ 154988 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 1 Start watching this issue

Dates

Created:: 2018-09-28 12:11

Updated:: 2018-09-30 13:25

Resolved:: 2018-09-30 13:25

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