Figure out why an {eq: value} fails where where: "x = value" succeeds

simonw commented 4 years ago

{
  country(search: "united") {
    edges {
      node {
        id
        name
      }
    }
  }
  this_does_not_work: election(sort_desc: date, filter:{country_id:{eq:"44"}}) {
    totalCount
    nodes {
      date
      comment
      country_id {
        id
        name
      }
    }
  }
  this_works: election(sort_desc: date, where: "country_id = 44") {
    totalCount
    nodes {
      date
      comment
      country_id {
        id
        name
      }
    }
  }
}

simonw commented 4 years ago

Could this be because country_id should be an integer but is a string? I don't understand why that would break though.

simonw commented 4 years ago

https://parlgov.datasettes.com/parlgov/election?country_id__exact=44 is showing 0 results too - which is really strange because https://parlgov.datasettes.com/parlgov/election?_where=country_id=44 DOES show results.

simonw commented 4 years ago

Faceting https://parlgov.datasettes.com/parlgov/election?_facet=country_id works but if you select a facet you get zero results.

Looks like this is a Datasette bug, not a datasette-graphql bug.

simonw commented 4 years ago

https://parlgov.datasettes.com/parlgov?sql=select+rowid%2C+id%2C+type_id%2C+country_id%2C+date%2C+first_round_election_id%2C+early%2C+wikipedia%2C+seats_total%2C+electorate%2C+votes_cast%2C+votes_valid%2C+data_source%2C+description%2C+comment%2C+previous_parliament_election_id%2C+previous_ep_election_id%2C+previous_cabinet_id%2C+old_countryID%2C+old_parlID+from+election+where+%22country_id%22+%3D+%3Ap0+order+by+rowid+limit+101&p0=44 has the bug

https://parlgov.datasettes.com/parlgov?sql=select+rowid%2C+id%2C+type_id%2C+country_id%2C+date%2C+first_round_election_id%2C+early%2C+wikipedia%2C+seats_total%2C+electorate%2C+votes_cast%2C+votes_valid%2C+data_source%2C+description%2C+comment%2C+previous_parliament_election_id%2C+previous_ep_election_id%2C+previous_cabinet_id%2C+old_countryID%2C+old_parlID+from+election+where+%22country_id%22+%3D+44+order+by+rowid+limit+101 does not.

simonw commented 4 years ago

The schema for the election table is missing any type information at all: https://parlgov.datasettes.com/parlgov/election

CREATE TABLE election("id", "type_id", "country_id", "date", "first_round_election_id", "early", "wikipedia", "seats_total", "electorate", "votes_cast", "votes_valid", "data_source", "description", "comment", "previous_parliament_election_id", "previous_ep_election_id", "previous_cabinet_id", "old_countryID", "old_parlID",
   FOREIGN KEY(country_id) REFERENCES country(id),
   FOREIGN KEY(previous_cabinet_id) REFERENCES cabinet(id),
   FOREIGN KEY(previous_parliament_election_id) REFERENCES election(id),
   FOREIGN KEY(previous_ep_election_id) REFERENCES election(id)
);

simonw commented 4 years ago

select * from pragma_table_info("election") confirms that those columns have no type: https://parlgov.datasettes.com/parlgov?sql=select+*+from+pragma_table_info%28%22election%22%29

simonw commented 4 years ago

I checked the original parlgov.db (from before I ran my fix_db.py script) and it too fails to define column types for the elections table:

CREATE TABLE election("id", "type_id", "country_id", "date", "first_round_election_id", "early", "wikipedia", "seats_total", "electorate", "votes_cast", "votes_valid", "data_source", "description", "comment", "previous_parliament_election_id", "previous_ep_election_id", "previous_cabinet_id", "old_countryID", "old_parlID");

Here's the full schema: https://gist.github.com/simonw/14c624a79b7734c0ea45448e03c68ab0

simonw commented 4 years ago

So this seems to be a SQLite bug!

simonw commented 4 years ago

Issue reported on the SQLite forum (which is where they ask you to post bug reports): https://www.sqlite.org/forum/forumpost/8a388072a1?t=h

simonw commented 4 years ago

Closing this since it's not a bug in datasette-graphql.

simonw / datasette-graphql

Figure out why an {eq: value} fails where where: "x = value" succeeds #43