chaisql / chai

Modern embedded SQL database
MIT License
1.56k stars 95 forks source link

Updating a row breaks its indexing, even if the column being indexed wasn't modified #345

Closed JustinJudd closed 3 years ago

JustinJudd commented 3 years ago

What version of Genji are you using?

$ genji version
Genji v0.10.0
Genji CLI v0.10.0

Does this issue reproduce with the latest release?

Yes

What did you do?

package main

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

    "github.com/genjidb/genji/sql/driver"
)

type User struct {
    Name    string
    Reviews []*Review
}

type Review struct {
    Item   string
    Review string
    Rating int
}

func getFoo(db *sql.DB) {
    rows, err := db.Query("SELECT * FROM users WHERE name = ?", "Foo")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var u User
        err = rows.Scan(driver.Scanner(&u))
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(u)
        for _, review := range u.Reviews {
            fmt.Printf("\t%+v\n", review)
        }
    }

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }

}

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

    _, err = db.Exec("CREATE TABLE users")
    if err != nil {
        log.Fatal(err)
    }
    _, err = db.Exec("CREATE UNIQUE INDEX idx_users_name ON users(name)")
    if err != nil {
        log.Fatal(err)
    }

    u := User{
        Name: "Foo",
        Reviews: []*Review{
            &Review{"Harry Potter", "Great Book", 9},
        },
    }
    _, err = db.Exec("INSERT INTO users VALUES ?", &u)
    if err != nil {
        log.Fatal(err)
    }

    rows, err := db.Query("SELECT * FROM users")
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    for rows.Next() {
        var u User
        err = rows.Scan(driver.Scanner(&u))
        if err != nil {
            log.Fatal(err)
        }
        fmt.Println(u)
    }

    err = rows.Err()
    if err != nil {
        log.Fatal(err)
    }

    getFoo(db)

    review := Review{"Lord of the Rings", "Amazing", 10}

    _, err = db.Exec("UPDATE users SET reviews = ? WHERE name = ?", []*Review{&review}, "Foo")
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Getting row")
    getFoo(db)

    db.Exec("REINDEX")
    fmt.Println("Getting row after reindexing")
    getFoo(db)

}

What did you expect to see?

Expected that the row would be able to be selected by it's name

What did you see instead?

Row was not found when selecting by name, since it was an indexed field, and the row had been updated

What Go version and environment are you using?

$ go version
go version go1.15.7 linux/amd64
go env Output
$ go env
GO111MODULE=""
GOARCH="amd64"
GOBIN=""
GOCACHE="/home/justin/.cache/go-build"
GOENV="/home/justin/.config/go/env"
GOEXE=""
GOFLAGS=""
GOHOSTARCH="amd64"
GOHOSTOS="linux"
GOINSECURE=""
GOMODCACHE="/home/justin/go/pkg/mod"
GONOPROXY=""
GONOSUMDB=""
GOOS="linux"
GOPATH="/home/justin/go"
GOPRIVATE=""
GOPROXY="https://proxy.golang.org,direct"
GOROOT="/usr/local/go"
GOSUMDB="sum.golang.org"
GOTMPDIR=""
GOTOOLDIR="/usr/local/go/pkg/tool/linux_amd64"
GCCGO="gccgo"
AR="ar"
CC="gcc"
CXX="g++"
CGO_ENABLED="1"
GOMOD=""
CGO_CFLAGS="-g -O2"
CGO_CPPFLAGS=""
CGO_CXXFLAGS="-g -O2"
CGO_FFLAGS="-g -O2"
CGO_LDFLAGS="-g -O2"
PKG_CONFIG="pkg-config"
GOGCCFLAGS="-fPIC -m64 -pthread -fmessage-length=0 -fdebug-prefix-map=/tmp/go-build027475063=/tmp/go-build -gno-record-gcc-switches"
asdine commented 3 years ago

Thanks @JustinJudd ! It was fixed by #368