Skip to content

"another row available" error for single row query #709

@leslie-wang

Description

@leslie-wang

Probably similiar with #681. It also show another row available. It can show up with below simple codes. You can also check source codes at https://github.com/leslie-wang/samples/blob/master/go-sqlite3/main.go.

func dbMain(args []string) int {
	db, err := sql.Open("sqlite3_tracing", ":memory:")
	if err != nil {
		fmt.Printf("Failed to open database: %#+v\n", err)
		return 1
	}
	defer db.Close()

	err = db.Ping()
	if err != nil {
		log.Panic(err)
	}

	if _, err := db.Exec(`
CREATE TABLE IF NOT EXISTS user (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 user_name TEXT NOT NULL
);
CREATE TABLE IF NOT EXISTS token(
 token TEXT NOT NULL,
 user_id INTEGER NOT NULL,
 device_id INTEGER NOT NULL
);
insert into user (user_name) values ("alice");
insert into token(token, user_id, device_id) values ("1234", 1, 1);

insert into user (user_name) values ("bob");
insert into token(token, user_id, device_id) values ("4321", 2, 2);
`); err != nil {
		log.Panic(err)
	}

	ctx, cancel := context.WithTimeout(context.Background(), time.Minute)
	defer cancel()

	tx, err := db.Begin()
	if err != nil {
		log.Panic(err)
	}
	defer tx.Rollback()

	stmt, err := tx.Prepare("select token, user_id, device_id from token as t inner join (select id from user where user_name = ?) as u on u.id = t.user_id")
	if err != nil {
		log.Printf("prepare select token got error: %s\n", err)
		log.Panic(err)
	}
	defer stmt.Close()

	var (
		tokenQuery string
		userid     int
		deviceid   int
	)
	if err := stmt.QueryRowContext(ctx, "alice").Scan(&tokenQuery, &userid, &deviceid); err != nil {
		log.Printf("query context got error: %s\n", err)
		log.Panic(err)
	}
	if err := tx.Commit(); err != nil {
		log.Panic(err)
	}
	fmt.Printf("--------- Receive: %s, %d, %d\n", tokenQuery, userid, deviceid)
	fmt.Println("--------- complete --------")

	return 1
}

Got below output.

Added trace config {0x410e2a0 15 true}: handle 0x7300080.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x7301e00 {"\nCREATE TABLE IF NOT EXISTS user (\n id INTEGER PRIMARY KEY AUTOINCREMENT,\n user_name TEXT NOT NULL\n);"} = exp.
Trace: ev 4 -AC- conn 0x7300080, stmt 0x466a130 {""}.
Trace: ev 4 -AC- conn 0x7300080, stmt 0x46689c0 {""}.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x7301e00 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x7301e00 {"CREATE TABLE IF NOT EXISTS token(\n token TEXT NOT NULL,\n user_id INTEGER NOT NULL,\n device_id INTEGER NOT NULL\n);"} = exp.
Trace: ev 4 -AC- conn 0x7300080, stmt 0x4660680 {""}.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x7301e00 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x4668e70 {"insert into user (user_name) values (\"alice\");"} = exp.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x4668e70 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x4661940 {"insert into token(token, user_id, device_id) values (\"1234\", 1, 1);"} = exp.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x4661940 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x4661490 {"insert into user (user_name) values (\"bob\");"} = exp.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x4661490 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x4660680 {"insert into token(token, user_id, device_id) values (\"4321\", 2, 2);"} = exp.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x4660680 {""}; time 0.
Trace: ev 1 -AC- conn 0x7300080, stmt 0x4660680 {"BEGIN"} = exp.
Trace: ev 2 +Tx+ conn 0x7300080, stmt 0x4660680 {""}; time 0.
Trace: ev 1 +Tx+ conn 0x7300080, stmt 0x4665630 {"select token, user_id, device_id from token as t inner join (select id from user where user_name = ?) as u on u.id = t.user_id"} expanded {"select token, user_id, device_id from token as t inner join (select id from user where user_name = 'alice') as u on u.id = t.user_id"}.
Trace: ev 4 +Tx+ conn 0x7300080, stmt 0x4665630 {""}.
Trace: ev 2 +Tx+ conn 0x7300080, stmt 0x4665630 {""}; time 0; DB error: sqlite3.Error{Code:100, ExtendedCode:100, err:"another row available"}
Trace: ev 1 +Tx+ conn 0x7300080, stmt 0x4668510 {"COMMIT"} = exp.
Trace: ev 2 -AC- conn 0x7300080, stmt 0x4668510 {""}; time 0.
--------- Receive: 1234, 1, 1
--------- complete --------
Pop handle 0x7300080: deleted trace config {0x410e2a0 15 true}.
Trace: ev 8 -AC- conn 0x7300080, stmt 0x0 {""}.

I'm mostly concerned about the line which should not be an error.

Trace: ev 2 +Tx+ conn 0x7300080, stmt 0x4665630 {""}; time 0; DB error: sqlite3.Error{Code:100, ExtendedCode:100, err:"another row available"}

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions