Kalletlamadhav/sql-optimization-env

1# tasks/medium/pds_cartesian.py2from pathlib import Path3from tasks.base_task import BaseTask4 5# FIX: Added missing import + schema_ddl + hint + reference_fix (all absent in PDF)6_schema_file = Path('data/schemas/pds_schema.sql')7_schema_ddl = _schema_file.read_text() if _schema_file.exists() else (8    "CREATE TABLE ration_card_beneficiaries ("9    "card_id TEXT PRIMARY KEY, household_head TEXT NOT NULL, "10    "state_code CHAR(2) NOT NULL, district_code CHAR(3) NOT NULL, "11    "block_code CHAR(5), village_code CHAR(7), "12    "card_type TEXT NOT NULL, members_count INTEGER DEFAULT 1, "13    "aadhar_linked INTEGER DEFAULT 0, mobile_number TEXT, "14    "created_at DATE, last_updated DATE);"15    "CREATE TABLE pds_allotments ("16    "allotment_id INTEGER PRIMARY KEY AUTOINCREMENT, "17    "card_id TEXT NOT NULL, month_year TEXT NOT NULL, "18    "commodity TEXT NOT NULL, entitled_qty_kg DECIMAL(6,2), "19    "offtake_qty_kg DECIMAL(6,2) DEFAULT 0, "20    "fair_shop_code TEXT, offtake_date DATE);"21)22 23TASK = BaseTask(24    task_id='pds_cartesian',25    goal=(26        'A PDS officer wants to find BPL card holders who have NOT collected rations in Jan 2025. '27        'The current query accidentally creates a Cartesian product because the JOIN condition '28        'between beneficiaries and allotments is missing. '29        'Fix the JOIN by adding the correct ON condition between the two tables.'30    ),31    slow_query="""32        SELECT r.card_id, r.household_head, r.state_code33        FROM ration_card_beneficiaries r, pds_allotments a34        WHERE r.card_type = 'BPL'35        AND a.month_year = '2025-01'36        AND a.offtake_qty_kg = 037    """,38    expected_pattern='CARTESIAN_PRODUCT',39    tables=['ration_card_beneficiaries', 'pds_allotments'],40    schema_ddl=_schema_ddl,41    difficulty='medium',42    curriculum_level=3,43    hint='The comma-separated FROM clause with no ON condition creates a Cartesian product. Use explicit JOIN ... ON r.card_id = a.card_id.',44    reference_fix="""45        SELECT r.card_id, r.household_head, r.state_code46        FROM ration_card_beneficiaries r47        JOIN pds_allotments a ON a.card_id = r.card_id48        WHERE r.card_type = 'BPL'49        AND a.month_year = '2025-01'50        AND a.offtake_qty_kg = 051    """52)