search

pgollangi / FireQL

Interactive CLI and Go library to query Google Firestore database using SQL syntax.
https://pgollangi.github.io/FireQL/
MIT License
31 stars 1 forks source link

Select queries should cope with null values in columns #13

Closed buzzware closed 10 months ago

buzzware commented 1 year ago

Describe the bug I have a collection of records with a generally consistent set of properties, however one of those properties can either be a number or null. I can't find a way to select the column with or without the rows with a null value. It works fine if I only select columns that are always non-null.

To Reproduce

I am using the Explore query feature of https://grafana.com/grafana/plugins/pgollangi-firestore-datasource/

I tried the query

Select channel_uuid,duration_ms,end_time_ms,lanes from LatestQuantum

FireQL doesn't cope with this, reporting "fireql.Execute: unknown field duration_ms in doc " (I've replaced the actual doc id). The value of the field in that doc exists with the value of null, while it is a number in other docs.

So I tried various govaluate operators eg. Select channel_uuid,(duration_ms ?? 0) as duration_ms,end_time_ms,lanes from LatestQuantum but I get the error "fireql.Execute: syntax error at position 35 near ':v1'"

I also can't seem to exclude those records :

select channel_uuid,end_time_ms,lanes,duration_ms as duration_ms from LatestQuantum where duration_ms != null

fireql.Execute: firestore: must use '==' when comparing <nil>
select channel_uuid,end_time_ms,lanes,duration_ms as duration_ms from LatestQuantum where (duration_ms ?? 0) > 0

fireql.Execute: syntax error at position 105 near ':v1'

Expected behavior The column should return whatever type the data contains. Or at least allow nullable types. Or at the very least allow me to exclude those records. Right now I simply can't use that column because the data may contain nulls.

Thanks for these projects though - they have a lot of potential

pgollangi commented 10 months ago

@buzzware thanks for reporting this issue. Sorry it took sometime to get back.

We can allow selecting fields having NULL values in the document. I'll make necessary changes.

However I have to check the possibility of filtering by NULL values using WHERE condition. let me know if you really need this? so that I can plan accordingly

pgollangi commented 10 months ago

This has been fixed with FireQL version 0.3.6 A new version of Grafana plugin submitted which includes this change. https://github.com/pgollangi/firestore-grafana-datasource/releases/tag/v0.2.6

Please give it a star and share if you think FireQL and Grafana Plugin is useful to you!

Thanks!