File size: 1,189 Bytes
de46078 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | id: null_safe_equality_join
category: nulls_types
difficulty: hard
probes: >
Matching NULL to NULL requires the null-safe equality operator (<=> /
eqNullSafe). A plain == silently drops the NULL-keyed pair. This is the
inverse of join_anti_null_key: same operator, opposite required behaviour,
so a model cannot pattern-match its way through both.
tags: [null_semantics, eqNullSafe, join]
prompt: |
Join `left_t` to `right_t` on the code column, treating NULL as a value that
matches NULL. Return columns: code, lval, rval, for matched pairs only.
fixtures:
- name: left_t
schema: code STRING, lval INT
rows:
- ["a", 1]
- ["b", 2]
- [null, 3]
- name: right_t
schema: code STRING, rval INT
rows:
- ["a", 10]
- ["c", 20]
- [null, 30]
solution: |
from pyspark.sql import functions as F
def solve(spark, left_t, right_t):
# eqNullSafe: NULL <=> NULL is true, so the (null, 3)/(null, 30) pair joins.
cond = left_t["code"].eqNullSafe(right_t["code"])
return (
left_t.join(right_t, cond, "inner")
.select(left_t["code"].alias("code"), "lval", "rval")
)
compare:
mode: rows
|