Equality comparison in join between comparable nested records

[philrz]

A community user discovered that the fix for #2779 did not cover nested records.

Details

Repro is with Zed commit 40a7db8.

(The initial repro of this issue was with Zed commit 17d2f86 in the time of bstring. However, bstring is now gone, so I've rewritten the repro below to be with current Zed commit 40a7db8, modern join syntax, and integer/float comparison instead.)

Given that these values are considered equal,

$ zq -version
Version: v1.10.0-34-g40a7db87

$ zq -z 'yield 1==1.'
true

this join works as expected.

$ cat a-int.zson 
{a:1,va:"value a"}

$ cat b-float.zson 
{b:1.,vb:"value b"}

$ cat join.zed 
file a-int.zson
| left join (
  file b-float.zson
) on a=b vb:=vb

$ zq -I join.zed 
{a:1,va:"value a",vb:"value b"}

However, if we nest the integer/float values one level deeper, the join no longer works.

$ cat a-nested.zson 
{a:{inner:1},va:"value a"}

$ cat b-nested.zson 
{b:{inner:1.},vb:"value b"}

$ cat join-nested.zed 
file a-nested.zson
| left join (
  file b-nested.zson
) on a=b vb:=vb

$ zq -I join-nested.zed 
{a:{inner:1},va:"value a"}

[philrz]

I revisited this issue in the SuperSQL era. If we attempt a similar join in Postgres it fails there as well, though it happens to deliver a helpful error message rather than SuperDB's silence. I think we could provide a similar error once we add support for static type checking (#5997).

Details

Repro is with super commit b8311a3 and Postgres 17.5.

The example shown earlier in this issue was all Zed-centric, so I'll start over with a new example derived from the Postgres docs on composite types since this is their concept roughly equivalent to our "nested records".

We'll prepare our composite types and input data in Postgres using this SQL. What's important to notice is that the price field in one composite type is integer and in the other it's real.

CREATE TYPE inventory_item AS (
    name            text,
    price           integer
);

CREATE TABLE on_hand (
    item      inventory_item,
    count     integer
);

INSERT INTO on_hand VALUES (ROW('fuzzy dice', 2), 1000);
INSERT INTO on_hand VALUES (ROW('air freshener', 1), 200);

CREATE TYPE wanted_item AS (
    name            text,
    price           real
);

CREATE TABLE wanted (
    item      wanted_item,
    count     integer
);

INSERT INTO wanted VALUES (ROW('fuzzy dice', 2.0), 500);
INSERT INTO wanted VALUES (ROW('sun shield', 9.50), 400);

When compared as primitive types, the price values of "fuzzy dice" shown here are equal:

$ psql postgres
psql (17.5 (Homebrew))
Type "help" for help.

postgres=# SELECT 2::integer=2.0::real;
 ?column? 
----------
 t
(1 row)

However, if we attempt a join by comparing the entire respective "item" values, it fails with an error message.

postgres=# SELECT
    oh.item,
    oh.count AS on_hand_count,
    w.count AS wanted_count
FROM on_hand oh
JOIN wanted w
  ON (oh.item) = (w.item);
ERROR:  cannot compare dissimilar column types integer and real at record column 2

Now, there's multiple ways to force this to succeed, such as by comparing the nested values individually:

postgres=# SELECT
    oh.item,
    oh.count AS on_hand_count,
    w.count AS wanted_count
FROM on_hand oh
JOIN wanted w
  ON (oh.item).name = (w.item).name
 AND (oh.item).price = (w.item).price;
       item       | on_hand_count | wanted_count 
------------------+---------------+--------------
 ("fuzzy dice",2) |          1000 |          500
(1 row)

Casting one side's nested value to the composite type of the other side doesn't work:

postgres=# SELECT
    oh.item,
    oh.count AS on_hand_count,
    w.count AS wanted_count
FROM on_hand oh
JOIN wanted w
  ON (oh.item)::wanted_item = (w.item);
ERROR:  cannot cast type inventory_item to wanted_item
LINE 7:   ON (oh.item)::wanted_item = (w.item);

But this ugly thing works:

postgres=# SELECT
    oh.item,
    oh.count AS on_hand_count,
    w.count AS wanted_count
FROM on_hand oh
JOIN wanted w
  ON (ROW((oh.item).name, (oh.item).price))::wanted_item = (w.item);
       item       | on_hand_count | wanted_count 
------------------+---------------+--------------
 ("fuzzy dice",2) |          1000 |          500
(1 row)

Now let's pivot to SuperDB. Our inputs once again have different types for the inner price values, since the trailing . on the latter causes the SUP reader to treat it as floating point.

$ cat on-hand.sup 
{item:{name:"fuzzy dice", price:2}, count:1000}
{item:{name:"air freshener", price:1}, count:200}

$ cat wanted.sup 
{item:{name:"fuzzy dice", price:2.0}, count:500}
{item:{name:"sun shield", price:9.50}, count:400}

$ echo "2 2.0" | super -c "values {value:this,type:typeof(this)}" -
{value:2,type:<int64>}
{value:2.,type:<float64>}

$ super -c "values 2=2.0"
true

Attempting the join between full inner item values, the failure unfortunately is expressed in the form of silence, reflecting the lack of matches.

$ super -version
Version: b8311a333

$ super -c "
from 'on-hand.sup'
| join (
  from 'wanted.sup'
) on left.item=right.item"

[no output]

Our join implementation can't yet AND together the separate individual key comparisons like we did next in Postgres, but casting one side to the full nested record type for the other is pretty straightforward, and now the join succeeds.

$ super -c "
from 'on-hand.sup'
| join (
  from 'wanted.sup'
) on left.item::<{name:string,price:float64}>=right.item"

{left:{item:{name:"fuzzy dice",price:2},count:1000},right:{item:{name:"fuzzy dice",price:2.},count:500}}

That final output shows the separate left/right portions for illustrative purposes. To get a single top-level record like we saw in Postgres, one would typically now tack on values like this:

$ super -c "
from 'on-hand.sup'
| join (
  from 'wanted.sup'
) on left.item::<{name:string,price:float64}>=right.item
| values {item:left.item, on_hand_count:left.count, wanted_count:right.count}"

{item:{name:"fuzzy dice",price:2},on_hand_count:1000,wanted_count:500}

In conclusion, SuperDB's silent failure does not seem ideal, but it seems like an inevitable side effect of all the typing currently being dynamic. Once we add support for static checking (#5997) we'll be able to know up front that the inner numeric types are different and deliver a message similar to the one from Postgres (or actually do the casting of the inner values automatically, but I'm guessing we may just choose to match what Postgres is doing). I've linked this issue to the #5997 Epic so it can be verified when we add support for static type checking.