(CAN DEPRECATE ISSUE) Issues raised when using the driver for creating API (especially list APIs)

[LukasGPC]

Critical Issues Report: go-irisnative v0.2.1

Environment Information

• Driver: github.com/caretdev/go-irisnative v0.2.1
• Go Version: 1.24.4 / 1.24.5
• Database: InterSystems IRIS
• Platform: Windows (issues also affect Linux/macOS)
• GitHub Repository: https://github.com/caretdev/go-irisnative

---

Executive Summary

This report documents 7 critical issues and 1 low-priority issue discovered during production development of a REST API server. All critical issues cause indefinite hangs or runtime panics, significantly impacting production viability. Each issue has documented workarounds, but they require careful query design and significantly impact development ergonomics.

Critical Issues:

1. CAST + JOINs → Indefinite Hang
2. SQL Functions (UPPER/LOWER) + JOINs → Hang
3. Parameterized UUIDs in WHERE → Panic
4. COUNT + JOINs → Panic
5. Out-of-Bounds Pagination + JOINs → Connection Leaks
6. Large Data Volume + JOINs → rows.Next() Hang
7. Invalid Foreign Keys → Indefinite Hang

Low-Priority:
8. UUID Field Type Warning Spam (cosmetic)

---

Issue 1: CAST Operations + JOINs Cause Indefinite Hang

Severity: ⚠️ CRITICAL (Most Severe)

Description:
Using CAST operations in SELECT queries that include JOIN operations causes the driver to hang indefinitely after reading all data but before returning results to the application.

Symptoms:

• Query executes successfully in IRIS SQL Prompt
• Driver reads all data from database
• Hangs indefinitely after reading, before returning results
• No error message, timeout, or panic
• Application becomes completely unresponsive
• Must force-kill application

Minimal Reproduction:

package main

import (
    "database/sql"
    "log"
    _ "github.com/caretdev/go-irisnative"
)

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

    // This query hangs indefinitely
    query := `
        SELECT
            CAST(o.Org_ID AS VARCHAR(36)),
            CAST(o.Org_Active AS INTEGER),
            ot.Org_Type_Name
        FROM GPC.Organization o
        LEFT JOIN GPC.Org_Type ot ON o.Org_Org_Type_ID = ot.Org_Type_ID
    `

    rows, err := db.Query(query) // Hangs here - never returns
    if err != nil {
        log.Fatal(err)
    }
    defer rows.Close()

    // Never reaches this point
    for rows.Next() {
        var id string
        var active int
        var typeName string
        rows.Scan(&id, &active, &typeName)
    }
}

Workaround:
Remove CAST operations from queries with JOINs and scan native IRIS types:

// Query without CAST (works fine)
query := `
    SELECT
        o.Org_ID,              -- No CAST
        o.Org_Active,          -- No CAST
        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
`

rows, err := db.Query(query) // Works fine

// Scan native types directly
var orgID string
var active bool  // Scan as bool, NOT int
var typeName string
rows.Scan(&orgID, &active, &typeName)

Note: CAST works fine in single-table queries without JOINs.

Impact: High - requires rewriting all queries with JOINs to avoid CAST, complicates type handling.

---

Issue 2: SQL Functions (UPPER/LOWER) + JOINs Cause Hang

Severity: ⚠️ CRITICAL

Description:
Using SQL functions like UPPER() or LOWER() in WHERE clauses combined with JOIN operations causes the driver to hang indefinitely.

Symptoms:

• Query hangs indefinitely
• No error message or timeout
• Application becomes unresponsive

Minimal Reproduction:

func authenticateUser(db *sql.DB, username string) error {
    // This query hangs indefinitely
    query := `
        SELECT
            u.PUser_ID, u.PUser_Name,
            ut.PUser_Type_Name,
            o.Org_Name
        FROM GPC.Platform_User u
        LEFT JOIN GPC.Platform_User_Type ut ON u.PUser_User_Type_ID = ut.PUser_Type_ID
        LEFT JOIN GPC.Organization o ON u.PUser_Organisation_ID = o.Org_ID
        WHERE UPPER(u.PUser_Name) = UPPER(?)
    `

    var userID, userName, typeName, orgName string
    err := db.QueryRow(query, username).Scan(&userID, &userName, &typeName, &orgName)
    // Hangs here - never returns

    return err
}

