Query with []int64 slice for Where int IN (?)

[cryptix]

trafficstars

Hi,

since we have SELECT ... From TableA WHERE id() in (SELECT idA from TableB Where ...), can we find a way to make int IN (...) take a parameter for ...?

I think we could get away with restricting the use slice Exec arguments to just these places.

In code: I'd like to use just the last example, which currently throws: sql: converting Exec argument #0's type: unsupported type []int64, a slice

package main

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

    _ "github.com/cznic/ql/driver"
)

type myStuff struct {
    Id int64
    A  int
    B  string
}

func main() {
    mdb, err := sql.Open("ql-mem", "mem.db")
    check(err)

    tx, err := mdb.Begin()
    check(err)

    _, err = tx.Exec(`CREATE TABLE Stuff(A int, B string);`)
    check(err)

    stmt, err := tx.Prepare("INSERT INTO Stuff (A,B) Values($1,$2)")
    check(err)
    stuff := []myStuff{
        {0, 1, "Some"},
        {0, 9, "xxx"},
        {0, 2, "Stuff"},
        {0, 10, "xxx"},
        {0, 3, "That"},
        {0, 12, "xxx"},
        {0, 23, "please"},
        {0, 42, "dont"},
        {0, 666, "uuaaaarggg"},
    }
    for _, v := range stuff {
        _, err = stmt.Exec(v.A, v.B)
        check(err)
    }
    check(stmt.Close())
    check(tx.Commit())

    fmt.Println("all orderd")
    queryAndPrint(mdb.Query(`SELECT id() as Id, A, B FROM Stuff ORDER BY A`))

    fmt.Println("filtered (static)")
    queryAndPrint(mdb.Query(`SELECT id() as Id, A, B FROM Stuff WHERE A IN (1,2,3,4)`))

    fmt.Println("filtered (by hand)")
    ids := []int64{9, 10, 12}
    queryAndPrint(mdb.Query(`SELECT id() as Id, A, B FROM Stuff WHERE A IN ($1,$2,$3)`, ids[0], ids[1], ids[2]))

    fmt.Println("filtered (semi-nice)")
    ids = []int64{23, 42, 666}
// please
    idStrs := make([]string, len(ids))
    for i, v := range ids {
// dont do
        idStrs[i] = strconv.FormatInt(v, 10)
    }
// THIS
    qry := "(" + strings.Join(idStrs, ",") + ")"
// EVER
    queryAndPrint(mdb.Query(`SELECT id() as Id, A, B FROM Stuff WHERE A IN ` + qry))

    fmt.Println("filtered (i'd like..)")
    ids = []int64{1, 2, 3, 4}
    queryAndPrint(mdb.Query(`SELECT id() as Id, A, B FROM Stuff WHERE id() IN ($1...)`, ids))
}

func queryAndPrint(rows *sql.Rows, err error) {
    check(err)
    for rows.Next() {
        var s myStuff
        err = rows.Scan(&s.Id, &s.A, &s.B)
        check(err)
        fmt.Printf("%+v\n", s)
    }
    check(rows.Err())
    check(rows.Close())
}

func check(err error) {
    if err != nil {
        log.Fatal(err)
    }
}

[cznic]

SELECT ... From TableA with id() in (SELECT idA from TableB Where ...)

I assume with is a typo and it should be WHERE instead. Then please note that the above is equivalent to

SELECT ... FROM TableA, TableB WHERE id(TableA) == TableB.idA

... which perhaps solves your task.

Other than that, implementing the proposal seems feasible to me through the native API. If it is at all possible to support the same thing while using the database/sql API is not clear to me ATM, I'll have to investigate. Please feel free to teach me if you find free time to look into it before I do ;-)

[cryptix]

I wasn't sure how the sub-queries are handled internally, I assumed it would iterate the inner query and than pass the resulting slice to the outer IN predicate. From your answer it seems like there is some kind of rewriting going on?

My problem right now is, that ql refuses to take any sort of slice as an Exec() parameter and thus, I don't see a way to do this without generating a query string which violates query-data separation. (I would be fine with it as a stop-gap, though)

(ps: Yup, with was a typo, sorry - fixed)

[cznic]

Correction - What I really wanted to write is, for example:

SELECT x, y, z FROM Table A WHERE id() IN (
        SELECT idA FROM TableB WHERE expression
)

equals

SELECT TableA.x, TableA.y, TableA.z
FROM TableA, TableB
WHERE id(TableA) == TableB.idA && expression

Currently the version with IN is more performant, namely when there exists a usable index on expression. I have ready a design which will make the cross join version perform better. It's only a matter of having a free day or two which I can dedicate to this task.

I took a brief look and what concerns passing a slice to the database/sql API: Note that the error message produced by your program is sql: converting Exec argument #0's type: unsupported type []int64, a slice. IOW, it's the sql API which rejects the slice passed as an argument to Exec. The ql (or any other) database/sql/driver implementation never sees the slice and cannot handle it - regardless of willing to do so or not. IOW, it doesn't seem feasible. If you have an idea how to solve this, I'm all ears ;-)

[cryptix]

Thanks for the clarification.

The ql (or any other) database/sql/driver implementation never sees the slice and cannot handle it.

Yup, I came up to the same conclusion while doing research on how lib/pq supports this.

In lib/pq land you can get away by implementing the Value interface so that it converts the slice to postgres internal array representation and return it as a string like this: (References Issue lib/pq#327 and PR lib/pq#353).

type int64slice []int64

func (a int64slice) Value() (driver.Value, error) {
    ints := make([]string, len(a))
    for i, v := range a {
        ints[i] = strconv.FormatInt(v, 10)
    }
    return "{" + strings.Join(ints, ",") + "}", nil
}

and then using the wrapped slice as an argument for a query like this: SELECT ... WHERE id = ANY ($1).

It feels like the database/sql package doesn't want to enforce a way on you on how to pass more complex types back and forth, which makes sense considering the differences in DB implementations. It irks me a little that you have to convert from int64 to string and back on the postgres side but that's not really our issue here.

I tried the same trick on ql but it currently can't reinterpret the string as an []int64, which is most likely not what we want anyway. Since ql is all in go land and also lives in the same memory as the application running the queries, I wonder if you could use the interface{} returned by Value() to poke through to the original slice by type assertion?

I'd like to experiment with this but I haven't found the time to look for the doorknob to ql's codebase yet. :)

[cryptix]

Hrm.. seeing the documentation on Value I'm not so sure any more about my last suggestion.

Value is a value that drivers must be able to handle. It is either nil or an instance of one of these types:

int64
float64
bool
[]byte
string   [*] everywhere except from Rows.Next.
time.Time

Is everything else really thrown away in error is it just a baseline recommendation?

[cznic]

Thank you very much for the pointers and ideas. I don't yet have any answers to your questions, though. I hope to make some progress though this weekend. (?)