JoinOrder Q7a — Biography Movies with Person Criteria

JoinOrder benchmark · all JoinOrder queries

Biography movies with specific person criteria (8 tables)

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 BETWEEN 'A' AND 'F'
  AND (
    n.gender = 'm' OR (
      n.gender = 'f' AND n.name LIKE 'B%'
    )
  )
  AND pi.note = 'Volker Boehm'
  AND t.production_year BETWEEN 1980 AND 1995
  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['7a'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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