Mark joins don't support null mark columns

From the Story of Joins paper, for queries like:

select Title, ECTS = any (select ECTS from Courses c2
where Lecturer = 123) someE‘ual
from Courses c1

The result column someEqual can have the values TRUE, FALSE, and NULL (i.e.,
unknown). Accordingly, some care is needed to implement the mark join correctly, as we
will discuss in Section 5. But the great benefit is that we now translate arbitrary exists/not
exists/unique/quantified-comparison queries into a join construct and eventually, after
further optimizations, obtain an efficient evaluation strategy.

This seems like desirable behavior, and consistent with the general direction of subquery de-correlation.

This limitation also seems to be documented in null_aware_anti_join.slt, and currently returns wrong results:

datafusion/datafusion/sqllogictest/test_files/null_aware_anti_join.slt

Lines 150 to 158 in 1e68674

    
           # KNOWN LIMITATION: Mark joins used for OR conditions don't support null-aware semantics. 
        
           # The NULL row is incorrectly returned here. According to SQL semantics: 
        
           # - NULL NOT IN (2, 4) = UNKNOWN 
        
           # - NULL NOT IN (1, 3) = UNKNOWN 
        
           # - UNKNOWN OR UNKNOWN = UNKNOWN (should be filtered out) 
        
           # But mark joins treat NULL keys as non-matching (FALSE), so: 
        
           # - NULL mark column = FALSE 
        
           # - NOT FALSE OR NOT FALSE = TRUE OR TRUE = TRUE (incorrectly included) 
        
           # TODO: Implement null-aware support for mark joins to fix this

take

Cool! I'm also interested in this area.

I have a branch locally that somewhat works, it needs some more work and thinking

I've started splitting up my PR because its too big IMO and its a pain to handle, first part is #22981 which is mostly the SLT tests I had, documenting some of the current behavior.

feat: Support null aware hash mark-joins (apache#21585)

Navigation Menu

Repository navigation

Mark joins don't support null mark columns #21309

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

	# KNOWN LIMITATION: Mark joins used for OR conditions don't support null-aware semantics.
	# The NULL row is incorrectly returned here. According to SQL semantics:
	# - NULL NOT IN (2, 4) = UNKNOWN
	# - NULL NOT IN (1, 3) = UNKNOWN
	# - UNKNOWN OR UNKNOWN = UNKNOWN (should be filtered out)
	# But mark joins treat NULL keys as non-matching (FALSE), so:
	# - NULL mark column = FALSE
	# - NOT FALSE OR NOT FALSE = TRUE OR TRUE = TRUE (incorrectly included)
	# TODO: Implement null-aware support for mark joins to fix this

Mark joins don't support null mark columns #21309

Description

Activity

AdamGS commented on Apr 1, 2026

neilconway commented on Apr 5, 2026

AdamGS commented on Apr 5, 2026

AdamGS commented on Jun 16, 2026

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions