DuckDB queries into structures aren't escaped correctly

[rsignell]

I'm trying to find all the items that contain a specific variable name, like "hurs".

The item with the "hurs" variable looks like this:

and I know that WHERE "cube:variables".hurs IS NOT NULL works.

But what should the filter argument for rustac look like?

cql_json_filter = {
    "op": "is_not_null",
    "args": [
        {"property": "cube:variables.hurs"}
    ]
}
items = await rustac.search('stac_items.parquet', filter=cql_json_filter)

doesn't error, but doesn't return my item either.

I pretty sure this is some simple user syntax error, here, right?

[gadomski]

This is a problem on our side, as we don't currently handle DuckDB queries into structures correctly. I'll take a look and see if we can't get a fix out for you.

[rsignell]

Is there an equivalent or alternative request I could make using the existing filter argument capabilities to get the items containing the "hurs" variable?

[gadomski]

Not via CQL2+rustac at the moment, as far as I can tell. I tried monkeying with the quoting (using the rustac command line) and did not have success:

$ cargo run -p rustac -- search crates/duckdb/data/datacube-item.parquet --filter "is_not_null('\"cube:variables\".hurs')"
    Finished `dev` profile [unoptimized + debuginfo] target(s) in 0.13s
     Running `target/debug/rustac search crates/duckdb/data/datacube-item.parquet --filter 'is_not_null('\''"cube:variables".hurs'\'')'`
ERROR: Catalog Error: Scalar Function with name is_not_null does not exist!
Did you mean "isnan"?

LINE 1: ...=true, hive_partitioning=false, union_by_name=true) WHERE is_not_null('"cube:variables".hurs')

[bitner]

As far as my reading, there is not an is_not_null operator or even isNotNull (https://schemas.opengis.net/cql2/1.0/cql2.json) in cql2-json. Also Note: the isNull operator has no underscores and is in camel case.

"foo" IS NOT NULL in cql2-text,
should break down to

{
  "op": "not",
  "args": [
    {
      "op": "isNull",
      "args": [
        {
          "property": "foo"
        }
      ]
    }
  ]
}

The bnf grammar for cql2-text is not going to allow quotes with then a dot notation outside of those quotes, so something like "cube:variables".hur will not be able to be expressed in cql2-text, however, it could be expressed as "cube:variables.hur" and then it is up to the conversion of a property identifier to be able to convert that into the correct SQL for whatever engine. Right now, the ToDuckSQL inherits most everything from the ToSqlAst impl which targets Postgres SQL and uses a quote_ident function from the pg_escape crate to sanitize the string, but it is going to sanitize it as a single identifier and not a set of potentially nested identifiers.

The big problem with the cql2 spec is that as far as I can see in the grammar, there really isn't a concept of a nested identifier and so it is left to the translation of how to try to implement that which leaves an ambiguity where a.b could be either a column that was "a"."b" in duckdb or postgres, or it could be a quoted identifier that was the column "a.b". In cql2-json, we could get around it with something like {"property":""a:b".c"} I think, but I don't think that would be able to be expressed in the cql2-text bnf (and at least, cql2rs can't parse that right now even if it is).

[rsignell]

I did try:

cql_json_filter = {
  "op": "not",
  "args": [
    {
      "op": "isNull",
      "args": [
        {
          "property": "cube:variables.hur"
        }
      ]
    }
  ]
}

and it didn't throw an error, but also did not return the item. I think that was expected, right?

[bitner]

@rsignell yes, that seems expected. That CQL2 is correct, but right now, that is searching for a column "cube:variables.hur" that doesn't exist. I'm exploring some ways right now that we can allow user defined functions or whitelist lookups for setting column paths in SQL backends in cql2-rs