Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.6.12
-
None
-
Ubuntu 22.04
Description
Given a sort of EAV table with the attached data, I tried defining a PIVOT table to transform the `campo` in actual columns, where the value would be `valore`, and the remaning `id_*` fields would be the remaning key fields as
CREATE OR REPLACE TABLE my_pivot_table
ENGINE=CONNECT
TABLE_TYPE=PIVOT
TABNAME=my_source_table
OPTION_LIST='user=connect,PivotCol=campo,FncCol=valore,GroupBy=1';
However this produces the wrong structure, as seen in the second sheet of attached data.
Here you see the `id_*` fields are repeated for each field determined by `campo`, and each rows populates only one field, leaving the others empty, while I would have expected a single row with all the fields populated.
I think the table definition is incorrect, hence this appears to be a problem in the connect engine itself.
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Description |
Given a sort of EAV table with the following data:
``` id_dipendente id_azienda id_sede revisione_documento campo valore 1 2 96 0 Matricola 177 1 2 96 0 ID Utente 177 1 2 96 0 ID Persona 177 1 2 96 0 First Name [REDACTED] 1 2 96 0 Last Name [REDACTED] 1 2 96 0 Company Code [REDACTED] 1 2 96 0 Company [REDACTED] 1 2 96 0 Business Unit Code BU-00008 1 2 96 0 Business Unit HSE 1 2 96 0 Division Code DIV-00007 1 2 96 0 Division HSE ITA 1 2 96 0 Department Code DEP-00072 1 2 96 0 Department HSE 1 2 96 0 Parent Department DEP-00078-CEO 1 2 96 0 Related Division DIV-00007-HSE ITA 1 2 96 0 Job Title Code PO-00093 1 2 96 0 Job Title HSE Manager 1 2 96 0 Employee Class Employee 2 2 96 0 Matricola 1014 2 2 96 0 ID Utente 1014 2 2 96 0 ID Persona 1014 2 2 96 0 First Name [REDACTED] 2 2 96 0 Last Name [REDACTED] 2 2 96 0 Company Code [REDACTED] 2 2 96 0 Company Aboca SPA [REDACTED] 2 2 96 0 Business Unit Code BU-00008 2 2 96 0 Business Unit HSE 2 2 96 0 Division Code DIV-00007 2 2 96 0 Division HSE ITA 2 2 96 0 Department Code DEP-00074 2 2 96 0 Department Health & Safety 2 2 96 0 Parent Department DEP-00072-HSE 2 2 96 0 Job Title Code PO-00096 2 2 96 0 Job Title Health & Safety Specialist 2 2 96 0 Employee Class Employee ``` I tried defining a PIVOT table to transform the `campo` in actual columns, where the value would be `valore`, and the remaning `id_*` fields would be the remaning key fields as ``` CREATE OR REPLACE TABLE my_pivot_table ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=my_source_table OPTION_LIST='user=connect,PivotCol=campo,FncCol=valore,GroupBy=1'; ``` However this produces the wrong structure ``` # id_dipendente id_azienda id_sede revisione_documento Business Unit Business Unit Code Company Company Code Department Department Code Division Division Code Employee Class First Name ID Persona ID Utente Job Title Job Title Code Last Name Matricola Parent Department Related Division Codici Mansioni Indirizzo E-mail Personale 1 2 96 0 HSE 1 2 96 0 BU-00008 1 2 96 0 Aboca SPA Società Agricola 1 2 96 0 LE-001 1 2 96 0 HSE 1 2 96 0 DEP-00072 1 2 96 0 HSE ITA 1 2 96 0 DIV-00007 1 2 96 0 Employee 1 2 96 0 ANGELINI 1 2 96 0 177 1 2 96 0 177 1 2 96 0 HSE Manager 1 2 96 0 PO-00093 1 2 96 0 FEDERICO 1 2 96 0 000177 1 2 96 0 DEP-00078-CEO 1 2 96 0 DIV-00007-HSE ITA 2 2 96 0 HSE 2 2 96 0 BU-00008 2 2 96 0 Aboca SPA Società Agricola 2 2 96 0 LE-001 2 2 96 0 Health & Safety 2 2 96 0 DEP-00074 2 2 96 0 HSE ITA 2 2 96 0 DIV-00007 2 2 96 0 Employee 2 2 96 0 GENNAIOLI 2 2 96 0 1014 2 2 96 0 1014 2 2 96 0 Health & Safety Specialist 2 2 96 0 PO-00096 2 2 96 0 ELENA 2 2 96 0 001014 2 2 96 0 DEP-00072-HSE ``` Here you see the `id_*` fields are repeated for each field determined by `campo`, and each rows populates only one field, leaving the others empty, while I would have expected a single row with all the fields populated. I *think* the table definition is incorrect, hence this appears to be a problem in the connect engine itself. |
Given a sort of EAV table with the following data:
I tried defining a PIVOT table to transform the `campo` in actual columns, where the value would be `valore`, and the remaning `id_*` fields would be the remaning key fields as ``` CREATE OR REPLACE TABLE my_pivot_table ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=my_source_table OPTION_LIST='user=connect,PivotCol=campo,FncCol=valore,GroupBy=1'; ``` However this produces the wrong structure ``` # id_dipendente id_azienda id_sede revisione_documento Business Unit Business Unit Code Company Company Code Department Department Code Division Division Code Employee Class First Name ID Persona ID Utente Job Title Job Title Code Last Name Matricola Parent Department Related Division Codici Mansioni Indirizzo E-mail Personale 1 2 96 0 HSE 1 2 96 0 BU-00008 1 2 96 0 Aboca SPA Società Agricola 1 2 96 0 LE-001 1 2 96 0 HSE 1 2 96 0 DEP-00072 1 2 96 0 HSE ITA 1 2 96 0 DIV-00007 1 2 96 0 Employee 1 2 96 0 ANGELINI 1 2 96 0 177 1 2 96 0 177 1 2 96 0 HSE Manager 1 2 96 0 PO-00093 1 2 96 0 FEDERICO 1 2 96 0 000177 1 2 96 0 DEP-00078-CEO 1 2 96 0 DIV-00007-HSE ITA 2 2 96 0 HSE 2 2 96 0 BU-00008 2 2 96 0 Aboca SPA Società Agricola 2 2 96 0 LE-001 2 2 96 0 Health & Safety 2 2 96 0 DEP-00074 2 2 96 0 HSE ITA 2 2 96 0 DIV-00007 2 2 96 0 Employee 2 2 96 0 GENNAIOLI 2 2 96 0 1014 2 2 96 0 1014 2 2 96 0 Health & Safety Specialist 2 2 96 0 PO-00096 2 2 96 0 ELENA 2 2 96 0 001014 2 2 96 0 DEP-00072-HSE ``` Here you see the `id_*` fields are repeated for each field determined by `campo`, and each rows populates only one field, leaving the others empty, while I would have expected a single row with all the fields populated. I *think* the table definition is incorrect, hence this appears to be a problem in the connect engine itself. |
| Attachment | pivot_error.xlsx [ 69549 ] |
| Description |
Given a sort of EAV table with the following data:
I tried defining a PIVOT table to transform the `campo` in actual columns, where the value would be `valore`, and the remaning `id_*` fields would be the remaning key fields as ``` CREATE OR REPLACE TABLE my_pivot_table ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=my_source_table OPTION_LIST='user=connect,PivotCol=campo,FncCol=valore,GroupBy=1'; ``` However this produces the wrong structure ``` # id_dipendente id_azienda id_sede revisione_documento Business Unit Business Unit Code Company Company Code Department Department Code Division Division Code Employee Class First Name ID Persona ID Utente Job Title Job Title Code Last Name Matricola Parent Department Related Division Codici Mansioni Indirizzo E-mail Personale 1 2 96 0 HSE 1 2 96 0 BU-00008 1 2 96 0 Aboca SPA Società Agricola 1 2 96 0 LE-001 1 2 96 0 HSE 1 2 96 0 DEP-00072 1 2 96 0 HSE ITA 1 2 96 0 DIV-00007 1 2 96 0 Employee 1 2 96 0 ANGELINI 1 2 96 0 177 1 2 96 0 177 1 2 96 0 HSE Manager 1 2 96 0 PO-00093 1 2 96 0 FEDERICO 1 2 96 0 000177 1 2 96 0 DEP-00078-CEO 1 2 96 0 DIV-00007-HSE ITA 2 2 96 0 HSE 2 2 96 0 BU-00008 2 2 96 0 Aboca SPA Società Agricola 2 2 96 0 LE-001 2 2 96 0 Health & Safety 2 2 96 0 DEP-00074 2 2 96 0 HSE ITA 2 2 96 0 DIV-00007 2 2 96 0 Employee 2 2 96 0 GENNAIOLI 2 2 96 0 1014 2 2 96 0 1014 2 2 96 0 Health & Safety Specialist 2 2 96 0 PO-00096 2 2 96 0 ELENA 2 2 96 0 001014 2 2 96 0 DEP-00072-HSE ``` Here you see the `id_*` fields are repeated for each field determined by `campo`, and each rows populates only one field, leaving the others empty, while I would have expected a single row with all the fields populated. I *think* the table definition is incorrect, hence this appears to be a problem in the connect engine itself. |
Given a sort of EAV table with the attached data, I tried defining a PIVOT table to transform the `campo` in actual columns, where the value would be `valore`, and the remaning `id_*` fields would be the remaning key fields as
CREATE OR REPLACE TABLE my_pivot_table ENGINE=CONNECT TABLE_TYPE=PIVOT TABNAME=my_source_table OPTION_LIST='user=connect,PivotCol=campo,FncCol=valore,GroupBy=1'; However this produces the wrong structure, as seen in the second sheet of attached data. Here you see the `id_*` fields are repeated for each field determined by `campo`, and each rows populates only one field, leaving the others empty, while I would have expected a single row with all the fields populated. I *think* the table definition is incorrect, hence this appears to be a problem in the connect engine itself. |
| Fix Version/s | 10.6 [ 24028 ] |
| Assignee | Andrew Hutchings [ JIRAUSER52179 ] |
I attached the original and pivoted data as Excel file as I was not able to properly format the table directly in the bug description, sorry for that.