Discarded record is actually submitted after removing another row

Question

Qt 6.8.0 under Ubuntu 24.10. I have a QTableView that shows the content of a QSqlTableModel (SQLite). The edit strategy is set to OnRowChange and using two QPushButton I allow the user to adds a new record or to delete one.

How to reproduce the issue

1. add a new record and edit as you wish

2. DO NOT submit it, i.e. press ESC to discard the current edit

3. add again a new record and edit as you wish

[

4. submit it, i.e. press ENTER

5. delete the record

Expected behavior

At step 2, we discarded the current edit and we did not submit the record. But at step 5, after deleting the submitted one (at step 4) the "ghost" of the discarded record is actually there!

By the way, you can see at step 4 that the index is 1: if the previous record was not discarded but submitted (for any reason) the index should be 2.

Questions

What is happening here? How to prevent this unwanted behavior?

Here a complete MRE

mainwindow.h

#ifndef MAINWINDOW_H
#define MAINWINDOW_H

#include <QMainWindow>
#include <QSqlRelationalTableModel>

QT_BEGIN_NAMESPACE
namespace Ui {
class MainWindow;
}
QT_END_NAMESPACE

class MainWindow : public QMainWindow
{
    Q_OBJECT

public:
    MainWindow(QWidget *parent = nullptr);
    ~MainWindow();

private:
    Ui::MainWindow *ui;
    QSqlRelationalTableModel *_model = nullptr;

private slots:
    void add();
    void remove();

};
#endif // MAINWINDOW_H

mainwindow.cpp

#include "mainwindow.h"
#include "ui_mainwindow.h"

#include <QSqlError>
#include <QSqlRecord>
#include <QSqlQuery>
#include <QSqlRelationalDelegate>

MainWindow::MainWindow(QWidget *parent) : QMainWindow(parent), ui(new Ui::MainWindow)
{
    ui->setupUi(this);

    connect(ui->btnAdd, &QPushButton::clicked, this, &MainWindow::add);
    connect(ui->btnRemove, &QPushButton::clicked, this, &MainWindow::remove);

    QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE", "mydb");
    db.setDatabaseName("mydb.sql");
    db.open();

    QSqlQuery query("DROP TABLE IF EXISTS foo", db);
    query.exec();

    QString sql = "CREATE TABLE foo \
        (variant TEXT NOT NULL, \
         name TEXT NOT NULL, \
         capacity INT NOT NULL DEFAULT '0', \
         PRIMARY KEY (name));";

    query.prepare(sql);
    query.exec();

    _model = new QSqlRelationalTableModel(this, db);
    _model->setTable("foo");
    _model->setEditStrategy(QSqlRelationalTableModel::OnRowChange);
    ui->table->setSortingEnabled(false);

    _model->select();

    ui->table->setModel(_model);
    ui->table->setItemDelegate(new QSqlRelationalDelegate(this));
    ui->table->setFocusPolicy(Qt::TabFocus);
}

MainWindow::~MainWindow()
{
    delete ui;
}

void MainWindow::add()
{
    _model->insertRow(_model->rowCount());
    QModelIndex index = _model->index(_model->rowCount() - 1, 0);
    if (!index.isValid()) return;
    ui->table->setCurrentIndex(index);
    ui->table->edit(index);
}

void MainWindow::remove()
{
    int row = ui->table->selectionModel()->currentIndex().row();
    _model->removeRow(row);
    _model->submitAll();
    _model->select();
}

mainwindow.ui

<?xml version="1.0" encoding="UTF-8"?>
<ui version="4.0">
 <class>MainWindow</class>
 <widget class="QMainWindow" name="MainWindow">
  <property name="geometry">
   <rect>
    <x>0</x>
    <y>0</y>
    <width>800</width>
    <height>600</height>
   </rect>
  </property>
  <property name="windowTitle">
   <string>MainWindow</string>
  </property>
  <widget class="QWidget" name="centralwidget">
   <layout class="QGridLayout" name="gridLayout">
    <item row="0" column="0" rowspan="3">
     <widget class="QTableView" name="table"/>
    </item>
    <item row="0" column="1">
     <widget class="QPushButton" name="btnAdd">
      <property name="text">
       <string>Add</string>
      </property>
     </widget>
    </item>
    <item row="1" column="1">
     <widget class="QPushButton" name="btnRemove">
      <property name="text">
       <string>Remove</string>
      </property>
     </widget>
    </item>
    <item row="2" column="1">
     <spacer name="verticalSpacer">
      <property name="orientation">
       <enum>Qt::Orientation::Vertical</enum>
      </property>
      <property name="sizeHint" stdset="0">
       <size>
        <width>20</width>
        <height>473</height>
       </size>
      </property>
     </spacer>
    </item>
   </layout>
  </widget>
  <widget class="QMenuBar" name="menubar">
   <property name="geometry">
    <rect>
     <x>0</x>
     <y>0</y>
     <width>800</width>
     <height>22</height>
    </rect>
   </property>
  </widget>
  <widget class="QStatusBar" name="statusbar"/>
 </widget>
 <resources/>
 <connections/>
</ui>