issue_comments
11 rows where issue = 959999095
This data as json, CSV (advanced)
Suggested facets: created_at (date), updated_at (date)
| id ▼ | html_url | issue_url | node_id | user | created_at | updated_at | author_association | body | reactions | issue | performed_via_github_app |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 894606796 | https://github.com/simonw/datasette/issues/1421#issuecomment-894606796 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41Up3M | simonw 9599 | 2021-08-07T05:16:39Z | 2021-08-07T05:16:39Z | OWNER | Urgh, yeah I've seen this one before. Fixing it pretty much requires writing a full SQLite SQL syntax parser in Python, which is frustratingly complicated for solving this issue! You can work around this for a canned query by using the optional `params:` argument documented here: https://docs.datasette.io/en/stable/sql_queries.html#canned-query-parameters | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894606843 | https://github.com/simonw/datasette/issues/1421#issuecomment-894606843 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41Up37 | simonw 9599 | 2021-08-07T05:17:12Z | 2021-08-07T05:17:12Z | OWNER | Marking this blocked because I don't have a way around the needing-a-SQLite-SQL-parser problem at the moment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894921512 | https://github.com/simonw/datasette/issues/1421#issuecomment-894921512 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V2so | simonw 9599 | 2021-08-09T03:05:26Z | 2021-08-09T03:05:26Z | OWNER | I may have a way to work around this, using `explain`. Consider this query: ```sql select * from facetable where state = :state and on_earth = :on_earth and neighborhood not like '00:04' ``` Datasette currently gets confused and shows three form fields: https://latest.datasette.io/fixtures?sql=select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth=&04= <img width="698" alt="fixtures__select___from_facetable_where_state____state_and_on_earth____on_earth_and_neighborhood_not_like__00_04__and_pyinfra_pip_py_at_current_·_Fizzadar_pyinfra" src="https://user-images.githubusercontent.com/9599/128656369-8af860bf-f7c1-4c9c-beba-1eb6887a8336.png"> But... if I run `explain` [against that](https://latest.datasette.io/fixtures?sql=explain+select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth=&04=) I get this (truncated): addr | opcode | p1 | p2 | p3 | p4 | p5 | comment -- | -- | -- | -- | -- | -- | -- | -- 20 | ResultRow | 6 | 10 | 0 | | 0 | 21 | Next | 0 | 3 | 0 | | 1 | 22 | Halt | 0 | 0 | 0 | | 0 | 23 | Transaction | 0 | 0 | 35 | 0 | 1 | 24 | Variable | 1 | 2 | 0 | :state | 0 | 25 | Variable | 2 | 3 | 0 | :on_earth | 0 | 26 | String8 | 0 | 4 | 0 | 00:04 | 0 | 27 | Goto | 0 | 1 | 0 | | 0 | Could it be as simple as pulling out those `Variable` rows to figure out the names of the variables in the query? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894922145 | https://github.com/simonw/datasette/issues/1421#issuecomment-894922145 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V22h | simonw 9599 | 2021-08-09T03:07:38Z | 2021-08-09T03:07:38Z | OWNER | I hoped this would work: ```sql with foo as ( explain select * from facetable where state = :state and on_earth = :on_earth and neighborhood not like '00:04' ) select p4 from foo where opcode = 'Variable' ``` But sadly [it returns an error](https://latest.datasette.io/fixtures?sql=with+foo+as+%28%0D%0A++explain+select+*+from+facetable%0D%0A++where+state+%3D+%3Astate%0D%0A++and+on_earth+%3D+%3Aon_earth%0D%0A++and+neighborhood+not+like+%2700%3A04%27%0D%0A%29%0D%0Aselect+p4+from+foo+where+opcode+%3D+%27Variable%27&state=&on_earth=&04=): > near "explain": syntax error | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894922703 | https://github.com/simonw/datasette/issues/1421#issuecomment-894922703 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V2_P | simonw 9599 | 2021-08-09T03:09:29Z | 2021-08-09T03:09:29Z | OWNER | Relevant code: https://github.com/simonw/datasette/blob/ad90a72afa21b737b162e2bbdddc301a97d575cd/datasette/views/database.py#L225-L231 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894925437 | https://github.com/simonw/datasette/issues/1421#issuecomment-894925437 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V3p9 | simonw 9599 | 2021-08-09T03:19:00Z | 2021-08-09T03:19:00Z | OWNER | This may not work: > `ERROR: sql = 'explain select 1 + :one + :two', params = None: You did not supply a value for binding 1.` The `explain` queries themselves want me to pass them parameters. I could try using the regex to pull out candidates and passing `None` for each of those, including incorrect ones like `:31`. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894925914 | https://github.com/simonw/datasette/issues/1421#issuecomment-894925914 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V3xa | simonw 9599 | 2021-08-09T03:20:42Z | 2021-08-09T03:20:42Z | OWNER | I think this works! ```python _re_named_parameter = re.compile(":([a-zA-Z0-9_]+)") async def derive_named_parameters(db, sql): explain = 'explain {}'.format(sql.strip().rstrip(";")) possible_params = _re_named_parameter.findall(sql) try: results = await db.execute(explain, {p: None for p in possible_params}) return [row["p4"].lstrip(":") for row in results if row["opcode"] == "Variable"] except sqlite3.DatabaseError: return [] ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894927185 | https://github.com/simonw/datasette/issues/1421#issuecomment-894927185 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V4FR | simonw 9599 | 2021-08-09T03:25:01Z | 2021-08-09T03:25:01Z | OWNER | One catch with this approach: if the SQL query is invalid, the parameters will not be extracted and shown as form fields. Maybe that's completely fine? Why display a form if it's going to break when the user actually runs the query? But it does bother me. I worry that someone who is still iterating on and editing their query before actually starting to use it might find the behaviour confusing. So maybe if the query raises an exception it could fall back on the regular expression results? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894929080 | https://github.com/simonw/datasette/issues/1421#issuecomment-894929080 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V4i4 | simonw 9599 | 2021-08-09T03:33:02Z | 2021-08-09T03:33:02Z | OWNER | Fixed! Fantastic, this one has been bothering me for *years*. https://latest.datasette.io/fixtures?sql=select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27 <img width="707" alt="fixtures__select___from_facetable_where_state____state_and_on_earth____on_earth_and_neighborhood_not_like__00_04__and_pyinfra_pip_py_at_current_·_Fizzadar_pyinfra" src="https://user-images.githubusercontent.com/9599/128657807-80ac818d-6fd9-4f70-ad26-900cec6a7482.png"> | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894929769 | https://github.com/simonw/datasette/issues/1421#issuecomment-894929769 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V4tp | simonw 9599 | 2021-08-09T03:36:49Z | 2021-08-09T03:36:49Z | OWNER | SQLite carries a warning about using `EXPLAIN` like this: https://www.sqlite.org/lang_explain.html > The output from EXPLAIN and EXPLAIN QUERY PLAN is intended for interactive analysis and troubleshooting only. The details of the output format are subject to change from one release of SQLite to the next. Applications should not use EXPLAIN or EXPLAIN QUERY PLAN since their exact behavior is variable and only partially documented. I think that's OK here, because of the regular expression fallback. If the format changes in the future in a way that breaks the query the error should be caught and the regex-captured parameters should be returned instead. Hmmm... actually that's not entirely true: https://github.com/simonw/datasette/blob/b1fed48a95516ae84c0f020582303ab50ab817e2/datasette/utils/__init__.py#L1084-L1091 If the format changes such that the same columns are returned but the `[row["p4"].lstrip(":") for row in results if row["opcode"] == "Variable"]` list comprehension returns an empty array it will break Datasette! I'm going to take that risk for the moment, but I'll actively watch out for problems in the future. If this does turn out to be bad I can always go back to the pure regular expression mechanism. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 | |
| 894930013 | https://github.com/simonw/datasette/issues/1421#issuecomment-894930013 | https://api.github.com/repos/simonw/datasette/issues/1421 | IC_kwDOBm6k_c41V4xd | simonw 9599 | 2021-08-09T03:38:06Z | 2021-08-09T03:38:06Z | OWNER | Amusing edge-case: if you run this against a `explain ...` query it falls back to using regular expressions, because `explain explain select ...` is invalid SQL. https://latest.datasette.io/fixtures?sql=explain+select+*+from+facetable%0D%0Awhere+state+%3D+%3Astate%0D%0Aand+on_earth+%3D+%3Aon_earth%0D%0Aand+neighborhood+not+like+%2700%3A04%27&state=&on_earth= | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | "Query parameters" form shows wrong input fields if query contains "03:31" style times 959999095 |
Advanced export
JSON shape: default, array, newline-delimited, object
CREATE TABLE [issue_comments] (
[html_url] TEXT,
[issue_url] TEXT,
[id] INTEGER PRIMARY KEY,
[node_id] TEXT,
[user] INTEGER REFERENCES [users]([id]),
[created_at] TEXT,
[updated_at] TEXT,
[author_association] TEXT,
[body] TEXT,
[reactions] TEXT,
[issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
ON [issue_comments] ([user]);