Gorm: First Method doesn't work with UUID in Where

[juame]

I believe the First(out interface{}, where ...interface{}) method doesn't work as expected when ID is type of uuid.UUID. I'm using github.com/google/uuid for the UUIDs...

Example:

package main

import (
    "fmt"
    "time"

    "github.com/google/uuid"
    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/mysql"
)

var db *gorm.DB

type Dog struct {
    ID        uuid.UUID  `json:"id" gorm:"primary_key;type:varchar(36)"`
    Name      string     `json:"name" gorm:"type:varchar(32);not null;unique"`
    Age       uint       `json:"age,omitempty"`
    CreatedAt time.Time  `json:"createdAt"`
    UpdatedAt time.Time  `json:"updatedAt"`
    DeletedAt *time.Time `json:"-"`
}

// BeforeCreate Callback for creating a struct in the DB.
func (d *Dog) BeforeCreate(scope *gorm.Scope) error {
    uuid := uuid.New()
    scope.SetColumn("ID", uuid)
    fmt.Println("Creating UUID " + uuid.String() + " for " + d.Name + ".")
    return nil
}

func main() {
    db, _ = gorm.Open("mysql", "gorm:password@tcp(127.0.0.1:3307)/gorm?parseTime=true")
    defer db.Close()
    db.LogMode(true)
    db.DropTableIfExists(Dog{})
    db.AutoMigrate(Dog{})

    foo := Dog{Name: "foo", Age: 1}
    bar := Dog{Name: "bar", Age: 2}
    db.Create(&foo)
    db.Create(&bar)

    var foo1, foo2, bar1, bar2 Dog

    db.First(&foo1, foo.ID)
    db.First(&bar1, bar.ID)
    db.Where("ID = ?", foo.ID).First(&foo2)
    db.Where("ID = ?", bar.ID).First(&bar2)

    fmt.Println("Real UUID: " + foo.ID.String() + " Foo's UUID: " + foo1.ID.String())
    fmt.Println("Real UUID: " + bar.ID.String() + " Bar's UUID: " + bar1.ID.String())
    fmt.Println("Real UUID: " + foo.ID.String() + " Foo's UUID with Where: " + foo2.ID.String())
    fmt.Println("Real UUID: " + bar.ID.String() + " Bar's UUID with Where: " + bar2.ID.String())

}

Problem: The ID is not inserted into the SQL SELECT with db.First(&foo1, foo.ID). It's returning the first record it gets randomly...

[2016-11-27 11:06:14]  [1.23ms]  SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL ORDER BY `dogs`.`id` ASC LIMIT 1

(/Users/julian/go/src/github.com/juame/testproject/main.go:47) 
[2016-11-27 11:06:14]  [1.15ms]  SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL ORDER BY `dogs`.`id` ASC LIMIT 1

(/Users/julian/go/src/github.com/juame/testproject/main.go:48) 
[2016-11-27 11:06:14]  [1.53ms]  SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL AND ((ID = '1b394083-3136-4f49-a779-ab48dbc0318f')) ORDER BY `dogs`.`id` ASC LIMIT 1

(/Users/julian/go/src/github.com/juame/testproject/main.go:49) 
[2016-11-27 11:06:14]  [1.97ms]  SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL AND ((ID = 'e3fdbc74-665a-47a3-bd11-2ff74ed14426')) ORDER BY `dogs`.`id` ASC LIMIT 1
Real UUID: 1b394083-3136-4f49-a779-ab48dbc0318f Foo's UUID: 1b394083-3136-4f49-a779-ab48dbc0318f
Real UUID: e3fdbc74-665a-47a3-bd11-2ff74ed14426 Bar's UUID: 1b394083-3136-4f49-a779-ab48dbc0318f
Real UUID: 1b394083-3136-4f49-a779-ab48dbc0318f Foo's UUID with Where: 1b394083-3136-4f49-a779-ab48dbc0318f
Real UUID: e3fdbc74-665a-47a3-bd11-2ff74ed14426 Bar's UUID with Where: e3fdbc74-665a-47a3-bd11-2ff74ed14426

Is something wrong in my code example? Or might this be an issue? Thanks!

[danhardman]

Not sure why this wouldn't throw an error, but could it be the uppercase ID in:

db.Where("ID = ?", foo.ID).First(&foo2)
db.Where("ID = ?", bar.ID).First(&bar2)

Should it be:

db.Where("id = ?", foo.ID).First(&foo2)
db.Where("id = ?", bar.ID).First(&bar2)

[juame]

@danhardman thanks for the help.
I think either way is okay. ID or id. Because it just changes the SELECT in the SQL Query:

With ID:

SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL AND ((ID = '58f10447-f326-4d48-91e7-e87ab16b8ea1')) ORDER BY `dogs`.`id` ASC LIMIT 1
SELECT * FROM `dogs`  WHERE `dogs`
deleted_at IS NULL AND ((ID = 'b9b87b01-f976-4128-90bb-1b5a14c35b96')) ORDER BY `dogs`.`id` ASC LIMIT 1

With id:

SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL AND ((id = 'ae5234d0-da11-400c-be09-0e7bd4ff5aa1')) ORDER BY `dogs`.`id` ASC LIMIT 1
SELECT * FROM `dogs`  WHERE `dogs`.deleted_at IS NULL 
 ((id = 'aee298ee-f74d-413e-8606-dee9c185c87f')) ORDER BY `dogs`.`id` ASC LIMIT 1

Do you have any idea for my problem with the First(out interface{}, where ...interface{}) method?

[danhardman]

@juame I've never noticed it before. I'll take a look though

[mehdy]

+1
I have the same issue

[jinzhu]

You couldn't use db.First(&foo1, foo.ID) unless primary key's type is integer