# 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.t1;
mysql> drop table if exists test.t2;

# Test timestamp column with time zone info in different scenarios
mysql> create table test.t1 (id int, value timestamp, set_value set('a','b','c'));
mysql> create table test.t2 (id int, value timestamp, set_value set('a','b','c'));

mysql> set time_zone='America/New_York'; insert into test.t1 values(1,'2020-01-01 11:11:11','a'),(2,'2020-01-02 16:11:11','b');
mysql> set time_zone='Asia/Shanghai'; insert into test.t2 values(1,'2020-01-02 00:11:11','a'),(2,'2020-01-03 05:11:11','b');

mysql> alter table test.t1 set tiflash replica 1;
mysql> alter table test.t2 set tiflash replica 1;

func> wait_table test t1
func> wait_table test t2

mysql> analyze table test.t1;
mysql> analyze table test.t2;

# default encode with non-utc timezone
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; select id, value from t1 where value = '2020-01-03 05:11:11';
+------+---------------------+
| id   | value               |
+------+---------------------+
|    2 | 2020-01-03 05:11:11 |
+------+---------------------+
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; select count(*), value from t1 where value = '2020-01-03 05:11:11' group by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-03 05:11:11 |
+----------+---------------------+
# default encode with utc timezone
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; select id, value from t1 where value = '2020-01-01 16:11:11';
+------+---------------------+
| id   | value               |
+------+---------------------+
|    1 | 2020-01-01 16:11:11 |
+------+---------------------+
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; select count(*), value from t1 where value = '2020-01-01 16:11:11' group by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-01 16:11:11 |
+----------+---------------------+
# chunk encode with non-utc timezone
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; select id, value from t1 where value = '2020-01-03 05:11:11';
+------+---------------------+
| id   | value               |
+------+---------------------+
|    2 | 2020-01-03 05:11:11 |
+------+---------------------+
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; select count(*), value from t1 where value = '2020-01-03 05:11:11' group by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-03 05:11:11 |
+----------+---------------------+
# chunk encode with utc timezone
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; select id, value from t1 where value = '2020-01-01 16:11:11';
+------+---------------------+
| id   | value               |
+------+---------------------+
|    1 | 2020-01-01 16:11:11 |
+------+---------------------+
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; select count(*), value from t1 where value = '2020-01-01 16:11:11' group by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-01 16:11:11 |
+----------+---------------------+

# default encode in tidb, chunk encode in tiflash, non-utc timezone
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ t1.id, t1.value, t2.id, t2.value from t1 join t2 on t1.value = t2.value order by t1.id;
+------+---------------------+------+---------------------+
| id   | value               | id   | value               |
+------+---------------------+------+---------------------+
|    1 | 2020-01-02 00:11:11 |    1 | 2020-01-02 00:11:11 |
|    2 | 2020-01-03 05:11:11 |    2 | 2020-01-03 05:11:11 |
+------+---------------------+------+---------------------+
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), t1.value from t1 join t2 on t1.value = t2.value group by t1.value order by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-02 00:11:11 |
|        1 | 2020-01-03 05:11:11 |
+----------+---------------------+
# default encode in tidb, default encode in tiflash, non-utc timezone
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1 join t2 on t1.value = t2.value order by t1.id;
+------+---------------------+-----------+------+---------------------+-----------+
| id   | value               | set_value | id   | value               | set_value |
+------+---------------------+-----------+------+---------------------+-----------+
|    1 | 2020-01-02 00:11:11 | a         |    1 | 2020-01-02 00:11:11 | a         |
|    2 | 2020-01-03 05:11:11 | b         |    2 | 2020-01-03 05:11:11 | b         |
+------+---------------------+-----------+------+---------------------+-----------+

mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(t1.set_value), count(t2.set_value), t1.value from t1 join t2 on t1.value = t2.value group by t1.value order by value;
+---------------------+---------------------+---------------------+
| count(t1.set_value) | count(t2.set_value) | value               |
+---------------------+---------------------+---------------------+
|                   1 |                   1 | 2020-01-02 00:11:11 |
|                   1 |                   1 | 2020-01-03 05:11:11 |
+---------------------+---------------------+---------------------+
# default encode in tidb, chunk encode in tiflash, utc timezone
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ t1.id, t1.value, t2.id, t2.value from t1 join t2 on t1.value = t2.value order by t1.id;
+------+---------------------+------+---------------------+
| id   | value               | id   | value               |
+------+---------------------+------+---------------------+
|    1 | 2020-01-01 16:11:11 |    1 | 2020-01-01 16:11:11 |
|    2 | 2020-01-02 21:11:11 |    2 | 2020-01-02 21:11:11 |
+------+---------------------+------+---------------------+
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=0; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), t1.value from t1 join t2 on t1.value = t2.value group by t1.value order by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-01 16:11:11 |
|        1 | 2020-01-02 21:11:11 |
+----------+---------------------+
# chunk encode in tidb, chunk encode in tiflash, non-utc timezone
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ t1.id, t1.value, t2.id, t2.value from t1 join t2 on t1.value = t2.value order by t1.id;
+------+---------------------+------+---------------------+
| id   | value               | id   | value               |
+------+---------------------+------+---------------------+
|    1 | 2020-01-02 00:11:11 |    1 | 2020-01-02 00:11:11 |
|    2 | 2020-01-03 05:11:11 |    2 | 2020-01-03 05:11:11 |
+------+---------------------+------+---------------------+
mysql> use test; set time_zone='Asia/Shanghai'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), t1.value from t1 join t2 on t1.value = t2.value group by t1.value order by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-02 00:11:11 |
|        1 | 2020-01-03 05:11:11 |
+----------+---------------------+

# chunk encode in tidb, chunk encode in tiflash, utc timezone
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ t1.id, t1.value, t2.id, t2.value from t1 join t2 on t1.value = t2.value order by t1.id;
+------+---------------------+------+---------------------+
| id   | value               | id   | value               |
+------+---------------------+------+---------------------+
|    1 | 2020-01-01 16:11:11 |    1 | 2020-01-01 16:11:11 |
|    2 | 2020-01-02 21:11:11 |    2 | 2020-01-02 21:11:11 |
+------+---------------------+------+---------------------+
mysql> use test; set time_zone='UTC'; set tidb_enable_chunk_rpc=1; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), t1.value from t1 join t2 on t1.value = t2.value group by t1.value order by value;
+----------+---------------------+
| count(*) | value               |
+----------+---------------------+
|        1 | 2020-01-01 16:11:11 |
|        1 | 2020-01-02 21:11:11 |
+----------+---------------------+

mysql> drop table if exists test.t1;
mysql> drop table if exists test.t2;