Driver hangs with empty result set

[cloudshiftchris]

Reproducer available. Execute via ./go-mssql-repro-1 --hostname <ip> --username <username> --password <password>

When executing queries (with certain attributes, detailed below) that do not return a result set the driver hangs, expecting to read data that isn't ever coming.

Using this based-on-real-world-issue query as an exemplar:

SELECT  TOP 50 'Top 50 queries' as Description,
                      a.*,
                      SUBSTRING(SqlText, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(SqlText)
            ELSE qs.statement_end_offset
            END - qs.statement_start_offset)/2) + 1) as statement,
                qs.*,
                queryplan.query_plan as query_plan_ext_xml
       FROM (SELECT DB_NAME(dbid) as [Database],
                    plan_handle,
                    UseCounts,
                    RefCounts,
                    size_in_bytes,
                    Cacheobjtype,
                    Objtype,
                    st.text as SqlText
             FROM sys.dm_exec_cached_plans cp
                      CROSS APPLY sys.dm_exec_sql_text(plan_handle) st
             WHERE (LEFT(TEXT,300) LIKE '%SOME_MATCHING_TEXT%')) a
                CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) queryplan
                INNER JOIN sys.dm_exec_query_stats qs on qs.plan_handle = a.plan_handle
          WHERE queryplan.query_plan IS NOT NULL AND DATEDIFF(hour,qs.last_execution_time,GETDATE()) < 12
             ORDER BY qs.total_elapsed_time DESC

As written the driver hangs executing that query, never to return. Removing the ORDER BY or TOP clause prevents hanging. Other nearly identical queries (with WHERE clauses that match results) do not hang.

To Reproduce Reproducer available. Execute via ./go-mssql-repro-1 --hostname <ip> --username <username> --password <password>

Expected behavior

Expecting that empty result sets are returned successfully, with rows.Next() returning false on first call.

Further technical details

SQL Server version: SQL Server 2019 Operating system: MacOS 13.1 M1 Pro

[cloudshiftchris]

Originally the query had a ROW_NUMBER construct that also appeared to cause (at least, in part) the hanging.

[sivan-koren]

Possibly related. Driver hangs even when not expecting a result set.

For example, a simple call to sp_addarticle hangs indefinitely when using the Exec method, which doesn't expect results.

E.g...

qry = "exec sp_addarticle @force_invalidate_snapshot = 1, @publication = N'EXMPL', @article = N'" + filter.Table + "', @source_owner = N'dbo', @source_object = N'" + filter.Table + "', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'" + filter.Table + "', @destination_owner = N'dbo', @status = 24, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dbo" + filter.Table + "]', @del_cmd = N'CALL [sp_MSdel_dbo" + filter.Table + "]', @upd_cmd = N'SCALL [sp_MSupd_dbo" + filter.Table + "]'"

fmt.Println("Adding to replication:\n", qry)

_, err = sqlserv.Exec(qry)

if err != nil {
    log.Println(err)
    break
}