# 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;
mysql> drop table if exists test.t1_not_null;
mysql> drop table if exists test.t2_not_null;
mysql> drop table if exists test.t1_decimal;
mysql> drop table if exists test.t2_decimal;

# Test broadcast join
mysql> create table test.t1 (id int, value int);
mysql> create table test.t2 (id int, value int);
mysql> create table test.t1_not_null (id int not null, value int not null);
mysql> create table test.t2_not_null (id int not null, value int not null);
mysql> create table test.t1_decimal (id decimal(10,2) not null, value int not null);
mysql> create table test.t2_decimal (id decimal(6,3) not null, value int not null);

mysql> insert into test.t1 values(1,2),(3,4),(3,5),(3,6),(5,6),(7,8),(9,10),(null,11),(null,12),(10,null),(null,null);
mysql> insert into test.t2 values(3,4),(5,6),(5,7),(5,8),(null,7),(null,8);
mysql> insert into test.t1_not_null values(0,1),(1,2),(3,4),(4,5),(5,6);
mysql> insert into test.t2_not_null values(4,5),(5,6),(6,7);
mysql> insert into test.t1_decimal values(0,1),(1,2),(3,4),(4,5),(5,6);
mysql> insert into test.t2_decimal values(4,5),(5,6),(6,7);

mysql> alter table test.t1 set tiflash replica 1;
mysql> alter table test.t2 set tiflash replica 1;
mysql> alter table test.t1_not_null set tiflash replica 1;
mysql> alter table test.t2_not_null set tiflash replica 1;
mysql> alter table test.t1_decimal set tiflash replica 1;
mysql> alter table test.t2_decimal set tiflash replica 1;

func> wait_table test t1
func> wait_table test t2
func> wait_table test t1_not_null
func> wait_table test t2_not_null
func> wait_table test t1_decimal
func> wait_table test t2_decimal

mysql> analyze table test.t1;
mysql> analyze table test.t2;
mysql> analyze table test.t1_not_null;
mysql> analyze table test.t2_not_null;
mysql> analyze table test.t1_decimal;
mysql> analyze table test.t2_decimal;

# tiflash inner join
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1 join t2 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    5 |     6 |    5 |     6 |
+------+-------+------+-------+

# tiflash left join
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1 left join t2 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    1 |     2 | NULL |  NULL |
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     6 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    7 |     8 | NULL |  NULL |
|    9 |    10 | NULL |  NULL |
| NULL |    11 | NULL |  NULL |
| NULL |    12 | NULL |  NULL |
|   10 |  NULL | NULL |  NULL |
| NULL |  NULL | NULL |  NULL |
+------+-------+------+-------+

# tiflash right join
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1 right join t2 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    5 |     6 |    5 |     6 |
| NULL |  NULL | NULL |     7 |
| NULL |  NULL | NULL |     8 |
+------+-------+------+-------+

# tiflash inner join
mysql> use test; 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 t2 join t1 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    5 |     6 |    5 |     6 |
+------+-------+------+-------+

# tiflash inner join with other condition
mysql> use test; 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 t2 join t1 on t2.id = t1.id and t1.value > t2.value;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
+------+-------+------+-------+

# tiflash left join
mysql> use test; 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 t2 left join t1 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    5 |     6 |    5 |     6 |
| NULL |  NULL | NULL |     7 |
| NULL |  NULL | NULL |     8 |
+------+-------+------+-------+

# tiflash left join with left condition
mysql> use test; 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 t2 left join t1 on t2.id = t1.id and t2.value > 5;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    5 |     6 |    5 |     6 |
| NULL |  NULL |    3 |     4 |
| NULL |  NULL | NULL |     7 |
| NULL |  NULL | NULL |     8 |
+------+-------+------+-------+

# tiflash left join with left condition and other condition
mysql> use test; 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 t2 left join t1 on t2.id = t1.id and t2.value > 5 and t1.value >= t2.value;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    5 |     6 |    5 |     6 |
| NULL |  NULL |    3 |     4 |
| NULL |  NULL |    5 |     7 |
| NULL |  NULL |    5 |     8 |
| NULL |  NULL | NULL |     7 |
| NULL |  NULL | NULL |     8 |
+------+-------+------+-------+

