Improve type detection for query results

When an aggregate function is used for a column in a SQL resultset then
it should ensure that the right data type is reported for that column.
This also concerns expressions when the returned column does not map
directly to a table column.

Test included for this.

Task-number: QTBUG-22038
Change-Id: I681297accc979081d14b44d190ab9d5f83aac215
Reviewed-by: Mark Brand <mabrand@mabrand.nl>
Reviewed-by: Honglei Zhang <honglei.zhang@nokia.com>
(cherry picked from commit 678ab52ccba0b6a6903e9aee404dbe84fb74c91d)

2 files changed, 147 insertions, 4 deletions

diff --git a/src/sql/drivers/sqlite/qsql_sqlite.cpp b/src/sql/drivers/sqlite/qsql_sqlite.cpp
index cc11770826..97d3531c8b 100644
--- a/src/sql/drivers/sqlite/qsql_sqlite.cpp
+++ b/src/sql/drivers/sqlite/qsql_sqlite.cpp
@@ -170,12 +170,37 @@ void QSQLiteResultPrivate::initColumns(bool emptyResultset)
         // must use typeName for resolving the type to match QSqliteDriver::record
         QString typeName = QString(reinterpret_cast<const QChar *>(
                     sqlite3_column_decltype16(stmt, i)));
-
-        int dotIdx = colName.lastIndexOf(QLatin1Char('.'));
-        QSqlField fld(colName.mid(dotIdx == -1 ? 0 : dotIdx + 1), qGetColumnType(typeName));
-
         // sqlite3_column_type is documented to have undefined behavior if the result set is empty
         int stp = emptyResultset ? -1 : sqlite3_column_type(stmt, i);
+
+        QVariant::Type fieldType;
+
+        if (!typeName.isEmpty()) {
+            fieldType = qGetColumnType(typeName);
+        } else {
+            // Get the proper type for the field based on stp value
+            switch (stp) {
+            case SQLITE_INTEGER:
+                fieldType = QVariant::Int;
+                break;
+            case SQLITE_FLOAT:
+                fieldType = QVariant::Double;
+                break;
+            case SQLITE_BLOB:
+                fieldType = QVariant::ByteArray;
+                break;
+            case SQLITE_TEXT:
+                fieldType = QVariant::String;
+                break;
+            case SQLITE_NULL:
+            default:
+                fieldType = QVariant::Invalid;
+                break;
+            }
+        }
+
+        int dotIdx = colName.lastIndexOf(QLatin1Char('.'));
+        QSqlField fld(colName.mid(dotIdx == -1 ? 0 : dotIdx + 1), fieldType);
         fld.setSqlType(stp);
         rInf.append(fld);
     }
diff --git a/tests/auto/qsqlquery/tst_qsqlquery.cpp b/tests/auto/qsqlquery/tst_qsqlquery.cpp
index 7d2c5475b2..147bbe1418 100644
--- a/tests/auto/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/qsqlquery/tst_qsqlquery.cpp
@@ -222,6 +222,8 @@ private slots:
     void sqlite_real_data() { generic_data("QSQLITE"); }
     void sqlite_real();
 
+    void aggregateFunctionTypes_data() { generic_data(); }
+    void aggregateFunctionTypes();
 private:
     // returns all database connections
     void generic_data(const QString &engine=QString());
@@ -3305,5 +3307,121 @@ void tst_QSqlQuery::sqlite_real()
     QCOMPARE(q.value(0).toDouble(), 5.6);
 }
 
+void tst_QSqlQuery::aggregateFunctionTypes()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+    {
+        const QString tableName(qTableName("numericFunctionsWithIntValues", __FILE__));
+        tst_Databases::safeDropTable( db, tableName );
+
+        QSqlQuery q(db);
+        QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER)"));
+
+        // First test without any entries
+        QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
+
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1)"));
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2)"));
+
+        QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toInt(), 3);
+        QCOMPARE(q.record().field(0).type(), QVariant::Int);
+
+        QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 1.5);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toInt(), 2);
+        QCOMPARE(q.record().field(0).type(), QVariant::Int);
+
+        QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toInt(), 1);
+        QCOMPARE(q.record().field(0).type(), QVariant::Int);
+
+        QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toInt(), 2);
+        QCOMPARE(q.record().field(0).type(), QVariant::Int);
+    }
+    {
+        const QString tableName(qTableName("numericFunctionsWithDoubleValues", __FILE__));
+        tst_Databases::safeDropTable( db, tableName );
+
+        QSqlQuery q(db);
+        QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id DOUBLE)"));
+
+        // First test without any entries
+        QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
+
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1.5)"));
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2.5)"));
+
+        QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 4.0);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 2.0);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toInt(), 2);
+        QCOMPARE(q.record().field(0).type(), QVariant::Int);
+
+        QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 1.5);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT MAX(id) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 2.5);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT ROUND(id, 1) FROM " + tableName + " WHERE id=1.5"));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 1.5);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+
+        QVERIFY_SQL(q, exec("SELECT ROUND(id, 0) FROM " + tableName + " WHERE id=2.5"));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toDouble(), 3.0);
+        QCOMPARE(q.record().field(0).type(), QVariant::Double);
+    }
+    {
+        const QString tableName(qTableName("stringFunctions", __FILE__));
+        tst_Databases::safeDropTable( db, tableName );
+
+        QSqlQuery q(db);
+        QVERIFY_SQL(q, exec("CREATE TABLE " + tableName + " (id INTEGER, txt VARCHAR(50))"));
+
+        QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
+
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (1, 'lower')"));
+        QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id, txt) VALUES (2, 'upper')"));
+
+        QVERIFY_SQL(q, exec("SELECT MAX(txt) FROM " + tableName));
+        QVERIFY(q.next());
+        QCOMPARE(q.value(0).toString(), QLatin1String("upper"));
+        QCOMPARE(q.record().field(0).type(), QVariant::String);
+    }
+}
+
 QTEST_MAIN( tst_QSqlQuery )
 #include "tst_qsqlquery.moc"