Exec method does not support queries that return results

[nalgeon]

Some sqlite pragmas return a value. For example, mmap_size (running in sqlite shell):

sqlite> pragma mmap_size=1024;
1024

The libsql driver's Exec method fails with an "Execute returned rows" for such pragmas. In fact, it fails for any query that returns a value (such as select 42).

I would argue that this is not an expected behavior. It's perfectly normal for an Exec query to return a value. Other drivers (such as mattn/go-sqlite3 and modernc.org/sqlite) allow this.

Code to reproduce:

package main

import (
	"database/sql"
	"fmt"
	"log"

	_ "github.com/tursodatabase/go-libsql"
)

func main() {
	db, err := sql.Open("libsql", "file:data.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	// You can replace the query with `select 42` to get the same error.
	res, err := db.Exec(`pragma mmap_size=1024`)
	fmt.Println("res", res)
	fmt.Println("err", err)
}

Result:

res <nil>
err failed to execute query pragma mmap_size=1024
error code = 2: Error executing statement: Execute returned rows

[haaawk]

Have you consider using Query?

[nalgeon]

Not looking for a workaround; I reported the issue so that the driver behaves as expected.

[haaawk]

The driver behavies as expected.
Execute returns number of affected rows and last affected rowid
Query returns rows.
All our drivers behave this way.
So does rusqlite -> https://docs.rs/rusqlite/latest/rusqlite/struct.Connection.html#method.execute,
go-sqlite3 and database/sql -> https://pkg.go.dev/database/sql/driver#Result

[nalgeon]

The libSQL driver returns an error. It's described in the issue. The error is not expected in this situation, and other Go SQLite drivers handle the described situation just fine.

[haaawk]

Ok. I'm still not sure what is a desired behaviour here but reopening the issue for now.

[nalgeon]

I'd say the expected behavior is to return a non-nil sql.Result and not return an error (like other drivers do):

package main

import (
	"database/sql"
	"fmt"
	"log"

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

func main() {
	db, err := sql.Open("sqlite3", "file:data.db")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	res, err := db.Exec(`pragma mmap_size=1024`)
	fmt.Println("res", res)
	fmt.Println("err", err)
}

res {0x14000146000 0x1400011c040}
err <nil>

[haaawk]

This is more complicated in case of turso which can have not only local backend but also a remote one. Ideally what you're suggesting would be best but I have to think about all available backends before being sure.

[nalgeon]

I see, thank you for clarifying that!

[adlion]

This is still an issue.
Does anyone know how to set busy_timeout.
Using Exec it throws an error as described in the issue and using Query or QueryRow does not set it

[adlion]

Ok

This is a little bit off

So Exec does return an error like:
error code = 2: Error executing statement: Execute returned rows

but you can ignore the error and than query the pragma and it is set

Here is my code

	pragmaRes, err := db.Exec("PRAGMA busy_timeout=10000;")
	if err != nil {
		fmt.Println(err)
	}

	fmt.Println(pragmaRes)

	res := db.QueryRow("PRAGMA busy_timeout;")
	err = res.Err()
	if err != nil {
		return nil, err
	}

	var timeout string
	err = res.Scan(&timeout)
	fmt.Println(err)
	if err != nil {
		return nil, fmt.Errorf("failed to scan busy_timeout result: %w", err)
	}
	fmt.Println(res)
	fmt.Println(timeout)