Complex Relationship Definitions

[djwaldo]

As per this definition, relationships seem to be limited to equi joins between columns. How will this standard address more advanced definitions such as:
1 Range Joins
2 Functions on Joins
3 Filters on Joins
4 multi table joins
5 AI joins. I.e. AI_FILTER to define a join condition

[kurtStirewalt]

Agreed here. I think we need a more powerful means for declaring how to derive relationships, and these derivation formulas should be able to reference multiple tables (and even other relationships).

[siljamardla]

Another example is geo-joins. Let me provide a relevant example. I define revenue and profit margin metrics. Then I want to do a query that looks roughly like this pseudo code:

SELECT 
    area_table.area_id
  , fact_table.metric1 -- revenue
  , fact_table.metric2 -- profit
FROM area_table
LEFT JOIN fact_table
  ON fact_table.coordinates fall within area_table.polygon

Keeping in mind that areas might be overlapping (e.g. a micromobility order is finished at a GPS location that falls both within a city zone no 1 and within a parking restriction area no 3).

[djwaldo]

Following up with concrete evidence from the ThoughtSpot converter work (#285). Mapping our model format onto the spec confirmed the gap this thread raised: ThoughtSpot supports range and ASOF joins (timezone bridges, SCD-2 effective-date windows, point-in-time lookups) that are structural, not incidental.

Today they cannot become relationships at all. From_columns/to_columns require equality pairs and the schema requires at least one (osi-schema.json makes a bare relationship invalid). Interim guidance we received was to carry the non-equality predicates in custom_extensions on the relationship, which our converter design now does. This means every vendor tool except ours sees a plain equi-join, which silently changes results.

Expression_language.md already anticipates "arbitrary join expressions"is that the intended long-term home? Happy to write up a concrete proposal if there's interest.