DAG params ordering goes by key string length for some backends

[Usiel]

Apache Airflow version

2.9.1

If "Other Airflow 2 version" selected, which one?

No response

What happened?

Took me a while to find the underlying issue for this one 😓

When creating a DAG with params the ordering does not follow the insertion order (as promised by https://airflow.apache.org/docs/apache-airflow/stable/core-concepts/params.html), but instead params are ordered by the length of their key when MySQL is used:

This seems to be an issue with MySQL's JSON column, which orders the keys on insertion. The Airflow side of things (and SQLAlchemy) does not cause any issues with the ordering.

What you think should happen instead?

Instead params should be shown in insertion order.

How to reproduce

1. Run Airflow with MySQL (I tested 5.7 and 8.0) or Postgres and the following DAG.

import datetime
import json
from pathlib import Path

from airflow.decorators import dag, task
from airflow.models import Param


@dag(
    dag_id=Path(__file__).stem,
    dag_display_name="Params UI MySQL Bug",
    schedule=None,
    start_date=datetime.datetime(2022, 3, 4),
    default_args={},
    params={
        "____________first": Param(default="3."),
        "_______second": Param(default="2."),
        "________________third": Param(default="4."),
        "_______fourth": Param(default="1."),
    },
)
def dag():
    @task(task_display_name="Show used parameters")
    def show_params(**kwargs) -> None:
        params = kwargs["params"]
        print(f"This DAG was triggered with the following parameters:\n\n{json.dumps(params, indent=4)}\n")

    show_params()

dag()

2. Trigger the DAG from the UI.

Deployment details

MySQL 5.7 or 8.0 / Postgres

Anything else?

Workaround: Set compress_serialized_dags = True to avoid usage of the MySQL JSON column (comes with the drawback of disabled DAG dependencies view).

Maybe related to #35944 (assuming some of the people who complained used MySQL).

Are you willing to submit PR?

• Yes I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct

[Usiel]

I do have a working code fix (no tests yet), but I also don't mind leaving this for someone else if anyone is keen.

[vatsrahul1001]

@Usiel feel free to take this up if you already have solution for this

[Usiel]

I checked the MySQL docs and found

To make lookups more efficient, MySQL also sorts the keys of a JSON object. You should be aware that the result of this ordering is subject to change and not guaranteed to be consistent across releases.
https://dev.mysql.com/doc/refman/8.4/en/json.html

Which hints to me that Postgres probably does the same or something similar, and I can, in fact, reproduce the same issue. I changed the issue title accordingly.