Improvements for OData Filters

Hello TIBCO Team!

As directed by the TIBCO support team in case 02162240, I am opening this Enhancement Request (ER) on behalf of the customer Petrobras to request improvements to the functionality and OData filters, as per the described errors in the following two scenarios. Both cases were identified in version 8.6.0 of TDV:

The OData filter $expand and $top/$skip do not work correctly according to the OData v4 specification (https://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part1-protocol.html). For example:

When executing the query, https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional/unidade_organizacional?$filter=numeroSistema eq 7603&$expand=responsavel,forca_trabalho

The return occurs successfully:

{ "@odata.context": https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional$metadata#unidade_organizacional(responsavel(),forca_trabalho()), "value": [ { "numeroSistema": 7603, "sigla": "UN-ES/ATP-NC/OP-N", "dataInicio": "2000-11-01T00:00:00.000Z", "dataFim": "9999-12-31T00:00:00.000Z", "responsavel": [], "forca_trabalho": [ { "hash": "7F24B41175102DC24B2EEB95C7D842F7", "chave": null, "indicador_estado_chave": null, "matricula": "40319550", "email": null, "nome_completo": "JTSL RYNGSL KYPTASTY", "nome_social": null, "nome_guerra": "JTSL", "empresa": "PETROBRAS", "inicio_vinculo": "2015-10-29T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2015-10-29T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 }, { "hash": "055D5B452B5B96EA3B80360CF0A36556", "chave": null, "indicador_estado_chave": null, "matricula": "40359431", "email": null, "nome_completo": "RTPÉRAT YLPSAÇY SALVANT", "nome_social": null, "nome_guerra": "RTPÉRAT", "empresa": "PETROBRAS", "inicio_vinculo": "2014-08-10T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2014-08-11T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 }, { "hash": "41B7B60BE2EAC9C9965A46D9130212B6", "chave": null, "indicador_estado_chave": null, "matricula": "40319851", "email": null, "nome_completo": "WSKSLSY ÇQYRTS ÇY SNRYRNYÇYT", "nome_social": null, "nome_guerra": "WSKSLSY", "empresa": "PETROBRAS", "inicio_vinculo": "2014-08-10T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2014-08-11T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 }, { "hash": "39E37B358ABD619507042D8980963C80", "chave": null, "indicador_estado_chave": null, "matricula": "40319574", "email": null, "nome_completo": "JTSS YÇALSTN RYPTS PYRSÇT", "nome_social": null, "nome_guerra": "YÇALSTN", "empresa": "PETROBRAS", "inicio_vinculo": "2014-08-10T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2014-08-11T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 }, { "hash": "CF338FA7B4951A4757C390795626D1E6", "chave": null, "indicador_estado_chave": null, "matricula": "40319771", "email": null, "nome_completo": "RTPQLT ÇS FYRAY S SALVY", "nome_social": null, "nome_guerra": "FYRAY", "empresa": "PETROBRAS", "inicio_vinculo": "2014-08-10T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2014-08-11T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 } ] } ] }

However, when performing a query using the $top filter https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional/unidade_organizacional?$filter=numeroSistema eq 7603&$expand=responsavel,forca_trabalho&$top=1 it returns the data of the "unidade_organizacional" with an empty "forca_trabalho" array.

{ "@odata.context": https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional$metadata#unidade_organizacional(responsavel(),forca_trabalho()), "value": [ { "numeroSistema": 7603, "sigla": "UN-ES/ATP-NC/OP-N", "dataInicio": "2000-11-01T00:00:00.000Z", "dataFim": "9999-12-31T00:00:00.000Z", "responsavel": [], "forca_trabalho": [] } ] }

When performing the query using the $top filter with a value of 2 https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional/unidade_organizacional?$filter=numeroSistema eq 7603&$expand=responsavel,forca_trabalho&$top=2 it returns the data of the "unidade_organizacional" with the "forca_trabalho" array containing 1 element.

{ "@odata.context": https://vdadosd.petrobras.com.br:9402/odata4/webservices/APIM/birh/v2/unidade_organizacional$metadata#unidade_organizacional(responsavel(),forca_trabalho()), "value": [ { "numeroSistema": 7603, "sigla": "UN-ES/ATP-NC/OP-N", "dataInicio": "2000-11-01T00:00:00.000Z", "dataFim": "9999-12-31T00:00:00.000Z", "responsavel": [], "forca_trabalho": [ { "hash": "7F24B41175102DC24B2EEB95C7D842F7", "chave": null, "indicador_estado_chave": null, "matricula": "40319550", "email": null, "nome_completo": "JTSL RYNGSL KYPTASTY", "nome_social": null, "nome_guerra": "JTSL", "empresa": "PETROBRAS", "inicio_vinculo": "2015-10-29T00:00:00.000Z", "fim_vinculo": "2017-08-08T00:00:00.000Z", "vinculo": "Prestador de Serviço", "inicioLotacao": "2015-10-29T00:00:00.000Z", "fimLotacao": "2018-02-23T00:00:00.000Z", "unor_fk": 7603 } ] } ] }

When performing the query using the $top filter with a value of 3, the "forca_trabalho" will be returned with 3 elements, and so on. Therefore, each record in the arrays counts as a new record for each return. As it is not possible to predict the quantity of elements returned to the array, using $skip and $top for pagination becomes unfeasible.

Implementation of the $any and $all filters as described in https://www.odata.org/blog/support-for-any-and-all/. When attempting to execute a query using these filters, errors are returned, as can be seen in the attached image.

Best regards.