sqlite: SELECT(Table.AllColumns).FROM(Table) returns no result (table has records)

[Gys]

Describe the bug My table has one record. Doing a SELECT(Table.AllColumns).FROM(Table) does not return the one record.

Environment

• OS: macosx
• Database: sqlite
  • Database driver: github.com/mattn/go-sqlite3
• Jet version: v2.9.0

Code snippet

See bug_test.go below with other required files. Expected behavior: all tests in TestBug() should pass, meaning the one record should be found and the found ID should be the same. sqlite3 bug.sqlite 'select * from bug;' shows the table contents.

FILE: bug_test.go

package bug

import (
    "database/sql"
    "testing"

    . "github.com/go-jet/jet/v2/sqlite"
    _ "github.com/mattn/go-sqlite3"
)

/*
    create bug.sqlite:
    cat create_bug_table.sql | sqlite3 bug.sqlite
*/

func TestBug(t *testing.T) {
    db, err := sql.Open("sqlite3", "bug.sqlite")
    if err != nil {
        t.Fatal(err)
    }
    defer db.Close()

    _, err = TableBug.DELETE().WHERE(TableBug.ID.GT(Int(-1))).Exec(db)
    if err != nil {
        t.Fatal(err)
    }

    insertQ := TableBug.INSERT(TableBug.AllColumns).MODEL(ModelBug{Title: "One"})
    _, err = insertQ.Exec(db)
    if err != nil {
        t.Fatal(err)
    }

    selectQ1 := SELECT(TableBug.AllColumns).FROM(TableBug)
    var dest1 ModelBug
    err = selectQ1.Query(db, &dest1)
    if err != nil {
        t.Fatal(err)
    }
    if dest1.Title != "One" {
        t.Fatal("dest1.Title != 'One'")
    }

    selectQ2 := SELECT(TableBug.AllColumns).FROM(TableBug)
    var dest2 ModelBug
    err = selectQ2.Query(db, &dest2)
    if err != nil {
        t.Fatal(err)
    }

    if *dest1.ID == 0 || *dest1.ID != *dest2.ID {
        t.Fatalf("Expected ID %d, got %d", *dest1.ID, *dest2.ID)
    }
}

FILE: create_bug_table.go

create table if not exists bug (
    id integer primary key autoincrement,
    title text not null
);

FILE: model_bug.go

package bug

type ModelBug struct {
    ID    *int32 `sql:"primary_key"`
    Title string
}

FILE: table_bug.go

package bug

import (
    "github.com/go-jet/jet/v2/sqlite"
)

var TableBug = newBugTable("", "bug", "")

type bugTable struct {
    sqlite.Table

    //Columns
    ID    sqlite.ColumnInteger
    Title sqlite.ColumnString

    AllColumns     sqlite.ColumnList
    MutableColumns sqlite.ColumnList
}

type BugTable struct {
    bugTable

    EXCLUDED bugTable
}

// AS creates new BugTable with assigned alias
func (a BugTable) AS(alias string) *BugTable {
    return newBugTable(a.SchemaName(), a.TableName(), alias)
}

// Schema creates new BugTable with assigned schema name
func (a BugTable) FromSchema(schemaName string) *BugTable {
    return newBugTable(schemaName, a.TableName(), a.Alias())
}

// WithPrefix creates new BugTable with assigned table prefix
func (a BugTable) WithPrefix(prefix string) *BugTable {
    return newBugTable(a.SchemaName(), prefix+a.TableName(), a.TableName())
}

// WithSuffix creates new BugTable with assigned table suffix
func (a BugTable) WithSuffix(suffix string) *BugTable {
    return newBugTable(a.SchemaName(), a.TableName()+suffix, a.TableName())
}

func newBugTable(schemaName, tableName, alias string) *BugTable {
    return &BugTable{
        bugTable: newBugTableImpl(schemaName, tableName, alias),
        EXCLUDED: newBugTableImpl("", "excluded", ""),
    }
}

