Search returning incorrect results after migration to latest version

[robintw]

I had an old version of pgstac running, which I have just upgraded to the latest version. Since then, I've found that some of the search queries I've run have returned incorrect results. I actually found the incorrect results when querying using stac_fastapi, but I've checked by running queries using the underlying pgstac SQL functions and have found that they are returning incorrect results, which are then just displayed by stac_fastapi).

For example, when running the query:

SELECT * FROM pgstac.search('{"collection":"test-bec-4"}'::jsonb)

I get a JSON result which starts like this:

{
  "next": "N51E000.tif",
  "prev": null,
  "type": "FeatureCollection",
  "context": {
    "limit": 10,
    "returned": 10
  },
  "features": [
    {
      "id": "core-4",
      "bbox": [
        35,
        19.3,
        35,
        19.3
      ],
      "type": "Feature",
      "links": [
        {
          "rel": "self",
          "href": "http://localhost/test.tif",
          "type": "application/json"
        }
      ],
      "assets": {},
      "geometry": {
        "type": "Point",
        "coordinates": [
          35,
          19.3
        ]
      },
      "collection": "test-anglo-cores-3",
      "properties": {
        "datetime": "2020-02-08T11:23:00Z",
        "core-type": "shallow",
        "last-reviewed": "2021-03-19"
      },
...

In that returned JSON, it lists the collection as test-anglo-cores-3 not test-bec-4.

Running

SELECT * FROM items WHERE collection_id = 'test-bec-4'

gives me 100 results, which is the correct number - and shows that there are items in the database with that collection ID. I've also checked the content field of the items table, and in there the collection is also specified correctly as test-bec-4.

I have no idea how to go forward with debugging this, as my SQL skills are quite limited. Any help would be very much appreciated.

[robintw]

A few more pieces of information that may be useful:

• The collection I was querying for (test-bec-4) was added after the migration
• The items that were incorrectly returned were added to the database before the migration
• I've created a new database at the latest version and added my new test-bec-4 items and the search is giving the correct results

I still have the old database that experienced this problem, and am happy to run diagnostic commands on it to work out what is going on.

[davidraleigh]

Hey @robintw which version was working? and by latest you mean v.0.4.3?

[robintw]

Yup, I upgraded to 0.4.3.

Before that I was running 0.3.4.

[robintw]

A bit more investigation has shown that this seems to be occurring for the latest version that I created from scratch (ie. without migrating from an old version) as well: I'm still getting entries from other collections appearing in the results for a search by collection.

I think for the moment I'm going to create another database using version 0.3.4 and stick with that for the moment, but it'd be good to get some ideas as to what is going on here, as it's scaring me a bit!

[bitner]

@robintw The major change at v0.4.0 was to add support for CQL2-JSON. With the CQL2-JSON implementation, all filters are part of the filters object, so to search for something by collection, you would use {"filter":{"op":"eq",{"property":"collection"},"test-bec-4"}.

You can get still use the old usage of collections=,ids= etc, query, and cql-json in the filter property by either setting the default-filter-lang globally to "cql-json" or by including the argument "filter-lang": "cql-json" in your query.

[robintw]

Thanks, I hadn't realised that change had occurred.

I've done some more investigation and I'm still having some problems.

Running this query:

SELECT * FROM pgstac.search('{"collections": ["test-bec-4"], "limit": 10, "filter-lang": "cql-json", "fields": {"include": [], "exclude": []}}'::jsonb)

gives the correct results - by setting filter-lang explicitly in the JSON.

However, setting it in the pgstac_settings table doesn't seem to have any effect. For example:

select * from pgstac_settings;

gives the following results:

"context_estimated_count"	"100000"
"context_estimated_cost"	"1000000"
"context_stats_ttl"	"1 day"
"default-filter-lang"	"cql-json"
"context"	"on"

but running the query as:

SELECT * FROM pgstac.search('{"collections": ["test-bec-4"], "limit": 10, "fields": {"include": [], "exclude": []}}'::jsonb)

that is, without the explicit passing of filter-lang gives results that include all collections.

Is there any way for me to debug this to see why the setting isn't being used?

[robintw]

Thanks @bitner - the PR seems to have fixed things, but I'm still concerned that setting default-filter-lang in the pgstac settings table doesn't seem to have had any effect on which filter lang is used. Do you have any ideas on this?