Gorm Error in generation groups queries in scopes

GORM Playground Link

https://github.com/daashuun/playground

Description

I tried to make SELECT * FROM "test" WHERE a = 1 AND (b = 2 OR c = 3)

but this request

DB.Table("test").Scopes(
  func(d *gorm.DB) *gorm.DB {
      return d.Where("a = 1")
  },
  func(d *gorm.DB) *gorm.DB {
      return d.Where(d.Or("b = 2").Or("c = 3"))
  },
).Rows()

makes sql SELECT * FROM "test" WHERE a = 1 OR b = 2 OR c = 3 AND (a = 1 OR b = 2 OR c = 3)

Comment From: github-actions[bot]

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 ✨

Comment From: black-06

It needs to be repaired and you can use this instead:

    db.Table("test").Scopes(
        func(d *gorm.DB) *gorm.DB {
            return d.Where("a = 1")
        },
        func(d *gorm.DB) *gorm.DB {
            return d.Where(db.Or("b = 2").Or("c = 3")) // it's db
        },
    ).Rows()

Comment From: github-actions[bot]

Comment From: a631807682

@black-06 This is a bug? Can it be assigned to you?

Comment From: github-actions[bot]

Comment From: black-06

@black-06 This is a bug? Can it be assigned to you?

I think so, it can be assigned to me. But it may require many changes to Scopes? Do you have any idea

Comment From: a631807682

But it may require many changes to Scopes? Do you have any idea

Sorry, I only read your comment and didn't check the question, I'll check and get back to you.

Comment From: github-actions[bot]

Comment From: a631807682

@black-06 This does not seem to be a scope problem, but a subquery processing problem. which will reuse Exprs to build a clause, even if it has been used externally. https://github.com/go-gorm/gorm/blob/master/statement.go#L331

    tx := DB.Where("a = 1").Session(&gorm.Session{})
    tx.Where(tx.Or("b = 2").Or("c = 3")).First(&test)
    //  SELECT * FROM `tests` WHERE a = 1 AND (a = 1 OR b = 2 OR c = 3) AND `tests`.`deleted_at` IS NULL ORDER BY `tests`.`id` LIMIT 1

Comment From: black-06

@black-06 This does not seem to be a scope problem, but a subquery processing problem. which will reuse Exprs to build a clause, even if it has been used externally. https://github.com/go-gorm/gorm/blob/master/statement.go#L331 ``go tx := DB.Where("a = 1").Session(&gorm.Session{}) tx.Where(tx.Or("b = 2").Or("c = 3")).First(&test) // SELECT * FROMtestsWHERE a = 1 AND (a = 1 OR b = 2 OR c = 3) ANDtests.deleted_atIS NULL ORDER BYtests.id` LIMIT 1

```

use a flag to make sure that expr only be used once?

It's broken for

tx = db.Model(&User{}).Where(xxx)... // common conditions

tx.Where(o1).Find(&u1)
tx.Where(o2).Find(&u2)

Comment From: github-actions[bot]

Comment From: a631807682

@black-06 This does not seem to be a scope problem, but a subquery processing problem. which will reuse Exprs to build a clause, even if it has been used externally. master/statement.go#L331 go tx := DB.Where("a = 1").Session(&gorm.Session{}) tx.Where(tx.Or("b = 2").Or("c = 3")).First(&test) // SELECT * FROM `tests` WHERE a = 1 AND (a = 1 OR b = 2 OR c = 3) AND `tests`.`deleted_at` IS NULL ORDER BY `tests`.`id` LIMIT 1

use a flag to make sure that expr only be used once?

It's broken for

```go tx = db.Model(&User{}).Where(xxx)... // common conditions

tx.Where(o1).Find(&u1) tx.Where(o2).Find(&u2) ```

Can we add such judgments? I didn't test it, it's just a possibility, when the DB instance is the same, the expression should have been added in the subquery.

if v != stmt.DB { 
    ...
}

Comment From: black-06

Umm... It can't work for this issue. @a631807682

DB.Table("test").Scopes(
  func(d *gorm.DB) *gorm.DB {
      return d.Where("a = 1")
  },
  func(d *gorm.DB) *gorm.DB {
      return d.Where(d.Or("b = 2").Or("c = 3"))
  },
).Rows()

Before:

SELECT * FROM `tests` WHERE a = 2 OR b = 2 OR c = 3 AND (a = 2 OR b = 2 OR c = 3)

After:

SELECT * FROM `tests` WHERE a = 2 AND (a = 2 OR b = 2 OR c = 3)

The externally condition gets clean, but internal doesn't.

I think maybe pass a clean database into it and then try to merge the returned results? https://github.com/go-gorm/gorm/blob/cc2d46e5be425300e064a39868cfdb333f24e4ac/callbacks.go#L78-L82

cleanDB = db.Session(&Session{})
for _, scope := range scopes { 
    result := scope(cleanDB)
    db.merge(result)
}

Comment From: a631807682

I think maybe pass a clean database into it and then try to merge the returned results?

This seems to be hard to do as user may pass some values with multiple scope, I have no suggestion. cc @jinzhu