Can't reuse queries.

[andradei]

If you're having a generation problem please answer these questions before submitting your issue. Thanks!

What version of SQLBoiler are you using (sqlboiler --version)?

SQLBoiler v3.6.1

What is your database and version (eg. Postgresql 10)

Postgresql 11.5

If this happened at runtime what code produced the issue? (if not applicable leave blank)

myQuery := models.Things(qm.Where("name=?", someName))

// Use myQuery once, it works.
exists, err := myQuery.Exists(
    context.Background(), db)
    if err != nil {
        return fmt.Errorf("error looking for thing: %w", err)
    }

// Use myQuery again, doesn't work.
thing, err := myQuery.One(context.Background(), db)
if err != nil {
    return fmt.Errorf("error getting existing Thing: %w", err)
}

Further information. What did you do, what did you expect?

I wanted to keep the code DRY by having a myQuery variable that I could reuse. But apparently that doesn't work. The code runs without a problem, but thing turns out to be the zero-valued models.Thing.

Is it an implementation idiosyncrasy of database/sql or of sqlboiler?

I know that not reusing myQuery fixes the problem, so it might not be a problem at all. But this was a little tricky to figure out at first. Seems like myQuery is consumed when it is run by the first Finisher, in this case, Exists().

[aarondl]

Queries in sqlboiler can only be re-used by substituting arguments, and even then only with a special call: https://github.com/volatiletech/sqlboiler/blob/master/queries/query.go#L203-L209

The reason for this is caching. When you run a query for the first time the sql is generated and saved into the query object:

https://github.com/volatiletech/sqlboiler/blob/master/queries/query_builders.go#L26-L27 https://github.com/volatiletech/sqlboiler/blob/master/queries/query_builders.go#L39-L41

It may be possible to invalidate the cache on finalizers. It does seem like reasonable thing to do doesn't it?

[andradei]

Thanks for linking the lines of code that do this. Now I understand the process whereas before (when I opened the issue) I was just guessing.

It may be possible to invalidate the cache on finalizers. It does seem like reasonable thing to do doesn't it?

That would be good. So calling a finalizer again without setting a new value could generate an error.