Workaround:
Split into separate queries - use functions only in queries without JOINs:

func authenticateUser(db *sql.DB, username string) error {
    // Query 1: Get user (no JOINs, UPPER() is safe)
    query1 := `
        SELECT PUser_ID, PUser_Name, PUser_User_Type_ID, PUser_Organisation_ID
        FROM GPC.Platform_User
        WHERE UPPER(PUser_Name) = UPPER(?)
    `
    var userID, userName, userTypeID, orgID string
    db.QueryRow(query1, username).Scan(&userID, &userName, &userTypeID, &orgID)

    // Query 2: Get user type name (separate, no JOIN)
    query2 := `SELECT PUser_Type_Name FROM GPC.Platform_User_Type WHERE PUser_Type_ID = ?`
    var typeName string
    db.QueryRow(query2, userTypeID).Scan(&typeName)

    // Query 3: Get org name (separate, no JOIN)
    query3 := `SELECT Org_Name FROM GPC.Organization WHERE Org_ID = ?`
    var orgName string
    db.QueryRow(query3, orgID).Scan(&orgName)

    return nil
}

Impact: High - requires splitting authentication/search queries into multiple round-trips, reduces performance.

---

Issue 3: Parameterized UUIDs in WHERE Clauses Cause Panic

Severity: ⚠️ CRITICAL

Description:
Using parameterized UUID placeholders (?) in WHERE clauses causes runtime panic with "index out of range" error.

Panic Message:

panic: runtime error: index out of range [30] with length 30
github.com/caretdev/go-irisnative/src/list.GetListItem

Symptoms:

• Runtime panic when executing query
• Stack trace points to go-irisnative internal code
• Only affects UUID fields (VARCHAR, INT fields work normally with parameters)

Minimal Reproduction:

func countBrandsByOrg(db *sql.DB, orgID string) (int, error) {
    var count int

    // This causes panic
    query := `SELECT COUNT(*) FROM GPC.Brand WHERE Brand_Org_ID = ?`
    err := db.QueryRow(query, orgID).Scan(&count)
    // PANIC: runtime error: index out of range [30] with length 30

    return count, err
}

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

    orgID := "12CA3331-ABA3-426B-9A4F-E4E8B5CA24F5"
    count, _ := countBrandsByOrg(db, orgID) // Panics here
}

Workaround:
Embed UUID directly in SQL string using fmt.Sprintf:

func countBrandsByOrg(db *sql.DB, orgID string) (int, error) {
    var count int

    // Workaround: Embed UUID directly in SQL string
    query := fmt.Sprintf(`SELECT COUNT(*) FROM GPC.Brand WHERE Brand_Org_ID = '%s'`, orgID)
    err := db.QueryRow(query).Scan(&count) // Works fine

    return count, err
}

Note: UUIDs are still validated by Chi router parsing and database query execution, so SQL injection risk is minimal in this specific case. However, this workaround is not ideal for general-purpose code.

Impact: High - breaks standard Go database/sql parameterized query patterns, increases SQL injection risk.

---

Issue 4: COUNT Queries with JOINs Cause Panic

Severity: ⚠️ CRITICAL

Description:
Using JOIN operations in COUNT(*) queries causes driver panic.

Symptoms:

• Runtime panic when executing COUNT with JOINs
• Application crashes

Minimal Reproduction:

func countOrganizations(db *sql.DB) (int, error) {
    var count int

    // This causes panic
    query := `
        SELECT COUNT(*)
        FROM GPC.Organization o
        LEFT JOIN GPC.Org_Type ot ON o.Org_Org_Type_ID = ot.Org_Type_ID
    `
    err := db.QueryRow(query).Scan(&count) // Panics here

    return count, err
}

Workaround:
Keep COUNT queries simple without JOINs:

func listOrganizations(db *sql.DB, page, pageSize int) ([]Organization, error) {
    // Count query - no JOINs
    var totalCount int
    countQuery := `SELECT COUNT(*) FROM GPC.Organization`
    db.QueryRow(countQuery).Scan(&totalCount)

    // Main query with JOINs
    offset := (page - 1) * pageSize
    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)
    // ... process results

    return organizations, nil
}

