# Copyright 2023 PingCAP, Inc.
#
# Licensed under the Apache License, Version 2.0 (the "License");
# you may not use this file except in compliance with the License.
# You may obtain a copy of the License at
#
#     http://www.apache.org/licenses/LICENSE-2.0
#
# Unless required by applicable law or agreed to in writing, software
# distributed under the License is distributed on an "AS IS" BASIS,
# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
# See the License for the specific language governing permissions and
# limitations under the License.

mysql> drop table if exists test.t;
mysql> create table test.t (a decimal(4, 2), b tinyint, c int, d decimal(40,20));
mysql> alter table test.t set tiflash replica 1;

func> wait_table test t

mysql> insert into test.t values(1, -80, -1, 1);
mysql> insert into test.t values(3, -90, -3, 0);

# issue https://github.com/pingcap/tics/issues/1483
mysql> set session tidb_isolation_read_engines='tiflash'; select a from test.t where a in (1.0, 2);
+------+
| a    |
+------+
| 1.00 |
+------+

mysql> set session tidb_isolation_read_engines='tiflash'; select b from test.t where b in (-302764853, 12498864);
# Expected to be Empty

# issue https://github.com/pingcap/tidb/issues/23198
mysql> set session tidb_isolation_read_engines='tiflash'; select c from test.t where c in (1, 18446744073709551616);
# Expected to be Empty

# issue https://github.com/pingcap/tics/issues/1543
mysql> set session tidb_isolation_read_engines='tiflash'; select * from test.t where d in (1, 1);
+------+------+------+------------------------+
| a    | b    | c    | d                      |
+------+------+------+------------------------+
| 1.00 |  -80 |   -1 | 1.00000000000000000000 |
+------+------+------+------------------------+

# issue https://github.com/pingcap/tics/issues/1541
mysql> select * from test.t where d in (2, 'y');
+------+------+------+------------------------+
| a    | b    | c    | d                      |
+------+------+------+------------------------+
| 3.00 |  -90 |   -3 | 0.00000000000000000000 |
+------+------+------+------------------------+

mysql> drop table if exists test.t;
mysql> create table test.t(a int primary key, b int not null, c int);
mysql> insert into test.t values(0,0,0),(1,0,1),(2,0,null),(3,1,0),(4,1,1),(5,1,null),(6,0,0),(7,0,1),(8,0,null),(9,1,0),(10,1,1),(11,1,null);
mysql> alter table test.t set tiflash replica 1;
func> wait_table test t

