Error: Query: could not match actual sql

[stori-rusher]

Hello, I raised a question about this in #51 (reply in thread), but am still encountering this error in another environment.

This is in the test I've written

ctx := context.Background()

pool, err := pgxmock.NewPool()
if err != nil {
	t.Fatalf("error creating mock db: %v", err)
}

sql := `
	SELECT acct_status
	FROM ccm.cc_account_mx
	WHERE acct_uuid = $1;
`
cols := []string{"acct_status"}
rows := pgxmock.NewRows(cols)

pool.ExpectQuery(sql).WillReturnRows(rows)

event := events.APIGatewayProxyRequest{
	QueryStringParameters: map[string]string{"acct_uuid": "889eba15-a551-4528-a2ff-b117b06db5cf"},
}

handler := accounts.AccountGet(pool)

res, err := handler(ctx, event)

And within my function the result of pgxmockPool.QueryRow().Scan() is "SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;" with expected regexp "SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;"

It seems to me that these two SQL strings should match, and do match, so I do not understand how this error is happening.

[pashagolub]

I don't check it yet but I see you're using multiline string constant. And I think you miss the point you will have line endings there. And these two lines are certainly not equal:

`
	SELECT acct_status
	FROM ccm.cc_account_mx
	WHERE acct_uuid = $1;
`
vs

"SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;"

In first case you have at least 4 CRLF and 4 tabs but not in the second

[pashagolub]

Please try this one:

pool.ExpectQuery("SELECT acct_status").WillReturnRows(rows)

That's usually enough

[pashagolub]

package main

import "fmt"

func main() {

	sql := `
	SELECT acct_status
	FROM ccm.cc_account_mx
	WHERE acct_uuid = $1;
`
	sql2 := "SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;"

	fmt.Printf("%+q\n%+q", sql, sql2)
}

Output:

"\n\tSELECT acct_status\n\tFROM ccm.cc_account_mx\n\tWHERE acct_uuid = $1;\n"
"SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;"

https://go.dev/play/p/-fvOjIH5vlj

[stori-rusher]

I don't check it yet but I see you're using multiline string constant. And I think you miss the point you will have line endings there. And these two lines are certainly not equal:

`
	SELECT acct_status
	FROM ccm.cc_account_mx
	WHERE acct_uuid = $1;
`
vs

"SELECT acct_status FROM ccm.cc_account_mx WHERE acct_uuid = $1;"

In first case you have at least 4 CRLF and 4 tabs but not in the second

Interesting, in my code both are multiline strings.

// in the test
	sql := `
		SELECT acct_status
		FROM ccm.cc_account_mx
		WHERE acct_uuid = $1;
	`

// in the actual code
	sql := `
		SELECT acct_status
		FROM ccm.cc_account_mx
		WHERE acct_uuid = $1;
	`

[stori-rusher]

Please try this one:

pool.ExpectQuery("SELECT acct_status").WillReturnRows(rows)

That's usually enough

When I try this, I get this error:

QueryRow res: Incorrect argument number 0 for columns 1
--- FAIL: TestErrNoRows (0.00s)
panic: runtime error: index out of range [0] with length 0 [recovered]
	panic: runtime error: index out of range [0] with length 0

goroutine 11 [running]:
testing.tRunner.func1.2({0x19fe280, 0xc00003ac00})
	/usr/local/Cellar/go/1.17.5/libexec/src/testing/testing.go:1209 +0x24e
testing.tRunner.func1()
	/usr/local/Cellar/go/1.17.5/libexec/src/testing/testing.go:1212 +0x218
panic({0x19fe280, 0xc00003ac00})
	/usr/local/Cellar/go/1.17.5/libexec/src/runtime/panic.go:1038 +0x215
github.com/pashagolub/pgxmock.(*rowSets).Scan(0x100ec87, {0xc00049ca90, 0x1929760, 0x1})
	/Users/rusher/go/pkg/mod/github.com/pashagolub/[email protected]/rows.go:69 +0x777
github.com/credifranco/cc-account-mx.getAccountByUUID({0x1d0b0c0, 0xc00048afc0}, {0x6d15298, 0xc000331280}, {0x0, 0x0})
	/Users/rusher/src/cc-account-mx/accounts.go:97 +0x22f
github.com/credifranco/cc-account-mx.AccountGet.func1({_, _}, {{0x0, 0x0}, {0x0, 0x0}, {0x0, 0x0}, 0x0, 0x0, ...})
	/Users/rusher/src/cc-account-mx/accounts.go:57 +0x17f
github.com/credifranco/cc-account-mx_test.TestErrNoRows(0xc000103d40)
	/Users/rusher/src/cc-account-mx/accounts_test.go:39 +0x26a
