Out-of-Bounds Pagination with JOINs Leaks Connections **Severity:** ⚠️ CRITICAL

[LukasGPC]

Severity: ⚠️ CRITICAL

Description:
When OFFSET exceeds available data in queries with JOINs, the driver doesn't properly clean up connections, leading to connection pool exhaustion over time.

Symptoms:

• Connections remain open after query completes
• Connection pool gradually exhausts
• Eventually causes "too many connections" errors
• Problem worsens with smaller connection pool sizes

Minimal Reproduction:

func getOrganizationsPage999(db *sql.DB) error {
    // Assume table only has 10 rows
    query := `
        SELECT
            o.Org_ID, o.Org_Name,
            COALESCE(ot.Org_Type_Name, '')
        FROM GPC.Organization o
        LEFT JOIN GPC.Org_Type ot ON o.Org_Org_Type_ID = ot.Org_Type_ID
        ORDER BY o.Org_Name
        OFFSET 10000 ROWS FETCH NEXT 50 ROWS ONLY
    `

    rows, err := db.Query(query)
    if err != nil {
        return err
    }
    defer rows.Close() // Connection still leaks despite defer

    // No rows returned, but connection is not properly cleaned up
    for rows.Next() {
        // Never executed - no rows
    }

    return nil
}

func main() {
    db, _ := sql.Open("iris", "iris://_SYSTEM:password@localhost:1972/USER")
    db.SetMaxOpenConns(5)
    db.SetMaxIdleConns(2)
    defer db.Close()

    // Call repeatedly - connections leak each time
    for i := 0; i < 20; i++ {
        getOrganizationsPage999(db)
    }
    // Connection pool now exhausted
}

Workaround:
Validate pagination bounds before executing query:

func listOrganizations(db *sql.DB, page, pageSize int) ([]Organization, int, error) {
    // Get total count first
    var totalCount int
    db.QueryRow(`SELECT COUNT(*) FROM GPC.Organization`).Scan(&totalCount)

    // Validate pagination bounds BEFORE executing main query
    totalPages := (totalCount + pageSize - 1) / pageSize
    if totalCount == 0 {
        totalPages = 1
    }

    offset := (page - 1) * pageSize
    if page > totalPages || offset >= totalCount {
        // Return empty result without executing query - prevents leak
        return []Organization{}, totalCount, nil
    }

    // Safe to execute query - offset is valid
    query := fmt.Sprintf(`
        SELECT o.Org_ID, o.Org_Name, COALESCE(ot.Org_Type_Name, '')
        FROM GPC.Organization o
        LEFT JOIN GPC.Org_Type ot ON o.Org_Org_Type_ID = ot.Org_Type_ID
        ORDER BY o.Org_Name
        OFFSET %d ROWS FETCH NEXT %d ROWS ONLY
    `, offset, pageSize)

    rows, _ := db.Query(query)
    defer rows.Close() // Now safe - no leak

    // ... process results

    return organizations, totalCount, nil
}

Impact: High - requires pagination validation in all list endpoints, can cause production outages if overlooked.

---