QSqlQuery in Qt6: in-place vs prepared

dviktor

Finally, I was able to make the simplest reproducible example.
CMakeLists.txt
main.cpp
main.h contains only DB credentials, I will not post it here.

Final result:

As you can see I have 12 tests: 6 for DATE (4 textual dump + 2 views) and 6 for INT (4 textual dump + 2 views) types for columns in MariaDB. Also there are table definitions.

From the results it can be seen that the only problematic case is DATE + prepared query + QTableView. For the simplicity I haven't used custom model at all - just stayed with QSqlQueryModel. Also re-checking the contents of separate queries and models shows that their data is valid and contains expected records. But the view doesn't display it properly for some reason

dviktor

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 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

Christian Ehrlicher

Your test still does not create and fill the table. Fix it if you want help from me.

dviktor

@Christian-Ehrlicher I've made direct dump of my test database. You can quickly feed it to the MariaDB with:
cat dbtest.sql | mariadb -u root -p -D dbtest
assuming dbtest database already created

PS: added '05' strings to make search return the same results
PPS: '05' was used as search string in my MRE

Christian Ehrlicher

This is working fine for me:

#include <QtSql>
#include <QtWidgets>

int main(int argc, char* argv[])
{
    QApplication a(argc, argv);
    QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
    db.setDatabaseName("testdb");
    db.setUserName("");
    db.setPassword("");
    db.setHostName("");
    db.setConnectOptions("MYSQL_OPT_SSL_VERIFY_SERVER_CERT=FALSE");
    if (!db.open()) {
        qDebug() << db.lastError();
        return 1;
    }
    QSqlQuery query;
    query.exec("DROP TABLE IF EXISTS test");
    if (!query.exec("CREATE TABLE test (id int, ts date)")) {
        qDebug() << query.lastError();
        return 1;
    }
    QString insertSql1 = "INSERT INTO test (id, ts) VALUES (1, now())";
    if (!query.exec(insertSql1)) {
        qDebug() << query.lastError();
        return 1;
    }
    QTableView tv1;
    auto model1 = new QSqlQueryModel;
    model1->setQuery(QSqlQuery("SELECT * FROM test WHERE id = 1"));
    tv1.setModel(model1);
    tv1.show();

    QTableView tv2;
    auto model2 = new QSqlQueryModel;
    QSqlQuery q;
    q.prepare("SELECT * FROM test WHERE id = :id");
    q.bindValue(":id", 1);
    q.exec();
    model2->setQuery(std::move(q));
    tv2.setModel(model2);
    tv2.show();

    int ret = a.exec();
    query.exec("DROP TABLE IF EXISTS test");
    db.close();
    return ret;
}

Please provide a minimal, compilable example to reproduce your problem.

dviktor

I've already attached all source code files as well as database dump. I can surely reproduce the problem described on attached dataset

Christian Ehrlicher

I told you what I need and also showed you that it works correctly so... Prove me wrong

dviktor

I don't argue that it may work on this simplest two-column example without any issues but that's not my case. My SQL query is a bit more sophisticated and includes JOINs as well as calculated columns (with MAX, DATEDIFF etc). Checking with bare SQL request via mariadb command line client or phpMyAdmin console shows that the query line itself is ok - I get all expected rows and columns.

And as it shown on the screenshots above the problem goes away if I wrap DATE-typed columns with DATE_FORMAT so I effectively get string objects instead of date. So that's why I've decided it's a bug somewhere in Qt. I've also prepared minimal database sample which exposes erratic behavior. Moreover, the database itself may be provided by third-party service and not by means of Qt-aided creation. Who knows - may be there are some difference in a way database were prepared.

JonB

@dviktor
I looked at your "minimal" repro. The queries are horrendously long and complex, and require many tables with many columns. If I were you and I wanted help/someone to look at it I would spend my time reducing to an absolute minimum, e.g. does it require a JOIN at all, does it show up with one JOIN, do I really need a calculated column, do I need MAX() or other "aggregate", does it matter whether a column is DATE versus DATETIME, does the WHERE clause matter, etc. I would hope to produce the absolute minimum code and database where I could say: "if you run this query it works fine but as soon as I make just this one little change it goes wrong". Up to you.

Tiziano Bacocco

I'm encountering same problem, below is a simpler test case

+----------+----------+------+-----+---------+----------------+
| Field    | Type     | Null | Key | Default | Extra          |
+----------+----------+------+-----+---------+----------------+
| id       | int(11)  | NO   | PRI | NULL    | auto_increment |
| apertura | datetime | NO   |     | NULL    |                |
| chiusura | datetime | NO   |     | NULL    |                |
+----------+----------+------+-----+---------+----------------+

This works

QSqlQuery query(db);

    if (!query.exec("SELECT * FROM giornate WHERE id = 56")) {
        qCritical() << "Query failed:";
        qCritical() << query.lastError().text();
        return 1;
    }

    while (query.next()) {
        int id = query.value("id").toInt();

        // Qt automatically converts SQL DATETIME to QDateTime
        QDateTime apertura = query.record().value("apertura").value<QDateTime>();
        QDateTime chiusura = query.value("chiusura").toDateTime();

        qDebug().noquote()
        << QString("ID: %1").arg(id);

        qDebug().noquote()
        << QString("  apertura: %1")
        .arg(apertura.toString(Qt::ISODate));

        qDebug().noquote()
        << QString("  chiusura: %1")
        .arg(chiusura.toString(Qt::ISODate));

        qDebug() << "";
    }

This doesn't

QSqlQuery query(db);
    query.prepare("SELECT * FROM giornate WHERE id = ?");
    query.bindValue(0,56);
    if (!query.exec()) {
        qCritical() << "Query failed:";
        qCritical() << query.lastError().text();
        return 1;
    }

    while (query.next()) {
        int id = query.value("id").toInt();

        // Qt automatically converts SQL DATETIME to QDateTime
        QDateTime apertura = query.record().value("apertura").value<QDateTime>();
        QDateTime chiusura = query.value("chiusura").toDateTime();

        qDebug().noquote()
        << QString("ID: %1").arg(id);

        qDebug().noquote()
        << QString("  apertura: %1")
        .arg(apertura.toString(Qt::ISODate));

        qDebug().noquote()
        << QString("  chiusura: %1")
        .arg(chiusura.toString(Qt::ISODate));

        qDebug() << "";
    }

It could be that Qt6 is attempting to use mysql binary protocol for prepared statement data transfer, mariadb: Server version: 12.3.2-MariaDB Arch Linux
Qt: Using Qt version 6.11.1 in /usr/lib
Same code used to work with qt5

SGaist

@Tiziano-Bacocco hi,

Which error message are you getting ?

Tiziano Bacocco

Hi, probably the above issue is related to this
https://jira.mariadb.org/projects/CONC/issues/CONC-821?filter=allopenissues

getting no error, just invalid qdatetime object, isnull returns false, but qdatetime is invalid

Christian Ehrlicher

@Tiziano-Bacocco said in QSqlQuery in Qt6: in-place vs prepared:

Hi, probably the above issue is related to this

Yes, it is. Thanks for the pointer

dviktor

@Tiziano-Bacocco thank you a lot for finding this! I'll check which MariaDB version I have on my production server (with the same behavior). So it looks like to be a bug in MariaDB / Connector interface rather than Qt side.
Thanks to all who took care and participation in tries to dig that out!

Tiziano Bacocco

@dviktor With the version shipped with debian trixie 11.8.6-MariaDB-0+deb13u1 , it works properly