search

stablekernel / postgresql-dart

Dart PostgreSQL driver: supports extended query format, binary protocol and statement reuse.
https://www.dartdocs.org/documentation/postgres/latest
BSD 3-Clause "New" or "Revised" License
129 stars 32 forks source link

mappedResultsQuery splits rows in 2 subsets with key null when there are functions in SELECT parameters #149

Open pedropastor opened 3 years ago

pedropastor commented 3 years ago

This query: SELECT index, mac::text, imei, backend, status, lockstatus, powerstatus, id, chargestatus, lastping::timestamptz, public.ST_AsText(lastpos) AS lastpos FROM iotdevices WHERE backend = "backend01"

Instead of returning this: {iotdevices: {index: 5, mac: e2:40:8b:ff:fe:48:02:63, imei: 867584031544559, backend: back01, status: OK, lockstatus: 1, powerstatus: 45, id: 010115, chargestatus: false, lastping: null, lastpos: null}}

It is incorrectly returning this, and I am not finding a way to retrieve the data from it properly: {iotdevices: {index: 5, imei: 867584031544559, backend: back01, status: OK, lockstatus: 1, powerstatus: 45, id: 010115, chargestatus: false, lastping: null}, null: {mac: e2:40:8b:ff:fe:48:02:63, lastpos: null}}

I guess there is some issue behind mapped query, and the execution (it may treat the query as 2 separate ones). When tested directly in DB works fine.

isoos commented 3 years ago

@pedropastor: Would you be able/willing to debug this a bit further with the postgres library? OID resolution happens here: https://github.com/stablekernel/postgresql-dart/blob/master/lib/src/connection.dart#L356-L398

It is likely that ::type casts change the oid for these fields and the SELECT relname FROM pg_class WHERE relkind='r' AND oid IN ($unresolvedIDString) ORDER BY oid ASC is not able to get any reasonable result for it.

isoos commented 3 years ago

As a workaround, you could use the regular query method, and when processing the row, calling toColumnMap, which doesn't use the table names, only the column names.

pedropastor commented 3 years ago

The workaround works, the regular query method returns things properly in one row.

I compromise to check the OID resolution, but we are now in a very tight schedule for delivering projects so I cannot promise anything about timing.

Thanks for your work and support.