testing.tRunner(0xc000103d40, 0x1ae0730)
	/usr/local/Cellar/go/1.17.5/libexec/src/testing/testing.go:1259 +0x102
created by testing.(*T).Run
	/usr/local/Cellar/go/1.17.5/libexec/src/testing/testing.go:1306 +0x35a

[pashagolub]

Your rows have no rows actually.

Try to add some, e.g. rows.AddRow(...).

Please, read the example carefully.

[stori-rusher]

Your rows have no rows actually.

Try to add some, e.g. rows.AddRow(...).

Please, read the example carefully.

Ok thank you, that does clear the errors. But then I'm not clear on how to explicitly return no rows. I'm attempting to test against a situation where QueryRow.Scan() will return no rows. Is there a scenario in which pgx.ErrNoRows would be returned?

[pashagolub]

Sorry, I cannot follow your code really, but I've added test case returning no rows to the blog example and works like a charm:

func TestNoPostsReturned(t *testing.T) {
	mock, err := pgxmock.NewConn()
	if err != nil {
		t.Fatalf("an error '%s' was not expected when opening a stub database connection", err)
	}
	defer mock.Close(context.Background())

	// create app with mocked db, request and response to test
	app := &api{mock}
	req, err := http.NewRequest("GET", "http://localhost/posts", nil)
	if err != nil {
		t.Fatalf("an error '%s' was not expected while creating request", err)
	}
	w := httptest.NewRecorder()

	mock.ExpectQuery("^SELECT (.+) FROM posts$").WillReturnRows(mock.NewRows([]string{"id", "title", "body"}))
	// now we execute our request
	app.posts(w, req)
	if w.Code != 200 {
		t.Fatalf("expected status code to be 200, but got: %d\nBody: %v", w.Code, w.Body)
	}

	// we make sure that all expectations were met
	if err := mock.ExpectationsWereMet(); err != nil {
		t.Errorf("there were unfulfilled expectations: %s", err)
	}
}

[pashagolub]

Lines covered by this new test

[niting3c]

Hi @pashagolub I am having a similar issue.

My setup :
I am using QueryRow().scan(&obj)

Code:
Snippet from actual query:

if err := tx.QueryRow(context.Background(), sql, args...).Scan(&id); err != nil {
		logrus.Errorf("Failed to create %s in database: %v", objectType, err)
		return id, &utils.ErrorMessage{StatusCode: http.StatusInternalServerError, Message: fmt.Sprintf(customError.FAILED_TO_CREATE_OBJ, objectType)}
	}

func TestCreateSuccess(t *testing.T) {
	mockDB, _ := pgxmock.NewPool()
	defer mockDB.Close()

	// Expect transaction begin
	mockDB.ExpectBegin()
	sql := `INSERT INTO objects (id) VALUES ($1) RETURNING id`

	mockDB.ExpectQuery(sql).
		WithArgs(1).
		WillReturnRows(pgxmock.NewRows([]string{"id"}).AddRow(1))

	// Expect transaction commit
	mockDB.ExpectCommit()

	objectType := "testObject"

	repo := NewCRUDRepository(mockDB)
	id, err := repo.Create(sql, objectType, 1)

	assert.Nil(t, err)
	assert.NotNil(t, id)
	assert.Equal(t, 1, id)
	if e := mockDB.ExpectationsWereMet(); e != nil {
		t.Errorf("there were unfulfilled expectations: %s", e)
	}
}

Whenever i try to mock this I get error:

time="2024-02-08T19:44:31Z" level=error msg="Failed to create testObject in database: could not match actual sql: \"INSERT INTO objects (id) VALUES ($1) RETURNING id\" with expected regexp \"INSERT INTO objects (id) VALUES ($1) RETURNING id\""
time="2024-02-08T19:44:31Z" level=error msg="Failed to rollback transaction: call to method Rollback(), was not expected, next expectation is: ExpectedQuery => expecting call to Query() or to QueryRow():\n\t- matches sql: 'INSERT INTO objects (id) VALUES ($1) RETURNING id'\n\t- is with arguments:\n\t\t0 - 1\n\t- returns data:\n\t\trow 0 - [1]\n\n"
    CRUDRepository_test.go:34: 
        	Error Trace:	~/Repository/CRUDRepository_test.go:34
        	Error:      	Expected nil, but got: &utils.ErrorMessage{StatusCode:500, Message:"Failed to create Object testObject"}
        	Test:       	TestCreateSuccess

[pashagolub]

@niting3c Do you remember that by default ExpectQuery expects regular expression? That means, this might work

mockDB.ExpectQuery("INSERT")