Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Executing count will clear Statement.Joins #7025

Open
liov opened this issue May 16, 2024 · 6 comments · May be fixed by #7027
Open

Executing count will clear Statement.Joins #7025

liov opened this issue May 16, 2024 · 6 comments · May be fixed by #7027
Assignees
@jinzhu
Labels
type:with reproduction steps with reproduction steps

Comments

@liov
Copy link
Contributor

liov commented May 16, 2024 •
edited

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 github-actions bot added the type:missing reproduction steps missing reproduction steps label May 16, 2024
@github-actions GitHub Actions
Copy link

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 ✨

@github-actions github-actions bot added status:stale type:with reproduction steps with reproduction steps and removed status:stale type:missing reproduction steps missing reproduction steps labels May 16, 2024
@ivila
Copy link

ivila commented May 17, 2024

@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
Copy link

ivila commented May 17, 2024

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
Copy link
Contributor Author

liov commented May 17, 2024

@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
Copy link
Contributor Author

liov commented May 17, 2024

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),
	  })
      }
    }
  }
}

This was referenced May 17, 2024
Closed
Open
@liov
Copy link
Contributor Author

liov commented May 21, 2024

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@jinzhu jinzhu

Labels
type:with reproduction steps with reproduction steps
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

fix: AfterQuery should clear FROM Clause's Joins rather than the Stat… liov/gorm
3 participants
@jinzhu @ivila @liov