[#MDEV-12691] Wrong result for: bigint_column BETWEEN time_column AND str

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 |

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

[MDEV-12691] Wrong result for: bigint_column BETWEEN time_column AND str_literal Created: 2017-05-04 Updated: 2022-12-05 Resolved: 2022-12-05
Status:	Closed
Project:	MariaDB Server
Component/s:	Data types
Affects Version/s:	10.1, 10.2, 10.3
Fix Version/s:	N/A

[MDEV-12691] Wrong result for: bigint_column BETWEEN time_column AND str_literal Created: 2017-05-04 Updated: 2022-12-05 Resolved: 2022-12-05