Aggregating functions do not work

[mciezkowski]

Hello
Do the aggregation functions work for you?
I'm using esp32-idf-sqlite3 library on SenseCAP Indicator D1 and I have a problem with aggregate functions. Normal select works without problem but select with avg, sum does not work. The query is correct because I checked on the PC version of sqlite. The program does not report an error, just the database does not return anything.

I use callback function and it is a code that returns nothing:
if (!sqlite3_open(DB_PATH, &db11))
{
int rc = sqlite3_exec(db11, query_buf1,
callback_woda_plot, (void*)data1, &zErrMsg);
sqlite3_close(db11);
}

Have you got any idea what is wrong?
Greetings
Maciek

[siara-cc]

Hi @mciezkowski , Aggregate functions should work if they don't violate the limitations of esp32. You could refer to this example although it is for the Arduino platform: https://github.com/siara-cc/esp32_arduino_sqlite3_lib/blob/master/examples/sqlite3_stackoverflow_users/sqlite3_stackoverflow_users.ino

One of the queries does a group by and count on a 1.9gb sqlite database over WiFi.

[mciezkowski]

Thank you siara-cc,
I will go follow your suggestions.

Greetings
Maciek

[mciezkowski]

Hi,
However, it turns out that the exec function returns error 10 (SQLITE_IOERR */ "disk I/O error",) when the query contains an aggregate function.
Does this somehow make the problem easier to diagnose?
I use spiffs.

Greetings
Maciek

[siara-cc]

SPIFFS known to cause such problems. Can try LITTLEFS?
siara-cc/esp32_arduino_sqlite3_lib#18

[mciezkowski]

Thanks a lot!
This is highly likely.

Greetings
Maciek