Error "no such table" when creating temp tables

[joeyak]

When I create a temp table, it throws an error that the table doesn't exist. When running the commands in datagrip and the sqlite cli with parameters replaced, it does not error. As long as I don't use the temp table (joins or selects) then it doesn't error in the go code. I believe this is an issue with go-sqlite3 or some configuration that I can't figure out because when I test it with github.com/glebarez/sqlite the query executes with no errors.

Actual application query

DROP TABLE IF EXISTS deleted_watchtimes;
CREATE TEMP TABLE deleted_watchtimes (
    row_id INTEGER
);

WITH cte AS (SELECT value FROM JSON_EACH(:names))
INSERT
INTO deleted_watchtimes
SELECT w._ROWID_
FROM watch_time AS w
JOIN user u ON w.user_id = u.id
WHERE u.name IN cte
  AND w.deleted = 0;

UPDATE watch_time
SET deleted = TRUE
WHERE _ROWID_ IN deleted_watchtimes;

SELECT DISTINCT u.name
FROM watch_time w
JOIN user u ON w.user_id = u.id
WHERE w._ROWID_ IN deleted_watchtimes;

Testing sql query

DROP TABLE IF EXISTS deleted_watchtimes;
CREATE TEMP TABLE deleted_watchtimes (row_id INTEGER);

WITH cte AS (SELECT value FROM JSON_EACH(:names))
INSERT
INTO deleted_watchtimes
SELECT *
FROM cte;

SELECT * FROM deleted_watchtimes

Test code that errors

package main

import (
	"database/sql"
	"fmt"

	_ "github.com/mattn/go-sqlite3"
	// _ "github.com/glebarez/sqlite"
)

func main() {
	// test.db does not exist
	db, err := sql.Open("sqlite3", "test.db")
	// db, err := sql.Open("sqlite", "test.db")
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer db.Close()

	rows, err := db.Query(`
	DROP TABLE IF EXISTS deleted_watchtimes;
	CREATE TEMP TABLE deleted_watchtimes (row_id INTEGER);
	
	WITH cte AS (SELECT value FROM JSON_EACH(:names))
	INSERT
	INTO deleted_watchtimes
	SELECT *
	FROM cte;

	SELECT * FROM deleted_watchtimes`, sql.Named("names", `["a","b"]`))
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var res string
		err := rows.Scan(&res)
		if err != nil {
			fmt.Printf("error: %v\n", err)
			return
		}
		fmt.Println(res)
	}
}

If I use the sql query below, it doesn't error

DROP TABLE IF EXISTS deleted_watchtimes;
CREATE TEMP TABLE deleted_watchtimes (row_id INTEGER);

[rittneje]

As discussed in #933, there is currently a bug in this library where passing multiple statements to Query does not work - only the last will actually be executed. I suggest splitting these statements and using db.Begin or db.Conn.

Also, as a reminder, you should always check rows.Err() after iterating.

	c, err := db.Conn(ctx)
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer c.Close()

	if _, err := c.ExecContext(ctx, "DROP TABLE IF EXISTS temp.deleted_watchtimes"); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

	if _, err := c.ExecContext(ctx, "CREATE TEMP TABLE deleted_watchtimes (row_id INTEGER)"); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer c.ExecContext(ctx, "DROP TABLE temp.deleted_watchtimes")

	if _, err := c.ExecContext(ctx, `
		WITH cte AS (SELECT value FROM JSON_EACH(:names))
		INSERT
		INTO temp.deleted_watchtimes
		SELECT *
		FROM cte`,
		sql.Named("names", `["a","b"]`),
	); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

	rows, err := c.QueryContext(ctx, "SELECT * FROM temp.deleted_watchtimes")
	if err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}
	defer rows.Close()

	for rows.Next() {
		var res string
		err := rows.Scan(&res)
		if err != nil {
			fmt.Printf("error: %v\n", err)
			return
		}
		fmt.Println(res)
	}

	if err := rows.Err(); err != nil {
		fmt.Printf("error: %v\n", err)
		return
	}

[joeyak]

Thank you for the good response. Sadly I'm currently using https://github.com/qustavo/dotsql in place of stored procedures since I don't want sql statements scattered throughout my code. I'll keep an eye on this issue so I know when it's resolves so I can add it back in because I would rather use this package than the other, since I feel this one is faster and more stable.

Also what is the reason for the rows.Err()/row.Err() check? The docs say If this error is not nil, this error will also be returned from Scan, so I would assume that my error checking on scans would catch the same error.

[rittneje]

rows.Next will return false if it encounters an error, at which point rows.Scan is not called.

Next prepares the next result row for reading with the Scan method. It returns true on success, or false if there is no next result row or an error happened while preparing it. Err should be consulted to distinguish between the two cases.