different behavior with MySQL for bytes in where clause

Description

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. What did you do?

An sql in copr-test

SELECT EXP( `col_set` ) AS field1, `col_timestamp_key` % '2013-03-22' AS field2, DEGREES( '15:00:34.037137' ) AS field3 FROM `table1_int_autoinc` WHERE `col_binary_8_key` AND '2006-04-04 16:25:30.047980'

2. What did you expect to see?

No row returned

3. What did you see instead?

DOUBLE overflow (because of a selected row)

4. What version of TiDB are you using (tidb-server -V or run select tidb_version(); on TiDB)?

Release Version: v4.0.0-beta-441-g12aabbdc6-dirty
Git Commit Hash: 12aabbdc6729e0d7c9a196b89e75c1486f5b4b57
Git Branch: master
UTC Build Time: 2020-03-18 08:45:54
GoVersion: go1.14
Race Enabled: false
TiKV Min Version: v3.0.0-60965b006877ca7234adaced7890d7b029ed1306
Check Table Before Drop: false

This bug was caused by optimizing WHERE clause into IndexScan. As the execution plan suggested (below), it only excludes 48, which is "0" ASCII. But it's not enough, in this example, this column is "lyashfuw". MySQL will give a warning on Truncated incorrect DOUBLE value: 'lyashfuw'

+---------------------------------+---------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id                              | estRows | task      | operator info                                                                                                                                                                                                                      |
+---------------------------------+---------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Projection_4                    | 6656.67 | root      | exp(cast(test.table1_int_autoinc.col_set, double BINARY))->Column#62, mod(cast(test.table1_int_autoinc.col_timestamp_key, double BINARY), 2013)->Column#63, 859.4366926962349->Column#64, test.table1_int_autoinc.col_binary_8_key |
|   IndexLookUp_10                | 6656.67 | root      |                                                                                                                                                                                                                                    |
|     IndexRangeScan_8(Build)     | 6656.67 | cop[tikv] | table:table1_int_autoinc, index:col_binary_8_key, range:[-inf,"[48]"), ("[48]",+inf], keep order:false, stats:pseudo                                                                                                               |
|     TableRowIDScan_9(Probe)     | 6656.67 | cop[tikv] | table:table1_int_autoinc, keep order:false, stats:pseudo                                                                                                                                                                           |
+---------------------------------+---------+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The execution plan of MySQL

+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table              | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | table1_int_autoinc | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 7879 |    90.00 | Using where |
+----+-------------+--------------------+------------+------+---------------+------+---------+------+------+----------+-------------+

SIG slack channel

#sig-planner

Score

• 300

Mentor

• @lzmhhh123