Large Data Volume with JOINs Causes rows.Next() Hang ⚠️ CRITICAL

[LukasGPC]

Issue 6: Large Data Volume with JOINs Causes rows.Next() Hang

Description:
Queries with JOINs that fetch large VARCHAR fields (VARCHAR(1024)) exceed the driver's buffer capacity (~72KB-150KB combined data), causing rows.Next() to hang indefinitely after processing a limited number of rows.

Symptoms:

• Works fine with small page sizes (8-15 rows depending on data volume per row)
• Hangs with larger page sizes or when fetching many large VARCHAR fields
• rows.Next() blocks indefinitely mid-iteration
• Driver appears to read data but hangs before returning
• Exact row limit depends on: (number of VARCHAR(1024) fields) × (number of JOINs) × (row count)

Measured Thresholds:

• Product table (8× VARCHAR(1024) + 2 JOINs): ~9 rows = 72KB → hang
• Brand table (9× VARCHAR(1024) + 1 JOIN): ~15 rows = 135KB → hang
• User table (2× VARCHAR(256) + 2 JOINs): ~18 rows = 86KB → hang

Minimal Reproduction:

func listProducts(db *sql.DB) error {
    // Product table has 8× VARCHAR(1024) heavyweight fields
    // ~8KB per row × 9 rows = ~72KB with JOINs → HANGS
    query := `
        SELECT TOP 50
            p.Prod_ID, p.Prod_Name,
            p.Prod_Description,          -- VARCHAR(1024)
            p.Prod_Allergens,            -- VARCHAR(1024)
            p.Prod_Nutritional_Info,     -- VARCHAR(1024)
            p.Prod_Product_Size,         -- VARCHAR(1024)
            p.Prod_Project_Storage,      -- VARCHAR(1024)
            p.Prod_Product_Ingredients,  -- VARCHAR(1024)
            p.Prod_Packaging,            -- VARCHAR(1024)
            p.Prod_Packaging_Percentage, -- VARCHAR(1024)
            COALESCE(o.Org_Name, ''),
            COALESCE(b.Brand_Name, '')
        FROM GPC.Product p
        LEFT JOIN GPC.Organization o ON p.Prod_Org_ID = o.Org_ID
        LEFT JOIN GPC.Brand b ON p.Prod_Brand_ID = b.Brand_ID
        ORDER BY p.Prod_Name
    `

    rows, _ := db.Query(query)
    defer rows.Close()

    count := 0
    for rows.Next() { // Hangs after ~9 iterations
        count++
        log.Printf("Row %d", count) // Last log: "Row 9"

        var id, name, desc, allergens, nutrition, size, storage, ingredients, packaging, packPercent, org, brand string
        rows.Scan(&id, &name, &desc, &allergens, &nutrition, &size, &storage, &ingredients, &packaging, &packPercent, &org, &brand)
    }
    // Never completes iteration

    return nil
}

Workaround 1: Lightweight List Query Pattern (Recommended)

Exclude heavyweight VARCHAR fields from list queries, keep them for detail queries:

func listProducts(db *sql.DB) ([]Product, error) {
    // Lightweight list query - removed 8 heavyweight fields
    // ~2KB per row × 50+ rows = 100KB total → WORKS
    query := `
        SELECT TOP 50
            p.Prod_ID, p.Prod_SKU, p.Prod_Name, p.Prod_Category,
            -- Removed: 8× VARCHAR(1024) heavyweight fields
            COALESCE(o.Org_Name, ''),
            COALESCE(b.Brand_Name, ''),
            p.Prod_Active, p.Prod_Date_Created
        FROM GPC.Product p
        LEFT JOIN GPC.Organization o ON p.Prod_Org_ID = o.Org_ID
        LEFT JOIN GPC.Brand b ON p.Prod_Brand_ID = b.Brand_ID
        ORDER BY p.Prod_Name
    `

    rows, _ := db.Query(query)
    defer rows.Close()

    var products []Product
    for rows.Next() { // Works fine for 50+ rows
        var p Product
        rows.Scan(&p.ID, &p.SKU, &p.Name, &p.Category, &p.OrgName, &p.BrandName, &p.Active, &p.DateCreated)
        products = append(products, p)
    }

    return products, nil
}

func getProductDetail(db *sql.DB, id string) (*Product, error) {
    // Detail query - includes ALL fields (only 1 row, no iteration issue)
    query := `
        SELECT
            p.*,
            COALESCE(o.Org_Name, ''),
            COALESCE(b.Brand_Name, '')
        FROM GPC.Product p
        LEFT JOIN GPC.Organization o ON p.Prod_Org_ID = o.Org_ID
        LEFT JOIN GPC.Brand b ON p.Prod_Brand_ID = b.Brand_ID
        WHERE p.Prod_ID = ?
    `

    var p Product
    err := db.QueryRow(query, id).Scan(&p.ID, &p.Name, &p.Description, /* all fields */)
    return &p, err
}

Workaround 2: Timeout Pattern

Wrap rows.Next() in goroutine with timeout to return partial data gracefully:

func listProductsWithTimeout(db *sql.DB) ([]Product, error) {
    query := `SELECT ... FROM GPC.Product p LEFT JOIN ...`
    rows, _ := db.Query(query)

    var products []Product
    timedOut := false

    for {
        // Wrap rows.Next() in goroutine with timeout
        hasNext := make(chan bool, 1)
        go func() {
            hasNext <- rows.Next()
        }()

        // Wait for Next() with 2-second timeout
        var hasRow bool
        select {
        case hasRow = <-hasNext:
            if !hasRow {
                break
            }
        case <-time.After(2 * time.Second):
            // Timeout - driver hung, return partial data
            timedOut = true
            log.Printf("WARNING: rows.Next() timeout after 2s, returning %d partial results", len(products))
            break
        }

        if !hasRow {
            break
        }

        var p Product
        rows.Scan(&p.ID, &p.Name /* ... */)
        products = append(products, p)
    }

    // Close rows only if we didn't timeout (timeout leaves goroutine stuck)
    if !timedOut {
        rows.Close()
    }

    return products, nil
}

Impact: Critical - requires complete redesign of list queries or implementing timeout pattern. Lightweight list pattern is recommended as it also follows REST API best practices.

---