issue_comments: 898564705
This data as json
html_url | issue_url | id | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
---|---|---|---|---|---|---|---|---|---|---|---|
https://github.com/simonw/datasette/issues/1293#issuecomment-898564705 | https://api.github.com/repos/simonw/datasette/issues/1293 | 898564705 | IC_kwDOBm6k_c41jwJh | 9599 | 2021-08-13T16:02:12Z | 2021-08-13T16:04:06Z | OWNER | 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 connection ``conn`` and a SQL query ``sql``, 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 ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | 849978964 |