Adapt SQL drivers to Qt 6 change of QVariant::isNull

In Qt 5, QVariant::isNull returned true if either the variant didn't
contain a value, or if the value was of a nullable type where the type's
isNull member function returned true.

In Qt 6, QVariant::isNull only returns true for variants that don't
contain a value; if the value contained is e.g. a null-QString or
QDateTime, then QVariant::isNull  returns false.

This change requires a follow up in the SQL drivers, which must
still treat null-values the same as null-variants, lest they write data
into the data base.

Add a static helper to QSqlResultPrivate that implements isNull-checking
of variants that contain a nullable type relevant for Sql, and add a
test case to the QSqlQuery test that exercises that code.

Pick-to: 6.2 6.3
Fixes: QTBUG-99408
Fixes: QTBUG-98471
Change-Id: I08b74a33aa3235c37d974f182da1f2bdcfd8217e
Reviewed-by: Fabian Kosmale <fabian.kosmale@qt.io>

1 files changed, 92 insertions, 0 deletions

diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 409038c909..cbc2cc2c1b 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -64,6 +64,8 @@ private slots:
     void size();
     void isNull_data() { generic_data(); }
     void isNull();
+    void writeNull_data() { generic_data(); }
+    void writeNull();
     void query_exec_data() { generic_data(); }
     void query_exec();
     void execErrorRecovery_data() { generic_data(); }
@@ -355,6 +357,7 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db )
     // drop all the table in case a testcase failed
     tablenames <<  qtest
                << qTableName("qtest_null", __FILE__, db)
+               << qTableName("qtest_writenull", __FILE__, db)
                << qTableName("qtest_blob", __FILE__, db)
                << qTableName("qtest_bittest", __FILE__, db)
                << qTableName("qtest_nullblob", __FILE__, db)
@@ -1769,6 +1772,95 @@ void tst_QSqlQuery::isNull()
     QVERIFY(q.isNull("unknown"));
 }
 
+void tst_QSqlQuery::writeNull()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+    const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+
+    QSqlQuery q(db);
+    const QString tableName = qTableName("qtest_writenull", __FILE__, db);
+
+    // the test data table is already used, so use a local hash to exercise the various
+    // cases from the QSqlResultPrivate::isVariantNull helper. Only PostgreSQL supports
+    // QUuid.
+    QMultiHash<QString, QVariant> nullableTypes = {
+        {"varchar(20)", QString("not null")},
+        {"varchar(20)", QByteArray("not null")},
+        {"date", QDateTime::currentDateTime()},
+        {"date", QDate::currentDate()},
+        {"date", QTime::currentTime()},
+    };
+    if (dbType == QSqlDriver::PostgreSQL)
+        nullableTypes["uuid"] = QUuid::createUuid();
+
+    // Helper to count rows with null values in the data column.
+    // Since QSqlDriver::QuerySize might not be supported, we have to count anyway
+    const auto countRowsWithNull = [&]{
+        q.exec("select id, data from " + tableName + " where data is null");
+        int size = 0;
+        while (q.next())
+            ++size;
+        return size;
+    };
+
+    for (const auto &nullableType : nullableTypes.keys()) {
+        auto tableGuard = qScopeGuard([&]{
+            q.exec("drop table " + tableName);
+        });
+        const QVariant nonNullValue = nullableTypes.value(nullableType);
+        // some useful diagnostic output in case of any test failure
+        auto errorHandler = qScopeGuard([&]{
+            qWarning() << "Test failure for data type" << nonNullValue.metaType().name();
+            q.exec("select id, data from " + tableName);
+            while (q.next())
+                qWarning() << q.value(0) << q.value(1);
+        });
+        QString createQuery = "create table " + tableName + " (id int, data " + nullableType;
+        if (dbType == QSqlDriver::MSSqlServer || dbType == QSqlDriver::Sybase)
+            createQuery += " null";
+        createQuery += ")";
+        QVERIFY_SQL(q, exec(createQuery));
+
+        int expectedNullCount = 0;
+        // verify that inserting a non-null value works
+        QVERIFY_SQL(q, prepare("insert into " + tableName + " values(:id, :data)"));
+        q.bindValue(":id", expectedNullCount);
+        q.bindValue(":data", nonNullValue);
+        QVERIFY_SQL(q, exec());
+        QCOMPARE(countRowsWithNull(), expectedNullCount);
+
+        // verify that inserting using a null QVariant produces a null entry in the database
+        QVERIFY_SQL(q, prepare("insert into " + tableName + " values(:id, :data)"));
+        q.bindValue(":id", ++expectedNullCount);
+        q.bindValue(":data", QVariant());
+        QVERIFY_SQL(q, exec());
+        QCOMPARE(countRowsWithNull(), expectedNullCount);
+
+        // verify that writing a null-value (but not a null-variant) produces a null entry in the database
+        const QMetaType nullableMetaType = nullableTypes.value(nullableType).metaType();
+        // creating a QVariant with meta type and nullptr does create a null-QVariant. We want
+        // to explicitly create a non-null variant, so we have to pass in a default-constructed
+        // value as well (and make sure that the default value is also destroyed again,
+        // which is clumsy to do using std::unique_ptr with a custom deleter, so use another
+        // scope guard).
+        void* defaultData = nullableMetaType.create();
+        const auto defaultTypeDeleter = qScopeGuard([&]{ nullableMetaType.destroy(defaultData); });
+        const QVariant nullValueVariant(nullableMetaType, defaultData);
+        QVERIFY(!nullValueVariant.isNull());
+
+        QVERIFY_SQL(q, prepare("insert into " + tableName + " values(:id, :data)"));
+        q.bindValue(":id", ++expectedNullCount);
+        q.bindValue(":data", nullValueVariant);
+        QVERIFY_SQL(q, exec());
+        QCOMPARE(countRowsWithNull(), expectedNullCount);
+
+        // all tests passed for this type if we got here, so don't print diagnostics
+        errorHandler.dismiss();
+    }
+}
+
 /*! TDS specific BIT field test */
 void tst_QSqlQuery::tds_bitField()
 {