Need to retry result request for get query result by qid

[shanhe-fullstory]

Please answer these questions before submitting your issue.
In order to accurately debug the issue this information is required. Thanks!

1. What version of GO driver are you using?
   1.6.23

2. What operating system and processor architecture are you using?
   Linux/x86_64 MacOS/arm64

3. What version of GO are you using?
   go1.19.1

4.Server version:* E.g. 1.90.1
7.35.1

5. What did you do?

ctx = gosnowflake.WithFetchResultByID(ctx, queryId)
rows, err := s.db.QueryContext(ctx, "")

6. What did you expect to see?
   It will return no error and no result after about 45 seconds.
   Note that the similar issue for async query is fixed in SNOW-782588: Retry result request for async query if still in progress #824
   But still need to fix rowsForRunningQuery in monitoring.go to check the resp.Code.
   It is possible for resp.Code to be queryInProgressAsyncCode after blocking for 45 seconds.

7. Can you set logging to DEBUG and collect the logs?

   https://community.snowflake.com/s/article/How-to-generate-log-file-on-Snowflake-connectors

8. What is your Snowflake account identifier, if any? (Optional)

[sfc-gh-dszmolka]

hi and thank you for raising this issue ! we'll take a look

[sfc-gh-dszmolka]

my colleagues are looking into the issue and could very well reproduce in async.go

respd.Code: 333334
respd.Message: Asynchronous execution in progress. Use provided query id to perform query monitoring and management."

but even with additional logging for rowsForRunningQuery in monitoring.go the long running (>45s) query produced

resp.Code: , Message: , Success: true

and they could not get to resp.Code: 333334.

Do (any of) you perhaps know a reliable way to reproduce getting this response queryInProgressAsyncCode in rowsForRunningQuery? That would be a great help in fixing this issue and validating the fix. Thank you in advance!

[shanhe-fullstory]

If we look it at the high level, do this:

1. Submit a query of sleeping for 70 seconds, and get the query id.
2. Use WithFetchResultByID and try to get the rows.
3. It will return prematurely at around 45 seconds without any result (should be a string saying has slept x seconds) and no error.
   The expected behavior is either it waits until the 70 seconds has passed or returns prematurely but with some error.

Right now, I am walking around by checking for no result and no error, and do retry in my application.

So do not bog down by the "check the resp.Code", I might be wrong. But the high level behavior needs to be fixed.

[shanhe-fullstory]

I just ran again, If I place a breakpoint at line 237 in monitoring.go,

func (sc *snowflakeConn) rowsForRunningQuery(
	ctx context.Context, qid string,
	rows *snowflakeRows) error {
	resultPath := fmt.Sprintf(urlQueriesResultFmt, qid)
	resp, err := sc.getQueryResultResp(ctx, resultPath)

After 45 seconds, the resp.Code is 333334 and Success is true.
If we retry, after the query finishes, the resp.Code is "" and Success is true.

[sfc-gh-dszmolka]

this is excellent, thank you so much, should be very helpful for the team !

[sfc-gh-dszmolka]

PR review is in progress under #1021 but if you wish, you can already check out the fix branch https://github.com/snowflakedb/gosnowflake/tree/705-WithFetchResultByID and see if it works for you

[sfc-gh-dszmolka]

PR has been merged and is now available to install from main. I'll need to confirm if it will be available with 1.7.2 (January) or only the next (February) release.

edit: February it is (as it hit main only after January's release was cut)

[sfc-gh-dszmolka]

fix released with gosnowflake 1.8.0