stac-utils / stac-utils/pgstac

Syntax error from temporal_op_query

Open
#281 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
PLpgSQL
Stars
223
Forks
57
PR merge metrics
No merged PRs in 30d

Description

Given what I believe to be a valid temporal filter, pgstac's temporal_op_query function appears to generate invalid SQL which raises a syntax error. This may simply be a case of me misunderstanding how to execute the function, so any additional information would be very much appreciated. I'm really struggling to find comprehensive documentation on temporal filters.

The following query's temporal operator is translated to an SQL construct here. However it appears that AND rl < lh < rh is invalid SQL, and should in fact be AND rl < lh AND lh < rh.

SELECT search('{
    "filter": {
        "op": "t_overlaps",
        "args": [
            "2011-08-16T00:00:00Z/2011-08-17T00:00:00Z"
        ]
    }
}')
ERROR:  syntax error at or near "<"
LINE 1: ...6 00:00:00+00'::timestamptz AND rl < end_datetime < '2011-08...
QUERY:  EXPLAIN (format json) SELECT 1 FROM items WHERE (datetime < '2011-08-16 00:00:00+00'::timestamptz AND rl < end_datetime < '2011-08-17 00:00:00+00'::timestamptz);

Because of challenges around documentation I'm not sure if this is the right way to perform t_overlaps. The extension documentation only provides an example of an intersection and from what I can gather pgstac's unit tests only address t_intersects, t_after, and t_before operators.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Reproduce the temporal_op_query failure with the search() query in the issue, then inspect src/pgstac/migrations/pgstac.0.9.1.sql around line 1606. Compare the generated t_overlaps expression with the extension's temporal-intersection example and the existing t_intersects, t_after, and t_before tests. Done means the reported query produces valid SQL and the behavior is covered or documented.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, sql
Domain
api, databases
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
35/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.