Is there a way to see the result of applying arguments to a query in sqlite?

Question

I'm using sqlite3 in Go and for debugging purposes would like to see the result of applying arguments to the query with the replacements, i.e., with ? replaced by the actual argument. Is this possible?

Currently, I have code like the following and the FTS5 query printed looks alright:

SELECT ownerid FROM __ftsindex WHERE ownertable=? AND 
__ftsindex MATCH ? AND ( __ftsindex MATCH ? ) LIMIT ?;

It does not return an error, but it also doesn't find what it should find, and I'm at a loss how to further debug this:

for i := range trails {
    if i == 0 {
        s += `( __ftsindex MATCH ?`
    } else {
        s += ` OR __ftsindex MATCH ?`
    }
}
s += " )"
query = fmt.Sprintf(`SELECT ownerid FROM __ftsindex WHERE ownertable=? AND __ftsindex MATCH ? AND %s LIMIT ?;`, s)
fmt.Println(query)
args := make([]interface{}, len(trails)+3)
args[0] = cat.Table()
args[1] = searchTerm
for i := range trails {
    args[i+2] = trails[i]
}
args[len(args)-1] = limit
rows, err := model.DB.Base().Query(query, args...)

Answer 1

If you are not getting the results from an SQL query that you expect, there may be rogue bytes in your input arguments.

I would suggest logging each individual argument using %+q:

for i, arg := range sqlArgs {
    if _, ok := arg.(string); ok {
        log.Printf("arg %d) %+q\n", i+1, arg) // catches any 'invisible' bytes or visually similar characters
    } else {
        log.Printf("arg %d) `%v`\n", i+1, arg)
    }
}

For example: https://play.golang.org/p/mDsnIjMAVwP

P.S. See this excellent go blog post on how to render obscure string encodings and byte-slices.