fix: replace is_() with == in dataset name filter

[joelbarmettlerUZH]

This pull request addresses a syntax error occurring in the list_datasets function when filtering datasets by name, specifically when using PostgreSQL 16.

Issue

When querying the /v1/datasets endpoint with PostgreSQL 16, the function encounters a syntax error. The error occurs due to the use of SQLAlchemy's is_() method for comparing the dataset name, which is typically used for NULL comparisons. This resulted in a PostgreSQL syntax error near "$1" when the query was executed.

Error logs revealed:

sqlalchemy.exc.ProgrammingError: (sqlalchemy.dialects.postgresql.asyncpg.ProgrammingError) <class 'asyncpg.exceptions.PostgresSyntaxError'>: syntax error at or near "$1"
[SQL: SELECT datasets.id, datasets.name, datasets.description, datasets.metadata, datasets.created_at, datasets.updated_at 
FROM datasets 
WHERE datasets.name IS $1::VARCHAR ORDER BY datasets.id DESC 
 LIMIT $2::INTEGER]
[parameters: ('test', 11)]
(Background on this error at: https://sqlalche.me/e/20/f405)

This error indicates that PostgreSQL 16 is not interpreting the IS operator correctly with the parameter placeholder $1.

Fix

Changed the dataset name filter from:

query = query.filter(models.Dataset.name.is_(name))

to:

query = query.filter(models.Dataset.name == name)

Rationale

According to the SQLAlchemy documentation, the is_() method is meant for NULL comparisons and generates SQL using the IS operator. For value equality comparisons, the == operator should be used instead. This change ensures compatibility with PostgreSQL 16 and correctly generates the SQL for name comparison.

Impact

This change resolves the SQL syntax error and allows proper filtering of datasets by name when using PostgreSQL 16. It ensures that the /v1/datasets endpoint functions correctly with parameter-based filtering.

Related documentation:

SQLAlchemy ColumnOperators.is_(): https://docs.sqlalchemy.org/en/20/core/sqlelement.html#sqlalchemy.sql.expression.ColumnOperators.is_

[github-actions]

CLA Assistant Lite bot All contributors have signed the CLA ✍️ ✅

[joelbarmettlerUZH]

I have read the CLA Document and I hereby sign the CLA

[RogerHYang]

Thank you for your contribution, @joelbarmettlerUZH!

[mikeldking]

Thank you @joelbarmettlerUZH ! Amazing.

[axiomofjoy]

Good catch @joelbarmettlerUZH thanks!