strings returned as numbers in view collections?

[bictorman]

Hi,

I'm seeing a really weird behavior where some queries in view collections would cast the result as a number if it looks like one inside a string field. There is more than one scenario in which this happens, but the one I could pinpoint it's really strange.

I create a collection with string field called myfield and in one of the rows I insert "0.40", then I create a view collection just selecting the fields. Notice at the end of this screenshot how it gets cast as a decimal 0.4.

The funny thing is, as you can see in the first request it returns it as a string, the difference between the first and the second request is that I insert a space in the query after myfield.

First request, view query:

SELECT id, myfield, created FROM test;

Second request, view query:

SELECT id, myfield , created FROM test;

I'm using and self-updated pocketbase_0.19.0_linux_amd64 and I have reproduced it on mac arm64. I suspect this might not be a bug in pocketbase but in the sqlite library.

I have another query with the same issue, the one that led me to the problem, this one without the space, but I haven't figured out how to fix it yet.

SELECT id, static, conversion, feed_scope, created
FROM (
    SELECT *,
    ROW_NUMBER() OVER(PARTITION BY feed_scope ORDER BY created DESC) as rn
    FROM cpc_rules
    WHERE valid_since < CURRENT_TIMESTAMP
) tmp
WHERE rn = 1;

I would love to know what's going on here.

[ganigeorgiev]

It is most likely a bug with the PocketBase auto schema fields detection.

Will try to check it later today.

I have another query with the same issue, the one that led me to the problem, this one without the space, but I haven't figured out how to fix it yet.

You can try forcing a text field type by using CAST(yourColumn as text).

[ganigeorgiev]

Just for info in case someone stumble here - the issue with the space and the wrong field type detection should be fixed in v0.19.1 release.

[bictorman]

@ganigeorgiev just FYI, with 0.19.1 I see the space issue resolved but not for the query with the subselect, although it's not a problem for now.

[ganigeorgiev]

If a column is an expression (as in your case as it comes from a subquery table) then it will treated as json field type (it should have {} icon in the Admin UI listings) and the row value will be returned as it is from the database, aka. it is possible that SQLite is performing its own normalizations based on the column/expression affinity type. In this case, forcing it with cast(...) is a good workaround.

[ganigeorgiev]

Actually no, while SQLite is possible to return different value for a single column based on the affinity type and column content, in this case the returned API response is loosely normalized on application level due to specifics how raw string json field values are handled (see SchemaField.PrepareValue). Aka. the issue is that the column is treated as json, which is the default when we can't auto detect its type based on the view query.

In any case the recommendation for the cast remains when you wan to force a view column to be treated as certain field type (in the future there will be also an option to specify custom schema field types for the view query as sometimes there are use case where a generated text column needs to be treated as relation).

[bictorman]

ah I see, since I used the same field names in my mind I was assuming it's the same columns as the main table, but it being a sub-select there is no simple way for that to be detected and the types need to be inferred. That makes sense.

[bictorman]

That was the first thing I tried, but schema validation fails if I do.

[ganigeorgiev]

The expression must be aliased so that it has a valid field name, aka. try with CAST(myfield as TEXT) myfieldalias.

[bictorman]

🤦‍♂️ yeah that helped