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>

3 files changed, 73 insertions, 37 deletions

diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index ada57996f4..aba99a9e20 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -2711,8 +2711,22 @@ void tst_QSqlQuery::lastInsertId()
 
     QSqlQuery q( db );
 
-    QVERIFY_SQL( q, exec( "insert into " + qtest + " values (41, 'VarChar41', 'Char41')" ) );
-
+    const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+    // PostgreSQL >= 8.1 relies on lastval() which does not work if a value is
+    // manually inserted to the serial field, so we create a table specifically
+    if (dbType == QSqlDriver::PostgreSQL) {
+        const auto tst_lastInsertId = qTableName("tst_lastInsertId", __FILE__, db);
+        tst_Databases::safeDropTable(db, tst_lastInsertId);
+        QVERIFY_SQL(q, exec(QStringLiteral("create table ") + tst_lastInsertId +
+                            QStringLiteral(" (id serial not null, t_varchar "
+                            "varchar(20), t_char char(20), primary key(id))")));
+        QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + tst_lastInsertId +
+                            QStringLiteral(" (t_varchar, t_char) values "
+                            "('VarChar41', 'Char41')")));
+    } else {
+        QVERIFY_SQL(q, exec(QStringLiteral("insert into ") + qtest +
+                            QStringLiteral(" values (41, 'VarChar41', 'Char41')")));
+    }
     QVariant v = q.lastInsertId();
 
     QVERIFY( v.isValid() );
@@ -3269,10 +3283,19 @@ void tst_QSqlQuery::timeStampParsing()
     const QString tableName(qTableName("timeStampParsing", __FILE__, db));
     tst_Databases::safeDropTable(db, tableName);
     QSqlQuery q(db);
-    QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName
-                        + QStringLiteral(" (id integer, datefield timestamp)")));
-    QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO ") + tableName
-                        + QStringLiteral(" (datefield) VALUES (current_timestamp)")));
+    QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
+    if (dbType == QSqlDriver::PostgreSQL) {
+        QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("("
+                            "id serial NOT NULL, "
+                            "datefield timestamp, primary key(id));")));
+    } else {
+        QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE ") + tableName + QStringLiteral("("
+                            "\"id\" integer NOT NULL PRIMARY KEY AUTOINCREMENT,"
+                            "\"datefield\" timestamp);")));
+    }
+    QVERIFY_SQL(q, exec(
+                    QStringLiteral("INSERT INTO ") + tableName + QStringLiteral(" (datefield) VALUES (current_timestamp);"
+                    )));
     QVERIFY_SQL(q, exec(QStringLiteral("SELECT * FROM ") + tableName));
     while (q.next())
         QVERIFY(q.value(1).toDateTime().isValid());
