UNION with ATTACH command not working

[marchingband]

I am calling ATTACH DATABASE '/emmc/foo.db' AS foo;
and ATTACH DATABASE '/emmc/bar.db' AS bar;
and then SELECT uid FROM main.Tokens UNION SELECT uid from foo.Tokens UNION SELECT uid from bar.Tokens;

but I only get rows from bar, those from main and from foo are gone.

my attach function looks like this:

static void db_attach(sqlite3 *db, char *event_id){
    char file_path[100];
    char sql_stmt[300];

    sprintf(file_path, "/emmc/%s.db", event_id);
    sprintf(sql_stmt, "ATTACH DATABASE '%s' AS %s;", file_path, event_id);
    ESP_LOGI(tag, "SQL: %s", sql_stmt);

    int rc = sqlite3_exec(db, sql_stmt, callback, &dbRecProc, &zErrMsg);
    if (rc != SQLITE_OK) {
        ESP_LOGE(tag, "SQL error: %s\n", zErrMsg);
        sqlite3_free(zErrMsg);
    } else {
        ESP_LOGI(tag, "Attach %s successful", file_path);
    }
}

I have also tried UNION ALL, as well as different way of doing the SELECT such as SELECT uid from (SELECT uid FROM main.Tokens ......). I always get just the rows from bar.

Am I missing any steps in the ATTACH process?

Any other ideas what could be going wrong?

thank you!

[marchingband]

I have tested running individual queries on the ATTACHed databases, and those all work, so I believe the ATTACH works, but for some reason the UNION does not. Using DB Browser for SQLite, I tested the query on the same data, and it returns all the ROWS, so I am a bit confused. Any tips welcome, or if I should escalate this to a bug report, I'd be happy to provide any details I can, etc.

[marchingband]

I have also run the application in DEBUG mode, and did not receive any DEBUG level logs that would indicate an error. The UNION seems to just silently fail.

[siara-cc]

Not sure what could be the issue. Seems UNIONUsually most issues boil down to IO or memory. Please check if page size of the database is 512, try with lesser records, check if enough file handles can be opened in EMMC.begin().
Also changing some of entries like SMALL_STACK to 0 in config_ext.h might help.

[marchingband]

I have tested again with the changes you suggested but no change.
There are a lot of config settings without explanation, some that i am curious about are:

SQLITE_OMIT_COMPOUND_SELECT
SQLITE_OMIT_EXPLAIN
SQLITE_OMIT_LOAD_EXTENSION

Are you able to suggest any changes other than SMALL_STACK that may be the culprit?
thanks!

[marchingband]

After a lot of testing it really seems as though it only actually searches the last database in the chain of UNIONs, regardless of the size, number of available file handles, etc. Is there possibly something broken about UNION between databases in this library?

[marchingband]

i found

/*
** An SQL user-function registered to do the work of an ATTACH statement. The
** three arguments to the function come directly from an attach statement:
**
**     ATTACH DATABASE x AS y KEY z
**
**     SELECT sqlite_attach(x, y, z)
**
** If the optional "KEY z" syntax is omitted, an SQL NULL is passed as the
** third argument.
**
** If the db->init.reopenMemdb flags is set, then instead of attaching a
** new database, close the database on db->init.iDb and reopen it as an
** empty MemDB.
*/

which is confusing but seems maybe relevant?
also

/*
** Resolve an expression that was part of an ATTACH or DETACH statement. This
** is slightly different from resolving a normal SQL expression, because simple
** identifiers are treated as strings, not possible column names or aliases.
**
** i.e. if the parser sees:
**
**     ATTACH DATABASE abc AS def
**
** it treats the two expressions as literal strings 'abc' and 'def' instead of
** looking for columns of the same name.
**
** This only applies to the root node of pExpr, so the statement:
**
**     ATTACH DATABASE abc||def AS 'db2'
**
** will fail because neither abc or def can be resolved.
*/

I have ' around my file name. The ATTACH works but maybe its causing problems?

[siara-cc]

After a lot of testing it really seems as though it only actually searches the last database in the chain of UNIONs, regardless of the size, number of available file handles, etc. Is there possibly something broken about UNION between databases in this library?

And is the last database the one that is open currently as sqlite3 *db ? Should we have all the databases open for UNION to work?

[marchingband]

no, in my example the last one would be bar.db

[siara-cc]

I think you have some reason for keeping these as separate databases, but keeping them as separate tables in the same database or handle the UNION in code may help move forward.

[siara-cc]

I would prefer to keep them in same table so we don't have to worry about how many file handles can be open at a time.

[marchingband]

Yes I have potential work-arounds.
If you don't think any configuration changes will help I'll move forward with them.
Let me know if you want me to do anything for debugging.
Thanks.