"where clause is ambiguous" occurs in "xorm.io/xorm", not in "github.com/go-xorm/xorm" #1568

New Issue

sylba2050 · 2020-03-03T05:12:09Z

sylba2050 commented

2020-03-03 05:12:09 +00:00

type Users struct {
    ID        int64     `xorm:"id autoincr pk" json:"id"`
    Name      string    `xorm:"name not null" json:"name"`
    CreatedAt time.Time `xorm:"created not null" json:"created_at"`
    UpdatedAt time.Time `xorm:"updated not null" json:"updated_at"`
    DeletedAt time.Time `xorm:"deleted" json:"deleted_at"`
}

type Books struct {
    ID            int64     `xorm:"id autoincr pk" json:"id"`
    Name          string    `xorm:"name not null" json:"name"`
    UserID        int64     `xorm:"user_id not null" json:"user_id"`
    CreatedAt     time.Time `xorm:"created not null" json:"created_at"`
    UpdatedAt     time.Time `xorm:"updated not null" json:"updated_at"`
    DeletedAt     time.Time `xorm:"deleted" json:"deleted_at"`
}

type BooksExtend struct {
    Books `xorm:"extends"`
    Users Users  `xorm:"extends" json:"users"`
}

var books []BooksExtend
err := engine.Table("books").Select("books.*, users.*").
    Join("INNER", "users", "books.user_id = users.id").
    Where("books.name LIKE ?", fmt.Sprintf("%%%s", request.Keyword)).Limit(request.Limit, request.Offset).
    Find(&books)

when importing "xorm.io/xorm", err is not nil (value is "Error 1052: Column 'deleted_at' in where clause is ambiguous").
The result of engine.ShowSQL (true) is below

SELECT books.*, users.* FROM `books` INNER JOIN users ON books.group_id = users.id WHERE (books.name LIKE ?) AND (deleted_at=? OR deleted_at IS NULL) LIMIT 1 []interface {}{"%", "0001-01-01 00:00:00"}

but, when importing "github.com/go-xorm/xorm", The correct data is returned.
The result of engine.ShowSQL (true) is below

SELECT books.*, users.* FROM `books` INNER JOIN users ON books.user_id = users.id WHERE (books.name LIKE ?) AND (`books`.`deleted_at` IS NULL OR `books`.`deleted_at`=?) LIMIT 1 []interface {}{"%", "0001-01-01 00:00:00"}

Is there anything I need to fix?

``` type Users struct { ID int64 `xorm:"id autoincr pk" json:"id"` Name string `xorm:"name not null" json:"name"` CreatedAt time.Time `xorm:"created not null" json:"created_at"` UpdatedAt time.Time `xorm:"updated not null" json:"updated_at"` DeletedAt time.Time `xorm:"deleted" json:"deleted_at"` } type Books struct { ID int64 `xorm:"id autoincr pk" json:"id"` Name string `xorm:"name not null" json:"name"` UserID int64 `xorm:"user_id not null" json:"user_id"` CreatedAt time.Time `xorm:"created not null" json:"created_at"` UpdatedAt time.Time `xorm:"updated not null" json:"updated_at"` DeletedAt time.Time `xorm:"deleted" json:"deleted_at"` } type BooksExtend struct { Books `xorm:"extends"` Users Users `xorm:"extends" json:"users"` } ``` ``` var books []BooksExtend err := engine.Table("books").Select("books.*, users.*"). Join("INNER", "users", "books.user_id = users.id"). Where("books.name LIKE ?", fmt.Sprintf("%%%s", request.Keyword)).Limit(request.Limit, request.Offset). Find(&books) ``` when importing "xorm.io/xorm", err is not nil (value is "Error 1052: Column 'deleted_at' in where clause is ambiguous"). The result of engine.ShowSQL (true) is below ``` SELECT books.*, users.* FROM `books` INNER JOIN users ON books.group_id = users.id WHERE (books.name LIKE ?) AND (deleted_at=? OR deleted_at IS NULL) LIMIT 1 []interface {}{"%", "0001-01-01 00:00:00"} ``` but, when importing "github.com/go-xorm/xorm", The correct data is returned. The result of engine.ShowSQL (true) is below ``` SELECT books.*, users.* FROM `books` INNER JOIN users ON books.user_id = users.id WHERE (books.name LIKE ?) AND (`books`.`deleted_at` IS NULL OR `books`.`deleted_at`=?) LIMIT 1 []interface {}{"%", "0001-01-01 00:00:00"} ``` Is there anything I need to fix?

lunny added the

need

test

label 2020-03-03 06:45:11 +00:00

lunny referenced a pull request that will close this issue

2020-03-03 12:21:34 +00:00

fix bug on deleted with join #1570

lunny closed this issue

2020-03-03 12:45:31 +00:00

Sign in to join this conversation.