SQL cell scrambled by `Format cell`

[laenan8466]

Describe the bug

When enabling Format on save in the Editor-Formatting user settings, this will convert SQL cells to python code for formatting, but keeping them a SQL cell. So next formatting will convert again to python, having a cascaded, not functional cell like:

result = mo.sql(
    f"""
    result = mo.sql(
        f\"""
        SELECT
            *
        FROM OBFUSCATED
        \""",
        engine=engine,
    )
    """,
    engine=engine
)

This is not the case for oneliners I have (I suspect, bc. they do not need formatting).

Will you submit a PR?

• Yes

Environment

{
  "marimo": "0.17.0",
  "editable": false,
  "location": "OBFUSCATED",
  "OS": "Windows",
  "OS Version": "11",
  "Processor": "Intel64 Family 6 Model 140 Stepping 1, GenuineIntel",
  "Python Version": "3.13.9",
  "Locale": "de_DE",
  "Binaries": {
    "Browser": "--",
    "Node": "v22.20.0"
  },
  "Dependencies": {
    "click": "8.3.0",
    "docutils": "0.22.2",
    "itsdangerous": "2.2.0",
    "jedi": "0.19.2",
    "markdown": "3.9",
    "narwhals": "2.9.0",
    "packaging": "25.0",
    "psutil": "7.1.1",
    "pygments": "2.19.2",
    "pymdown-extensions": "10.16.1",
    "pyyaml": "6.0.3",
    "starlette": "0.48.0",
    "tomlkit": "0.13.3",
    "typing-extensions": "4.15.0",
    "uvicorn": "0.38.0",
    "websockets": "15.0.1"
  },
  "Optional Dependencies": {
    "altair": "5.5.0",
    "duckdb": "1.4.1",
    "nbformat": "5.10.4",
    "openai": "2.6.0",
    "pandas": "2.3.3",
    "polars": "1.34.0",
    "pyarrow": "21.0.0",
    "loro": "1.8.1",
    "pytest": "8.4.2",
    "python-lsp-server": "1.13.1",
    "ruff": "0.14.1",
    "sqlglot": "27.28.1"
  },
  "Experimental Flags": {
    "sql_linter": true
  }
}

Code to reproduce

1. Save code from minimal example below as reproduction-6882.py
2. Run marimo in edit mode on file
3. Enable Format on save
4. Hit save & wait

--> cell will become scrambled

Minimal Example:

import marimo

__generated_with = "0.17.0"
app = marimo.App(width="medium")


@app.cell
def _():
    import marimo as mo
    import pandas as pd
    return mo, pd


@app.cell
def _(pd):
    _data = {
        "id": range(1, 11),
        "value": [x * 1.5 for x in range(1, 11)],
    }
    df = pd.DataFrame(_data, dtype="str")
    return (df,)


@app.cell
def _(df, mo):
    _df = mo.sql(
        f"""
        CREATE OR REPLACE TABLE sample_data AS
        SELECT * FROM df
        """
    )
    return (sample_data,)


@app.cell
def _(df, mo, sample_data):
    _df = mo.sql(
        f"""
        SELECT
            id AS idid,
            value as valval
        FROM
            sample_data
        WHERE
            id IN ({','.join(df['id'][0:2].to_list())})
        """
    )
    return


if __name__ == "__main__":
    app.run()

[mscolnick]

@laenan8466 are you running with --watch or any other flags? is this consistent or just sometimes?

[laenan8466]

@mscolnick

@laenan8466 are you running with --watch or any other flags?

nope, running with uv run marimo edit reproduction-6882.py

@laenan8466 is this consistent or just sometimes?

With this example this is consistent. I tested around a bit, and it seems this is connected to 'function calls' (but not all) within the {...}. So when only referencing variables or no {...} at all, this does not happen. It also happens on other occasions (also when triggering formatting manually), but not on all SQL-cells.

Were you able to reproduce with the minimal example?

[mscolnick]

@laenan8466 i was not able to reproduce this myself, but i will keep trying, maybe with slow connection

