issue_comments: 898564705

More debug output: table_rootpage_by_register={0: 43, 1: 42} names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_id=0 cid=6 column_register=3 table_id=0 cid=5 column_register=4 table_id=1 cid=1 column_register=6 table_id=0 cid=4 column_register=7 table_id=0 cid=6 column_register=5 table_id=3 cid=2 column_register=8 table_id=3 cid=2 column_register=8 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=8 = (table='facetable', cid=2) table_id=3 cid=1 column_register=7 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=7 = (table='facetable', cid=1) table_id=3 cid=0 column_register=6 KeyError 3 table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0] names_and_types_by_rootpage={42: ('facet_cities', 'table'), 43: ('facetable', 'table')} table_rootpage_by_register={0: 43, 1: 42} table_id=3 columns_by_column_register[column_register] = (table, cid) column_register=6 = (table='facetable', cid=0) result_registers=[6, 7, 8] columns_by_column_register={3: ('facetable', 6), 4: ('facetable', 5), 6: ('facet_cities', 1), 7: ('facetable', 4), 5: ('facetable', 6)} all_column_names={('facet_cities', 0): 'id', ('facet_cities', 1): 'name', ('facetable', 0): 'pk', ('facetable', 1): 'created', ('facetable', 2): 'planet_int', ('facetable', 3): 'on_earth', ('facetable', 4): 'state', ('facetable', 5): 'city_id', ('facetable', 6): 'neighborhood', ('facetable', 7): 'tags', ('facetable', 8): 'complex_array', ('facetable', 9): 'distinct_some_null'} Those KeyError are happening here because of a lookup in table_rootpage_by_register for table_id=3 - but table_rootpage_by_register only has keys 0 and 1.

It looks like that 3 actually corresponds to the OpenPseudo table from here:

16 OpenPseudo 3 10 5 00 5 columns in r[10] 17 SorterSort 2 24 0 00 18 SorterData 2 10 3 00 r[10]=data 19 Column 3 2 8 00 r[8]=state 20 Column 3 1 7 00 r[7]=facet_cities.name 21 Column 3 0 6 00 r[6]=neighborhood 22 ResultRow 6 3 0 00 output=r[6..8]

Python code:

`python def columns_for_query(conn, sql, params=None): """ Given a SQLite connectionconnand a SQL querysql, returns a list of(table_name, column_name)`` pairs corresponding to the columns that would be returned by that SQL query.

Each pair indicates the source table and column for the returned column, or
``(None, None)`` if no table and column could be derived (e.g. for "select 1")
"""
if sql.lower().strip().startswith("explain"):
    return []
opcodes = conn.execute("explain " + sql, params).fetchall()
table_rootpage_by_register = {
    r["p1"]: r["p2"] for r in opcodes if r["opcode"] == "OpenRead"
}
print(f"{table_rootpage_by_register=}")
names_and_types_by_rootpage = dict(
    [(r[0], (r[1], r[2])) for r in conn.execute(
        "select rootpage, name, type from sqlite_master where rootpage in ({})".format(
            ", ".join(map(str, table_rootpage_by_register.values()))
        )
    )]
)
print(f"{names_and_types_by_rootpage=}")
columns_by_column_register = {}
for opcode_row in opcodes:
    if opcode_row["opcode"] in ("Rowid", "Column"):
        addr, opcode, table_id, cid, column_register, p4, p5, comment = opcode_row
        print(f"{table_id=} {cid=} {column_register=}")
        try:
            table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0]
            columns_by_column_register[column_register] = (table, cid)
        except KeyError as e:
            print("  KeyError")
            print("   ", e)
            print("    table = names_and_types_by_rootpage[table_rootpage_by_register[table_id]][0]")
            print(f"    {names_and_types_by_rootpage=} {table_rootpage_by_register=} {table_id=}")
            print("    columns_by_column_register[column_register] = (table, cid)")
            print(f"    {column_register=} = ({table=}, {cid=})")
            pass
result_row = [dict(r) for r in opcodes if r["opcode"] == "ResultRow"][0]
result_registers = list(range(result_row["p1"], result_row["p1"] + result_row["p2"]))
print(f"{result_registers=}")
print(f"{columns_by_column_register=}")
all_column_names = {}
for (table, _) in names_and_types_by_rootpage.values():
    table_xinfo = conn.execute("pragma table_xinfo({})".format(table)).fetchall()
    for column_info in table_xinfo:
        all_column_names[(table, column_info["cid"])] = column_info["name"]
print(f"{all_column_names=}")
final_output = []
for register in result_registers:
    try:
        table, cid = columns_by_column_register[register]
        final_output.append((table, all_column_names[table, cid]))
    except KeyError:
        final_output.append((None, None))
return final_output

```