[BUG] target undo op not found after I execute a SQL query

Anki Development
1

My code:


    undo_entry = mw.col.add_custom_undo_entry("flatten")
    cards_exceed_future = mw.col.db.all(
        f"""
    WITH ranked_cards AS (
        SELECT id,
            true_due,
            stability,
            ROW_NUMBER() OVER (
                PARTITION BY true_due
                ORDER BY stability
            ) AS rank
        FROM (
                SELECT id,
                    {true_due} AS true_due,
                    json_extract(data, '$.s') AS stability
                FROM cards
                WHERE true_due >= { today }
                AND data != ''
                AND json_extract(data, '$.s') IS NOT NULL
                AND queue = {QUEUE_TYPE_REV}
                {"AND did IN %s" % did_list if did is not None else ""}
            )
    ),
    overdue AS (
        SELECT {true_due} AS true_due
        FROM cards
        WHERE true_due >= { today }
        AND queue = {QUEUE_TYPE_REV}
        AND data != ''
        AND json_extract(data, '$.s') IS NOT NULL
        {"AND did IN %s" % did_list if did is not None else ""}
        GROUP BY true_due
        HAVING COUNT(*) > { desired_flatten_limit }
    )
    SELECT id
        , true_due
        , stability
    FROM ranked_cards
    WHERE true_due IN (
            SELECT true_due
            FROM overdue
        )
    AND rank > { desired_flatten_limit }
    ORDER BY true_due
        """
    )
    mw.col.merge_undo_entries(undo_entry)

Debug Info:

Anki 24.06.3 (0d8d816a)  (ao)
Python 3.9.18 Qt 6.6.2 PyQt 6.6.1
Platform: macOS-14.5-arm64-arm-64bit

Traceback (most recent call last):
  File "aqt.taskman", line 142, in _on_closures_pending
  File "aqt.taskman", line 86, in <lambda>
  File "/Users/jarrettye/Library/Application Support/Anki2/addons21/759844606/schedule/flatten.py", line 38, in on_done
    finish_text = future.result()
  File "concurrent.futures._base", line 439, in result
  File "concurrent.futures._base", line 391, in __get_result
  File "concurrent.futures.thread", line 58, in run
  File "/Users/jarrettye/Library/Application Support/Anki2/addons21/759844606/schedule/flatten.py", line 44, in <lambda>
    lambda: flatten_background(did, desired_flatten_limit), on_done
  File "/Users/jarrettye/Library/Application Support/Anki2/addons21/759844606/schedule/flatten.py", line 111, in flatten_background
    mw.col.merge_undo_entries(undo_entry)
  File "anki.collection", line 1021, in merge_undo_entries
  File "anki._backend_generated", line 265, in merge_undo_entries
  File "anki._backend", line 171, in _run_command
anki.errors.InvalidInput: target undo op not found

I don’t know why the query breaks undo entry. It’s a select query with WITH. I think it doesn’t modify the database.

1 Like
2

is_dql() expects read-only queries to start with ‘select’. I’m not sure how we can solve this elegantly - it’s important it doesn’t let any mutating commands through accidentally, and I’m worried that attempting to parse out the CTE portion ourselves will inadvertently lead to false positives. Suggestions welcome. :slight_smile:

3

This information is very helpful. I re-wrote the SQL to replace WITH expression with subquery and JOIN. That fixed my issue.

2 Likes
4

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.