Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.16
-
None
-
RHEL 7 running rh-mariadb105 from EPEL
Description
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:
- removing the MAX function in the SRCDEF clause i.e. MAX(trainingDate) as trainingDate and removing the FUNCTION=MAX from the OPTIONS_LIST. I think this MAX is probably necessary though due otherwise the intermediate table gives the first value of trainingDate.
- adding DATE_FORMAT=YYYY-MM-DD to the options list
- reading the docs https://mariadb.com/kb/en/connect-pivot-table-type/#defining-a-pivot-table-from-a-source-table(https://mariadb.com/kb/en/connect-pivot-table-type/#defining-a-pivot-table-from-a-source-table)
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 |
|
+----------+-----------+--------------+------------+ |