Tidb: INTERVAL function returns incorrect results & wrong warning

[zhangysh1995]

Bug Report

1. Minimal reproduce step (Required)

````
drop table if exists t1;
create table t1 (col0 BLOB, col1 CHAR(74), col2 DATE UNIQUE);
insert into t1 values ('l', '7a34bc7d-6786-461b-92d3-fd0a6cd88f39', '1000-01-03');
insert into t1 values ('l', NULL, '1000-01-04');
insert into t1 values ('b', NULL, '1000-01-02');

-- result incorrect
select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, col1) from t1;

drop table t1;
````

2. What did you expect to see? (Required)

mysql> select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) from t1; +--------------------------------------------------------------------------+ | INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) | +--------------------------------------------------------------------------+ | 0 | | 0 | | 0 | +--------------------------------------------------------------------------+ 3 rows in set (0.00 sec)

3. What did you see instead (Required)

``` mysql> select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496,col1) from t1; +--------------------------------------------------------------------------+ | INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496,col1`) |
+--------------------------------------------------------------------------+
| 0 |
| 2 |
| 2 |
+--------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+-------------------------------------------------------------------------+
| Warning | 1292 | Truncated incorrect FLOAT value: '7a34bc7d-6786-461b-92d3-fd0a6cd88f39' |
+---------+------+-------------------------------------------------------------------------+
1 row in set (0.00 sec)
````

4. Affected version (Required)

commit a9177fe846bf2638261f64514ab4ebd0155cc60c (HEAD -> master, origin/master, origin/HEAD) Author: Boqin Qin <[email protected]> Date: Mon Jul 6 19:28:12 2020 +0800

5. Root Cause Analysis

[nullnotnil]

Verified as described. Thank you for the bug report:

drop table if exists t1;
create table t1 (col0 BLOB, col1 CHAR(74), col2 DATE UNIQUE);
insert into t1 values ('l', '7a34bc7d-6786-461b-92d3-fd0a6cd88f39', '1000-01-03');
insert into t1 values ('l', NULL, '1000-01-04');
insert into t1 values ('b', NULL, '1000-01-02');

-- result incorrect
select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) from t1;

drop table t1;

..

mysql> select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) from t1;
+--------------------------------------------------------------------------+
| INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496, `col1`) |
+--------------------------------------------------------------------------+
|                                                                        0 |
|                                                                        2 |
|                                                                        2 |
+--------------------------------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> select tidb_version()\G
*************************** 1. row ***************************
tidb_version(): Release Version: v4.0.0-beta.2-750-g8a661044c
Edition: Community
Git Commit Hash: 8a661044cedf8daad1de4fbf79a390962b6f6c3b
Git Branch: master
UTC Build Time: 2020-07-10 10:52:37
GoVersion: go1.13
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false
1 row in set (0.00 sec)

[fzhedu]

simple reproduce:

mysql> select INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496,null);
+-----------------------------------------------------------------------+
| INTERVAL( ( CONVERT( -11752 USING utf8 ) ), 6558853612195285496,null) |
+-----------------------------------------------------------------------+
|                                                                     2 |
+-----------------------------------------------------------------------+
1 row in set (0.00 sec)

[ichn-hu]

/assign @ichn-hu

[ichn-hu]

/unassign @fzhedu

[ichn-hu]

The root cause is found. It is due to the binary search algorithm we currently employed can't handle NULL values correctly.

[ichn-hu]

@zhangysh1995 Do you have any suggestions about binary searching an array where there are uncomparable holes inside? One possible strategy I can come out with is to look around the hole to find the nearest comparable value, I am not sure if MySQL takes this strategy, I am going to read the implementation of the interval function in MySQL.

[zhangysh1995]

@ichn-hu I had a look at MySQL's source code here:

I think there maybe something wrong with the implementation, the following behavior is not given in the documentation:

```` MySQL
-- not meaningful result?
mysql> select interval( -1, null, 1); [2/74]+------------------------+ | interval( -1, null, 1) |
+------------------------+
| 1 |
+------------------------+
1 row in set (0.00 sec)

-- ?
mysql> select interval( -1, null, null);
+---------------------------+
| interval( -1, null, null) |
+---------------------------+
| 2 |
+---------------------------+
1 row in set (0.00 sec)

-- ?
mysql> select interval( -1, null, null, null);
+---------------------------------+
| interval( -1, null, null, null) |
+---------------------------------+
| 3 |
+---------------------------------+
1 row in set (0.00 sec)
````

[ichn-hu]

@ichn-hu I had a look at MySQL's source code here:

I think there maybe something wrong with the implementation, the following behavior is not given in the documentation:

-- not meaningful result?
mysql> select interval( -1, null, 1);                                                                                                                                                                                                                           [2/74]+------------------------+                                                                                                                                                                                                                                            | interval( -1, null, 1) |                                                                                                                                                                                                                                            
+------------------------+
|                      1 |
+------------------------+
1 row in set (0.00 sec)

-- ?
mysql> select interval( -1, null, null);
+---------------------------+
| interval( -1, null, null) |
+---------------------------+
|                         2 |
+---------------------------+
1 row in set (0.00 sec)

-- ?
mysql> select interval( -1, null, null, null);
+---------------------------------+
| interval( -1, null, null, null) |
+---------------------------------+
|                               3 |
+---------------------------------+
1 row in set (0.00 sec)

I've also checked the implementation, and MySQL uses linear scan when there is null value in the arguements, the behavior is indeed undefined, but it is reasonable when linear search is employed, I will also implement the linear search logic in TiDB.

[zhangysh1995]

I reported it as a bug to MySQL, and it is verified now. See bug 100670