information_schema.DDL_JOBS has wrong results for time columns

[dveeden]

Bug Report

Please answer these questions before submitting your issue. Thanks!

1. Minimal reproduce step (Required)

-- Some DDL operation on table t1 that takes at least a few seconds, like adding an index
SELECT 
  *,
  DATEDIFF(END_TIME,START_TIME),
  END_TIME=START_TIME,
  END_TIME-START_TIME
FROM
  information_schema.DDL_JOBS
WHERE
  TABLE_NAME='t1'\G

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

Non-zero result for DATEDIFF(END_TIME,START_TIME)

3. What did you see instead (Required)

mysql> SELECT *,DATEDIFF(END_TIME,START_TIME),END_TIME=START_TIME,END_TIME-START_TIME FROM information_schema.DDL_JOBS WHERE TABLE_NAME='t1'\G
*************************** 1. row ***************************
                       JOB_ID: 10106
                      DB_NAME: test
                   TABLE_NAME: t1
                     JOB_TYPE: add index /* txn-merge */
                 SCHEMA_STATE: public
                    SCHEMA_ID: 2
                     TABLE_ID: 10104
                    ROW_COUNT: 2097152
                  CREATE_TIME: 2024-04-26 11:20:02
                   START_TIME: 2024-04-26 11:20:02
                     END_TIME: 2024-04-26 11:20:17
                        STATE: synced
                        QUERY: alter table t1 add index (c1(100))
DATEDIFF(END_TIME,START_TIME): 0
          END_TIME=START_TIME: 0
          END_TIME-START_TIME: 15
*************************** 2. row ***************************
                       JOB_ID: 10105
                      DB_NAME: test
                   TABLE_NAME: t1
                     JOB_TYPE: create table
                 SCHEMA_STATE: public
                    SCHEMA_ID: 2
                     TABLE_ID: 10104
                    ROW_COUNT: 0
                  CREATE_TIME: 2024-04-26 11:18:40
                   START_TIME: 2024-04-26 11:18:40
                     END_TIME: 2024-04-26 11:18:40
                        STATE: synced
                        QUERY: create table t1(id int primary key auto_increment, c1 text)
DATEDIFF(END_TIME,START_TIME): 0
          END_TIME=START_TIME: 0
          END_TIME-START_TIME: 0
2 rows in set (0.12 sec)

4. What is your TiDB version? (Required)

mysql> SELECT TIDB_VERSION()\G
*************************** 1. row ***************************
TIDB_VERSION(): Release Version: v8.2.0-alpha-50-g10971ea5b0
Edition: Community
Git Commit Hash: 10971ea5b0d0ca48792751801954c301e2f9fe08
Git Branch: master
UTC Build Time: 2024-04-26 09:09:10
GoVersion: go1.22.2
Race Enabled: false
Check Table Before Drop: false
Store: unistore
1 row in set (0.00 sec)

In addition to this it would have been nice to have microsecond timestamps on these columns

[dveeden]

I should have used TIMEDIFF() instead of DATEDIFF().