Filter invoices on invoice date after optimization

In relationship to Teamleader V2 performance improvements in server-side filtering of date columns, I am seeing some numbers I can’t explain in a test scenario on 23.0.8:

select count(*)
from   v2.[invoices@tlr]@InvantiveBridgeOnline
where  invoice_date >= trunc(sysdateutc) - VALUE

returns unlogic values it seems:

Value Count
0 28
1 11
2 185

I would expect the value for 1 to be between 28 and 185, and not to be 11.

Test on UniversalSQL-editor

Through direct access on the UniversalSQL-editor on Invantive Cloud, the measurements were:

Value Count
0 10
1 84
2 95
3 117
4 171
5 241

using the statement:

select count(*)
from   v2.invoices@tlr
where  invoice_date >= trunc(sysdateutc) - VALUE

This number range seems logical.

The API-calls executed seem correct:

invoice_date_after = 02-09-2023 00:00:00 (datetime)
invoice_date_after = 01-09-2023 00:00:00 (datetime)
invoice_date_after = 31-08-2023 00:00:00 (datetime)
invoice_date_after = 30-08-2023 00:00:00 (datetime)
invoice_date_after = 29-08-2023 00:00:00 (datetime)
invoice_date_after = 28-08-2023 00:00:00 (datetime)

as determined by

select distinct parameter_list
from   sessionios@datadictionary
where  url like '%invoices.list'

Bridge Online-driver

As a next step, the access has been tested using the Invantive Bridge Online-driver as shown in the original report, using Invantive Query Tool 22.0.

The results are intriguingly different:

Value Count
0 10
1 84
2 11
3 33
4 87
5 157

The OData4-queries were:

  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-09-02T00:00:00Z
  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-09-01T00:00:00Z
  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-08-31T00:00:00Z
  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-08-30T00:00:00Z
  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-08-29T00:00:00Z
  • PATH/Teamleader.V2.Invoices@tlr?$filter=invoice_date%20ge%202023-08-28T00:00:00Z

which by itself seem correct.

Invantive Bridge Online rewrites this into:

select t.*
from   Teamleader.V2.Invoices@tlr t
where  ([invoice_date] >= :w1)

with :w1 binding to values such as 28-08-2023 00:00:00 (datetime).

Bridge Online then returns the the number of rows expected from the first query:

These results are then filtered client-side where necessary, in which process more rows are removed by the Bridge Online driver.


The rows are compared from both approaches, replacing count(*) by:

select invoice_number invoice_number
,      to_char(invoice_date, 'YYYYMMDD') invoicedate
from   ...

On Invantive Cloud, the invoicedate is displayed as a text like 20230829, but on Invantive Query Tool it displays as 08/29/2023 00:00:00 +00:00, which signals a difference. The original value on Query Tool is 29-8-2023 00:00:00 +00:00.

The problem seems to be that invoice_date is handled differently.

The table columns gives a hint:


Conforming to OData4-protocol, some datatypes can be altered. In this case, the date has become datetimeoffset. And even worse, some operations implemented in the Bridge Online driver seem to incorrectly handle this datatype in filtering client side. Additionally, the to_char gives an incorrect result.

For a future release, improvements will be applied.

The downloads from Bridge Online without using the Query Tool are correct.

Deze vraag is automatisch gesloten na 2 weken inactiviteit. Het laatste gegeven antwoord is gemarkeerd als oplossing.

Gelieve een nieuwe vraag te stellen via een apart topic als het probleem opnieuw optreedt. Gelieve in de nieuwe vraag een link naar dit topic op te nemen door de URL er van in de tekst te plakken.

Dit topic is 7 dagen na het laatste antwoord automatisch gesloten. Nieuwe antwoorden zijn niet meer toegestaan.