issue_comments
7 rows where issue = 309558826
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 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 377065541 | https://github.com/simonw/datasette/issues/190#issuecomment-377065541 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzA2NTU0MQ== | simonw 9599 | 2018-03-28T22:58:52Z | 2018-03-28T22:58:52Z | OWNER | This is because the SQL we are using here is: select * from compound_primary_key where "pk1" > "d" and "pk2" > "v" order by pk1, pk2 limit 101 This is incorrect. The correct SQL syntax (according to the example on https://www.sqlite.org/rowvalue.html#scrolling_window_queries ) is: select * from compound_primary_key where ("pk1", "pk2") > ("d", "v") order by pk1, pk2 limit 101 BUT... this uses "row values" syntax which was only added to SQLite in version 3.15.0 in October 2016: https://sqlite.org/changes.html#version_3_15_0 The version on https://datasette-issue-190-compound-pks.now.sh/compound-pks-9aafe8f?sql=select+sqlite_version%28%29%3B is 3.8.7.1 from October 2014. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377066466 | https://github.com/simonw/datasette/issues/190#issuecomment-377066466 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzA2NjQ2Ng== | simonw 9599 | 2018-03-28T23:03:45Z | 2018-03-28T23:03:57Z | OWNER | Without row values syntax, the necessary SQL to retrieve the next page after `d, v` gets a bit gnarly: select * from compound_primary_key where pk1 >= "d" and not (pk1 = "d" and pk2 <= "v") order by pk1, pk2 See https://datasette-issue-190-compound-pks.now.sh/compound-pks-9aafe8f?sql=select+*+from+compound_primary_key+where+pk1+%3E%3D+%22d%22+and+not+%28pk1+%3D+%22d%22+and+pk2+%3C%3D+%22v%22%29+order+by+pk1%2C+pk2 This article was useful for figuring this out: https://use-the-index-luke.com/sql/partial-results/fetch-next-page | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377067541 | https://github.com/simonw/datasette/issues/190#issuecomment-377067541 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzA2NzU0MQ== | simonw 9599 | 2018-03-28T23:09:18Z | 2018-03-28T23:09:51Z | OWNER | Here's how I generated the table for testing this with 3 compound primary keys: CREATE_SQL = ''' CREATE TABLE compound_three_primary_keys ( pk1 varchar(30), pk2 varchar(30), pk3 varchar(30), content text, PRIMARY KEY (pk1, pk2, pk3) );''' alphabet = 'abcdefghijklmnopqrstuvwxyz' for a in alphabet: for b in alphabet: for c in alphabet: print(''' INSERT INTO compound_three_primary_keys VALUES ('{}', '{}', '{}', '{}'); '''.strip().format(a, b, c, '{}-{}-{}-{}-{}-{}'.format(a,b,c,a,b,c))) | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377072022 | https://github.com/simonw/datasette/issues/190#issuecomment-377072022 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzA3MjAyMg== | simonw 9599 | 2018-03-28T23:32:24Z | 2018-03-28T23:32:24Z | OWNER | Here's the SQL for a next page with three compound primary keys: https://datasette-issue-190-compound-pks.now.sh/compound-pks-8e99805?sql=select+*+from+compound_three_primary_keys%0D%0Awhere%0D%0A++%28pk1+%3E+%3Apk1%29%0D%0A++++or%0D%0A++%28pk1+%3D+%3Apk1+and+pk2+%3E+%3Apk2%29%0D%0A++++or%0D%0A++%28pk1+%3D+%3Apk1+and+pk2+%3D+%3Apk2+and+pk3+%3E+%3Apk3%29%0D%0Aorder+by+pk1%2C+pk2%2C+pk3%3B%0D%0A%0D%0A%0D%0A&pk1=a&pk2=d&pk3=v ``` select * from compound_three_primary_keys where (pk1 > :pk1) or (pk1 = :pk1 and pk2 > :pk2) or (pk1 = :pk1 and pk2 = :pk2 and pk3 > :pk3) order by pk1, pk2, pk3; ``` | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377454591 | https://github.com/simonw/datasette/issues/190#issuecomment-377454591 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzQ1NDU5MQ== | simonw 9599 | 2018-03-30T06:11:59Z | 2018-03-30T06:11:59Z | OWNER | Re-opening this issue: my fix doesn't play nicely with extra filter arguments. Consider this page: https://datasette-issue-190-compound-pks-not-quite-fixed.now.sh/compound-pks-8e99805/compound_three_primary_keys?content__contains=d The next link is to `?_next=f%2Cz%2Ct&content__contains=z` (that's next of `f,z,t`) but that gives us https://datasette-issue-190-compound-pks-not-quite-fixed.now.sh/compound-pks-8e99805/compound_three_primary_keys?_next=b%2Cx%2Cd&content__contains=d which shows `a,a,d` at the top. Sure enough, the generated SQL looks like this: https://datasette-issue-190-compound-pks-not-quite-fixed.now.sh/compound-pks-8e99805?sql=select+%2A+from+compound_three_primary_keys+where+%22content%22+like+%3Ap0+and+%28%5Bpk1%5D+%3E+%3Ap0%29%0A++or%0A%28%5Bpk1%5D+%3D+%3Ap0+and+%5Bpk2%5D+%3E+%3Ap1%29%0A++or%0A%28%5Bpk1%5D+%3D+%3Ap0+and+%5Bpk2%5D+%3D+%3Ap1+and+%5Bpk3%5D+%3E+%3Ap2%29+order+by+pk1%2C+pk2%2C+pk3+limit+101&p0=%25d%25&p1=b&p2=x&p3=d select * from compound_three_primary_keys where "content" like :p0 and ([pk1] > :p0) or ([pk1] = :p0 and [pk2] > :p1) or ([pk1] = :p0 and [pk2] = :p1 and [pk3] > :p2) order by pk1, pk2, pk3 limit 101 The parameters here are confused. The :p0 should be reserved just for the like clause - the other parameters should be p1, p2 and p3 (not p0, p1 and p2). | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377457087 | https://github.com/simonw/datasette/issues/190#issuecomment-377457087 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzQ1NzA4Nw== | simonw 9599 | 2018-03-30T06:30:23Z | 2018-03-30T06:30:23Z | OWNER | Interestingly, in deploying a copy of the database to demonstrate this final bug fix I had to use the `--force` argument like so: datasette publish now --branch=master compound-pks.db --force This is because `now` had already deployed a Dockerfile referencing `--branch=master` once already, so it thought nothing had changed and it could re-use that last deployment. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 | |
| 377457214 | https://github.com/simonw/datasette/issues/190#issuecomment-377457214 | https://api.github.com/repos/simonw/datasette/issues/190 | MDEyOklzc3VlQ29tbWVudDM3NzQ1NzIxNA== | simonw 9599 | 2018-03-30T06:31:15Z | 2018-03-30T06:31:15Z | OWNER | Fixed! https://datasette-issue-190-compound-pks-second-fix.now.sh/compound-pks-8e99805/compound_three_primary_keys?_next=b%2Cx%2Cd&content__contains=d now correctly shows `b,y,d` as the first row on the page. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | Keyset pagination doesn't work correctly for compound primary keys 309558826 |
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]);