Random "truncate incorrect value"

[ekexium]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

	sqls := []string{
		`create table tbl_1 ( col_1 date    default '2013-07-07' ,col_2 year , primary key  ( col_2 ,col_1 ) /*T![clustered_index] clustered */ ) charset gbk collate gbk_chinese_ci ;`,
		`create table tbl_2 ( col_3 text ( 163 )   not null ,col_4 decimal ( 49 , 29 )   not null default 0.87 , unique key idx_2 ( col_4 ,col_3 ( 1 ) ) ) charset utf8mb4 collate utf8mb4_unicode_ci ;`,
		`insert into tbl_1 values ( '1995-12-26','2007' );`,
		`insert into tbl_1 values ( '1986-01-27','2004' );`,
		`insert into tbl_2 values ( 'B牔Y橲袭抣oX緁wr',0.4 );`,
		`insert into tbl_2 values ( 'MIcT@輧覅悗)^9齝3鈙qgH-3',20278.284 );`,
		`create table tbl_3 ( col_5 varbinary ( 30 ) ,col_6 timestamp   not null , unique key idx_3 ( col_5 ( 2 ) ,col_6 ) ) charset utf8mb4 collate utf8mb4_unicode_ci ;`,
		`create table tbl_4 ( col_7 year    default '1998' ,col_8 float   not null , key idx_4 ( col_8 ) ) charset gbk collate gbk_chinese_ci ;`,
		`update tbl_4 set tbl_4.col_7 = '1970' ,tbl_4.col_8 = 9576.383120177688 where IsNull( tbl_4.col_7 ) or tbl_4.col_7 between '2007' and '1992' ;`,
		`insert  into tbl_2 set col_3 = '豒8MDCoP俇', col_4 = 45975.7508 on duplicate key update col_4 = 6.09, col_3 = '暌tN箳F羦^9dDp##2';`,
		`create table tbl_5 ( col_9 datetime   not null default '1997-02-01' ,col_10 set ( 'Alice','Bob','Charlie','David' ) , key idx_5 ( col_10 ,col_9 ) ) charset utf8mb4 collate utf8mb4_unicode_ci ;`,
		`delete from tbl_2 where tbl_2.col_4 in ( 8 ,4.2 ,2 ,548177.198 ,320.4688 );`,
		`delete from tbl_1 where col_1 is null ;`,
		`insert  into tbl_3 set col_5 = 'tbbZXZ%+Xa%', col_6 = '2004-12-10' on duplicate key update col_5 = 'cH^fYq)QHqk&2', col_6 = '2017-10-25';`,
		`replace into tbl_2 set col_3 = 'm璤L$镡k菅誴t募0驰', col_4 = 26;`,
		`update tbl_4 set tbl_4.col_7 = '2022' where not( tbl_4.col_8 between 3347.8054742388067 and 5012.483302961393 ) and tbl_4.col_8 in ( select col_3 from tbl_2 where tbl_4.col_8 between 3595.8669129337122 and 2395.668689032017 ) ;`,
	}

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

Either stable failure or stable success.

3. What did you see instead (Required)

The last statement randomly fails with [types:1292]Truncated incorrect DOUBLE value: 'B牔Y橲袭抣oX緁wr'

4. What is your TiDB version? (Required)

master (735adb0)

[windtalker]

The root cause is for the last statement, the plan is

+--------------------------------+---------+-----------+---------------+-----------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                       |
+--------------------------------+---------+-----------+---------------+-----------------------------------------------------+
| Update_8                       | N/A     | root      |               | N/A                                                 |
| └─HashJoin_11                  | 0.00    | root      |               | semi join, equal:[eq(tpch10.tbl_4.col_8, Column#7)] |
|   ├─Projection_13(Build)       | 4.00    | root      |               | cast(tpch10.tbl_2.col_3, double BINARY)->Column#7   |
|   │ └─TableReader_15           | 4.00    | root      |               | data:TableFullScan_14                               |
|   │   └─TableFullScan_14       | 4.00    | cop[tikv] | table:tbl_2   | keep order:false, stats:pseudo                      |
|   └─TableDual_12(Probe)        | 0.00    | root      |               | rows:0                                              |
+--------------------------------+---------+-----------+---------------+-----------------------------------------------------+

Note it is a semi join and the probe side is an empty table, so this query will always return empty result.
In TiDB's hash join, it starts both build side and probe side simultaneously, and there is an optimazation for the cases that probe side is empty:

tidb/pkg/executor/join.go

Lines 271 to 273 in 12af2e4

	if probeSideResult.NumRows() == 0 && !fetcher.useOuterToBuild {
	fetcher.finished.Store(true)
	}

It means if the probe side is empty and useOuterToBuild is false(which is always false for semi join), the join will finished without check the build side's status. And the error [types:1292]Truncated incorrect DOUBLE value: 'B牔Y橲袭抣oX緁wr' comes from the build side(Projection_13), so there is a random issues here:
if the probe side return's result ealier than the build side's error, the sql will finish quietly, but if the probe side returns result later than build side's error, the sql will throw out error.
For this specific query, I think the optimizer should elimilate the hash join since it always return empty result. But for queries that the probe side is found to be empty at runtime, there is no easy way to fix it. Maybe disable the earier exist optimization is a proper way.