proposal: execute arbitrary SQL in one round trip, returning rows or the OK-packet result (QueryResultContext)

I maintain a MySQL proxy written in Go. Like any tool that executes SQL it didn't write, it must return whatever MySQL sends back: a result set, or the OK packet's affected-rows/last-insert-id.

database/sql makes me commit to Query or Exec before the server has said which kind of response the statement produces. For arbitrary SQL that's a guess, and both wrong guesses lose data silently:

• QueryContext on a write: the OK packet's affectedRows/insertId are stored on the connection's unexported fields — I get zero-column rows and the counts are unreachable.
• ExecContext on a statement that returns rows: the driver reads and discards the result set (readUntilEOF) — unrecoverable, the statement already executed.

So today's options look like this (CALL p() returns rows or not depending on the procedure body, so no classifier is ever right):

// Option 1: guess — and maintain a SQL parser in order to use a SQL driver.
if looksLikeItReturnsRows(query) {
	rows, err = db.QueryContext(ctx, query)
} else {
	res, err = db.ExecContext(ctx, query)
}

// Option 2: Query everything, then ask the server what happened.
// An extra round trip per statement, and ROW_COUNT()/LAST_INSERT_ID()
// have sticky semantics that make this subtly wrong in edge cases.
rows, err := conn.QueryContext(ctx, query)
// ... drain the zero-column rows ...
err = conn.QueryRowContext(ctx,
	"SELECT ROW_COUNT(), LAST_INSERT_ID()").Scan(&affected, &insertID)

The protocol doesn't have this problem — a COM_QUERY response is self-describing (result set or OK packet), and other ecosystems expose that directly: libmysqlclient (mysql_field_count() == 0), JDBC (Statement.execute()), pgx (CommandTag). Only in Go must the caller guess ahead of the wire.

What I'd like to write instead

One call that returns what actually came back, reached the same way mysql.Result (#1261 → #1309) already is — a type assertion via sql.Conn.Raw():

conn, _ := db.Conn(ctx)
err := conn.Raw(func(dc any) error {
	rows, result, err := dc.(mysql.QueryerResult).QueryResultContext(ctx, query, nil)
	if err != nil {
		return err
	}
	if rows != nil {
		defer rows.Close()
		return forwardRows(rows) // the statement returned a result set
	}
	return forwardOK(result) // OK packet: RowsAffected/LastInsertId available
})

No classifier, no second round trip, nothing discarded.

Proposal

// QueryerResult executes any statement in one round trip. On success exactly
// one of rows/result is non-nil: rows for a result set response, result for
// an OK-packet response (including the mysql.Result extension methods).
type QueryerResult interface {
	QueryResultContext(ctx context.Context, query string, args []driver.NamedValue) (driver.Rows, driver.Result, error)
}

Optionally (a separate second commit in our patch): rows from this method deliver cells as raw MySQL wire text ([]byte, the usual aliasing contract), skipping parseTime/numeric conversion — pass-through consumers avoid parse→format round trips and lossy conversions (a 0000-00-00 date can't round-trip through time.Time).

Why not extend Query/Exec instead?

The helpers are split between the two return types by design: Result can never carry rows, and Rows gives no path to the OK-packet counts (database/sql's wrapper doesn't forward driver-specific interfaces on rows).

• Exec everything: the rows are drained to keep the connection command-ready - unrecoverable by the time Exec returns. Returning them means buffering unbounded result sets in Result, or a new interface anyway.
• Query everything: callers can detect an OK packet (zero columns) but not read its counts. A "last counts" getter on the conn would be valid only until the driver's next wire command — and database/sql issues commands the caller didn't write - i.e. the LAST_INSERT_ID() footgun rebuilt client-side. Attaching a Result to the rows costs the same new Raw-reachable interface as this proposal, with worse ergonomics: every INSERT returns an open cursor that pins the connection until Close.

There is no zero-new-API way to close the gap; the question is only the shape. Returning rows-or-result from the call that produced them beats mutable conn state or overloaded streaming semantics.

Why in this driver rather than database/sql

Same reason as #1309: database/sql can't carry driver-specific results, and Conn.Raw is the stdlib's sanctioned escape hatch (golang/go#5606 → Raw in Go 1.13). The Exec/Query split also encodes connection lifetime in the pooled API (Exec releases the conn, Query pins it until rows.Close()), so a rows-or-result call only fits on a raw conn the caller already holds. #180 and #971 are the same underlying gap and predate Raw; #1179 is still open.

Implementation

If there's appetite for this, I'm happy to submit a PR.

WIP branch @ https://github.com/morgo/mysql/tree/query-result-context