# tiflash right join
mysql> use test; 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 t2 right join t1 on t2.id = t1.id;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    1 |     2 | NULL |  NULL |
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 |    3 |     4 |
|    5 |     6 |    5 |     6 |
|    5 |     6 |    5 |     8 |
|    5 |     6 |    5 |     7 |
|    7 |     8 | NULL |  NULL |
|    9 |    10 | NULL |  NULL |
| NULL |    11 | NULL |  NULL |
| NULL |    12 | NULL |  NULL |
|   10 |  NULL | NULL |  NULL |
| NULL |  NULL | NULL |  NULL |
+------+-------+------+-------+

# tiflash right join with right condition
mysql> use test; 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 t2 right join t1 on t2.id = t1.id and t1.value < 6;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    1 |     2 | NULL |  NULL |
|    3 |     4 |    3 |     4 |
|    3 |     5 |    3 |     4 |
|    3 |     6 | NULL |  NULL |
|    5 |     6 | NULL |  NULL |
|    7 |     8 | NULL |  NULL |
|    9 |    10 | NULL |  NULL |
| NULL |    11 | NULL |  NULL |
| NULL |    12 | NULL |  NULL |
|   10 |  NULL | NULL |  NULL |
| NULL |  NULL | NULL |  NULL |
+------+-------+------+-------+

# tiflash left join with left condition and other condition
mysql> use test; 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 t2 right join t1 on t2.id = t1.id and t1.value < 6 and t1.value > t2.value;
+------+-------+------+-------+
| id   | value | id   | value |
+------+-------+------+-------+
|    1 |     2 | NULL |  NULL |
|    3 |     4 | NULL |  NULL |
|    3 |     5 |    3 |     4 |
|    3 |     6 | NULL |  NULL |
|    5 |     6 | NULL |  NULL |
|    7 |     8 | NULL |  NULL |
|    9 |    10 | NULL |  NULL |
| NULL |    11 | NULL |  NULL |
| NULL |    12 | NULL |  NULL |
|   10 |  NULL | NULL |  NULL |
| NULL |  NULL | NULL |  NULL |
+------+-------+------+-------+

# tiflash semi join, can not use hint to force broadcast join, and tidb cbo has some bugs so set tidb_opt_cpu_factor=1000000 to make the optimizer choose broadcast join
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 where exists (select id from  t2 where t2.value = t1.value);
+------+-------+
| id   | value |
+------+-------+
|    3 |     4 |
|    3 |     6 |
|    5 |     6 |
|    7 |     8 |
+------+-------+

# tiflash semi join with other condition 
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 where exists (select id from  t2 where t2.value = t1.value and t2.id > t1.id);
+------+-------+
| id   | value |
+------+-------+
|    3 |     6 |
+------+-------+

# tiflash anti semi join 
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 where not exists (select id from  t2 where t2.value = t1.value);
+------+-------+
| id   | value |
+------+-------+
|    1 |     2 |
|    3 |     5 |
|    9 |    10 |
| NULL |    11 |
| NULL |    12 |
|   10 |  NULL |
| NULL |  NULL |
+------+-------+

# tiflash anti semi join with other condition 
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 where not exists (select id from  t2 where t2.value = t1.value and t2.id < t1.id);
+------+-------+
| id   | value |
+------+-------+
|    1 |     2 |
|    3 |     4 |
|    3 |     5 |
|    3 |     6 |
|    5 |     6 |
|    9 |    10 |
| NULL |    11 |
| NULL |    12 |
|   10 |  NULL |
| NULL |  NULL |
+------+-------+

