Error in CQL2 using IN operator

[OldLipe]

We are performing some tests with CQL2's filter extension and have encountered the following issue. We want to retrieve the 20LKP and 20LNP tiles from the sentinel-2-l2a collection:

curl --get \
--data-urlencode "filter=collection = 'sentinel-2-l2a' AND (s2:mgrs_tile = '20LKP' OR s2:mgrs_tile = '20LNP')" \
-d "filter-lang=cql2-text" \
-d "limit=10" \
https://planetarycomputer.microsoft.com/api/stac/v1/search

So far so good. However, when we use the IN operator to simplify the expression, we get the following error:

curl --get \
--data-urlencode "filter=collection = 'sentinel-2-l2a' AND s2:mgrs_tile IN ('20LKP','20LNP')" \
-d "filter-lang=cql2-text" \
-d "limit=10" \
https://planetarycomputer.microsoft.com/api/stac/v1/search

# {"code":"DatatypeMismatchError","description":"argument of AND must be type boolean, not type text"}

We looked a little further to see if the problem was with the IN operator, so we made the following queries:

curl --get \
--data-urlencode "filter=id = 'S2B_MSIL2A_20221124T143729_R096_T20LKP_20221126T004750'" \
-d "filter-lang=cql2-text" \
https://planetarycomputer.microsoft.com/api/stac/v1/search

curl --get \
--data-urlencode "filter=id = 'S2A_MSIL2A_20221106T142721_R053_T20LNP_20221107T065301'" \
-d "filter-lang=cql2-text" \
https://planetarycomputer.microsoft.com/api/stac/v1/search

Both queries worked as expected. However, when we use the operator IN, an error occurs:

curl --get \
--data-urlencode "filter=id IN ('S2B_MSIL2A_20221124T143729_R096_T20LKP_20221126T004750','S2A_MSIL2A_20221106T142721_R053_T20LNP_20221107T065301')" \
-d "filter-lang=cql2-text" \
https://planetarycomputer.microsoft.com/api/stac/v1/search

# {"code":"PostgresSyntaxError","description":"syntax error at or near \"IN\""}

Any idea what might be going on?

[TomAugspurger]

Looking at https://planetarycomputer.microsoft.com/explore?c=-59.7036%2C-8.5442&z=4.39&v=2&d=sentinel-2-l2a&s=false%3A%3A100%3A%3Atrue&ae=0&sr=desc&m=cql%3A1c28c5ba255d0d7b2b03010cccefd8a4&r=Natural+color, there's the "Explore results in Hub" generates the cql-json passed to pystac-client.

search = catalog.search(filter_lang="cql2-json", filter={
  "op": "and",
  "args": [
    {"op": "s_intersects", "args": [{"property": "geometry"}, aoi]},
    {"op": "anyinteracts", "args": [{"property": "datetime"}, daterange]},
    {"op": "=", "args": [{"property": "collection"}, "sentinel-2-l2a"]},
    {"op": "in", "args": [{"property": "s2:mgrs_tile"}, ["20LNP", "20LKP"]]}
  ]
})

Passing that to curl seems to work:

curl --get \
  --data-urlencode '{"op": "and","args": [{"op": "s_intersects", "args": [{"property": "geometry"}, aoi]},{"op": "anyinteracts", "args": [{"property": "datetime"}, daterange]},{"op": "=", "args": [{"property": "collection"}, "sentinel-2-l2a"]},{"op": "in", "args": [{"property": "s2:mgrs_tile"}, ["20LNP", "20LKP"]]}]}' \
  -d "filter-lang=cql2-json" \
  https://planetarycomputer.microsoft.com/api/stac/v1/search

@mmcfarland might be able to say more, but IIRC cql2-json is maybe preferred over cql2-text... I don't recall the details.

[OldLipe]

Thanks! We are testing the cql2 functionality in the rstac package and we have detected in our test suite this issue with cql2-text, but not with cql2-json.

[mmcfarland]

Thanks for this report @OldLipe. I can reproduce this and am currently looking into which upstream project is introducing this behavior. I'll update this thread when I have more information.

[mmcfarland]

I've submitted a fix to the pygeofilter project which is a dependency of stac-fastapi, used by our STAC API. It will take a little bit to thread this change through to production, but we'll include it in the next release notes. Thanks again for the report!