Columns of views have incorrect datatype

simonw / datasette-graphql

Datasette plugin providing an automatic GraphQL API for your SQLite databases

https://datasette-graphql-demo.datasette.io/

Apache License 2.0

98 stars 6 forks source link

Columns of views have incorrect datatype #87

Open ad-si opened 2 years ago

ad-si commented 2 years ago

As far as I can tell all columns of views (which don't have any type affinity per default) are coerced to TEXT by datasette-graphql.

One way to figure out the correct datatype would be to inspect all values (or a sample?) and use that information:

sqlite> select typeof(name), typeof(name_length) from songs_view;
text|integer
text|integer
text|integer

simonw commented 2 years ago

I'm not seeing that for this view: https://datasette-graphql-demo.datasette.io/github/repos_starred

Here's what I see in GraphiQL:

simonw commented 2 years ago

The definition of that view is:

CREATE VIEW repos_starred AS select
  stars.starred_at,
  starring_user.login as starred_by,
  repos.*
from
  repos
  join stars on repos.id = stars.repo
  join users as starring_user on stars.user = starring_user.id
  join users on repos.owner = users.id
order by
  starred_at desc;

Maybe the column type affinities are making it through because SQLite can track which original table they came from?

simonw commented 2 years ago

@ad-si can you provide SQL that produces a small example database that exhibits this bug?

ad-si commented 1 year ago

CREATE VIEW multi_type AS
SELECT 1 AS col UNION
SELECT 2.2 AS col UNION
SELECT 'three' AS col UNION
SELECT NULL AS col

But since GraphQL does not support multi-type fields (or unions on scalars) converting to String is probably the best workaround…

ad-si commented 1 year ago

This is what I was talking about at the beginning:

CREATE VIEW length AS
SELECT 3 as col UNION
SELECT 4 as col

Type of col is actually INTEGER, but should (can?) the GraphQL endpoint be able to pick that up. I'm not sure …