Really confused about the default sql matcher

[rantianhua]

By default, sqlmock is preserving backward compatibility and default query matcher is sqlmock.QueryMatcherRegexp which uses expected SQL string as a regular expression to match incoming query string. However, I always encountered could not match actual sql: exception:

My repository code:

    accountID := "a1"
    baseTime := "2023-04-17T00:00:00Z"
    offset := 0
    pageSize := 10
    c.mockedDB.Mock.
        ExpectQuery(regexp.QuoteMeta(`SELECT yaye_events.* FROM yaye_events left join event_pinnings on yaye_events.id = event_pinnings.event_id.event_id WHERE event_pinnings.account_id != ? AND ((? >= yaye_events.start_date and ? <= yaye_events.end_date) OR ? > yaye_events.end_date) ORDER BY yaye_events.start_date desc LIMIT ?`)).
        WithArgs(accountID, baseTime, baseTime, baseTime, pageSize+1).
        WillReturnRows(&sqlmock.Rows{})

My test code:

c.mockedDB.Mock.
        ExpectQuery(regexp.QuoteMeta(`SELECT yaye_events(.*) FROM yaye_events left join event_pinnings on yaye_events.id = event_pinnings.event_id.event_id WHERE event_pinnings.account_id != (.+) AND (((.+) >= yaye_events.start_date and (.+) <= yaye_events.end_date) OR (.+) > yaye_events.end_date) ORDER BY yaye_events.start_date desc LIMIT 11`)).
        WithArgs(accountID, baseTime, baseTime, baseTime, pageSize+1).
        WillReturnRows(&sqlmock.Rows{})

No matter how I changed the query string, I always encountered the following exception:

could not match actual sql: "SELECT yaye_events.* FROM `yaye_events` left join event_pinnings on yaye_events.id = event_pinnings.event_id.event_id WHERE event_pinnings.account_id != ? AND ((? >= yaye_events.start_date and ? <= yaye_events.end_date) OR ? > yaye_events.end_date) ORDER BY yaye_events.start_date desc LIMIT 11" with expected regexp "SELECT yaye_events\(\.\*\) FROM yaye_events left join event_pinnings on yaye_events\.id = event_pinnings\.event_id\.event_id WHERE event_pinnings\.account_id != \(\.\+\) AND \(\(\(\.\+\) >= yaye_events\.start_date and \(\.\+\) <= yaye_events\.end_date\) OR \(\.\+\) > yaye_events\.end_date\) ORDER BY yaye_events\.start_date desc LIMIT 11"; Query: could not match actual sql: "SELECT yaye_events.* FROM `yaye_events` left join event_pinnings on yaye_events.id = event_pinnings.event_id.event_id WHERE event_pinnings.account_id != ? AND ((? >= yaye_events.start_date and ? <= yaye_events.end_date) OR ? > yaye_events.end_date) ORDER BY yaye_events.start_date desc LIMIT 11" with expected regexp "SELECT yaye_events\(\.\*\) FROM yaye_events left join event_pinnings on yaye_events\.id = event_pinnings\.event_id\.event_id WHERE event_pinnings\.account_id != \(\.\+\) AND \(\(\(\.\+\) >= yaye_events\.start_date and \(\.\+\) <= yaye_events\.end_date\) OR \(\.\+\) > yaye_events\.end_date\) ORDER BY yaye_events\.start_date desc LIMIT 11"

I really confused about how should I write my test code to match the actual query.

[l3pp4rd]

.?* are special characters in regexp, and hance needs to be escaped

[rantianhua]

I also tried

regexp.QuoteMeta(`SELECT yaye_events\.\* FROM yaye_events left join event_pinnings on yaye_events\.id = event_pinnings\.event_id\.event_id WHERE event_pinnings\.account_id != $1 AND (($2 >= yaye_events\.start_date and $3 <= yaye_events\.end_date) OR $4 > yaye_events\.end_date) ORDER BY yaye_events\.start_date desc LIMIT $5`)

But it still didn't work.

[rantianhua]

Use " instead of ` and remove escapes solved my problem.

[mauleyzaola]

In my case, passing this option fixed the issue

sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

[thecodecafe]

In my case, passing this option fixed the issue

sqlmock.New(sqlmock.QueryMatcherOption(sqlmock.QueryMatcherEqual))

Funny how this worked.