`table_name` is not supported with Mysql

[smoothdvd]

db.Clauses(exclause.NewWith("cte", "SELECT * FROM `kbw_user_account`")).Table("cte").Scan(&results)

[16.641ms] [rows:-] WITH `cte` AS (SELECT * FROM `kbw_user_account`) SELECT * FROM `cte`
Error executing the query: Error 1146 (42S02): [17003, 2023121019401419216822211203453556666] Table adb.kbw2.cte does not exist

[WinterYukky]

@smoothdvd Thank you open the issue. I tried reproduction your error, however I can't reproduction it. Could you tell me your MySQL version? MySQL 5.7 doesn't support WITH clause.

My test code is this. It has success. (using MySQL 8.2)

func TestMySQL(t *testing.T) {
    type User struct {
        gorm.Model
        Name  string
        Email *string
        Age   uint8
    }
    dsn := "test_user:password@tcp(mysql:3306)/test_db?charset=utf8mb4&parseTime=True&loc=Local"
    db, _ := gorm.Open(mysql.Open(dsn), &gorm.Config{
        Logger: logger.Default.LogMode(logger.Info),
    })
    db.Use(extraClausePlugin.New())
    db.AutoMigrate(&User{})

    var results []User
    err := db.Clauses(NewWith("cte", "SELECT * FROM `users`")).Table("cte").Scan(&results).Error
    if err != nil {
        t.Errorf(err.Error())
    }
    defer db.Migrator().DropTable(&User{})
}

[smoothdvd]

I using AnalyticDB for MySQL provide by Aliyun. It's support WITH clause. I can run WITH clause in DataGrip like following:

[2023-12-19 12:02:50] Connected
kbw2> use kbw2
[2023-12-19 12:02:50] completed in 28 ms
kbw2> WITH cte AS (SELECT * FROM kbw_user_account LIMIT 10) SELECT * FROM `cte`
[2023-12-19 12:02:51] 10 rows retrieved starting from 1 in 173 ms (execution: 88 ms, fetching: 85 ms)

If WITH clause with wrapped with `` in definition, it will failed:

kbw2> WITH `cte` AS (SELECT * FROM kbw_user_account LIMIT 10) SELECT * FROM `cte`
[2023-12-19 12:04:30] [42S02][1146] [17003, 2023121912043119216822211203453023793] Table adb.kbw2.cte does not exist