mysql> set tidb_enforce_mpp=1; select avg(a or 1) in (null,0,0), avg(a or 1) in (null,1,1), avg(b or 1) in (0,0), avg(b or 1) in (1,1), avg(c or 1) in (0,0), avg(c or 1) in (1,1) from test.t group by a,b,c;
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
| avg(a or 1) in (null,0,0) | avg(a or 1) in (null,1,1) | avg(b or 1) in (0,0) | avg(b or 1) in (1,1) | avg(c or 1) in (0,0) | avg(c or 1) in (1,1) |
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
mysql> set tidb_enforce_mpp=1; select avg(a or 0) in (null,0,0), avg(a or 0) in (null,1,1), avg(b or 0) in (0,0), avg(b or 0) in (1,1), avg(c or 0) in (0,0), avg(c or 0) in (1,1) from test.t group by a,b,c;
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
| avg(a or 0) in (null,0,0) | avg(a or 0) in (null,1,1) | avg(b or 0) in (0,0) | avg(b or 0) in (1,1) | avg(c or 0) in (0,0) | avg(c or 0) in (1,1) |
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
|                         1 |                      NULL |                    1 |                    0 |                    1 |                    0 |
|                      NULL |                         1 |                    1 |                    0 |                    0 |                    1 |
|                      NULL |                         1 |                    1 |                    0 |                 NULL |                 NULL |
|                      NULL |                         1 |                    0 |                    1 |                    1 |                    0 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                 NULL |                 NULL |
|                      NULL |                         1 |                    1 |                    0 |                    1 |                    0 |
|                      NULL |                         1 |                    1 |                    0 |                    0 |                    1 |
|                      NULL |                         1 |                    1 |                    0 |                 NULL |                 NULL |
|                      NULL |                         1 |                    0 |                    1 |                    1 |                    0 |
|                      NULL |                         1 |                    0 |                    1 |                    0 |                    1 |
|                      NULL |                         1 |                    0 |                    1 |                 NULL |                 NULL |
+---------------------------+---------------------------+----------------------+----------------------+----------------------+----------------------+
mysql> set tidb_enforce_mpp=1; select avg(a and 0) in (null,0,0), avg(a and 0) in (null,1,1), avg(b and 0) in (0,0), avg(b and 0) in (1,1), avg(c and 0) in (0,0), avg(c and 0) in (1,1) from test.t group by a,b,c;
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
| avg(a and 0) in (null,0,0) | avg(a and 0) in (null,1,1) | avg(b and 0) in (0,0) | avg(b and 0) in (1,1) | avg(c and 0) in (0,0) | avg(c and 0) in (1,1) |
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
mysql> set tidb_enforce_mpp=1; select avg(a and 1) in (null,0,0), avg(a and 1) in (null,1,1), avg(b and 1) in (0,0), avg(b and 1) in (1,1), avg(c and 1) in (0,0), avg(c and 1) in (1,1) from test.t group by a,b,c;
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
| avg(a and 1) in (null,0,0) | avg(a and 1) in (null,1,1) | avg(b and 1) in (0,0) | avg(b and 1) in (1,1) | avg(c and 1) in (0,0) | avg(c and 1) in (1,1) |
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
|                          1 |                       NULL |                     1 |                     0 |                     1 |                     0 |
|                       NULL |                          1 |                     1 |                     0 |                     0 |                     1 |
|                       NULL |                          1 |                     1 |                     0 |                  NULL |                  NULL |
|                       NULL |                          1 |                     0 |                     1 |                     1 |                     0 |
|                       NULL |                          1 |                     0 |                     1 |                     0 |                     1 |
|                       NULL |                          1 |                     0 |                     1 |                  NULL |                  NULL |
|                       NULL |                          1 |                     1 |                     0 |                     1 |                     0 |
|                       NULL |                          1 |                     1 |                     0 |                     0 |                     1 |
|                       NULL |                          1 |                     1 |                     0 |                  NULL |                  NULL |
|                       NULL |                          1 |                     0 |                     1 |                     1 |                     0 |
|                       NULL |                          1 |                     0 |                     1 |                     0 |                     1 |
|                       NULL |                          1 |                     0 |                     1 |                  NULL |                  NULL |
+----------------------------+----------------------------+-----------------------+-----------------------+-----------------------+-----------------------+
mysql> set tidb_enforce_mpp=1; select (a or 1) in (null,0,0), (a or 1) in (null,1,1), (b or 1) in (0,0), (b or 1) in (1,1), (c or 1) in (0,0), (c or 1) in (1,1) from test.t;
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
| (a or 1) in (null,0,0) | (a or 1) in (null,1,1) | (b or 1) in (0,0) | (b or 1) in (1,1) | (c or 1) in (0,0) | (c or 1) in (1,1) |
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
mysql> set tidb_enforce_mpp=1; select (a or 0) in (null,0,0), (a or 0) in (null,1,1), (b or 0) in (0,0), (b or 0) in (1,1), (c or 0) in (0,0), (c or 0) in (1,1) from test.t;
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
| (a or 0) in (null,0,0) | (a or 0) in (null,1,1) | (b or 0) in (0,0) | (b or 0) in (1,1) | (c or 0) in (0,0) | (c or 0) in (1,1) |
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
|                      1 |                   NULL |                 1 |                 0 |                 1 |                 0 |
|                   NULL |                      1 |                 1 |                 0 |                 0 |                 1 |
|                   NULL |                      1 |                 1 |                 0 |              NULL |              NULL |
|                   NULL |                      1 |                 0 |                 1 |                 1 |                 0 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |              NULL |              NULL |
|                   NULL |                      1 |                 1 |                 0 |                 1 |                 0 |
|                   NULL |                      1 |                 1 |                 0 |                 0 |                 1 |
|                   NULL |                      1 |                 1 |                 0 |              NULL |              NULL |
|                   NULL |                      1 |                 0 |                 1 |                 1 |                 0 |
|                   NULL |                      1 |                 0 |                 1 |                 0 |                 1 |
|                   NULL |                      1 |                 0 |                 1 |              NULL |              NULL |
+------------------------+------------------------+-------------------+-------------------+-------------------+-------------------+
mysql> set tidb_enforce_mpp=1; select (a and 0) in (null,0,0), (a and 0) in (null,1,1), (b and 0) in (0,0), (b and 0) in (1,1), (c and 0) in (0,0), (c and 0) in (1,1) from test.t;
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+
| (a and 0) in (null,0,0) | (a and 0) in (null,1,1) | (b and 0) in (0,0) | (b and 0) in (1,1) | (c and 0) in (0,0) | (c and 0) in (1,1) |
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+
mysql> set tidb_enforce_mpp=1; select (a and 1) in (null,0,0), (a and 1) in (null,1,1), (b and 1) in (0,0), (b and 1) in (1,1), (c and 1) in (0,0), (c and 1) in (1,1) from test.t;
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+
| (a and 1) in (null,0,0) | (a and 1) in (null,1,1) | (b and 1) in (0,0) | (b and 1) in (1,1) | (c and 1) in (0,0) | (c and 1) in (1,1) |
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+
|                       1 |                    NULL |                  1 |                  0 |                  1 |                  0 |
|                    NULL |                       1 |                  1 |                  0 |                  0 |                  1 |
|                    NULL |                       1 |                  1 |                  0 |               NULL |               NULL |
|                    NULL |                       1 |                  0 |                  1 |                  1 |                  0 |
|                    NULL |                       1 |                  0 |                  1 |                  0 |                  1 |
|                    NULL |                       1 |                  0 |                  1 |               NULL |               NULL |
|                    NULL |                       1 |                  1 |                  0 |                  1 |                  0 |
|                    NULL |                       1 |                  1 |                  0 |                  0 |                  1 |
|                    NULL |                       1 |                  1 |                  0 |               NULL |               NULL |
|                    NULL |                       1 |                  0 |                  1 |                  1 |                  0 |
|                    NULL |                       1 |                  0 |                  1 |                  0 |                  1 |
|                    NULL |                       1 |                  0 |                  1 |               NULL |               NULL |
+-------------------------+-------------------------+--------------------+--------------------+--------------------+--------------------+

