Accessing rows_before_limit information

[gutweiler]

Hey,

i would like to access the “rows before limit”-number for select-queries with LIMIT.

In JSON format, the value is returned as “rows_before_limit_at_least”: https://clickhouse.tech/docs/en/interfaces/formats/#json

In native ClickHouse protocol, the “rows before limit”-number is returned from the server in a server profileInfo packet, and is already decoded, but not stored alongside with the rows-result.

This change adds the profileinfo result into the rows-struct for later use.

https://github.com/ClickHouse/clickhouse-go/blob/master/rows.go

diff --git a/vendor/github.com/ClickHouse/clickhouse-go/rows.go b/vendor/github.com/ClickHouse/clickhouse-go/rows.go
index 30e844f6..b97a6b22 100644
--- a/vendor/github.com/ClickHouse/clickhouse-go/rows.go
+++ b/vendor/github.com/ClickHouse/clickhouse-go/rows.go
@@ -25,6 +25,7 @@ type rows struct {
        stream       chan *data.Block
        columns      []string
        blockColumns []column.Column
+       profileInfo  *profileInfo
 }
 
 func (rows *rows) Columns() []string {
@@ -108,6 +109,7 @@ func (rows *rows) receiveData() error {
                        if profileInfo, err = rows.ch.profileInfo(); err != nil {
                                return rows.setError(err)
                        }
+                       rows.profileInfo = profileInfo
                        rows.ch.logf("[rows] <- profiling: rows=%d, bytes=%d, blocks=%d", profileInfo.rows, profileInfo.bytes, profileInfo.blocks)
                case protocol.ServerData, protocol.ServerTotals, protocol.ServerExtremes:

Now the “rows before limit”-number can then be fetched from outside the package via this reflect-code:

    rows, _ := conn.Query("SELECT col FROM table LIMIT 0, 10")
    for rows.Next() {
     // …
    }
	
    rowsiPtr := reflect.ValueOf(rows).Elem().FieldByName("rowsi")
    rowsi := reflect.Indirect(rowsiPtr).Elem()
    profileInfoPtr := reflect.Indirect(rowsi).FieldByName("profileInfo")
    rowsBeforeLimit := reflect.Indirect(profileInfoPtr).FieldByName("rowsBeforeLimit").Uint()

This is for sure not beautiful.
But without further modification of the package, it is the only way to access these values.

Is there a reason why this information is not accessible right now?

The sql.Rows Interface does not support returning values like this, but the clickhouse-go package could provide a function that accepts the sql.Rows and returns the values?

Any thoughts on this?

Thanks!

[lingpeng0314]

Same requirement for the progress data.
Could we add the related info in query context and anyone want these info could fetch them by sql query context.

[kshvakov]

I'll add this feature same as progress/logs/events. See Context options.

PS: @lingpeng0314 progress is supported in V2

[kshvakov]

Done

[frkntplglu]

How could I get this data? Can you help me out?

[arsssen]

How could I get this data? Can you help me out?

here's an example of using context options:

        var total uint64   
	ctxWProfile := clickhouse.Context(ctx, clickhouse.WithProfileInfo(func(info *clickhouse.ProfileInfo) {
		if info != nil {
			total = info.RowsBeforeLimit
		}
	}))
       
	rows, err := db.QueryContext(ctxWProfile, query, params...)  // db is *sql.DB
        // total now contains the "rows before limit" info