HTTP 500 on `/v1/genie/meta/`

[pmussak]

I am using the meta REST endpoint inixmp4-ts. While developing I found that,

1. calls for https://ixmp.ece.iiasa.ac.at/v1/genie/meta/?join_run_index=true&table=true result in HTTP 500 ** context: current prod deployment of ixmp4 (0.7.1)

2. calls for https://ixmp.ece.iiasa.ac.at/v1/genie/meta/?table=true work as expected

3. calls for https://ixmp.ece.iiasa.ac.at/v1/genie/meta/?join_run_index=true result in HTTP 400 ***

I expect that all three requests result in HTTP 2** responses with data in the result.

** ixmp4 error log for first request

sqlalchemy.exc.ProgrammingError: (psycopg.errors.DuplicateAlias) table name "model" specified more than once
[SQL: SELECT DISTINCT model.name AS model, scenario.name AS scenario, run.version, runmetaentry.run__id, runmetaentry.key, runmetaentry.type, runmetaentry.value_int, runmetaentry.value_str, runmetaentry.value_float, runmetaentry.value_bool, runmetaentry.id 
FROM runmetaentry JOIN run ON run.id = runmetaentry.run__id JOIN model ON model.id = run.model__id JOIN model ON model.id = run.model__id JOIN scenario ON scenario.id = run.scenario__id 
WHERE model.name LIKE %(name_1)s::VARCHAR AND run.is_default AND run.is_default = true AND model.name LIKE %(name_2)s::VARCHAR AND run.is_default AND run.is_default = true ORDER BY runmetaentry.id ASC 
 LIMIT %(param_1)s::INTEGER OFFSET %(param_2)s::INTEGER]
[parameters: {'name_1': '%', 'name_2': '%', 'param_1': 5000, 'param_2': 0}]
(Background on this error at: https://sqlalche.me/e/20/f405)

*** error response for third request

{
    "message": "The provided filter arguments are malformed.",
    "kwargs": {
        "model": "run",
        "errors": [
            {
                "type": "extra_forbidden",
                "loc": [
                    "join_run_index"
                ],
                "msg": "Extra inputs are not permitted",
                "input": true,
                "url": "https://errors.pydantic.dev/2.6/v/extra_forbidden"
            }
        ]
    },
    "error_name": "bad_filter_arguments"
}

[pmussak]

Maybe related to https://github.com/iiasa/ixmp4/pull/37 ?

[meksor]

Sounds like the model table is getting joined multiple times accidentally there ... Probably just requires adding a if utils.is_joined(...) check in the right place.

[pmussak]

thanks @meksor I will try to fix it then

[pmussak]

@meksor I commited the non-working fix to this branch: origin/bug/rest-meta-endpoint-error

I found another problem with the REST meta endpoint. The parameter join_run_index is only properly handled when table == True. If table == False and join_run_index is not explicitly set, it still gets set to default (false) and then is not handled by the list method and therefor throws an illegal filter error (see "error response for third request" in the issue description). I build a quick fix for that and committed it to origin/bug/rest-meta-endpoint-error as well.

[meksor]

OK, I've done some investigating: Lets get the easy one out of the way first: 3's behaviour is almost correct using join_run_index without table=True should result in a 400. The error message is incorrect though and caused by an unrelated part of the code. Ill add an explicit 'check and raise' in the rest layer.

1 is trickier, i suspected at first that the model table is being joined twice. This is not the case however, it looks like there is a problem with selecting the model.name column as model and joining the model table as for some reason the postgres version (15) on the server does not like this ambiguity. My local (i think newer, 16?) postgres does not care and handles the query as expected... Same thing for sqlite, it doesnt care.

[pmussak]

@meksor thanks for looking into this! 1 sounds like it was a pain to debug

[meksor]

Fix here: https://github.com/iiasa/ixmp4/pull/69