func newBugTableImpl(schemaName, tableName, alias string) bugTable {
    var (
        IDColumn       = sqlite.IntegerColumn("id")
        TitleColumn    = sqlite.StringColumn("title")
        allColumns     = sqlite.ColumnList{IDColumn, TitleColumn}
        mutableColumns = sqlite.ColumnList{TitleColumn}
    )

    return bugTable{
        Table: sqlite.NewTable(schemaName, tableName, alias, allColumns...),

        //Columns
        ID:    IDColumn,
        Title: TitleColumn,

        AllColumns:     allColumns,
        MutableColumns: mutableColumns,
    }
}

[go-jet]

Hi @Gys,

Your query does not match the destination type. Check the wiki.

There are two ways you can fix your code, you can change destination type to TableBug or you use autogenerated type model.TableBug

[Gys]

Hi @go-jet! Thanks for the quick response.

From the wiki I understand the autogenerated type can be a destination type. You say the same in your comment?

In my example code the model_bug.go is a renamed copy of the autogenerated file model/bug.go. I only changed the filename (model/bug.go -> model_bug.go), package name (model -> bug) and type name to prevent name conflicts (type Bug -> type ModelBug).

I made the same adaption for table/bug.go -> table_bug.go

So I am using the autogenerated model.TableBug (actually, the generated code is model.Bug) by using ModelBug. But maybe I made some (other) mistake?

[Gys]

I thought copying model/bug.go to model_bug.go would not matter. But I now find that referring to model.Bug instead of ModelBug indeed makes a difference. Strange. Anyways, the test works in that case.

[go-jet]

Different package doesn't make any difference, only struct type name and struct field name matters. The easiest way for you to spot a mistake, if you're using custom destination types, is by printing the statement. For instance:

selectQ1 := SELECT(TableBug.AllColumns).FROM(TableBug)
fmt.Println(selectQ1.Sql())

Will give print query:

SELECT table_bug.id as "table_bug.id" -- aliasing is specifing destination
     table_bug.title as "table_bug.title"
FROM table_bug;

Now you know that destination has to be TableBug struct with two fields ID and title.

[Gys]

Thank you for explaining very clearly!

[hanxuanliang]

Different package doesn't make any difference, only struct type name and struct field name matters. The easiest way for you to spot a mistake, if you're using custom destination types, is by printing the statement. For instance:

selectQ1 := SELECT(TableBug.AllColumns).FROM(TableBug)
fmt.Println(selectQ1.Sql())

Will give print query:

SELECT table_bug.id as "table_bug.id" -- aliasing is specifing destination
     table_bug.title as "table_bug.title"
FROM table_bug;

Now you know that destination has to be TableBug struct with two fields ID and title.

Regarding this, I have a question: Is there a global configuration that can directly remove the table name prefix from all fields? For example:

SELECT table_bug.id as "id" -- aliasing is specifing destination
     table_bug.title as "title"
FROM table_bug;

[go-jet]

No there is no such config. Although on master branch there is As method on ColumnList type, but it is still per query:

SELECT(
    TableBug.AllColumns.As(""), // this will remove table name from alias.
).FROM(
    TableBug,
)

[hanxuanliang]

No there is no such config. Although on master branch there is As method on ColumnList type, but it is still per query:

SELECT(
    TableBug.AllColumns.As(""), // this will remove table name from alias.
).FROM(
    TableBug,
)

From what I see now, I think it's enough. However, I still hope to be able to specify when generating. Moreover, I initially thought that marking alias:"xxx" on the struct could solve this issue. My question is, what problem does alias:"xxx" solve?

[go-jet]

It is mostly used for custom model types to match fields with projection. You can find more info here: https://github.com/go-jet/jet/wiki/Query-Result-Mapping-(QRM).
I'm not sure what are you trying to achieve. Are you using jet as struct mapper? Are you using custom model types?