JoinOrder Q7c — Canonical JoinOrder 7c

JoinOrder benchmark · all JoinOrder queries

Canonical JoinOrder SQL query 7c; generated DataFrame translation

Representative SQL

Rendered for DataFusion with default parameters.

SELECT
  MIN(n.name) AS cast_member_name,
  MIN(pi.info) AS cast_member_info
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 IS NOT NULL
  AND (
    an.name LIKE '%a%' OR an.name LIKE 'A%'
  )
  AND it.info = 'mini biography'
  AND lt.link IN ('references', 'referenced in', 'features', 'featured in')
  AND n.name_pcode_cf BETWEEN 'A' AND 'F'
  AND (
    n.gender = 'm' OR (
      n.gender = 'f' AND n.name LIKE 'A%'
    )
  )
  AND pi.note IS NOT NULL
  AND t.production_year BETWEEN 1980 AND 2010
  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['7c'])

For the DataFrame form:

from benchbox.core.query_catalog import get_dataframe_render

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

Source

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