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
	}