Impact: Medium - requires separating COUNT and main queries, complicates pagination logic.

---

Issue 5: Out-of-Bounds Pagination with JOINs Leaks Connections

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.

---

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

Severity: ⚠️ CRITICAL

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.

---

Issue 7: Invalid Foreign Key References Cause Hang

Severity: ⚠️ CRITICAL

Description:
Queries that reference invalid foreign key GUIDs (GUIDs that don't exist in the referenced table) cause the driver to hang indefinitely instead of returning an error or empty result.

Symptoms:

• Query hangs indefinitely
• No error message or timeout
• Graceful shutdown fails (30s timeout)
• Connection remains open indefinitely
• Typically discovered during authentication or data retrieval

Minimal Reproduction:

-- Step 1: Insert user with invalid org_id (GUID doesn't exist in Organization table)
INSERT INTO GPC.Platform_User (
    PUser_ID,
    PUser_Name,
    PUser_Organisation_ID
) VALUES (
    NEWID(),
    'testuser',
    '00000000-0000-0000-0000-000000000000'  -- Invalid org_id
)

func authenticateUser(db *sql.DB, username string) error {
    // Get user with invalid org_id
    var orgID string
    query1 := `SELECT PUser_Organisation_ID FROM GPC.Platform_User WHERE PUser_Name = ?`
    db.QueryRow(query1, username).Scan(&orgID)
    // orgID = "00000000-0000-0000-0000-000000000000" (doesn't exist in Organization table)

    // This query hangs indefinitely
    var orgName string
    query2 := `SELECT Org_Name FROM GPC.Organization WHERE Org_ID = ?`
    err := db.QueryRow(query2, orgID).Scan(&orgName)
    // Expected: sql.ErrNoRows
    // Actual: Hangs indefinitely ❌

    return err
}

Workaround:
Validate foreign keys exist before insert/update:

func createUser(db *sql.DB, userName, orgID string) error {
    // Validate organization exists first
    var count int
    query := `SELECT COUNT(*) FROM GPC.Organization WHERE Org_ID = ?`
    db.QueryRow(query, orgID).Scan(&count)

    if count == 0 {
        return errors.New("invalid organization ID - organization does not exist")
    }

    // Safe to insert user - org_id is valid
    insertQuery := `
        INSERT INTO GPC.Platform_User (PUser_ID, PUser_Name, PUser_Organisation_ID)
        VALUES (NEWID(), ?, ?)
    `
    _, err := db.Exec(insertQuery, userName, orgID)
    return err
}

Finding Existing Invalid Data:

-- Find users with invalid org_id
SELECT u.PUser_ID, u.PUser_Name, u.PUser_Organisation_ID
FROM GPC.Platform_User u
LEFT JOIN GPC.Organization o ON u.PUser_Organisation_ID = o.Org_ID
WHERE u.PUser_Organisation_ID IS NOT NULL
  AND o.Org_ID IS NULL;

-- Fix: Set to NULL or valid org_id
UPDATE GPC.Platform_User
SET PUser_Organisation_ID = NULL  -- or a valid org_id
WHERE PUser_ID = '<user-guid>';

Impact: High - can cause production authentication failures, requires foreign key validation before all inserts/updates.

---

Issue 8: UUID Field Type Warning Spam (Low Priority)

Severity: LOW (Cosmetic Only)

Description:
When reading UUID fields without CAST operations (which must be avoided due to Issue #1), the driver logs warnings for every UUID field read.

Warning Message:

fromODBC: invalid type: -11 - list.ListItem{size:0x24, itemType:0x1, data:[]uint8{...}}

Symptoms:

• Warning logged for every UUID field in every row
• Clutters console output and log files
• Does not affect functionality

Example:

func listOrganizations(db *sql.DB) error {
    query := `SELECT Org_ID, Org_Name FROM GPC.Organization`
    rows, _ := db.Query(query)
    defer rows.Close()

    for rows.Next() {
        var orgID, orgName string
        rows.Scan(&orgID, &orgName) // Logs warning for Org_ID field
    }
    // Console output:
    // fromODBC: invalid type: -11 - list.ListItem{...}
    // fromODBC: invalid type: -11 - list.ListItem{...}
    // fromODBC: invalid type: -11 - list.ListItem{...}
    // (repeated for every row)

    return nil
}

Workaround:
Implement filtered log writer to suppress these warnings:

type FilteredWriter struct {
    writer io.Writer
}

func (fw *FilteredWriter) Write(p []byte) (n int, err error) {
    logMessage := string(p)

    // Filter out IRIS driver UUID warnings
    if strings.Contains(logMessage, "fromODBC: invalid type: -11") {
        return len(p), nil // Suppress this message
    }

    // Pass through all other log messages
    return fw.writer.Write(p)
}

func main() {
    // Set up filtered log output
    log.SetOutput(&FilteredWriter{writer: os.Stdout})

    // ... rest of application
}

Impact: Low - cosmetic only, but significantly clutters logs in production.

---

Summary and Patterns

Statistics

• Total Critical Issues: 7 (all causing hangs or panics)
• Total Low-Priority Issues: 1 (cosmetic logging)
• Driver Version Affected: go-irisnative v0.2.1
• Production Impact: Severe - multiple workarounds required for production viability

Common Patterns Observed

1. JOIN Operations Are Problematic

JOIN operations trigger issues when combined with:

• CAST operations (Issue (CAN DEPRECATE ISSUE) Issues raised when using the driver for creating API (especially list APIs) #1) → Indefinite hang
• SQL functions like UPPER/LOWER (Issue Buffer Size Constraints / Large Data Hang (Critical Blocking Issue) #2) → Indefinite hang
• COUNT queries (Issue CAST / JOINS / Functions / Empty Dataset combinations hangs and Panics: Timeouts and Outbounds #4) → Panic
• Large data volumes (Issue SQL Functions (UPPER/LOWER) + JOINs Cause Hang **Severity:** ⚠️ CRITICAL #6) → rows.Next() hang
• Out-of-bounds pagination (Issue CAST Operations + JOINs Cause Indefinite Hang ⚠️ CRITICAL #5) → Connection leaks

2. UUID Handling Issues

Multiple issues specifically with UUID fields:

• Parameterized UUIDs in WHERE clauses (Issue Fix GUID Handling #3) → Panic
• CAST with UUIDs (subset of Issue (CAN DEPRECATE ISSUE) Issues raised when using the driver for creating API (especially list APIs) #1) → Hang
• Invalid foreign key UUIDs (Issue Parameterized UUIDs in WHERE Clauses Cause Panic **Severity:** ⚠️ CRITICAL #7) → Indefinite hang
• Cosmetic logging warnings (Issue COUNT Queries with JOINs Cause Panic **Severity:** ⚠️ CRITICAL #8) → Log spam

3. Resource Management Issues

Driver fails to properly clean up resources:

• Connection leaks with out-of-bounds pagination (Issue CAST Operations + JOINs Cause Indefinite Hang ⚠️ CRITICAL #5)
• No proper cleanup when invalid foreign keys referenced (Issue Parameterized UUIDs in WHERE Clauses Cause Panic **Severity:** ⚠️ CRITICAL #7)
• Goroutines stuck when rows.Next() hangs (Issue SQL Functions (UPPER/LOWER) + JOINs Cause Hang **Severity:** ⚠️ CRITICAL #6)

Impact on Development

These issues require:

1. Query Restructuring: Separate queries for counts, avoid CAST, avoid functions with JOINs
2. Data Validation: Validate foreign keys before insert/update, validate pagination bounds
3. Type Handling: Scan native IRIS types, handle multiple type possibilities
4. Defensive Patterns: Implement timeout wrappers, lightweight list queries
5. Testing: Extensive testing with edge cases (out-of-bounds pages, large datasets, invalid foreign keys)

All issues have documented workarounds, but they significantly impact development ergonomics and require careful architectural decisions to ensure production stability.

---

Recommended Actions

1. Issue (CAN DEPRECATE ISSUE) Issues raised when using the driver for creating API (especially list APIs) #1 (CAST + JOINs): Fix driver's result set processing to handle CAST with JOINs
2. Issue Buffer Size Constraints / Large Data Hang (Critical Blocking Issue) #2 (Functions + JOINs): Fix driver to handle SQL functions with JOIN operations
3. Issue Fix GUID Handling #3 (Parameterized UUIDs): Fix UUID parameter binding in WHERE clauses
4. Issue CAST / JOINS / Functions / Empty Dataset combinations hangs and Panics: Timeouts and Outbounds #4 (COUNT + JOINs): Fix driver to handle COUNT queries with JOIN operations
5. Issue CAST Operations + JOINs Cause Indefinite Hang ⚠️ CRITICAL #5 (Pagination Leaks): Properly clean up connections when OFFSET exceeds data
6. Issue SQL Functions (UPPER/LOWER) + JOINs Cause Hang **Severity:** ⚠️ CRITICAL #6 (Data Volume): Increase buffer capacity or implement streaming for large result sets
7. Issue Parameterized UUIDs in WHERE Clauses Cause Panic **Severity:** ⚠️ CRITICAL #7 (Invalid Foreign Keys): Return sql.ErrNoRows instead of hanging
8. Issue COUNT Queries with JOINs Cause Panic **Severity:** ⚠️ CRITICAL #8 (UUID Warnings): Suppress or fix UUID type handling to eliminate warnings

---

Additional Information

If you need additional details, reproduction cases, or have questions about any of these issues, please feel free to ask. We have extensive production logs and test cases available.

Test Environment Available:

• Full production schema available
• Reproducible test cases for all issues
• Detailed timing measurements for Issue SQL Functions (UPPER/LOWER) + JOINs Cause Hang **Severity:** ⚠️ CRITICAL #6
• Connection pool monitoring data for Issue CAST Operations + JOINs Cause Indefinite Hang ⚠️ CRITICAL #5

Thank you for maintaining this driver. These issues are blockers for production use, and we look forward to seeing them resolved.

[daimor]

that's great, but could you post issues separately, it will help better track progress

[daimor]

And would be helpful to have DDL queries as well, so I could create tables used in the examples

[LukasGPC]

Will do in the next day or too Thanks! Lukas Green Parrot Sustainability made Simple Lukas Svoboda Chief Technology Officer o: m: e: w: Auckland, NEW ZEALAND +64 21 522 945 ***@***.*** www.gpc.green From: Dmitry Maslennikov ***@***.***> Date: Friday, 7 November 2025 at 23:46 To: caretdev/go-irisnative ***@***.***> Cc: Lukas Svoboda ***@***.***>, Author ***@***.***> Subject: Re: [caretdev/go-irisnative] Issues raised when using the driver for creating API (especially list APIs) (Issue #1) [https://avatars.githubusercontent.com/u/1212251?s=20&v=4]daimor left a comment (caretdev/go-irisnative#1)<#1 (comment)> that's great, but could you post issues separately, it will help better track progress — Reply to this email directly, view it on GitHub<#1 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BPAI57YLZG4EJHSG3PR6TZT33RZ6JAVCNFSM6AAAAACLJ4L6W6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTKMBRHAZDKNBWG4>. You are receiving this because you authored the thread.

[LukasGPC]

Will do in a day or two Thanks! Lukas Green Parrot Sustainability made Simple Lukas Svoboda Chief Technology Officer o: m: e: w: Auckland, NEW ZEALAND +64 21 522 945 ***@***.*** www.gpc.green From: Dmitry Maslennikov ***@***.***> Date: Saturday, 8 November 2025 at 00:55 To: caretdev/go-irisnative ***@***.***> Cc: Lukas Svoboda ***@***.***>, Author ***@***.***> Subject: Re: [caretdev/go-irisnative] Issues raised when using the driver for creating API (especially list APIs) (Issue #1) [https://avatars.githubusercontent.com/u/1212251?s=20&v=4]daimor left a comment (caretdev/go-irisnative#1)<#1 (comment)> And would be helpful to have DDL queries as well, so I could create tables used in the examples — Reply to this email directly, view it on GitHub<#1 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/BPAI574XAJTVVIJA2T2G3V333SCDZAVCNFSM6AAAAACLJ4L6W6VHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZTKMBSGEZTKOJSG4>. You are receiving this because you authored the thread.

[LukasGPC]

This issue made be closed as a summary / duplication issue @daimor