The result of comparison function on date value should return DATE type

[pcqz]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

bash$ mysql --column-type-info

mysql> select coalesce(current_date(),null) from dual;

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

Field   1:  `coalesce(current_date(),null)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATE
...

3. What did you see instead (Required)

Field   1:  `coalesce(current_date(),null)`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
...

4. What is your TiDB version? (Required)

v6.6.0

[dveeden]

This causes issues with MySQL Connector/J 8.x

package com.pingcap.tidb_demo_java;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.PreparedStatement;

public class App 
{
    public static void main( String[] args )
    {
	try {
		Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
	} catch (Exception ex) {
		System.out.println("Failed to load driver: " + ex.getMessage());
	}

	try {
		Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:4000?user=root");

		// This works
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery("SELECT CURRENT_DATE()");
		rs.next();
		System.out.println("CURRENT_DATE() result: " + rs.getString("CURRENT_DATE()"));

		// This results in SQLException: Invalid length (10) for type TIMESTAMP
		// Tested on TiDB v7.3.0
		Statement stmt2 = conn.createStatement();
		ResultSet rs2 = stmt.executeQuery("SELECT COALESCE(CURRENT_DATE(),NULL) a");
		rs2.next();
		System.out.println("CURRENT_TIMESTAMP() result: " + rs2.getString("a"));
	} catch (SQLException ex) {
		System.out.println("SQLException: " + ex.getMessage());
	}

    }
}

Output:

$ java -cp /usr/share/java/mysql-connector-j.jar:target/tidb_demo_java-1.0-SNAPSHOT.jar com.pingcap.tidb_demo_java.App
CURRENT_DATE() result: 2023-08-29
SQLException: Invalid length (10) for type TIMESTAMP

[seiya-annie]

/found gs

[yibin87]

/report customer