Select fails when querying single record to vector.

[povilasb]

I'm using Sqlite3 backend. I've set up a sample repo in github: https://github.com/povilasb/soci-sample.

I insert elements like this:

vector< pair<string, string> > users = {
    pair<string, string>("user1", "pass1"),
    pair<string, string>("user2", "pass2")
};

for (auto user : users) {
    sql << "insert or ignore into users values(:user, :pass);",
        use(user.first), use(user.second);
}

I query users like this:

vector<string> user_names(user_count);
vector<string> user_passwords(user_count);
sql << "select * from users;", into(user_names), into(user_passwords);

This all works well. But if database contains only one element, it is not selected. So if i insert elements with this snippet:

vector< pair<string, string> > users = {
//  pair<string, string>("user1", "pass1"),
    pair<string, string>("user2", "pass2")
};

for (auto user : users) {
    sql << "insert or ignore into users values(:user, :pass);",
        use(user.first), use(user.second);
}

I receive zero elements, while it should be "user2" "pass2" pair. Everyhing is ok if my select result vector has atleast 2 preallocated elements:

vector<string> user_names(user_count + 1);
vector<string> user_passwords(user_count + 1);

[cxw42]

I am seeing what I think is the same issue. I have added a test to my fork: https://github.com/cxw42/soci/commit/b8f73a5a53bd8d503671155f4be33d98ff5c4b8d

Test results:

-------------------------------------------------------------------------------
SQLite use and vector into, one result row, vector size 1
-------------------------------------------------------------------------------
../tests/sqlite3/test-sqlite3.cpp:150
...............................................................................

../tests/sqlite3/test-sqlite3.cpp:171: FAILED:
  CHECK( v.size() == 1 )
with expansion:
  0 == 1

System:

$ uname -a ; lsb_release -a
Linux HOSTNAME 5.4.0-73-generic #82-Ubuntu SMP Wed Apr 14 17:39:42 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux
No LSB modules are available.
Distributor ID: Ubuntu
Description:    Ubuntu 20.04.2 LTS
Release:        20.04
Codename:       focal

$ apt list --installed \*sqlite3\*
Listing... Done
libaprutil1-dbd-sqlite3/focal,now 1.6.1-4ubuntu2 amd64 [installed,automatic]
libdbd-sqlite3-perl/focal,now 1.64-1build1 amd64 [installed,automatic]
libsqlite3-0/focal-updates,focal-security,now 3.31.1-4ubuntu0.2 amd64 [installed,automatic]
libsqlite3-dev/focal-updates,focal-security,now 3.31.1-4ubuntu0.2 amd64 [installed,automatic]
sqlite3-doc/focal-updates,focal-security,now 3.31.1-4ubuntu0.2 all [installed,automatic]
sqlite3/focal-updates,focal-security,now 3.31.1-4ubuntu0.2 amd64 [installed]

[cxw42]

It looks like it might be in sqlite3_statement_backend::execute(int number):

        if (1 == number)
        {
            retVal = load_one();
        }
        else
        {
            retVal = load_rowset(number);
        }

load_rowset() updates dataCache_. However, load_one() does not. In this case, load_one() is called. Therefore, when soci::sqlite3_statement_backend::get_number_of_rows() is later called, and does return static_cast<int>(dataCache_.size()), dataCache_ is empty.

[vadz]

I don't use SQLite3 backend currently and I don't really know this code, but it definitely looks wrong, thanks for debugging this.

I think the problem is that sqlite3_statement_backend::get_number_of_rows() uses dataCache_ in the first place, the latter is only used for bulk (vector) operations, but this function should work after any kind of operation. The simplest fix would be to just maintain some numRowsFetched_ variable, as in the other backends, and set it to dataCache.size() for bulk selects and 0 or 1 for the other ones. If you can make a PR doing this (and including your new test), please don't hesitate to do it, TIA!

[cxw42]

(Note to self) No PR yet, unfortunately, but I want to note I found a related issue --- when there are no results from a select, the vector doesn't get resized to 0 elements.