mysql> drop table if exists test.t;
mysql> create table test.t (col0 integer, col1 integer);
mysql> insert into test.t values(20, 30), (30, 40), (40, 50), (null, null);
mysql> alter table test.t set tiflash replica 1;
func> wait_table test t
mysql> alter table test.t compact tiflash replica;
mysql> set session tidb_isolation_read_engines='tiflash'; select * from test.t where col0 in (null, col0);
+------+------+
| col0 | col1 |
+------+------+
|   20 |   30 |
|   30 |   40 |
|   40 |   50 |
+------+------+
mysql> drop table test.t;

mysql> create table test.t (a int unsigned);
mysql> insert into test.t values (300), (null), (981);
mysql> alter table test.t set tiflash replica 1;
func> wait_table test t
mysql> alter table test.t compact tiflash replica;
mysql> set session tidb_isolation_read_engines='tiflash'; select * from test.t where not cast(a as signed) not in (a, 88);
+------+
| a    |
+------+
|  300 |
|  981 |
+------+
mysql> drop table test.t;

mysql> create table test.t (a int, b int);
mysql> insert into test.t values (10,30), (50, 60);
mysql> alter table test.t set tiflash replica 1;
func> wait_table test t

mysql> alter table test.t compact tiflash replica;
mysql> set session tidb_isolation_read_engines='tiflash'; select * from test.t where not a not in (0, cast(+ a as signed));
+------+------+
| a    | b    |
+------+------+
|   10 |   30 |
|   50 |   60 |
+------+------+
mysql> drop table test.t;