# complex query(tpch query 21)
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 t1 where value > 5 and exists (select * from t1 t2 where t1.id = t2.id and t1.value <> t2.value) and not exists (select * from t1 t3 where t1.id = t3.id and t1.value <> t3.value and t3.value > 5);
+------+-------+
| id   | value |
+------+-------+
|    3 |     6 |
+------+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; set session tidb_opt_cpu_factor=1000000; select * from t1 t1 where not exists (select * from t1 t3 where t1.id = t3.id and t1.value <> t3.value);
+------+-------+
| id   | value |
+------+-------+
|    1 |     2 |
|    5 |     6 |
|    7 |     8 |
|    9 |    10 |
| NULL |    11 |
| NULL |    12 |
|   10 |  NULL |
| NULL |  NULL |
+------+-------+

# out join for not null table
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1_not_null t1 left join t2_not_null t2 on t2.id = t1.id;
+----+-------+------+-------+
| id | value | id   | value |
+----+-------+------+-------+
|  0 |     1 | NULL |  NULL |
|  1 |     2 | NULL |  NULL |
|  3 |     4 | NULL |  NULL |
|  4 |     5 |    4 |     5 |
|  5 |     6 |    5 |     6 |
+----+-------+------+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), count(t2.id) from t1_not_null t1 left join t2_not_null t2 on t2.id = t1.id;
+----------+--------------+
| count(*) | count(t2.id) |
+----------+--------------+
|        5 |            2 |
+----------+--------------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1_not_null t1 right join t2_not_null t2 on t2.id = t1.id;
+------+-------+----+-------+
| id   | value | id | value |
+------+-------+----+-------+
|    4 |     5 |  4 |     5 |
|    5 |     6 |  5 |     6 |
| NULL |  NULL |  6 |     7 |
+------+-------+----+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), count(t1.id) from t1_not_null t1 right join t2_not_null t2 on t2.id = t1.id;
+----------+--------------+
| count(*) | count(t1.id) |
+----------+--------------+
|        3 |            2 |
+----------+--------------+

# test join key with different type in TiFlash
mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1_decimal t1 join t2_decimal t2 on t2.id = t1.id;
+------+-------+-------+-------+
| id   | value | id    | value |
+------+-------+-------+-------+
| 4.00 |     5 | 4.000 |     5 |
| 5.00 |     6 | 5.000 |     6 |
+------+-------+-------+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), count(t2.id) from t1_decimal t1 join t2_decimal t2 on t2.id = t1.id;
+----------+--------------+
| count(*) | count(t2.id) |
+----------+--------------+
|        2 |            2 |
+----------+--------------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1_decimal t1 left join t2_decimal t2 on t2.id = t1.id;
+------+-------+-------+-------+
| id   | value | id    | value |
+------+-------+-------+-------+
| 0.00 |     1 |  NULL |  NULL |
| 1.00 |     2 |  NULL |  NULL |
| 3.00 |     4 |  NULL |  NULL |
| 4.00 |     5 | 4.000 |     5 |
| 5.00 |     6 | 5.000 |     6 |
+------+-------+-------+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), count(t2.id) from t1_decimal t1 left join t2_decimal t2 on t2.id = t1.id;
+----------+--------------+
| count(*) | count(t2.id) |
+----------+--------------+
|        5 |            2 |
+----------+--------------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ * from t1_decimal t1 right join t2_decimal t2 on t2.id = t1.id;
+------+-------+-------+-------+
| id   | value | id    | value |
+------+-------+-------+-------+
| 4.00 |     5 | 4.000 |     5 |
| 5.00 |     6 | 5.000 |     6 |
| NULL |  NULL | 6.000 |     7 |
+------+-------+-------+-------+

mysql> use test; set session tidb_isolation_read_engines='tiflash'; set session tidb_opt_broadcast_join=1; select /*+ broadcast_join(t1,t2) */ count(*), count(t1.id) from t1_decimal t1 right join t2_decimal t2 on t2.id = t1.id;
+----------+--------------+
| count(*) | count(t1.id) |
+----------+--------------+
|        3 |            2 |
+----------+--------------+

mysql> drop table if exists test.t1;
mysql> drop table if exists test.t2;
mysql> drop table if exists test.t1_not_null;
mysql> drop table if exists test.t2_not_null;
mysql> drop table if exists test.t1_decimal;
mysql> drop table if exists test.t2_decimal;