"""Compare actual query plans on authored rows; no benchmark or stable plan string assertion."""
import sqlite3,json
ROWS=[(i,'open'if i%3==0 else'closed',i%5,'R'+str(i))for i in range(1,49)]
QUERY='SELECT id,created,code FROM request WHERE state=? ORDER BY created,id LIMIT ?'
def demo():
    cases=[];oracle=[[r[0],r[2],r[3]]for r in sorted([x for x in ROWS if x[1]=='open'],key=lambda x:(x[2],x[0]))[:5]]
    for name,columns in [('none',None),('state_first','state,created,id'),('created_first','created,state,id'),('covering','state,created,id,code')]:
        c=sqlite3.connect(':memory:');c.execute('CREATE TABLE request(id INTEGER PRIMARY KEY,state TEXT NOT NULL,created INTEGER NOT NULL,code TEXT NOT NULL)');c.executemany('INSERT INTO request VALUES(?,?,?,?)',ROWS)
        if columns:c.execute('CREATE INDEX candidate ON request('+columns+')')
        plan=[list(x)for x in c.execute('EXPLAIN QUERY PLAN '+QUERY,('open',5))];result=[list(x)for x in c.execute(QUERY,('open',5))];assert result==oracle
        cases.append({'case':name,'index_columns':columns,'actual_plan_rows':plan,'result':result});c.close()
    assert [x[0]for x in oracle]==[15,30,45,6,21]
    return {'sqlite_version':sqlite3.sqlite_version,'authored_rows':[list(x)for x in ROWS],'query':QUERY,'parameters':['open',5],'cases':cases,'independent_python_sorted_oracle':oracle,'runtime_timing_performed':False,'scope':'Observed plan only for this SQLite build, schema and48rows. No plan text golden-string tests or production speed/size claim. SCAN can use an index; covering depends on selected columns. No app DB modified.'}
if __name__=='__main__':print(json.dumps(demo(),indent=2))