What happens?

The comparison of two struct values will return a wrong (non-NULL) result if relevant fields contain NULL. The behaviour of DuckDB deviates from that of SQLite3 and PostgreSQL.

EDIT: I am aware of the discussion of Comparison Operators on https://duckdb.org/docs/sql/data_types/struct. I am skeptical of DuckDB's current rationale that the behaviour of the comparison with NULL is dependent on whether NULL appears "top-level" or in a struct field. That violates the principles of least surprise and compositionality.

To Reproduce

DuckDB

OK (field b is not relevant to decide the comparison):

> SELECT {a:1, b:2} < {a:2, b:NULL} AS OK;
┌─────────┐
│   OK    │
│ boolean │
├─────────┤
│ true    │
└─────────┘

Bug (field b is relevant, the comparison of 2 with NULL should yield NULL and the entire struct comparison is thus expected to return NULL; in other words: embedding NULL into a struct should not render NULL comparable):

> SELECT {a:1, b:2} < {a:1, b:NULL} AS Bug;
┌─────────┐
│   Bug   │
│ boolean │
├─────────┤
│ true    │
└─────────┘

Likewise:

> SELECT (1,1) < (2,NULL) AS OK;
┌─────────┐
│   OK    │
│ boolean │
├─────────┤
│ true    │
└─────────┘
> SELECT (1,1) < (1,NULL) AS Bug;
┌─────────┐
│   Bug   │
│ boolean │
├─────────┤
│ true    │
└─────────┘

PostgreSQL (v15.6)

> SELECT (1,1) < (2,NULL) AS OK;
┌────┐
│ ok │
├────┤
│ t  │
└────┘

> SELECT (1,1) < (1,NULL) AS OK;
┌──────┐
│ ok   │
├──────┤
│ NULL │
└──────┘

SQLite3 (v3.39.5)

> SELECT (1,1) < (2,NULL);
1

> SELECT ((1,1) < (1,NULL)) IS NULL;
1

OS:

macOS

DuckDB Version:

v0.10.1-dev397 d745e29

DuckDB Client:

CLI

Full Name:

Torsteh Grust

Affiliation:

U Tübingen, Germany

Have you tried this on the latest nightly build?

I have tested with a nightly build

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have