JSON query: a multi-argument coalesce returns an empty result on the exv collection

[robinskil]

Summary

A JSON query that projects a multi-argument coalesce returns an empty result. The same query without the coalesce returns rows. The report comes from the exv collection.

The reporter narrowed the trigger to coalesce with more than one argument.

Reported query

Full JSON body

{
    "select": [
        { "column": "TIME", "alias": "time" },
        { "column": "LONGITUDE", "alias": "longitude" },
        { "column": "LATITUDE", "alias": "latitude" },
        {
            "function": "coalesce",
            "args": [
                {
                    "function": "pressure_to_depth_teos_10",
                    "args": [
                        {
                            "function": "coalesce",
                            "args": [
                                { "column": "PRES", "alias": null },
                                { "column": "PRESPR01", "alias": null },
                                { "column": "PRESPS02", "alias": null }
                            ],
                            "alias": "PRESSURE"
                        },
                        { "column": "LATITUDE", "alias": null }
                    ],
                    "alias": "depth_from_pres"
                },
                { "column": "DEPTH", "alias": null },
                { "column": "ADEPZZ01", "alias": null },
                { "column": "DEPH", "alias": null },
                { "column": "DEPHPR01", "alias": null }
            ],
            "alias": "depth"
        },
        { "column": "subsurface_temperature", "alias": "value" },
        {
            "function": "concat",
            "args": [
                { "value": "https://cdi.seadatanet.org/report/edmo/" },
                { "column": "SDN_EDMO_CODE", "alias": null },
                { "value": "/" },
                { "column": "SDN_LOCAL_CDI_ID", "alias": null }
            ],
            "alias": "cdi_link"
        }
    ],
    "output": { "format": "parquet" },
    "filters": [
        { "column": "time", "gt_eq": "2010-01-01T00:00:00", "lt_eq": "2039-12-31T23:59:59" },
        { "column": "longitude", "gt_eq": -103.655105, "lt_eq": 13.20265 },
        { "column": "latitude", "gt_eq": -0.957951, "lt_eq": 69.188116 },
        { "longitude_column": "longitude", "latitude_column": "latitude",
          "geometry": { "type": "Polygon", "coordinates": [ "... large multi-ring polygon, elided ..." ] } },
        { "is_not_null": { "column": "value" } },
        { "column": "depth", "gt_eq": 0.0, "lt_eq": 5.0 }
    ],
    "from": "exv"
}

Expected result

The query returns the rows the filters select. The depth column holds the first non-null value of the coalesce arguments.

Actual result

The query returns an empty result.

Does SQL fail too?

No difference found. The SQL spelling returns the same rows as the JSON body in every case tested below.

The JSON compiler adds no coalesce-specific logic. Select::Function::to_expr looks the name up in the session scalar-function registry and calls the same ScalarUDF the SQL parser uses (mod.rs:195). So a failure that is JSON-only must come from the surrounding compile step, not from coalesce itself.

One planning difference is worth recording. DataFusion simplify replaces a one-argument coalesce with its argument. With two or more arguments it builds a nested CASE. So only the multi-argument form produces a CASE in the plan, and only that form can change how the predicate pushes down.

Not reproducible on main

Checked on main at 0ac879e (2.0.0-rc.2). Every case below returned the correct rows in both surfaces.

Case	Result
coalesce(a, b) over one CSV file	correct
coalesce(a, b) over two Parquet files with disjoint columns	correct
Same, plus a range filter on the coalesce alias	correct
Same, plus is_not_null on another alias	correct
Mixed argument types (Float64 + Utf8, super-typed to Utf8)	correct
The full reported shape: coalesce(pressure_to_depth_teos_10(coalesce(...) AS PRESSURE, LATITUDE) AS depth_from_pres, DEPTH, ...) with the time, longitude, latitude, GeoJSON polygon, is_not_null and depth filters	correct
The same shape with file statistics enabled and a completed analysis pass	correct
coalesce over two rank-1 netCDF variables (external table)	correct

So coalesce is not broken on its own. The trigger depends on the exv data or on the node.

A column that no file carries fails loudly. It raises Schema error: No field named "PRESPR01". It does not produce an empty result.

Leading hypothesis: the projection changes the nd grid

The JSON compiler collects every column named in select, including columns nested inside function arguments. It pushes that list into the scan (compiler.rs:24, file_collection.rs:80).

Each extra coalesce argument therefore adds one column to the scan projection.

For nd formats (netCDF, HDF5, Zarr) the decoded grid follows the projected columns. The encoder drops the target grid and the decoder re-infers it from the columns it receives. A projection that adds a column with another dimension changes the row count.

This is the one mechanism in the codebase where one more column in select changes the number of rows. It matches the report exactly, because the multi-argument form is the only form that adds the extra columns.

If exv is netCDF or HDF5, PRESPR01, PRESPS02, ADEPZZ01, DEPH and DEPHPR01 may sit on other dimensions than PRES and DEPTH. A zero-length or incompatible axis then collapses the grid for the whole file.

Second, smaller observation in the same function: with_pushdown_projection rebuilds the table schema with Schema::new(filtered_fields). This drops the schema metadata. Any format that carries dimension information in schema metadata loses it on the JSON query path.

How to confirm

Run these on the node that serves exv. Compare the row counts.

SELECT count(*) FROM exv;
SELECT count(*) FROM (SELECT PRES FROM exv);
SELECT count(*) FROM (SELECT PRES, PRESPS02 FROM exv);
SELECT count(*) FROM (SELECT PRES, PRESPR01, PRESPS02, DEPTH, ADEPZZ01, DEPH, DEPHPR01 FROM exv);

A drop to zero on the wider projections confirms the hypothesis. Equal counts rule it out.

Then remove one filter at a time from the JSON body, keeping the coalesce, to find which filter empties the result.

Information needed

• The Beacon version of the node that serves exv.
• The storage format of exv (Parquet, netCDF, HDF5, Zarr, ODV).
• The row counts from the queries above.
• The output of the reported query with the depth filter removed but the coalesce kept.

[robinskil]

Fixed in #499