Created attachment 127843 [details]
Sample KEXI database with a table and a report based of on that table, with a group using the first column that shows different values in header and footer of same group.

SUMMARY

I believe this is actually a bug in KDb, but it is most visible when doing a report that has data sourced fields in a group footer: when there is a single group the QVariant returned by KexiDBReportDataSource is invalid; when there is more than one group it uses the first row of the next group.

STEPS TO REPRODUCE
1. Create a new file-based KEXI project.
2. Add a new table with two fields, it does not matter their data type.
3. Add some rows to that table and repeat some values in one of the two columns.
4. Add a new report with a section grouped by the column that has repeated values; mark at least the “Show group footer” option.
5. Add a field in the group header and set its data source to the field for grouping.
6. Add the same field but on the group footer.
6. Switch to edit mode.

OBSERVED RESULT
The fields in the header and footer of *the same group* do not have the same values, in fact the footer has the same value as the header for the *next* group; the last group’s footer has no value.

EXPECTED RESULT
The header and footer fields should have the same value because they are in the same group and use the grouping field as data source.

(I’ve attached a sample database that shows this behavior).


SOFTWARE/OS VERSIONS
Linux/KDE Plasma: openSUSE Leap 15.2
KDE Plasma Version: N/A
KDE Frameworks Version: 5.69.0
Qt Version: 5.12.2

ADDITIONAL INFORMATION

Is i said, i believe that i narrowed the problem down to KDb, but i do not know how to fix it.

This is what i found.

In KReportPreRenderePrivate::renderDetailSection, KReport does something along the lines of (pseudocode):

    dataSource->moveFirst();
    if (group->hasHeader()) {
        renderSection(group->header());
    }
    while (!dataSource->eof()) {
        renderSection(group->detail());
        dataSource->moveNext();
    }
    if (group->hasFooter()) {
        if (dataSource->movePrevious()) {
            renderSection(group->footer());
        }
    }

The problem is that dataSource->movePrevious() returns true but does NOT move back one record.

The only KReportDataSource-derivated class i could find in KEXI is KexiDBReportDataSource and its movePrevious is implemented as such:

    bool KexiDBReportDataSource::movePrevious()
    {
        if ( d->cursor ) return d->cursor->movePrev();
        return false;
    }

I have verified that d->cursor, a pointer to KDbCursor, is not null and that its movePrev() return true.

KDbCursor::movePrev only works if the cursor is buffered, and KexiDBReportDataSource makes sure of it. The rellevant part of movePrev is:

    if (m_afterLast && (m_records_in_buf > 0)) {
        drv_bufferMovePointerTo(m_records_in_buf - 1);
        m_at = m_records_in_buf;
        d->atBuffer = true; //now current record is stored in the buffer
        d->validRecord = true;
        m_afterLast = false;
        return true;
    }

It seems that buffering should be implemented by drivers and drv_bufferMovePointerTo tells the driver’s cursor to move its pointer to the last record in its cache.

However, i’ve seen that the driver for PostgreSQL does not implement buffering and SqlCursor does the following:

    void SqliteCursor::drv_bufferMovePointerTo(qint64 at)
    {
        d->curr_coldata = d->records.at(at);
    }

It seems that d->records is the “raw” record data from the database —the cache buffer—, and buffered cursor call  drv_appendCurrentRecordToBuffer() to record the current record to that buffer.  However, SqlCursor’s method, even though i’ve checked it is called, never stores anything inside records because the first thing it does is:

    void SqliteCursor::drv_appendCurrentRecordToBuffer()
    {
        if (!d->curr_coldata)
            return;
        …

The *only* time i could find that curr_coldata is set is precisely within drv_bufferMovePointerTo, that can only set it to null because records has never been filled.

Moveover, even if somehow curr_coldata were set, i do not see where it is being used and SqliteCursor::value always calls SQLite3’s function to retrieve the data from the prepared query.  Once sqlite3_step returns SQLITE_DONE after the last row, the prepared statement can not be used, SqliteCursor::value returns QVariant() and that is why the last group header does not display anything.

I do not know what do do now.

It seems to me that SQLite’s driver buffering does nothing, PostgreSQL’s driver explicitly says it so in its comments, and the only driver that it *might* work —i can not test it right now— is with MySQL’s as it uses mysql_data_seek() and mysql_fetch_row() inside drv_bufferMovePointerTo to retrieve the data from MySQL’s own buffer.