Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Cannot Reproduce
-
10.1, 10.2, 10.3
-
None
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 |
|
+--------------------------------------+
|