Convert Boolean value into integer

According to documentation, SQLite doesn't have a separate Boolean
storage class. Instead, values are stored as integers 0(false) and
1(true). In QSqlQuery::bindValue(), if a boolean value is bound
to a placeholder, it is converted to text true and false. This fix
converts boolean value to integer 0 and 1.

Task-number: QTBUG-23895
Change-Id: I4945971172f0b5e5819446700390033a1a4ce301
Reviewed-by: Michael Goddard <michael.goddard@nokia.com>
Reviewed-by: Mark Brand <mabrand@mabrand.nl>

2 files changed, 65 insertions, 0 deletions

diff --git a/src/sql/drivers/sqlite/qsql_sqlite.cpp b/src/sql/drivers/sqlite/qsql_sqlite.cpp
index deb29f8059..168500e5a8 100644
--- a/src/sql/drivers/sqlite/qsql_sqlite.cpp
+++ b/src/sql/drivers/sqlite/qsql_sqlite.cpp
@@ -92,6 +92,9 @@ static QVariant::Type qGetColumnType(const QString &tpName)
         return QVariant::Double;
     if (typeName == QLatin1String("blob"))
         return QVariant::ByteArray;
+    if (typeName == QLatin1String("boolean")
+        || typeName == QLatin1String("bool"))
+        return QVariant::Bool;
     return QVariant::String;
 }
 
@@ -413,6 +416,7 @@ bool QSQLiteResult::exec()
                                             ba->size(), SQLITE_STATIC);
                     break; }
                 case QVariant::Int:
+                case QVariant::Bool:
                     res = sqlite3_bind_int(d->stmt, i + 1, value.toInt());
                     break;
                 case QVariant::Double:
diff --git a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
index 3de65d00ad..559afc4ef4 100644
--- a/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
+++ b/tests/auto/sql/kernel/qsqlquery/tst_qsqlquery.cpp
@@ -215,6 +215,8 @@ private slots:
     void QTBUG_21884();
     void QTBUG_16967_data() { generic_data("QSQLITE"); }
     void QTBUG_16967(); //clean close
+    void QTBUG_23895_data() { generic_data("QSQLITE"); }
+    void QTBUG_23895(); //sqlite boolean type
 
     void sqlite_constraint_data() { generic_data("QSQLITE"); }
     void sqlite_constraint();
@@ -331,6 +333,7 @@ void tst_QSqlQuery::dropTestTables( QSqlDatabase db )
                << qTableName("bug5765", __FILE__)
                << qTableName("bug6852", __FILE__)
                << qTableName("bug21884", __FILE__)
+               << qTableName("bug23895", __FILE__)
                << qTableName( "qtest_lockedtable", __FILE__ )
                << qTableName( "Planet", __FILE__ )
                << qTableName( "task_250026", __FILE__ )
@@ -3264,6 +3267,64 @@ void tst_QSqlQuery::QTBUG_16967()
     }
 }
 
+/**
+  * In SQLite when a boolean value is bound to a placeholder, it should be converted
+  * into integer 0/1 rather than text "false"/"true". According to documentation,
+  * SQLite does not have separate Boolean storage class. Instead, Boolean values are
+  * stored as integers.
+  */
+void tst_QSqlQuery::QTBUG_23895()
+{
+    QFETCH(QString, dbName);
+    QSqlDatabase db = QSqlDatabase::database(dbName);
+    CHECK_DATABASE(db);
+
+    QSqlQuery q(db);
+
+    QString tableName(qTableName("bug23895", __FILE__ ));
+    q.prepare("create table " + tableName + "(id integer primary key, val1 bool, val2 boolean)");
+    QVERIFY_SQL(q, exec());
+    q.prepare("insert into " + tableName + "(id, val1, val2) values(?, ?, ?);");
+    q.addBindValue(1);
+    q.addBindValue(true);
+    q.addBindValue(false);
+    QVERIFY_SQL(q, exec());
+
+    QString sql="select * from " + tableName;
+    QVERIFY_SQL(q, exec(sql));
+    QVERIFY_SQL(q, next());
+
+    QCOMPARE(q.record().field(0).type(), QVariant::Int);
+    QCOMPARE(q.value(0).type(), QVariant::LongLong);
+    QCOMPARE(q.value(0).toInt(), 1);
+    QCOMPARE(q.record().field(1).type(), QVariant::Bool);
+    QCOMPARE(q.value(1).type(), QVariant::LongLong);
+    QCOMPARE(q.value(1).toBool(), true);
+    QCOMPARE(q.record().field(2).type(), QVariant::Bool);
+    QCOMPARE(q.value(2).type(), QVariant::LongLong);
+    QCOMPARE(q.value(2).toBool(), false);
+
+    q.prepare("insert into " + tableName + "(id, val1, val2) values(?, ?, ?);");
+    q.addBindValue(2);
+    q.addBindValue(false);
+    q.addBindValue(false);
+    QVERIFY_SQL(q, exec());
+
+    sql="select * from " + tableName + " where val1";
+    QVERIFY_SQL(q, exec(sql));
+    QVERIFY_SQL(q, next());
+    QCOMPARE(q.value(0).toInt(), 1);
+    QVERIFY(!q.next());
+
+    sql="select * from " + tableName + " where not val2";
+    QVERIFY_SQL(q, exec(sql));
+    QVERIFY_SQL(q, next());
+    QCOMPARE(q.value(0).toInt(), 1);
+    QVERIFY_SQL(q, next());
+    QCOMPARE(q.value(0).toInt(), 2);
+    QVERIFY(!q.next());
+}
+
 void tst_QSqlQuery::oraOCINumber()
 {
     QFETCH( QString, dbName );