Wrong results returned when querying a RECORD column about bigquery-emulator

[ohaibbq]

I can take a look at this soon if you could assign it to me @totem3.
I have a pending PR up that fixes some API-level value encoding issues similar to this one at goccy/go-zetasqlite#111

[prismec]

The behaviour for TIMESTAMP fields of records is also affected (and maybe also others)

In this case, selecting the record returns '2023-08-01T21:22:23Z' for the inserted timestamp value 2023-08-01T21:22:23.123456Z. Educated guess is that the expected results should be a numeric timestamp value. Also the precision of the returned value is degraded.

bq outputs for this case

SELECT * FROM record_test

+------------------------------------------------------------------------------------------------+
|                                              rec                                               |
+------------------------------------------------------------------------------------------------+
| {"numeric_value":"47123456789/1000000000","timestamp_value":"<date out of range for display>"} |
+------------------------------------------------------------------------------------------------+

SELECT rec.timestamp_value FROM record_test

+---------------------+
|   timestamp_value   |
+---------------------+
| 2023-08-01 21:22:23 |
+---------------------+

[totem3]

Thank you for the detailed report.
I have confirmed that there are indeed differences in output compared to BigQuery, and also outputs vary between values within a structure and those that are not.

I briefly investigated the cause and would like to document it.

Internally, in go-zetasqlite which we use for constructing responses, there is a difference in the method of construction for structured and non-structured data.

For Numeric types, we convert to a string using NumericValue's ToString.
https://github.com/goccy/go-zetasqlite/blob/e0a36047b564fab7bc36f17031c86225dabf9fea/internal/rows.go#L246-L251

In the case of a Struct, we first convert it to a StructValue using StructValue's ToStruct.
https://github.com/goccy/go-zetasqlite/blob/e0a36047b564fab7bc36f17031c86225dabf9fea/internal/rows.go#L297-L302

Then, we use StructValue's Interface to convert it to interface{}. In this process, we recursively call Interface for each field to convert all fields.

The definition of NumericValue's Interface is as follows, nv.Rat.String().
https://github.com/goccy/go-zetasqlite/blob/e0a36047b564fab7bc36f17031c86225dabf9fea/internal/value.go#L798-L800
This indeed returns a value like 47123456789/1000000000.

On the other hand, the definition of ToString is as follows, which indeed returns a value like 47.123456789.

Due to these differences, it seems that outputs within structures are different for other types as well.

[totem3]

@ohaibbq

Thank you for your response. As I haven’t started working on it yet, I would be grateful if you could address this issue.

[ohaibbq]

I haven't taken a look at this yet. If you have some time @totem3, that'd be appreciated.
I'm currently looking at if it'd be possible to rewrite how go-zetasqlite handles window functions to fix goccy/go-zetasqlite#153 and goccy/go-zetasqlite#161 as it'd block our usage from further adoption.