There's no function in SQLite to attach a table, so I'm not sure what you mean by the above.

I suspect that either you're using your library's 'execute' function incorrectly, or your program is stomping on some memory that SQLite is meant to be using. If you want us to help you'll have to show us some of your code, and tell us which library you're using to execute SQLite commands.

typo.... attach/detach database.

Simon's advice to show your actual code rather than pseudo-code still applies. In what you have shown, there are no table names used and there is no clue as to where the unexpected result occurs. Given that, my answer would be: It must be something you wrote but have not yet revealed.

I was able to reproduce it exactly as described.

First the DB bootstrap:

rm db1
sqlite3 db1 "create table t1(a,b,c); insert into t1 (a,b,c) values (1,2,3); select * from t1"
rm db2
sqlite3 db2 "create table t2(a,b,c); insert into t2 (a,b,c) values (8,9,10); select * from t2"

then the code

package main

import (
        "database/sql"
        "fmt"
        "log"
        "strings"
        _ "github.com/mattn/go-sqlite3"
)

func read(db *sql.DB, f bool) {
        rows, err := db.Query("SELECT t1.a FROM t1 left join db2.t2")
        if err != nil {
                log.Fatal(err)
        }
        if ! f {
        names := make([]string, 0)
        for rows.Next() {
                var name string
                if err := rows.Scan(&name); err != nil {
                        log.Fatal(err)
                }
                names = append(names, name)
                read(db, true)
        }
        log.Printf("%s are %d years old", strings.Join(names, ", "), 12)
        }
        defer rows.Close()
}

func attach(db *sql.DB) {
        result, err := db.Exec("attach 'db2' as db2")
        fmt.Printf("attach(): result=%v, err=%v\n", result, err);
}


func main() {
        db, err := sql.Open("sqlite3", "db1")
        defer db.Close()
        attach(db)
        read(db, false)
        fmt.Printf("open(): %v\n", err);
}

this is what the console showed when I ran it:

$ go run -v example.go
command-line-arguments
attach(): result={0xc0000b2000 0xc000014120}, err=<nil>
2022/06/09 11:08:04 no such table: db2.t2
exit status 1

the flow

open the db
attach the other db
select query into rows
read a row
select query a second time -- ERROR no such table

but if I attach db2 again just before the second query then it works.

Cannot reproduce using the CLI from SQLite version 3.37.0 2021-12-09 01:34:53.

simon@183 ~ % sqlite3 db1 "create table t1(a,b,c); insert into t1 (a,b,c) values (1,2,3); select * from t1"
1|2|3
simon@183 ~ % sqlite3 db2 "create table t2(a,b,c); insert into t2 (a,b,c) values (8,9,10); select * from t2"
8|9|10
simon@183 ~ % sqlite3 db1
SQLite version 3.37.0 2021-12-09 01:34:53
Enter ".help" for usage hints.
sqlite> attach 'db2' as db2;
sqlite> SELECT t1.a FROM t1 left join db2.t2;
1

Can you try the above on your own computer ? Do everything through the CLI instead of using your own code ?

The thing that jumps out at me, though it seems to be the way it should be done, is your use of quotes in the ATTACH command. Can you instead try

ATTACH db2 as db2;

? Perhaps the apostrophies are being messed up by GO.

Your example and mine are not the same thing. In fact it's not possible to reproduce in the CLI. It's only possible in other languages.

Doing the same procedure using a different shim (language) indicates that the procedure works corrctly and that it is the GO shim that is working contrary to expectations.

Not having examined the shim I cannot say for sure what it is doing, but it appears on retrospective examination to be using a different underlying connection (by magically) other than the one expected to be used.

This is a reasonable conclusion since it has already been observed that the go shim behaves in this manner of arbitrarily trying to "create" connections for operations (and doing a piss-poor job of it). The CLI does the same thing with the .expert command resulting in attachments and non-auto extensions to not be accessible in the sub-environment.

This is not to say that the GO shim behaviour is incorrect, there is merely an expectation mismatch between how the person USING the go shim expects it to work, and how the designer of the go shim designed it to work.

Other shims (such as pysqlite2/sqlite3 for Python) exhibit the same behaviour -- the user of the shim expects it to work like native SQLite3, however, this was not the intent of the shim writer and when the "expectation of the author" and the "expectation of the user" collide the result will be an undeniable "working as designed", not "working as expected by the user".

not sure how you came to this conclusion. There is but one complete example. The other person did not reproduce my code in the CLI as it is not possible, There is no way to query a DB and then process the results one row at a time in the CLI.

I came to that conclusion because the only possible explanation is that the database connection on which the query is executed is not the same connection the second time as it was the first time.

When reproducing in any language where there is no mismatch between behavioural expectations and design behaviour, the issue does not exist.

import apsw
import os

if os.path.exists('db1.db'):
    os.unlink('db1.db')
if os.path.exists('db2.db'):
    os.unlink('db2.db')


db = apsw.Connection('db1.db')
for row in db.execute("create table t1(a,b,c); insert into t1 (a,b,c) values (1,2,3); select * from t1"):
    print(row)
db.close()
db = apsw.Connection('db2.db')
for row in db.execute("create table t2(a,b,c); insert into t2 (a,b,c) values (8,9,10); select * from t2"):
    print(row)
