MariaDB Connect引擎透视含日期值表格时日期显示异常求助

我在MariaDB 10.5中，需要将InnoDB引擎下的长格式training表，通过Connect引擎的pivot表类型转换为宽格式pivTraining表，统计每个用户对应各program的最新trainingDate（使用MAX聚合）。

执行的SQL语句 #

CREATE TABLE pivTraining 
ENGINE=connect TABLE_TYPE=pivot
OPTION_LIST='PIVOTCOL=programID,FNCCOL=trainingDate,FUNCTION=MAX,USER=root,PASSWORD=######' 
SRCDEF='SELECT userName, CONCAT(''Program_'',programID) AS programID, MAX(trainingDate) AS trainingDate FROM training GROUP BY userName,programID';

期望结果 #

MariaDB [db]> select * from pivTraining;                                                                                                                                                                                         
+----------+------------+------------+
| userName | Program_1  | Program_2  |
+----------+------------+------------+
| kennjoh  | 2023-10-04 | NULL       |
| kennjoh3 | 2023-12-21 | 2022-12-05 |
| kennjoh4 | 2023-12-19 | NULL       |
+----------+------------+------------+

实际结果 #

实际查询得到的所有日期均为1970-01-01：

MariaDB [db]> select * from pivTraining;                                                                                                                                                                                         
+----------+------------+------------+
| userName | Program_1  | Program_2  |
+----------+------------+------------+
| kennjoh  | 1970-01-01 | 1970-01-01 |
| kennjoh3 | 1970-01-01 | 1970-01-01 |
| kennjoh4 | 1970-01-01 | 1970-01-01 |
+----------+------------+------------+

推测原因与已尝试方案 #

推测是Connect引擎处理日期类型时出现数值为0的情况导致。已尝试移除MAX函数、添加DATE_FORMAT参数、查阅官方文档等方法，均未解决问题。

相关表结构 #

training表结构 #

MariaDB [db]> describe training;
+----------------+------------------+------+-----+---------+----------------+
| Field          | Type             | Null | Key | Default | Extra          |
+----------------+------------------+------+-----+---------+----------------+
| tid            | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| userName       | varchar(255)     | NO   |     | NULL    |                |
| programID      | int(10) unsigned | NO   |     | NULL    |                |
| trainingDate   | date             | YES  |     | NULL    |                |
| trainingExpiry | int(10) unsigned | NO   |     | 365     |                |
+----------------+------------------+------+-----+---------+----------------+

pivTraining表结构 #

MariaDB [db]> describe pivTraining;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| userName | varchar(255) | NO   |     | NULL    |       |
| 1        | date         | YES  |     | NULL    |       |
| 2        | date         | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+

中间验证查询（结果正确） #

MariaDB [db]> SELECT userName, 
CONCAT(''Program_'',programID) AS program, 
MAX(trainingDate) AS trainingDate, 
MAX(ADDDATE(trainingDate, INTERVAL trainingExpiry DAY)) AS expiryDate 
FROM training 
GROUP BY userName, programID;
+----------+-----------+--------------+------------+
| userName | program   | trainingDate | expiryDate |
+----------+-----------+--------------+------------+
| kennjoh  | Program_1 | 2023-10-04   | 2024-10-03 |
| kennjoh3 | Program_1 | 2023-12-21   | 2024-12-20 |
| kennjoh3 | Program_2 | 2022-12-05   | 2023-12-05 |
| kennjoh4 | Program_1 | 2023-12-19   | 2024-12-18 |
+----------+-----------+--------------+------------+

恳请熟悉Connect引擎的人士帮忙排查问题原因。

内容的提问来源于stack exchange，提问作者drJPK