psql: Fix SQL tests

This also accounts for some quirks on the PostgreSQL side:
 - Null values for a related table are placed in a different
   order when sorted.
 - Functions (sum, count) return a different type than other databases
 - Using quotes to account for case sensitivity with tables

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

1 files changed, 21 insertions, 12 deletions

diff --git a/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp b/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp
index 84cca482fb..f1c55df1ef 100644
--- a/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp
+++ b/tests/auto/sql/models/qsqlrelationaltablemodel/tst_qsqlrelationaltablemodel.cpp
@@ -385,6 +385,7 @@ void tst_QSqlRelationalTableModel::setData()
         model.setRelation(1, QSqlRelation(reltest5, "title", "abbrev"));
         model.setEditStrategy(QSqlTableModel::OnManualSubmit);
         model.setJoinMode(QSqlRelationalTableModel::LeftJoin);
+        model.setSort(0, Qt::AscendingOrder);
         QVERIFY_SQL(model, select());
 
         QCOMPARE(model.data(model.index(0,1)).toString(), QString("Mr"));
@@ -783,24 +784,32 @@ void tst_QSqlRelationalTableModel::sort()
     QVERIFY_SQL(model, select());
 
     QCOMPARE(model.rowCount(), 6);
-    QCOMPARE(model.data(model.index(0, 2)).toString(), QString("mister"));
-    QCOMPARE(model.data(model.index(1, 2)).toString(), QString("mister"));
-    QCOMPARE(model.data(model.index(2, 2)).toString(), QString("herr"));
-    QCOMPARE(model.data(model.index(3, 2)).toString(), QString("herr"));
-    QCOMPARE(model.data(model.index(4, 2)).toString(), QString(""));
-    QCOMPARE(model.data(model.index(5, 2)).toString(), QString(""));
+
+    QStringList stringsInDatabaseOrder;
+    // PostgreSQL puts the null ones (from the table with the original value) first in descending order
+    // which translate to empty strings in the related table
+    if (dbType == QSqlDriver::PostgreSQL)
+        stringsInDatabaseOrder << "" << "" << "mister" << "mister" << "herr" << "herr";
+    else
+        stringsInDatabaseOrder << "mister" << "mister" << "herr" << "herr" << "" << "";
+    for (int i = 0; i < 6; ++i)
+        QCOMPARE(model.data(model.index(i, 2)).toString(), stringsInDatabaseOrder.at(i));
 
     model.setSort(3, Qt::AscendingOrder);
     QVERIFY_SQL(model, select());
 
+    // PostgreSQL puts the null ones (from the table with the original value) first in descending order
+    // which translate to empty strings in the related table
+    stringsInDatabaseOrder.clear();
+    if (dbType == QSqlDriver::PostgreSQL)
+        stringsInDatabaseOrder << "herr" << "mister" << "mister" << "mister" << "mister" << "";
+    else if (dbType != QSqlDriver::Sybase)
+        stringsInDatabaseOrder << "" << "herr" << "mister" << "mister" << "mister" << "mister";
+
     if (dbType != QSqlDriver::Sybase) {
         QCOMPARE(model.rowCount(), 6);
-        QCOMPARE(model.data(model.index(0, 3)).toString(), QString(""));
-        QCOMPARE(model.data(model.index(1, 3)).toString(), QString("herr"));
-        QCOMPARE(model.data(model.index(2, 3)).toString(), QString("mister"));
-        QCOMPARE(model.data(model.index(3, 3)).toString(), QString("mister"));
-        QCOMPARE(model.data(model.index(4, 3)).toString(), QString("mister"));
-        QCOMPARE(model.data(model.index(5, 3)).toString(), QString("mister"));
+        for (int i = 0; i < 6; ++i)
+            QCOMPARE(model.data(model.index(i, 3)).toString(), stringsInDatabaseOrder.at(i));
     } else {
         QCOMPARE(model.data(model.index(0, 3)).toInt(), 1);
         QCOMPARE(model.data(model.index(1, 3)).toInt(), 2);