db.close()

db = apsw.Connection('db1.db')
db.execute("attach database 'db2.db' as db2")

for row in db.execute('select t1.a from t1 left join t2'):
    print('outer', tuple(zip(row._colorig, row._values)))
    for row in db.execute('select t2.b from t1 left join t2'):
        print('inner', tuple(zip(row._colorig, row._values)))

db.close()

produces

>test
Row(a=1, b=2, c=3)
Row(a=8, b=9, c=10)
outer (('main.t1.a', 1),)
inner (('db2.t2.b', 9),)

This example is slightly better at demonstration. Note that it uses a customized version of APSW that implements a Row object and adds execute methods onto the connection.

If you use a "second connection" then the following happens:

import apsw
import os

if os.path.exists('db1.db'):
    os.unlink('db1.db')
if os.path.exists('db2.db'):
    os.unlink('db2.db')


db = apsw.Connection('db1.db')
for row in db.execute("create table t1(a,b,c); insert into t1 (a,b,c) values (1,2,3); select * from t1"):
    print(row)
db.close()
db = apsw.Connection('db2.db')
for row in db.execute("create table t2(a,b,c); insert into t2 (a,b,c) values (8,9,10); select * from t2"):
    print(row)
db.close()

db = apsw.Connection('db1.db')
db.execute("attach database 'db2.db' as db2")

for row in db.execute('select t1.a from t1 left join t2'):
    print('outer', tuple(zip(row._colorig, row._values)))
    db2 = apsw.Connection('db1.db')
    for row in db2.execute('select t2.b from t1 left join t2'):
        print('inner', tuple(zip(row._colorig, row._values)))

db.close()

with the result

>test
Row(a=1, b=2, c=3)
Row(a=8, b=9, c=10)
outer (('main.t1.a', 1),)
Traceback (most recent call last):
  File "D:\work\go\test.py", line 26, in <module>
    for row in db2.execute('select t2.b from t1 left join t2'):
  File "C:\Python\Lib\site-packages\Local\newapsw.py", line 328, in execute
    return self.cursor().execute(sql, bindings)
  File "C:\Python\Lib\site-packages\Local\newapsw.py", line 826, in execute
    self.__cursor.execute(sql, newbindings)
  File "src/cursor.c", line 1000, in APSWCursor_execute.sqlite3_prepare
apsw.SQLError: SQLError: no such table: t2

Therefore, the only possible conclusion is that the go shim is attempting to "duplicate" (albiet unsuccessfully) the connection for the second query -- probably on some theory that this will enforce "isolation" between queries.

Whereupon, if you apply your fix of re-executing the attach to the second connection instance:

import apsw
import os

if os.path.exists('db1.db'):
    os.unlink('db1.db')
if os.path.exists('db2.db'):
    os.unlink('db2.db')


db = apsw.Connection('db1.db')
for row in db.execute("create table t1(a,b,c); insert into t1 (a,b,c) values (1,2,3); select * from t1"):
    print(row)
db.close()
db = apsw.Connection('db2.db')
for row in db.execute("create table t2(a,b,c); insert into t2 (a,b,c) values (8,9,10); select * from t2"):
    print(row)
db.close()

db = apsw.Connection('db1.db')
db.execute("attach database 'db2.db' as db2")

for row in db.execute('select t1.a from t1 left join t2'):
    print('outer', tuple(zip(row._colorig, row._values)))
    db2 = apsw.Connection('db1.db')
    db2.execute("attach database 'db2.db' as db2")
    for row in db2.execute('select t2.b from t1 left join t2'):
        print('inner', tuple(zip(row._colorig, row._values)))

db.close()

produces

>test
Row(a=1, b=2, c=3)
Row(a=8, b=9, c=10)
outer (('main.t1.a', 1),)
inner (('db2.t2.b', 9),)

Which exactly duplicates your observations.

Retrospectively the conclusion that the mismatch occurs in the go shim is confirmed. Examination of the shim code will likely reveal that this behaviour is deliberate and by design. Or perhaps it is a bug that the go shim designer should fix.

Retrospectively the conclusion that the mismatch occurs in the go shim is confirmed. Examination of the shim code will likely reveal that this behaviour is deliberate and by design. Or perhaps it is a bug that the go shim designer should fix.

I received a reply from the go shim developers. Their connection is implemented as a connection pool. Therefore the "attach" commands and results are not there on subsequent queries. They do have a connectionhook callback that can be used to re-execute whatever customization needed.

Your example suggests that the connection pool might actually be a waste since sqlite seems to permit the two open queries.

THANK YOU !!!

the connection pool might actually be a waste

Effective connection pooling is quite tricky to get right with SQLite because the connections, once used properly, shouldn't cross hardware threads (very different to other databases where connections are wrappers around socket handles rather than around file handles). I've got one (in Java) that works well, but that works by being rather closely integrated with the thread pool strategy I'm using. I'm not going to point to the code because I'm not at all convinced that others can usefully adopt some of the conditions to the approach.

The cost of opening a connection (and making any needed functions) is usually pretty small. Unless you do an integrity check, but that's probably at most an application-launch-time task.