Executing count will clear Statement.Joins

[liov]

go-gorm/playground#736

type AB struct {
	Id  int `json:"id" gorm:"primaryKey"`
	BId int `json:"aId" gorm:"index"`
	AId int `json:"bId"`
	B   B   `json:"b"`
	A   A   `json:"a"`
}

type A struct {
	Id   int    `json:"id" gorm:"primaryKey"`
	Name string `json:"name" gorm:"uniqueIndex"`
}

type B struct {
	Id   int    `json:"id" gorm:"primaryKey"`
	Name string `json:"name" gorm:"uniqueIndex"`
}

func main() {
	db, _ := gorm.Open(tests.DummyDialector{}, &gorm.Config{
		NamingStrategy: schema.NamingStrategy{
			SingularTable: true,
		},
	})

	var (
		count    int64
		appNodes = []*AB{}
	)
	db:= db.Model(&AB{}).Joins("A").Joins("B").Where(`id = ?`, 1)

	db.Count(&count)
	db.Limit(1).Offset(0).Order("id DESC").Find(&appNodes)
}

db := db.Model(&AB{}).Preload("A").Preload("B").Where(`id = ?`, 1)

The two pieces of code have different results, preload will get the correct result, because Count will call AfterQuery,it will "clear the joins after query because preload need it" ,I don't know why did it, this means need to write Joins twice.

[github-actions]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 30 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

[ivila]

@liov What version are you using?

        var (
                count    int64
                appNodes = []*AB{}
        )
        db1 := db.Model(&AB{}).Joins("A").Joins("B").Where("abs.`id` = ?", 1)

        // [0.572ms] [rows:1] SELECT count(*) FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1
        db1.Debug().Count(&count)
        // [0.705ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`A`.`id` AS `A__id`,`A`.`name` AS `A__name`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1 ORDER BY abs.`id` DESC LIMIT 1
        db1.Limit(1).Offset(0).Order("abs.`id` DESC").Debug().Find(&appNodes)
        var ab AB
        // [0.630ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`A`.`id` AS `A__id`,`A`.`name` AS `A__name`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `as` `A` ON `abs`.`a_id` = `A`.`id` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.`id` = 1 ORDER BY abs.`id` DESC LIMIT 1
        db1.Model(&AB{}).Preload("A").Preload("B").Debug().Find(&ab)

I run it with version v1.25.9, and the two pieces of code have same results.

[ivila]

And testing with your playground (after fix your syntax error with the: Error 1052 (23000): Column 'id' in where clause is ambiguous), the two pieces of code have same results too (with both version v1.25.9 and your playground version v1.25.4)

        // [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11
        db = DB.Model(&AB{}).Preload("A").Joins("B").Debug().Where(`abs.id = ?`, 11).Find(&ab)
        // [2.033ms] [rows:1] SELECT count(*) FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11
        db.Debug().Count(&count)
        // [1.860ms] [rows:0] SELECT `abs`.`id`,`abs`.`b_id`,`abs`.`a_id`,`B`.`id` AS `B__id`,`B`.`name` AS `B__name` FROM `abs` LEFT JOIN `bs` `B` ON `abs`.`b_id` = `B`.`id` WHERE abs.id = 11 ORDER BY abs.id DESC LIMIT 1
        db.Limit(1).Offset(0).Order("abs.id DESC").Debug().Find(&appNodes)

[liov]

@ivila I'm mistaken, actually it's right to clear joins here, but count affects the select, please try the latest go-gorm/playground#736

[liov]

I guess that's where the problem lies

for _, join := range db.Statement.Joins {
// xxx
  if isRelations {
    for _, s := range relation.FieldSchema.DBNames {
      if v, ok := selectColumns[s]; (ok && v) || (!ok && !restricted) {
	      clauseSelect.Columns = append(clauseSelect.Columns, clause.Column{
		      Table: tableAliasName,
		      Name:  s,
		      Alias: utils.NestedRelationName(tableAliasName, s),
	  })
      }
    }
  }
}

[liov]

Is anyone looking into this? This bug was introduced by the fix for this issue #6715. Now, I just need to call count in front, and it can be fully reproduced. go-gorm/playground#736

2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:35
[0.313ms] [rows:1] SELECT count(*) FROM `users` LEFT JOIN `accounts` `Account` ON `users`.`id` = `Account`.`user_id` AND `Account`.`deleted_at` IS NULL LEFT JOIN `pets` `Account__Pet` ON `Account`.`id` = `Account__Pet`.`account_id` AND `Account__Pet`.`deleted_at` IS NULL WHERE `users`.`deleted_at` IS NULL

2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.144ms] [rows:2] SELECT * FROM `companies` WHERE `companies`.`account_id` = 1

2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.445ms] [rows:1] SELECT * FROM `accounts` WHERE `accounts`.`user_id` = 1 AND `accounts`.`deleted_at` IS NULL

2024/05/21 03:15:28 /home/runner/work/playground/playground/main_test.go:39
[0.859ms] [rows:1] SELECT `users`.`id`,`users`.`created_at`,`users`.`updated_at`,`users`.`deleted_at` FROM `users` LEFT JOIN `accounts` `Account` ON `users`.`id` = `Account`.`user_id` AND `Account`.`deleted_at` IS NULL LEFT JOIN `pets` `Account__Pet` ON `Account`.`id` = `Account__Pet`.`account_id` AND `Account__Pet`.`deleted_at` IS NULL WHERE `users`.`deleted_at` IS NULL AND `users`.`id` = 1 ORDER BY `users`.`id` LIMIT 1
    main_test.go:48: Failed, got ''

Also, I have fixed it now. #7027