[laenan8466]

@mscolnick What do you mean by slow connection (sorry, I seem to be slow myself ;-) )? I ran marimo locally and the minimal example uses only a in-memory DB.
I will try to reproduce this on another machine/OS later and report back.

For clarification, I experienced it in this cell from the example:

        SELECT
            id AS idid,
            value as valval
        FROM
            sample_data
        WHERE
            id IN ({','.join(df['id'][0:2].to_list())})

I will try to create a gif/movie of the bug.

[Antyos]

I am having the same issue with Markdown cells:

Original:

@app.cell(hide_code=True)
def _():
    mo.md(r"""### My heading""")
    return

Sometime later:

@app.cell(hide_code=True)
def _():
    mo.md(r"""mo.md(r\"""### My heading\""")""")
    return

Sometimes they get nested in even more mo.md() calls. Later they will get turned into @app._unparsable_cell().

I thought this was related to auto-format on save, but I turned it off and still get it. I have yet to reproduce it in a minimal example (which I know would help), but I know in my big notebook it keeps happening. (The only reproducible element is that it seems to only happen when my file has other uncommitted changes 🫠)

I didn't encounter this prior to 0.17.0.

[laenan8466]

@mscolnick now testing on linux OS (env below). I had problems with reproducing from loading the python file with marimo, bc. it is loaded as python cell, not sql cell. Is this a bug on its own?

Here are two gifs, showing different kind of behaviors.

Format SQL cell manually

Description:

• enabled the validation to display errors
• hit the 'format sql' button to show, that nothing happens
• select Format Cell from context menu (or CTRL + B, or Format on Save)
• See scrambling of cell with error from validation

Conclusion: This is not an Format on save error, but a formatting error.

Convert python-cell to sql-cell

Description:

• A python cell with only SQL code
• Select Convert to SQL from context menu
• See scrambled SQL cell, bc. python code does not get removed

Conclusion: This seems to be a different bug, but could be related to the formatting part.

---

What more can I do to help debug this?
Should I open new bugs for load pure sql cell as sql and convert to sql scrambles sql, or will we track both in this bug?

Environment Linux:

{
  "marimo": "0.17.2",
  "editable": false,
  "location": ".../.venv/lib/python3.13/site-packages/marimo",
  "OS": "Linux",
  "OS Version": "6.12.48-1-MANJARO",
  "Processor": "",
  "Python Version": "3.13.8",
  "Locale": "en_US",
  "Binaries": {
    "Browser": "--",
    "Node": "v22.20.0"
  },
  "Dependencies": {
    "click": "8.3.0",
    "docutils": "0.22.2",
    "itsdangerous": "2.2.0",
    "jedi": "0.19.2",
    "markdown": "3.9",
    "narwhals": "2.9.0",
    "packaging": "25.0",
    "psutil": "7.1.1",
    "pygments": "2.19.2",
    "pymdown-extensions": "10.16.1",
    "pyyaml": "6.0.3",
    "starlette": "0.48.0",
    "tomlkit": "0.13.3",
    "typing-extensions": "4.15.0",
    "uvicorn": "0.38.0",
    "websockets": "15.0.1"
  },
  "Optional Dependencies": {
    "altair": "5.5.0",
    "duckdb": "1.4.1",
    "loro": "1.8.2",
    "mcp": "1.19.0",
    "nbformat": "5.10.4",
    "openai": "2.6.1",
    "pandas": "2.3.3",
    "polars": "1.34.0",
    "pyarrow": "22.0.0",
    "python-lsp-ruff": "2.3.0",
    "python-lsp-server": "1.13.1",
    "ruff": "0.14.2",
    "sqlglot": "27.28.1"
  },
  "Experimental Flags": {}
}

[mscolnick]

These seem related, so we can stick to this issue for now. If we find out they are unrelated, we can open a new issue.

I still cannot reproduce this at all with the given steps, but we are still looking into this. I will have others on the team see if they can get the same error state.