Incorrect query result

[sayJason]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

CREATE TABLE t1(c0 decimal(10,0) not null );
INSERT INTO t1 VALUES (1);
SELECT c0 FROM t1 WHERE ((CASE (t1.c0 IS NULL) WHEN true THEN 0.1 ELSE 1 END )) LIKE 1; -- actual:{}, expected:{1}

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

The SELECT statement should return the value 1.

3. What did you see instead (Required)

The SELECT statement returns nothing.

4. What is your TiDB version? (Required)

Release Version: v6.6.0 Edition: Community Git Commit Hash: f4ca0821fb96a2bdd37d2fb97eb26c07fc58d4e4 Git Branch: heads/refs/tags/v6.6.0 UTC Build Time: 2023-02-17 14:49:02 GoVersion: go1.19.5 Race Enabled: false TiKV Min Version: 6.2.0-alpha Check Table Before Drop: false Store: tikv

[yibin87]

Explain Analyze:

mysql> explain analyze SELECT c0 FROM t1 WHERE ((CASE (t1.c0 IS NULL) WHEN true THEN 0.1 ELSE 1 END )) LIKE 1;
+-------------+---------+---------+------+---------------+----------------------------------+---------------+--------+------+
| id          | estRows | actRows | task | access object | execution info                   | operator info | memory | disk |
+-------------+---------+---------+------+---------------+----------------------------------+---------------+--------+------+
| TableDual_6 | 0.00    | 0       | root |               | time:833ns, loops:1, RU:0.000000 | rows:0        | N/A    | N/A  |
+-------------+---------+---------+------+---------------+----------------------------------+---------------+--------+------+
1 row in set (0.00 sec)

A little change:

mysql> explain analyze SELECT c0 FROM t1 WHERE ((CASE (t1.c0 IS NULL) WHEN true THEN 0.1 ELSE 1 END )) LIKE 1.0;
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
| id                    | estRows | actRows | task      | access object | execution info                                                                                                                                                                                                                                                                         | operator info                  | memory    | disk |
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
| TableReader_6         | 1.00    | 1       | root      |               | time:1.03ms, loops:2, RU:0.503830, cop_task: {num: 1, max: 949.9µs, proc_keys: 1, tot_proc: 84.5µs, tot_wait: 175.5µs, rpc_num: 1, rpc_time: 921.9µs, copr_cache_hit_ratio: 0.00, build_task_duration: 11.1µs, max_distsql_concurrency: 1}                                             | data:TableFullScan_5           | 294 Bytes | N/A  |
| └─TableFullScan_5     | 1.00    | 1       | cop[tikv] | table:t1      | tikv_task:{time:0s, loops:1}, scan_detail: {total_process_keys: 1, total_process_keys_size: 43, total_keys: 2, get_snapshot_time: 153µs, rocksdb: {key_skipped_count: 1, block: {}}}, time_detail: {total_process_time: 84.5µs, total_wait_time: 175.5µs, tikv_wall_time: 598.3µs}     | keep order:false, stats:pseudo | N/A       | N/A  |
+-----------------------+---------+---------+-----------+---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+--------------------------------+-----------+------+
2 rows in set (0.00 sec)

Thus it seems a cast decimal/int to string precision issue.