JoinOrder Q7b — Canonical JoinOrder 7b

JoinOrder benchmark · all JoinOrder queries

Canonical JoinOrder SQL query 7b; generated DataFrame translation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  MIN(n.name) AS of_person,
  MIN(t.title) AS biography_movie
FROM aka_name AS an, cast_info AS ci, info_type AS it, link_type AS lt, movie_link AS ml, name AS n, person_info AS pi, title AS t
WHERE
  an.name LIKE '%a%'
  AND it.info = 'mini biography'
  AND lt.link = 'features'
  AND n.name_pcode_cf LIKE 'D%'
  AND n.gender = 'm'
  AND pi.note = 'Volker Boehm'
  AND t.production_year BETWEEN 1980 AND 1984
  AND n.id = an.person_id
  AND n.id = pi.person_id
  AND ci.person_id = n.id
  AND t.id = ci.movie_id
  AND ml.linked_movie_id = t.id
  AND lt.id = ml.link_type_id
  AND it.id = pi.info_type_id
  AND pi.person_id = an.person_id
  AND pi.person_id = ci.person_id
  AND an.person_id = ci.person_id
  AND ci.movie_id = ml.linked_movie_id

Representative DataFrame

Expression family (Polars, PySpark, DataFusion), default parameters.

def _impl(ctx: DataFrameContext) -> Any:
        return _execute_joinorder_expression_query(ctx, query_id)

Get the exact query for your run

The blocks above are representative. To get the precise statement a specific platform runs at a specific scale, substitute your own values:

from benchbox import JoinOrder

bench = JoinOrder(scale_factor=1.0)            # your scale factor
queries = bench.get_queries(dialect="datafusion")   # your platform's dialect; drop dialect= for native SQL
print(queries['7b'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

print(get_dataframe_render('joinorder', '7b', family="expression").source)  # or family="pandas"

Source

Query text: benchbox/core/joinorder/queries.py