@@ -3643,15 +3666,17 @@ void tst_QSqlQuery::QTBUG_18435()
 
 void tst_QSqlQuery::QTBUG_5251()
 {
+    // Since QSqlTableModel will escape the identifiers, we need to escape
+    // them for databases that are case sensitive
     QFETCH( QString, dbName );
     QSqlDatabase db = QSqlDatabase::database( dbName );
     CHECK_DATABASE( db );
     const QString timetest(qTableName("timetest", __FILE__, db));
-
+    tst_Databases::safeDropTable(db, timetest);
     QSqlQuery q(db);
-    q.exec("DROP TABLE " + timetest);
-    QVERIFY_SQL(q, exec("CREATE TABLE  " + timetest + " (t  TIME)"));
-    QVERIFY_SQL(q, exec("INSERT INTO " + timetest +  " VALUES ('1:2:3.666')"));
+    QVERIFY_SQL(q, exec(QStringLiteral("CREATE TABLE \"") + timetest + QStringLiteral("\" (t TIME)")));
+    QVERIFY_SQL(q, exec(QStringLiteral("INSERT INTO \"") + timetest +
+                        QStringLiteral("\" VALUES ('1:2:3.666')")));
 
     QSqlTableModel timetestModel(0,db);
     timetestModel.setEditStrategy(QSqlTableModel::OnManualSubmit);
@@ -3664,7 +3689,8 @@ void tst_QSqlQuery::QTBUG_5251()
     QVERIFY_SQL(timetestModel, submitAll());
     QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:12:34.500"));
 
-    QVERIFY_SQL(q, exec("UPDATE " + timetest + " SET t = '0:11:22.33'"));
+    QVERIFY_SQL(q, exec(QStringLiteral("UPDATE \"") + timetest +
+                        QStringLiteral("\" SET t = '0:11:22.33'")));
     QVERIFY_SQL(timetestModel, select());
     QCOMPARE(timetestModel.record(0).field(0).value().toTime().toString("HH:mm:ss.zzz"), QString("00:11:22.330"));
 
@@ -4241,12 +4267,18 @@ void tst_QSqlQuery::aggregateFunctionTypes()
     QSqlDatabase db = QSqlDatabase::database(dbName);
     CHECK_DATABASE(db);
     QVariant::Type intType = QVariant::Int;
+    QVariant::Type sumType = intType;
+    QVariant::Type countType = intType;
     // QPSQL uses LongLong for manipulation of integers
     const QSqlDriver::DbmsType dbType = tst_Databases::getDatabaseType(db);
-    if (dbType == QSqlDriver::PostgreSQL)
-        intType = QVariant::LongLong;
-    else if (dbType == QSqlDriver::Oracle)
-        intType = QVariant::Double;
+    if (dbType == QSqlDriver::PostgreSQL) {
+        sumType = countType = QVariant::LongLong;
+    } else if (dbType == QSqlDriver::Oracle) {
+        intType = sumType = countType = QVariant::Double;
+    } else if (dbType == QSqlDriver::MySqlServer) {
+        sumType = QVariant::Double;
+        countType = QVariant::LongLong;
+    }
     {
         const QString tableName(qTableName("numericFunctionsWithIntValues", __FILE__, db));
         tst_Databases::safeDropTable( db, tableName );
@@ -4259,10 +4291,8 @@ void tst_QSqlQuery::aggregateFunctionTypes()
         QVERIFY(q.next());
         if (dbType == QSqlDriver::SQLite)
             QCOMPARE(q.record().field(0).type(), QVariant::Invalid);
-        else if (dbType == QSqlDriver::MySqlServer)
-            QCOMPARE(q.record().field(0).type(), QVariant::Double);
         else
-            QCOMPARE(q.record().field(0).type(), intType);
+            QCOMPARE(q.record().field(0).type(), sumType);
 
         QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (1)"));
         QVERIFY_SQL(q, exec("INSERT INTO " + tableName + " (id) VALUES (2)"));
@@ -4270,10 +4300,7 @@ void tst_QSqlQuery::aggregateFunctionTypes()
         QVERIFY_SQL(q, exec("SELECT SUM(id) FROM " + tableName));
         QVERIFY(q.next());
         QCOMPARE(q.value(0).toInt(), 3);
-        if (dbType == QSqlDriver::MySqlServer)
-            QCOMPARE(q.record().field(0).type(), QVariant::Double);
-        else
-            QCOMPARE(q.record().field(0).type(), intType);
+        QCOMPARE(q.record().field(0).type(), sumType);
 
         QVERIFY_SQL(q, exec("SELECT AVG(id) FROM " + tableName));
         QVERIFY(q.next());
@@ -4289,7 +4316,7 @@ void tst_QSqlQuery::aggregateFunctionTypes()
         QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
         QVERIFY(q.next());
         QCOMPARE(q.value(0).toInt(), 2);
-        QCOMPARE(q.record().field(0).type(), dbType != QSqlDriver::MySqlServer ? intType : QVariant::LongLong);
+        QCOMPARE(q.record().field(0).type(), countType);
 
         QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
         QVERIFY(q.next());
@@ -4332,7 +4359,7 @@ void tst_QSqlQuery::aggregateFunctionTypes()
         QVERIFY_SQL(q, exec("SELECT COUNT(id) FROM " + tableName));
         QVERIFY(q.next());
         QCOMPARE(q.value(0).toInt(), 2);
-        QCOMPARE(q.record().field(0).type(), dbType != QSqlDriver::MySqlServer ? intType : QVariant::LongLong);
+        QCOMPARE(q.record().field(0).type(), countType);
 
         QVERIFY_SQL(q, exec("SELECT MIN(id) FROM " + tableName));
         QVERIFY(q.next());
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);
diff --git a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
index bf76ed2bb6..ded360ef8d 100644
--- a/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
+++ b/tests/auto/sql/models/qsqltablemodel/tst_qsqltablemodel.cpp
@@ -275,7 +275,7 @@ void tst_QSqlTableModel::init()
 
 void tst_QSqlTableModel::cleanup()
 {
-    repopulateTestTables();
+    recreateTestTables();
 }
 
 void tst_QSqlTableModel::select()