[MDEV-12691] Wrong result for: bigint_column BETWEEN time_column AND str_literal - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Cannot Reproduce
Affects Version/s: 10.1(EOL), 10.2(EOL), 10.3(EOL)
Fix Version/s: N/A
Component/s: Data types
Labels:
None

Epic Link:
Data type cleanups

Description

I run this script:

CREATE OR REPLACE TABLE t1 (a TIME, b TIME);

INSERT INTO t1 VALUES ('00:00:20','00:00:00');

SELECT a BETWEEN b AND '10:00:00' FROM t1;

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

| a BETWEEN b AND '10:00:00' |

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

|                          1 |

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

It correctly returns 1, because comparison is done as TIME, and '00:00:20' is really between '00:00:00' AND '10:00:00'.

Now I change the data type for the column a from TIME to INT. Nothing should change: INT, TIME and STRING arguments should be compared as TIME. So BETWEEN should still return 1.

ALTER TABLE t1 MODIFY a INT;

SELECT a BETWEEN b AND '10:00:00' FROM t1;

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

| a BETWEEN b AND '10:00:00' |

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

|                          1 |

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

Works fine so far.

Now I change the data type for a from INT to BIGINT. Nothing should change. It should still return 1. However, something different happens:

ALTER TABLE t1 MODIFY a BIGINT;

SELECT a BETWEEN b AND '10:00:00' FROM t1;

SHOW WARNINGS;

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

| a BETWEEN b AND '10:00:00' |

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

|                          0 |

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

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

| Level   | Code | Message                                      |

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

| Warning | 1292 | Truncated incorrect DOUBLE value: '10:00:00' |

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

Notice, the result changed to 0, and now it returns a warning.
The expected behavior would be to return 1 without warnings.

Now if I wrap a into a function, it returns 1 again:

SELECT COALESCE(a) BETWEEN b AND '10:00:00' FROM t1;

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

| COALESCE(a) BETWEEN b AND '10:00:00' |

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

|                                    1 |

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

Attachments

Activity

Ascending order - Click to sort in descending order

Alexander Barkov created issue - 2017-05-04 13:48

Alexander Barkov made changes - 2017-05-04 13:48

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

Alexander Barkov made changes - 2017-05-04 13:50

Description

{code:sql}
CREATE OR REPLACE TABLE t1 (a TIME, b TIME);
INSERT INTO t1 VALUES ('00:00:20','00:00:00');
SELECT a BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 1 |
+----------------------------+
{noformat}

Now I change the data type for the column {{a}} from {{TIME}} to {{INT}}. Nothing should change: {{INT}}, {{TIME}} and {{STRING}} arguments should be compared as {{TIME}}. So {{BETWEEN}} should still return {{1}}.
{code:sql}
ALTER TABLE t1 MODIFY a INT;
SELECT a BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 1 |
+----------------------------+
{noformat}
Works fine so far.

Now I change the data type for {{a}} from {{INT}} to {{BIGINT}}. Nothing should change. It should still return {{1}}. However, something different happens:

{code:sql}
ALTER TABLE t1 MODIFY a BIGINT;
SELECT a BETWEEN b AND '10:00:00' FROM t1;
SHOW WARNINGS;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 0 |
+----------------------------+
{noformat}
{noformat}
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '10:00:00' |
+---------+------+----------------------------------------------+
{noformat}
Notice, the result changed to {{0}}, and now it returns a warning.
The expected behavior would be to return {{1}} without warnings.

Now if I wrap {{a}} into a function, it returns {{1}} again:
{code:sql}
SELECT COALESCE(a) BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+--------------------------------------+
| COALESCE(a) BETWEEN b AND '10:00:00' |
+--------------------------------------+
| 1 |
+--------------------------------------+
{noformat}

I run this script:
{code:sql}
CREATE OR REPLACE TABLE t1 (a TIME, b TIME);
INSERT INTO t1 VALUES ('00:00:20','00:00:00');
SELECT a BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 1 |
+----------------------------+
{noformat}
It correctly returns {{1}}, because comparison is done as {{TIME}}, and '{{00:00:20}}' is really between '{{00:00:00}}' AND '{{10:00:00}}'.

Now I change the data type for the column {{a}} from {{TIME}} to {{INT}}. Nothing should change: {{INT}}, {{TIME}} and {{STRING}} arguments should be compared as {{TIME}}. So {{BETWEEN}} should still return {{1}}.
{code:sql}
ALTER TABLE t1 MODIFY a INT;
SELECT a BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 1 |
+----------------------------+
{noformat}
Works fine so far.

Now I change the data type for {{a}} from {{INT}} to {{BIGINT}}. Nothing should change. It should still return {{1}}. However, something different happens:

{code:sql}
ALTER TABLE t1 MODIFY a BIGINT;
SELECT a BETWEEN b AND '10:00:00' FROM t1;
SHOW WARNINGS;
{code}
{noformat}
+----------------------------+
| a BETWEEN b AND '10:00:00' |
+----------------------------+
| 0 |
+----------------------------+
{noformat}
{noformat}
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: '10:00:00' |
+---------+------+----------------------------------------------+
{noformat}
Notice, the result changed to {{0}}, and now it returns a warning.
The expected behavior would be to return {{1}} without warnings.

Now if I wrap {{a}} into a function, it returns {{1}} again:
{code:sql}
SELECT COALESCE(a) BETWEEN b AND '10:00:00' FROM t1;
{code}
{noformat}
+--------------------------------------+
| COALESCE(a) BETWEEN b AND '10:00:00' |
+--------------------------------------+
| 1 |
+--------------------------------------+
{noformat}

Alexander Barkov made changes - 2017-12-27 08:57

Component/s

Data types [ 13906 ]

Sergei Golubchik made changes - 2019-03-29 12:04

Fix Version/s

10.4 [ 22408 ]

Julien Fritsch made changes - 2019-11-18 11:41

Epic Link

MDEV-21071 [ 80504 ]

Julien Fritsch made changes - 2019-11-18 11:54

Link

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

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

Workflow

MariaDB v3 [ 80606 ]

MariaDB v4 [ 140292 ]

Alice Sherepa added a comment - 2022-12-05 10:19

currently 10.3 (4e9206736c4032069)-10.11 return the correct result, 1, no warning.

Alice Sherepa added a comment - 2022-12-05 10:19 currently 10.3 (4e9206736c4032069)-10.11 return the correct result, 1, no warning.

Alice Sherepa made changes - 2022-12-05 10:19

Fix Version/s		N/A [ 14700 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Cannot Reproduce [ 5 ]
Status	Open [ 1 ]	Closed [ 6 ]

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 0 Vote for this issue

Watchers:: 2 Start watching this issue

Dates

Created:: 2017-05-04 13:48

Updated:: 2022-12-05 10:19

Resolved:: 2022-12-05 10:19

Git Integration

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

MariaDB Server

Details

Description

Attachments

Activity

People

Dates

Git Integration