|
When using connect engine on table type pivot date type values are not handled correctly when using function MAX resulting in 0 in the pivot table and displayed value of 1970-01-01
I am working with an Innodb table in long format (training) and need to pivot this to wide format (pivTraining) reporting the latest (MAX???) date for each program.
I am using the following statement on Maria 10.5.16 running on RHEL 7
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';
|
and am trying to go from this:
MariaDB [db]> select * from training; +-----+----------+-----------+--------------+----------------+
|
| tid | userName | programID | trainingDate | trainingExpiry |
|
+-----+----------+-----------+--------------+----------------+
|
| 1 | kennjoh4 | 1 | 2023-12-17 | 365 |
|
| 2 | kennjoh4 | 1 | 2023-12-19 | 365 |
|
| 3 | kennjoh3 | 1 | 2023-12-19 | 365 |
|
| 4 | kennjoh4 | 1 | 2023-12-19 | 365 |
|
| 6 | kennjoh3 | 1 | 2023-12-21 | 365 |
|
| 7 | kennjoh3 | 1 | 2023-12-21 | 365 |
|
| 10 | kennjoh3 | 2 | 2022-12-05 | 365 |
|
| 11 | kennjoh | 1 | 2023-10-04 | 365 |
|
+-----+----------+-----------+--------------+----------------+
|
to this:
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 |
|
+----------+------------+------------+
|
However what I'm getting is:
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 |
|
+----------+------------+------------+
|
This looks to me like the connect engine is seeing the date type but doing something to it that results in a value of 0 hence the 1970-01-01
I have tried:
It has been suggested that this is a bug in the code.
outputs of describe:
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 | |
|
+----------------+------------------+------+-----+---------+----------------+
|
|
MariaDB [db]> describe pivTraining;
|
+----------+--------------+------+-----+---------+-------+
|
| Field | Type | Null | Key | Default | Extra |
|
+----------+--------------+------+-----+---------+-------+
|
| userName | varchar(255) | NO | | NULL | |
|
| 1 | date | YES | | NULL | |
|
| 2 | date | YES | | NULL | |
|
+----------+--------------+------+-----+---------+-------+
|
Intermediate Table
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 |
|
+----------+-----------+--------------+------------+
|
|