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? https://github.com/siara-cc/esp32_arduino_sqlite3_lib/issues/18

[mciezkowski]

Thanks a lot! This is highly likely.

Greetings Maciek