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.
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:
| Parameter |
|---|
| 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'
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:
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.
