search

codeforIATI / iati-tables

https://iati-tables.codeforiati.org
MIT License
1 stars 1 forks source link

RIGHT and FULL OUTER JOINs are not currently supported #17

Open odscrachel opened 11 months ago

odscrachel commented 11 months ago

This isn't a big issue and I don't think it's supported in SQLite, which is what datasette is built on. At the very least we could add this to some documentation with some instructions on work arounds.

BibianaC commented 1 month ago

@odscrachel @odscjames I think SQLite now supports Right and Full Outer Joins.

https://www.sqlitetutorial.net/sqlite-full-outer-join/ https://www.sqlitetutorial.net/sqlite-right-join/

odscjames commented 1 month ago

@BibianaC Cool! Can you just run some test queries on our live server and make sure we are running a good enough version of SQLite/Datasette? We may be running an older version.

BibianaC commented 1 month ago

@odscjames

I have ran a query in https://datasette.codeforiati.org/iati. It is not supported.

Screenshot 2024-10-03 at 11 31 12 I don't see where is the datasette is hosted to check what version is running.

I have checked the change history of SQLite.

2022-06-25 (3.39.0)
Add (long overdue) support for [RIGHT and FULL OUTER JOIN](https://www.sqlite.org/lang_select.html#rjoin).
BibianaC commented 1 month ago

@odscjames We are using Ubuntu 22.04 which comes with SQLite 3.31 (The SQLite fix was done two months later 22.06).

The latest Ubuntu is 24.04 which comes with SQLite 3.45.

Is it viable to upgrade now to the latest Ubuntu?

odscjames commented 1 month ago

That's probably the easiest fix, and a good thing to do anyway. Thanks.

odscjames commented 3 weeks ago

Whoops, the language in my commit message wasn't quite correct - this isn't fixed until new server goes live

odscjames commented 5 days ago

Ok, putting this upgrade on hold.

Problems are found running tables on the new Ubuntu. A particular SQL query would take up all the memory until Linux would OOM Kill the Postgres server.

The query is the CREATE TABLE "_object_type_aggregate" ... one.

( Also note, when doing this upgrade we were confused by a python crash but this is nothing to do with this upgrade: https://github.com/codeforIATI/iati-tables/issues/57 )