concat(ifnull(time(3)) returns different results from MySQL

[espresso98]

Bug Report

SET TIMESTAMP doesn't take effect.

1. Minimal reproduce step (Required)

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('00:00:00.567', '2002-01-01');
SELECT CONCAT(IFNULL(t3, d)) AS col1 FROM t1;

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

+-------------------------+
| col1                    |
+-------------------------+
| 2019-03-11 00:00:00.567 |
+-------------------------+

3. What did you see instead (Required)

+----------------+
| col1           |
+----------------+
| 2021-11-04 00: |
+----------------+

4. What is your TiDB version? (Required)

+-------------------------+--------------------------------------------------------------------------+
| Variable_name           | Value                                                                    |
+-------------------------+--------------------------------------------------------------------------+
| innodb_version          | 5.6.25                                                                   |
| protocol_version        | 10                                                                       |
| tidb_analyze_version    | 2                                                                        |
| tidb_row_format_version | 2                                                                        |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2                                                    |
| version                 | 5.7.25-TiDB-v5.2.2                                                       |
| version_comment         | TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible |
| version_compile_machine | x86_64                                                                   |
| version_compile_os      | osx10.8                                                                  |
+-------------------------+--------------------------------------------------------------------------+

[morgo]

I took a look at this. The minimal testcase is:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (t3 TIME(3), d DATE);
INSERT INTO t1 VALUES ('00:00:00.567', '2002-01-01');
SELECT CONCAT(IFNULL(t3, d)) AS col1 FROM t1;

Because IFNULL returns t3 (since t3 is not null), which (not accounting for type conversion) this should be:

SELECT CONCAT(t3) AS col1 FROM t1;

Which (not accounting for type conversion) becomes:

SELECT t3 AS col1 FROM t1;

So the specific problem is type conversion.

TiDB gets it right if the query is just SELECT IFNULL(t3, d) AS col1 FROM t1; (it should be datetime), but gets it wrong once there are 2 conversions.

[mjonss]

I had a related comment here.

[fuzhe1989]

/assign @XuHuaiyu

[XuHuaiyu]

The Length should be 23 instead of 14

tidb> SELECT IFNULL(t3, d) AS col1 FROM t1;
Field   1:  `col1`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  utf8mb4_bin (46)
Length:     14
Max_length: 23
Decimals:   3
Flags:


+-------------------------+
| col1                    |
+-------------------------+
| 2021-12-03 00:00:00.567 |
+-------------------------+
1 row in set (0.00 sec)

[XuHuaiyu]

We need to add a check after line 145

tidb/expression/builtin_control.go

Lines 145 to 146 in cec4acb

	resultEvalType := resultFieldType.EvalType()
	if resultEvalType == types.ETInt {

Like this:

	if resultEvalType == types.ETDatetime {
		// the `+1` is the length of the decimal point
		resultFieldType.Flen = mysql.MaxDatetimeWidthNoFsp + mathutil.Max(lhs.Decimal, rhs.Decimal) + 1
	}

[bestwoody]

As @XuHuaiyu said, “The Length should be 23 instead of 14”. Operating Date op with Time op will result in Datetime result. However, TiDB uses max(Time.length, Date.length) not Datetime.legnth to derive length info which led truncated result. PR

[bestwoody]

PR: #30588

[XuHuaiyu]

/label affects-5.0

[bestwoody]

/label affects-5.1
/label affects-5.2
/label affects-5.3

[yibin87]

Reproduced it on local dev machine.

[bestwoody]

this PR will also solve cases of "case when" and "union". However, since every func has its own flen compute logic, other funcs may has similar problems too. But some func is difficult to change since it may change the behavior, such as: issue#26485

[github-actions]

Please check whether the issue should be labeled with 'affects-x.y' or 'fixes-x.y.z', and then remove 'needs-more-info' label.