FromDateAsDate column is marked as primary Key

[Florian Haase]

Hi,

interesting problem here: In the exchange Table the fromDate column is a part of the primary key. Due to paging issues when not sort-ordering by primary key columns we have implemented a generic way to sort always by PK (something Visma should have implemented in the API-Layer, but that's another thread).

In the Exchange-Table scheme the fromDateAsDate column is also marked as primaryKey even this is a generated column by the API-engine. OrderBy on that column will not work.

The PK marking of that column should be removed on all tables where fromDate is a part of the PK. We have not checked if there are any time-columns with the same problem...

Florian

[StianVrba]

Hi,

I work with Florian and have additional information about the issue.

First: It is easy to verify that a query on a large table (>500k rows) returns different rows for the same "first" and "skip" arguments when OrderBy is not given. (You have to skip ca 300k rows to trigger the problem.)

Second: The model information query for columns (Model information | Documentation (vismasoftware.no)) does not return information about which columns constitute the PK of the table. Because composite PKs are possible, the model information query should flag each PK column with its physical position in the composite PK. Requesting ordering by PK columns in different order than their physical order will return a correct result, but with a heavy performance hit. (Example: if a table has schema (A, B, C, D) with (A, B) as composite PK, then column A should be flagged with "PK[0]" and column B with "PK[1]".)

Third: Using GraphQL schema introspection query, it is possible to get a "description" for each column. The "description" for PK columns begins with "[PK]", but there is no indication of the column's physical position in the PK.

Lastly: The GraphQL API exposes date and time columns both as integers and as "proper" date/time types (with "AsDate", "AsTime" suffix). The schema introspection query flags both the "int" column and "AsDate" column as PK, presumably because they map to the same physical column. The "AsDate"/"AsTime" columns should never be flagged as PK (when present in the PK) because they're not even available for use in OrderBy.