QSqlQuery in Qt6: in-place vs prepared

First of all, Qt version is 6.10.0, testing on Arch Linux rolling. I think I've found a bug in Qt but maybe it's just my fault.

My program asks MariaDB database for some data and displays it in QTableView instance via a QSqlQueryModel subclass - I've just reimplemented columnCount(), headerData() and data() methods to get read-only model:

• columnCount() gives constant number (query used with this model always have constant number of columns in SELECT statement)
• headerData() is reimplemented just to give human-readable column names
• data() method is used to format raw values and apply some styling. This is my implementation of data():

QVariant EquipmentModel::data(const QModelIndex &item, int role) const {
    // shortcuts
    QVariant value = QSqlQueryModel::data(item, role);
    int column = item.column();

    // stash raw value for column 2
    if (role == Qt::UserRole) {
        if (column == 2)
            return QSqlQueryModel::data(item, Qt::DisplayRole);
    }

    // data to be displayed
    if (role == Qt::DisplayRole) {
        // name
        if (column == 1)
            return value;

        // type
        if (column == 2) {
            if (value.toString() == APP_TYPE1)
                return QString(APP_TYPE1_TXT);

            if (value.toString() == APP_TYPE2)
                return QString(APP_TYPE2_TXT);

            if (value.toString() == APP_TYPE)
                return QString(APP_TYPE3_TXT);
        }

        // verification dates
        if ((column == 3) || (column == 4)) {
            if (value.isNull())
                return QVariant(QMetaType::fromType<QDate>());
            else
                return QDateTime::fromString(value.toString(), Qt::ISODate).toString("dd.MM.yyyy");
        }
    }

    // text alignment in a cell
    if (role == Qt::TextAlignmentRole) {
        // default alignment
        int alignment = Qt::AlignVCenter;

        // type, verification date, verification valid to
        if ((column == 2) || (column == 3) || (column == 4))
            alignment |= Qt::AlignCenter;

        // name
        if (column == 1)
            alignment |= Qt::AlignLeft;

        return alignment;
    }

    // background colors
    if (role == Qt::BackgroundRole) {
        // verification date
        if (
            (column == 3) &&
            (item.siblingAtColumn(2).data(Qt::UserRole).toString() == APP_TYPE1) &&
            item.data(Qt::DisplayRole).isNull())
            return QBrush(QColor("red"), Qt::SolidPattern);

        // verification upto date
        if (
            (column == 4)  &&
            (item.siblingAtColumn(2).data(Qt::UserRole).toString() == APP_TYPE1)) {
            // time has out or empty value (no info about verifications)
            if (item.data(Qt::DisplayRole).isNull() || (item.siblingAtColumn(5).data(Qt::DisplayRole).toInt() <= 0))
                return QBrush(QColor("red"), Qt::SolidPattern);

            // month or less left
            if (item.siblingAtColumn(5).data(Qt::DisplayRole).toInt() <= 30)
                return QBrush(QColor("orange"), Qt::SolidPattern);
        }
    }

    // the rest of model-specific data
    return value;
}

In main program code I'm assigning the instance of my EquipmentModel to the main QTableView via its setModel() and then setting SQL SELECT statement via setQuery(). I have some form of basic query which contains optional WHERE clause:

QString basic_query =
        "SELECT DISTINCT e.id, e.name, e.type, v.ts, v.upto, v.d_left "
        "FROM Equipment AS e "
        "INNER JOIN Users AS u ON u.id = e.user_id "
        "LEFT JOIN (SELECT equipment_id, max(ts) AS ts, max(upto) AS upto, DATEDIFF(MAX(upto), NOW()) AS d_left FROM EquipmentVerifications GROUP BY equipment_id) AS v ON v.equipment_id = e.id "
        "LEFT JOIN Equipment_Supplies AS es ON es.equipment_id = e.id "
        "LEFT JOIN Supplies AS s ON s.id = es.supply_id "
        "WHERE %1 "
        "ORDER BY e.name";

At the first run (or on ResetSearchBtn->clicked() signal) I apply this query to the model with the following:

model->setQuery(basic_query.arg("1 = 1"));

which in turn gives me all the records from desired table. For this case it's okay for me to use in-place query because no validation of user input should be done. However when search field is populated and user has clicked on search button I use prepared statement approach according to the Qt documentation:

QSqlQuery q;
        
q.prepare(basic_query.arg("e.name LIKE :search OR s.name LIKE :search OR s.description LIKE :search OR s.sn LIKE :search"));
q.bindValue(":search", QString("%%1%").arg(SearchStringFld->text()));
q.exec();

model->setQuery(std::move(q));

The problem is that it works only partially. For example, values in some columns are displayed properly, background coloring works, but columns 3 and 4 doesn't contain any values in it. When I try to qDebug() ram item values in EquipmentModel::data() method I can see that this method is called multiple times (as expected) and first calls regarding Qt::DisplayRole and columns 3 and 4 gives proper valid QDates. However, later calls lead to invalid QDate instances so the view doesn't show any data in these columns. I've re-checked the correctness of the SELECT statement by dumping executedQuery() and feeding it directly in MariaDB. Moreover, if I try to dump model's data by walking through rows and columns right after assigning query via setQuery() I see that all data cells are good.

To check if this is the problem with prepared query I've replaced query assignment with this one:

model->setQuery(basic_query.arg("e.name LIKE '%%1%' OR s.name LIKE '%%1%' OR s.description LIKE '%%1%' OR s.sn LIKE '%%1%'").arg(SearchStringFld->text()));

and the problem gone! Here are two screenshots to illustrate what I'm saying (columns with indices 0 and 5 are hidden because of their pure technical role):

As you can see the number of rows in query is the same, column 1 is populated properly and background coloring as applied as well, however, columns 3 and 4 are not populated because of invalid QDate in case of prepared query. It looks like a bug in Qt, however, there is a chance that I'm doing something terribly wrong. I've found thread on Qt forum which gives the same advice: don't allocate on heap, use stack and then move to the setQuery(). I'd like to use prepared query in that case because I'm dealing with user-provided input and want to avoid SQL injections during my queries to the DB.

So, to summarize: am I doing something wrong? What is the proper way to use prepared queries in Qt 6 when assigning them to the models?

ADDITIONAL INFO I've found that the problem somehow related to the data types used in database (MariaDB in my case). To check this I've changed data type for columns 3 and 4 from DATE to INT and edited SQL query to use direct difference instead of DATEDIFF - and all works fine! Seems like QSqlQueryModel somehow invalidates values for these columns on successive requests so QDate(Invalid) returned instead. That was not a problem at least on Qt 5.12. Maybe some date/datetime handling was changed during Qt 6 development cycle...

ADDITIONAL INFO 2 There is MRE:

• CMakeLists.txt
• main.cpp
• main.h contains only DB credentials, I will not post it here.
• Database schema

And here is the screenshot of final test:

ADDITIONAL INFO 3 If I change my basic SQL query statement (the first line of it) from:

"SELECT DISTINCT e.id, e.name, e.type, v.ts, v.upto, v.d_left "

to:

"SELECT DISTINCT e.id, e.name, e.type, DATE_FORMAT(v.ts, '%d.%m.%Y'), DATE_FORMAT(v.upto, '%d.%m.%Y'), v.d_left "

(remember that I still use plain QSqlQueryModel for MRE so no custom data conversions involved) then all works fine!

So, it looks like QSqlQueryModel misbehaves somehow in connection with QTableView when some of the columns are of QDate type

ADDITIONAL INFO 4 There is example dataset with anonymized strings