IN clause on a non-integer column with NULL value would cause unexpected warning or error

[MyonKeminta]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

/* t2 */ drop table if exists test.t;
-- t2 >> 0 rows affected
/* t2 */ create table test.t (a varchar(10), b tinyint(1));
-- t2 >> 0 rows affected
/* t2 */ insert into test.t values ("abc", 1);
-- t2 >> 1 rows affected
/* t2 */ explain select * from test.t where (a, b) in (('a', 1), (null, 0));
-- t2 >> +---------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+
-- t2    |         id          | estRows  |   task    | access object |                                               operator info                                                |
-- t2    +---------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+
-- t2    | TableReader_7       |     8.01 | root      |               | data:Selection_6                                                                                           |
-- t2    | └─Selection_6       |     8.01 | cop[tikv] |               | or(and(eq(test.t.a, "a"), eq(test.t.b, 1)), and(eq(cast(test.t.a, double BINARY), NULL), eq(test.t.b, 0))) |
-- t2    |   └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                                                                             |
-- t2    +---------------------+----------+-----------+---------------+------------------------------------------------------------------------------------------------------------+
/* t2 */ show warnings;
-- t2 >> +-------+------+---------+
-- t2    | Level | Code | Message |
-- t2    +-------+------+---------+
-- t2    +-------+------+---------+
/* t2 */ select * from test.t where (a, b) in (('a', 1), (null, 0));
-- t2 >> +---+---+
-- t2    | a | b |
-- t2    +---+---+
-- t2    +---+---+
/* t2 */ show warnings;
-- t2 >> +---------+------+-------------------------------------------------------------+
-- t2    |  Level  | Code |                           Message                           |
-- t2    +---------+------+-------------------------------------------------------------+
-- t2    | Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'abc' |
-- t2    +---------+------+-------------------------------------------------------------+
/* t2 */ update test.t set b = 0 where (a, b) in (('a', 1), (null, 0));
-- t2 >> E1292: Truncated incorrect INTEGER value: 'abc'

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

The statements above should be executed successfully with correct result.

3. What did you see instead (Required)

As pasted above, the select statement reports a warning, and the update statement failed with an error.

4. What is your TiDB version? (Required)

Master.

/* t1 */ select tidb_version();
-- t1 >> +-----------------------------------------------------------+
-- t1    |                      tidb_version()                       |
-- t1    +-----------------------------------------------------------+
-- t1    | Release Version: v7.6.0-alpha-dirty                       |
-- t1    | Edition: Community                                        |
-- t1    | Git Commit Hash: ccbd1b57493607c5e750055e02538ccdbc90b1e5 |
-- t1    | Git Branch: heads/refs/tags/v7.6.0-alpha                  |
-- t1    | UTC Build Time: 2023-11-27 11:47:40                       |
-- t1    | GoVersion: go1.21.3                                       |
-- t1    | Race Enabled: false                                       |
-- t1    | Check Table Before Drop: false                            |
-- t1    | Store: tikv                                               |
-- t1    +-----------------------------------------------------------+
/* t1 */ select * from information_schema.cluster_info;
-- t1 >> +------+-------------------------------------------------------------------+-------------------------------------------------------------------+-------------------+------------------------------------------+---------------------+---------------------+-----------+
-- t1    | TYPE |                             INSTANCE                              |                          STATUS_ADDRESS                           |      VERSION      |                 GIT_HASH                 |     START_TIME      |       UPTIME        | SERVER_ID |
-- t1    +------+-------------------------------------------------------------------+-------------------------------------------------------------------+-------------------+------------------------------------------+---------------------+---------------------+-----------+
-- t1    | tidb | tidb-b-tidb-0.tidb-b-tidb-peer.utf-go-tps-4770189-1-688.svc:4000  | tidb-b-tidb-0.tidb-b-tidb-peer.utf-go-tps-4770189-1-688.svc:10080 | 7.6.0-alpha-dirty | ccbd1b57493607c5e750055e02538ccdbc90b1e5 | 2023-11-28 11:11:50 | 21h55m9.303109385s  |       582 |
-- t1    | pd   | tidb-b-pd-0.tidb-b-pd-peer.utf-go-tps-4770189-1-688.svc:2379      | tidb-b-pd-0.tidb-b-pd-peer.utf-go-tps-4770189-1-688.svc:2379      | 7.6.0-alpha-dirty | 4bcac208ac81506d9e6a953417b448ab183b0270 | 2023-11-28 03:27:35 | 29h39m24.303113617s |         0 |
-- t1    | tikv | tidb-b-tikv-0.tidb-b-tikv-peer.utf-go-tps-4770189-1-688.svc:20160 | 10.233.98.177:20180                                               | 7.6.0-alpha       | 88542955b6953815e1f5ca53071f60084f61632d | 2023-11-28 03:27:53 | 29h39m6.303115299s  |         0 |
-- t1    +------+-------------------------------------------------------------------+-------------------------------------------------------------------+-------------------+------------------------------------------+---------------------+---------------------+-----------+

[MyonKeminta]

It's not necessary to include 2 value pairs in the IN clause

/* t2 */ explain select * from test.t where (a, b) in ((null, 0));
-- t2 >> +---------------------+----------+-----------+---------------+----------------------------------------------------------+
-- t2    |         id          | estRows  |   task    | access object |                      operator info                       |
-- t2    +---------------------+----------+-----------+---------------+----------------------------------------------------------+
-- t2    | TableReader_7       |     8.00 | root      |               | data:Selection_6                                         |
-- t2    | └─Selection_6       |     8.00 | cop[tikv] |               | eq(cast(test.t.a, double BINARY), NULL), eq(test.t.b, 0) |
-- t2    |   └─TableFullScan_5 | 10000.00 | cop[tikv] | table:t       | keep order:false, stats:pseudo                           |
-- t2    +---------------------+----------+-----------+---------------+----------------------------------------------------------+
/* t2 */ select * from test.t where (a, b) in ((null, 0));
-- t2 >> +---+---+
-- t2    | a | b |
-- t2    +---+---+
-- t2    +---+---+
/* t2 */ show warnings;
-- t2 >> +---------+------+-------------------------------------------------------------+
-- t2    |  Level  | Code |                           Message                           |
-- t2    +---------+------+-------------------------------------------------------------+
-- t2    | Warning | 1292 | evaluation failed: Truncated incorrect INTEGER value: 'abc' |
-- t2    +---------+------+-------------------------------------------------------------+

[yibin87]

Reproduced the issue(both warning and error) in v5.1.0, seems an existing bug from very beginning.

[yibin87]

Confirmed that the issue is introduced by #14571, and takes effect in this issue by following steps:

1. Infer null constant type as int, since it is parsed as one of row function's parameters, the other one is int "1" , code is here

   tidb/pkg/expression/scalar_function.go

   Line 143 in 069631e

   func typeInferForNull(args []Expression) {

2. In

   tidb/pkg/planner/core/expression_rewriter.go

   Line 1654 in 069631e

   func (er *expressionRewriter) inToExpression(lLen int, not bool, tp *types.FieldType) {

   , (a, b) in ((null, 1)) will be rewritten to (a == null) and (b == 1)
3. For (a == null), a's type is string, while null is inferred as int, then their comparison type will be real. Therefore, cast a to real type before comparison, and warning is generated.