MySQL: Handle TIME types as a string to allow the full range of data

As the full range of TIME is '-838:59:59' to '838:59:59' then we cannot
use QTime as the object to store this data in. Therefore a QString is
used instead for passing the data to and from. This does not impact
existing code using QTime already as it will still convert it from
the QString to a QTime to give the same result as before.

[ChangeLog][QtSql][MySQL] The TIME data type is now treated like a
string-based type in order to respect the full range of the TIME data
type.

Task-number: QTBUG-57028
Change-Id: Ieb7105bff3043b845f76bc873d088e6bac1e4f10
Reviewed-by: Edward Welbourne <edward.welbourne@qt.io>

1 files changed, 55 insertions, 0 deletions

diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index c64310a715..4ce1009c90 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -180,6 +180,8 @@ private slots:
     void timeStampParsing();
     void sqliteVirtualTable_data() { generic_data("QSQLITE"); }
     void sqliteVirtualTable();
+    void mysql_timeType_data() { generic_data("QMYSQL"); }
+    void mysql_timeType();
 
 #ifdef NOT_READY_YET
     void task_229811();
@@ -4722,5 +4724,58 @@ void tst_QSqlQuery::sqliteVirtualTable()
     QCOMPARE(qry.value(1).toString(), "Peter");
 }
 
+void tst_QSqlQuery::mysql_timeType()
+{
+    // The TIME data type is different to the standard with MySQL as it has a range of
+    // '-838:59:59' to '838:59:59'.
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+    const auto tableName = qTableName("mysqlTimeType", __FILE__, db);
+    tst_Databases::safeDropTables(db, { tableName });
+    QSqlQuery qry(db);
+    QVERIFY_SQL(qry, exec("create table " + tableName + " (t time(6))"));
+
+    // MySQL will convert days into hours and add them together so 17 days 11 hours becomes 419 hours
+    const QStringList timeData = { "-838:59:59.000000", "-123:45:56.789", "000:00:00.0", "123:45:56.789",
+        "838:59:59.000000", "15:50", "12", "1213", "0 1:2:3", "17 11:22:33" };
+    const QStringList resultTimeData = { "-838:59:59.000000", "-123:45:56.789000", "00:00:00.000000",
+        "123:45:56.789000", "838:59:59.000000", "15:50:00.000000", "00:00:12.000000", "00:12:13.000000",
+        "01:02:03.000000", "419:22:33.000000" };
+    for (const QString &time : timeData)
+        QVERIFY_SQL(qry, exec("insert into " + tableName + " (t) VALUES ('" + time + "')"));
+
+    QVERIFY_SQL(qry, exec("select * from " + tableName));
+    for (const QString &time : qAsConst(resultTimeData)) {
+        QVERIFY(qry.next());
+        QCOMPARE(qry.value(0).toString(), time);
+    }
+
+    QVERIFY_SQL(qry, exec("delete from " + tableName));
+    for (const QString &time : timeData) {
+        QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)"));
+        qry.bindValue(0, time);
+        QVERIFY_SQL(qry, exec());
+    }
+    QVERIFY_SQL(qry, exec("select * from " + tableName));
+    for (const QString &time : resultTimeData) {
+        QVERIFY(qry.next());
+        QCOMPARE(qry.value(0).toString(), time);
+    }
+
+    QVERIFY_SQL(qry, exec("delete from " + tableName));
+    const QList<QTime> qTimeBasedData = { QTime(), QTime(1, 2, 3, 4), QTime(0, 0, 0, 0), QTime(23,59,59,999) };
+    for (const QTime &time : qTimeBasedData) {
+        QVERIFY_SQL(qry, prepare("insert into " + tableName + " (t) VALUES (:time)"));
+        qry.bindValue(0, time);
+        QVERIFY_SQL(qry, exec());
+    }
+    QVERIFY_SQL(qry, exec("select * from " + tableName));
+    for (const QTime &time : qTimeBasedData) {
+        QVERIFY(qry.next());
+        QCOMPARE(qry.value(0).toTime(), time);
+    }
+}
+
 QTEST_MAIN( tst_QSqlQuery )
 #include "tst_qsqlquery.moc"