QGIS Failing to read some values from MDB joined to GPKG

[Saijin-Naib]

Describe the bug
For some odd reason QGIS does not properly read some records from some columns of a vanilla/aspatial Access MDB when joined against a GPKG with mixed spatial/aspatial data.

How to Reproduce
Have GPKG with geometries and some tabular data joined against a completely vanilla/aspatial MDB
Note that some records in QGIS are not parsed/loaded correctly (For example, CAMP_Length)
Verify that these records can be recalled properly outside of QGIS

QGIS view:

Note how many segments have a CAMP_Length of 0. This is not valid. Some records in the dataset are NULL, but they show properly as NULL.

Compare to the view in CAMP-RS:

Compare to the view in MDBPlus:

QGIS and OS versions

QGIS version
3.18.3-Zürich
QGIS code revision
735cc85
Compiled against Qt
5.15.2
Running against Qt
5.15.2
Compiled against GDAL/OGR
3.3.0
Running against GDAL/OGR
3.3.0
Compiled against GEOS
3.9.1-CAPI-1.14.2
Running against GEOS
3.9.1-CAPI-1.14.2
Compiled against SQLite
3.35.2
Running against SQLite
3.35.2
Compiled against PDAL
2.2.0
Running against PDAL
2.2.0 (git-version: 365171)
PostgreSQL Client Version
13.0
SpatiaLite Version
5.0.1
QWT Version
6.1.3
QScintilla2 Version
2.11.5
Compiled against PROJ
8.0.1
Running against PROJ
Rel. 8.0.1, March 5th, 2021
OS Version
Windows 10 Version 2009
Active python plugins
CalculateGeometry;
MemoryLayerSaver;
slyr;
zoom_level;
db_manager;
MetaSearch;
processing

Additional context
Same dataset as Issue #43287
#43287 (comment)

[nyalldawson]

What does ogrinfo report?

[Saijin-Naib]

Not sure exactly how you want me to query these data, but here's what I have thus far:

PS C:\OSGeo4W64\bin> .\ogrinfo.exe Q:\User\Brett_Carlock\Projects\QGIS\Data\CAMP-RS\CAMP-RS.mdb
INFO: Open of `Q:\User\Brett_Carlock\Projects\QGIS\Data\CAMP-RS\CAMP-RS.mdb'
      using driver `ODBC' successful.
1: Budget
2: Distress
3: Distress_Tree
4: Inventory
5: Priority_Settings
6: Repair
7: Repair_Category
8: Shoulder
9: Surface
10: Survey_Data
11: Survey_Master
12: Units
13: Units_selected
14: inventory_view

PS C:\OSGeo4W64\bin> .\ogrinfo.exe Q:\User\Brett_Carlock\Projects\QGIS\Data\CAMP-RS\CAMP-RS.gpkg
INFO: Open of `Q:\User\Brett_Carlock\Projects\QGIS\Data\CAMP-RS\CAMP-RS.gpkg'
      using driver `GPKG' successful.
1: Inventory_DPW_Centroids (Point)
2: Inventory_Midpoints (Point)
3: Inventory_Segments (Multi Line String)
4: Snow_Segments (Multi Line String)
5: Points (Point)
6: Inventory_Snow (None)
7: Inventory_DOT (None)
8: Inventory_Areas (None)
9: Inventory_Debris (None)
10: Inventory_Inventory (None)
11: Snow_Route_Types (None)
12: Snow_Route_Type_1 (None)
13: Snow_Route_Type_2 (None)
14: Snow_Route_Type_3 (None)
15: Snow_Route_Type_4 (None)
16: Snow_Route_IDs (None)
17: Snow_Points_Type_3 (None)
18: Snow_Segments_Type_3 (None)

[nyalldawson]

If you query the "inventory" table using ogrinfo, does it show the missing values?

[Saijin-Naib]

Sorry it took me so long. Didn't know how to use OGRInfo and wasn't at work.

PS C:\OSGeo4W64\bin> .\ogrinfo.exe -fields=yes -sql "SELECT * FROM Inventory" Q:\User\Brett_Carlock\Projects\QGIS\Data\CAMP-RS\CAMP-RS.mdb > stdout 2> stderr

It looks like OGR isn't reading some of the values properly, at least according to stderr. I guess these are the ones that read out as 0 when they should not be.

It is tricky to pick out in the stdout because BEGIN_MP of 0 is totally valid, especially for a segment that begins that roadway (the milepoint of that roadway would normally begin at 0.0mi and increase from there). However, END_MP and LENGTH can NEVER be 0, so any records that OGR parses with those as 0 are erroneous. NULL is perfectly acceptable.

stdout.txt
stderr.txt

[gioman]

It looks like OGR isn't reading some of the values properly, at least according to stderr.

@Saijin-Naib so probably this is an upstream issue.

[Saijin-Naib]

@Saijin-Naib so probably this is an upstream issue.

Thanks, I've filed an Issue there. Do we park this in feedback or close for now?

[gioman]

Thanks, I've filed an Issue there

@Saijin-Naib maybe we can leave it here in feedback, please please add the link/reference of the upstream ticket.

[Saijin-Naib]

Upstream (GDAL) Issue:
OSGeo/gdal#3885

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[Saijin-Naib]

Awaiting merge of upstream fix to QGIS codebase to attempt reproducing again.

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[Saijin-Naib]

Still awaiting upstream to reproduce.

[nyalldawson]

Ah, I can 100% confirm this is an upstream issue

[github-actions]

The QGIS project highly values your report and would love to see it addressed. However, this issue has been left in feedback mode for the last 14 days and is being automatically marked as "stale".
If you would like to continue with this issue, please provide any missing information or answer any open questions. If you could resolve the issue yourself meanwhile, please leave a note for future readers with the same problem and close the issue.
In case you should have any uncertainty, please leave a comment and we will be happy to help you proceed with this issue.
If there is no further activity on this issue, it will be closed in a week.

[Saijin-Naib]

Still pending.

[alexbruy]

Fixed on GDAL side. @Saijin-Naib does it work now in QGIS?

[Saijin-Naib]

I will have to test, but I'm no longer on windows where I opened the issue, though I'm not sure it matters.

Might take a while to get to my desktop to reproduce it, so sorry in advance.

[alexbruy]

Ok, I tested it and it is fixed. I don't have missed values in the END_MP and LENGTH fields.