Compare DATE column with INT value result in NULL and wrong result

[zhangysh1995]

Description

Bug Report

1. Minimal reproduce step (Required)

drop table if exists t7;
create table t7 (col0 SMALLINT, col1 VARBINARY(1), col2 DATE, col3 BIGINT, col4 BINARY(166));
insert into t7 values ('32767', '', '1000-01-03', '-0', '11101011');
create index wscjr on t7 (col2, col3);
select col2 = 1 from t7;
select col2 != 1 from t7;

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

-- MySQL 5.7
mysql> select col2 = 1 from t7;
+----------+
| col2 = 1 |
+----------+
|        0 |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql> select col2 != 1 from t7;
+-----------+
| col2 != 1 |
+-----------+
|         1 |
+-----------+
1 row in set, 1 warning (0.00 sec)

3. What did you see instead (Required)

mysql>  select col2 = 1 from t7;                                                                                                                                                                                                                      +----------+                                                                                                                                                                                                                                          
| col2 = 1 |
+----------+
|     NULL |
+----------+
1 row in set, 1 warning (0.00 sec)

mysql>  select col2 != 1 from t7;
+-----------+
| col2 != 1 |
+-----------+
|      NULL |
+-----------+
1 row in set, 1 warning (0.00 sec)

4. Affected version (Required)

commit 8369ffd500f3fb235d8b584ac4298b2e59d8db55 (HEAD -> master, origin/master, origin/HEAD)
Author: Soup <ilovesoup@gmail.com>
Date:   Tue May 26 15:38:29 2020 +0800

Reproducible on master branch:

commit c9c9f873e92c8c258a52d2a125ca6eb6c781d11d (HEAD -> master, origin/master, origin/HEAD)
Author: Chengpeng Yan <41809508+Reminiscent@users.noreply.github.com>
Date:   Tue Jun 9 14:56:38 2020 +0800

5. Root Cause Analysis

SIG slack channel

#sig-exec

Score

• 300

Mentor

• @qw4990

[LENSHOOD]

/pick-up

[ti-challenge-bot]

Pick up success.

[LENSHOOD]

The root cause of this issue is about two parts:

1. ScalarFunction.Eval(...) method takes all EvalXXX() result as "null" when there's some sort of err occurred.

// at scalar_function.go: line 326
func (sf *ScalarFunction) Eval(row chunk.Row) (d types.Datum, err error) {
	...
	switch tp, evalType := sf.GetType(), sf.GetType().EvalType(); evalType {
	...
	case types.ETDatetime, types.ETTimestamp:
		res, isNull, err = sf.EvalTime(sf.GetCtx(), row)
	...
	}

        // as long as err != nil, the returned Datum will be set to null
	if isNull || err != nil {
		d.SetNull()
		return d, err
	}
	d.SetValue(res, sf.RetType)
	return
}

In the expression select col2 = 1 from t7, constant 1 is not a valid datetime value, so cast 1 to datetime will cause "err != nil", that lead Eval() return a null Datum as cast result of 1.

Therefore, col2 = 1 become col2 = NULL, and that's explain why select col2 = 1 from t7; get NULL returned.

At Eval(), it is irresponsible to simply set null when err != nil, the more properly way is leave the decision to EvalXXX(), and call d.SetNull() only if isNull == true.

2. All evalTime() implementations return isNull as true when any error occurred.

Even if we modify Eval() to set d.SetNull() only when isNull == true, we still cannot solve the issue.

Because all evalTime() implementations return isNull as true when any error occurred, for example:

// builtin_cast.go: line 571
func (b *builtinCastIntAsTimeSig) evalTime(row chunk.Row) (res types.Time, isNull bool, err error) {
	... 
	res, err = types.ParseTimeFromNum(b.ctx.GetSessionVars().StmtCtx, val, b.tp.Tp, int8(b.tp.Decimal))
	if err != nil {
                 // isNull returned as true, but res is types.ZeroTime
		return types.ZeroTime, true, handleInvalidTimeError(b.ctx, err)
	}
	...
}

So cast 1 to datetime will always return null Datum.
There are 68 implementations of evalTime(), all share the same logic to deal with err != nil.

---

So far I cannot find a good solution. Any suggestions? @qw4990 @zhangysh1995

[zhangysh1995]

@LENSHOOD I found a related description in MySQL 8 documentation:

As a convenience, MySQL automatically converts a date to a number if the date is used in numeric context and vice versa

It seems it should do a DATE->INT conversion, which will call this function:

func (b *builtinCastTimeAsIntSig) evalInt(row chunk.Row) (res int64, isNull bool, err error)

Which will further call EvalTime on a column:

func (col *Column) EvalTime(ctx sessionctx.Context, row chunk.Row) (types.Time, bool, error) 

At last the value is converted to a number:

func (b *builtinCastTimeAsIntSig) evalInt(row chunk.Row) (res int64, isNull bool, err error) {
       // extract the value
	val, isNull, err := b.args[0].EvalTime(b.ctx, row)
	if isNull || err != nil {
		return res, isNull, err
	}
       // not sure what is done here
	sc := b.ctx.GetSessionVars().StmtCtx
	t, err := val.RoundFrac(sc, types.DefaultFsp)
	if err != nil {
		return res, false, err
	}
       // convert to a numer
	res, err = t.ToNumber().ToInt()
	return res, false, err
}

As long as DATE value is valid, it will not return isNull = true to the caller function, then it should solve at least the comparison between a date and an integer.

[ti-challenge-bot]

@LENSHOOD You did not submit PR within 7 days, so give up automatically.

[LENSHOOD]

@LENSHOOD I found a related description in MySQL 8 documentation:

As a convenience, MySQL automatically converts a date to a number if the date is used in numeric context and vice versa

It seems it should do a DATE->INT conversion, which will call this function:

func (b *builtinCastTimeAsIntSig) evalInt(row chunk.Row) (res int64, isNull bool, err error)

Which will further call EvalTime on a column:

func (col *Column) EvalTime(ctx sessionctx.Context, row chunk.Row) (types.Time, bool, error) 

At last the value is converted to a number:

func (b *builtinCastTimeAsIntSig) evalInt(row chunk.Row) (res int64, isNull bool, err error) {
       // extract the value
	val, isNull, err := b.args[0].EvalTime(b.ctx, row)
	if isNull || err != nil {
		return res, isNull, err
	}
       // not sure what is done here
	sc := b.ctx.GetSessionVars().StmtCtx
	t, err := val.RoundFrac(sc, types.DefaultFsp)
	if err != nil {
		return res, false, err
	}
       // convert to a numer
	res, err = t.ToNumber().ToInt()
	return res, false, err
}

As long as DATE value is valid, it will not return isNull = true to the caller function, then it should solve at least the comparison between a date and an integer.

In TiDB, the comparison logic is transfer constant (at this case num "1") to the column type ("date"), so when planner generate logic plan from AST, it will choose related transfer function by comparison pair, we can see this logic at 'foldConstant()' function.

Therefore, planner will transfer int to date rather than date to int, but the num 1 is not a valid date num...

[ti-srebot]

Please edit this comment or add a new comment to complete the following information

Not a bug

1. Remove the 'type/bug' label
2. Add notes to indicate why it is not a bug

Duplicate bug

1. Add the 'type/duplicate' label
2. Add the link to the original bug

Bug

Note: Make Sure that 'component', and 'severity' labels are added
Example for how to fill out the template: #20100

1. Root Cause Analysis (RCA) (optional)

2. Symptom (optional)

3. All Trigger Conditions (optional)

4. Workaround (optional)

5. Affected versions

6. Fixed versions