Case-insensitive string comparison

[cportele]

Transferred from #579:

The general idea was/is to add requirements for UPPER() and LOWER() function. However, there is still the unresolved question how we exactly define UPPER() and LOWER() - or if we should define a unicode case folding function instead (see Unicode or W3C).

@pvretano will look into how the various backends support upper/lower functions. We will discuss this aspect after the analysis.

[cportele]

Meeting 2021-09-27: @pvretano is still investigating all the different implementations (SQL and NoSQL/graph databases). Documentation is slim and testing with the implementations is necessary, using German and Greek examples. The proposal will go in the direction of UPPER/LOWER functions using the Unicode case folding function. Since the goal is "just" case-insensitive comparisons, language-specific case folding is not important for CQL2.

[cportele]

Meeting 2021-10-11: Peter finished the DBMS analysis. All have a capability for case-insensitive comparison using case-folding. Some use functions like UPPER()/LOWER() some use other mechanisms. We want to support case-insensitive comparison, so we should have a single function that supports case-folding (not something like UPPER() or LOWER()). Peter is working on a proposal in this direction, probably in time for the next meeting.

[robintw]

What is the current situation with case insensitive filtering with cql2-json? I've found various issues on here talking about UPPER and LOWER and CASEI and various other things, and I'm not sure whether a) case insensitive comparison is currently supported, and b) if so, how to use it.

I've got a basic filter like this:

{
    "collections": [
        "test-geophys-2"
    ],
    "filter": {
        "like": [
            {
                "property": "geophys:SURVEY_NAME"
            },
            "%calcutta%"
        ]
    },
    "filter-lang": "cql-json",
    "limit": 10
}

and I want the like operator to match Calcutta, CALCUTTA, calcutta etc.

I've tried adding a lower operator:

{
    "filter-lang": "cql2-json",
    "filter": {
        "op": "like",
        "args": [
            {
                "lower": {
                    "property": "geophys:SURVEY_NAME"
                }
            },
            "%calcutta%"
        ]
    }
}

which doesn't seem to work, and I've tried adding a nocase argument:

{
    "filter-lang": "cql2-json",
    "filter": {
        "op": "like",
        "args": [
            {
                "property": "geophys:SURVEY_NAME",
                "nocase": true
            },
            "%calcutta%"
        ]
    }
}

and that doesn't work either.

Is this possible at the moment? And if so, how?

[cportele]

@robintw - See PR #641 for the current status (which should be merged into master soon, after some final changes).

I think the filter should be (@pvretano - please check; I also think that "arguments" should "args", but the current schema has "arguments"):

{
  "op": "like",
  "args": [
    {
      "function": {
        "name": "casei",
        "arguments": [{ "property": "geophys:SURVEY_NAME" }]
      }
    },
    {
      "function": {
        "name": "casei",
        "arguments": ["%calcutta%"]
      }
    }
  ]
}

[pvretano]

@cportele merged #641. Verified that the example you gave above validates and also added the example to the example's folder.