Why is `sqlmock` failing to match a query even though the queries are identical and the logs show them to be the same?

sqlmock does not match the query, but the query is the same and the log output shows the same

Encountered an issue while writing test code using Gorm and sqlmock , mainly related to the update function.

The first workflow section only queries records from the database. Even though the log output shows they are the same, I can't get it to match my SQL.

The error message is as follows:

(database.go:263)
[2020-01-08 10:29:40]  查询：无法匹配实际 SQL：“SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1” with expected regexp "SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1"

I also tried using ExpectExec to insert ExpectQuery.

  for _, c := range cases {
    db, mock, err := sqlmock.New()
    if err != nil {
      t.Fatal(err)
    }
    DB, err := gorm.Open("sqlite3", db)
    if err != nil {
      t.Fatal(err)
    }
    DB.LogMode(true)

    mock.ExpectQuery(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1`)

    err = UpdateStoragePool(DB, &c.givenPool)
    if !reflect.DeepEqual(c.wantedError, err) {
      t.Fatalf("expecting errror %q, got %q", c.wantedError, err)
    }

    // 如果事务期间没有发生任何错误，则检查是否满足所有预期
    if c.wantedError == nil {
      if err := mock.ExpectationsWereMet(); err != nil {
        t.Fatalf(err.Error())
      }
    }
  }

I also tried:

mock.ExpectQuery(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = '1')) ORDER BY "storage_pools"."id" ASC LIMIT 1`).WithArgs(1)  
mock.ExpectExec(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = ?)) ORDER BY "storage_pools"."id" ASC LIMIT 1`)  
mock.ExpectExec(`SELECT * FROM "storage_pools" WHERE "storage_pools"."deleted_at" IS NULL AND ((poolid = '1')) ORDER BY "storage_pools"."id" ASC LIMIT 1`).WithArgs(1)

There may be two possible reasons for this problem:

1. Try to put double quotes (") in the query string. sqlmock requires backticks (`) in the query string. To fix this, replace the double quotes with backticks. Try using the ? placeholder in the query. sqlmock requires the SQL standard parameter placeholder $n. To fix this, replace the ? placeholder with $n.

The above is the detailed content of Why is `sqlmock` failing to match a query even though the queries are identical and the logs show them to be the same?. For more information, please follow other related articles on the PHP Chinese website!