why?

[complexusprada]

I have 2 codes that produce same sql statement:
1.
This code works correctly.

func applyFilter(filter Filter, data map[string]interface{}, buf *bytes.Buffer) {
    var wc []string

    arrjoin := ` 
        LEFT OUTER JOIN 
            caldera_operations__arrangements 
        ON 
            caldera_operations.id = caldera_operations__arrangements.operation_id `
    endjoin := ` 
        LEFT OUTER JOIN 
            endpoints 
        ON 
            endpoints.id = caldera_operations__arrangements.endpoint_id `

    if filter.Name != nil {
        data["name"] = fmt.Sprintf("%%%s%%", *filter.Name)
        wc = append(wc, "LOWER(name) LIKE LOWER(:name)")
    }

    if filter.ArrangementID != nil {
        data["arrangement_id"] = *filter.ArrangementID
        // arrjoin = " INNER JOIN caldera_operations__arrangements ON caldera_operations.id = caldera_operations__arrangements.operation_id "
        wc = append(wc, "caldera_operations__arrangements.arrangement_id = :arrangement_id")
    }

    if filter.EndpointID != nil {
        data["endpoint_id"] = *filter.EndpointID
        // endjoin = " INNER JOIN caldera_operations__arrangements ON caldera_operations.id = caldera_operations__arrangements.operation_id " 
        wc = append(wc, "caldera_operations__arrangements.endpoint_id = :endpoint_id")
    }

    buf.WriteString(arrjoin)
    buf.WriteString(endjoin)

    if len(wc) > 0 {
        buf.WriteString(" WHERE ")
        buf.WriteString(strings.Join(wc, " AND "))
    }
}

func (s Store) Operations(ctx context.Context, page, limit int, filter Filter) ([]Operation, error) {
    var operations []Operation
    data := map[string]interface{}{
        "offset": (page - 1) * limit,
        "limit":  limit,
    }

    // const q = `
 //        SELECT  
 //            caldera_operations.id,
 //            caldera_operations.name,
 //            caldera_operations.description,
 //            caldera_operations.adversary_profile_id,
 //            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
 //            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
 //            coalesce(endpoints.name, '') AS endpoint_hostname,
 //            caldera_operations.created_at,
 //            caldera_operations.updated_at 
 //        FROM 
 //            caldera_operations
 //        LEFT OUTER JOIN 
 //            caldera_operations__arrangements 
 //        ON 
 //            caldera_operations.id = caldera_operations__arrangements.operation_id
 //        LEFT OUTER JOIN 
 //            endpoints 
 //        ON 
 //            endpoints.id = caldera_operations__arrangements.endpoint_id `

    const q = `
        SELECT  
            caldera_operations.id,
            caldera_operations.name,
            caldera_operations.description,
            caldera_operations.adversary_profile_id,
            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
            coalesce(endpoints.name, '') AS endpoint_hostname,
            caldera_operations.created_at,
            caldera_operations.updated_at 
        FROM 
            caldera_operations`

    buf := bytes.NewBufferString(q)

    applyFilter(filter, data, buf)

    buf.WriteString(" ORDER BY created_at DESC")
    buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")

    fmt.Println(buf.String())

    rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
    if err != nil {
        return nil, base.DBError(err)
    }

    for rows.Next() {
        var a Operation
        err := rows.StructScan(&a)
        if err != nil {
            return nil, base.DBError(err)
        }

        operations = append(operations, a)
    }

    return operations, nil
}

the code produces the following sql statement.

        SELECT
            caldera_operations.id,
            caldera_operations.name,
            caldera_operations.description,
            caldera_operations.adversary_profile_id,
            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
            coalesce(endpoints.name, '') AS endpoint_hostname,
            caldera_operations.created_at,
            caldera_operations.updated_at
        FROM
            caldera_operations
        LEFT OUTER JOIN
            caldera_operations__arrangements
        ON
            caldera_operations.id = caldera_operations__arrangements.operation_id
        LEFT OUTER JOIN
            endpoints
        ON
            endpoints.id = caldera_operations__arrangements.endpoint_id  WHERE caldera_operations__arrangements.arrangement_id = :arrangement_id ORDER BY created_at DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY

The second one which also produces the same sql statement for some reason doesn't work.
2.

    var operations []Operation
    data := map[string]interface{}{
        "offset": (page - 1) * limit,
        "limit":  limit,
    }

    const q = `
        SELECT  
            caldera_operations.id,
            caldera_operations.name,
            caldera_operations.description,
            caldera_operations.adversary_profile_id,
            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
            coalesce(endpoints.name, '') AS endpoint_hostname,
            caldera_operations.created_at,
            caldera_operations.updated_at 
        FROM 
            caldera_operations
        LEFT OUTER JOIN 
            caldera_operations__arrangements 
        ON 
            caldera_operations.id = caldera_operations__arrangements.operation_id
        LEFT OUTER JOIN 
            endpoints 
        ON 
            endpoints.id = caldera_operations__arrangements.endpoint_id `

    // const q = `
 //        SELECT  
 //            caldera_operations.id,
 //            caldera_operations.name,
 //            caldera_operations.description,
 //            caldera_operations.adversary_profile_id,
 //            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
 //            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
 //            coalesce(endpoints.name, '') AS endpoint_hostname,
 //            caldera_operations.created_at,
 //            caldera_operations.updated_at 
 //        FROM 
 //            caldera_operations`

    buf := bytes.NewBufferString(q)

    applyFilter(filter, data, buf)

    buf.WriteString(" ORDER BY created_at DESC")
    buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")

    fmt.Println(buf.String())

    rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
    if err != nil {
        return nil, base.DBError(err)
    }

    for rows.Next() {
        var a Operation
        err := rows.StructScan(&a)
        if err != nil {
            return nil, base.DBError(err)
        }

        operations = append(operations, a)
    }

    return operations, nil
}

func (s Store) Operations(ctx context.Context, page, limit int, filter Filter) ([]Operation, error) {
    var operations []Operation
    data := map[string]interface{}{
        "offset": (page - 1) * limit,
        "limit":  limit,
    }

    const q = `
        SELECT  
            caldera_operations.id,
            caldera_operations.name,
            caldera_operations.description,
            caldera_operations.adversary_profile_id,
            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
            coalesce(endpoints.name, '') AS endpoint_hostname,
            caldera_operations.created_at,
            caldera_operations.updated_at 
        FROM 
            caldera_operations
        LEFT OUTER JOIN 
            caldera_operations__arrangements 
        ON 
            caldera_operations.id = caldera_operations__arrangements.operation_id
        LEFT OUTER JOIN 
            endpoints 
        ON 
            endpoints.id = caldera_operations__arrangements.endpoint_id `

    // const q = `
 //        SELECT  
 //            caldera_operations.id,
 //            caldera_operations.name,
 //            caldera_operations.description,
 //            caldera_operations.adversary_profile_id,
 //            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
 //            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
 //            coalesce(endpoints.name, '') AS endpoint_hostname,
 //            caldera_operations.created_at,
 //            caldera_operations.updated_at 
 //        FROM 
 //            caldera_operations`

    buf := bytes.NewBufferString(q)

    applyFilter(filter, data, buf)

    buf.WriteString(" ORDER BY created_at DESC")
    buf.WriteString(" OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY")

    fmt.Println(buf.String())

    rows, err := sqlx.NamedQueryContext(ctx, s.db, buf.String(), data)
    if err != nil {
        return nil, base.DBError(err)
    }

    for rows.Next() {
        var a Operation
        err := rows.StructScan(&a)
        if err != nil {
            return nil, base.DBError(err)
        }

        operations = append(operations, a)
    }

    return operations, nil
}

it produces the following sql statement:

        SELECT
            caldera_operations.id,
            caldera_operations.name,
            caldera_operations.description,
            caldera_operations.adversary_profile_id,
            coalesce(caldera_operations__arrangements.endpoint_id, 0) AS endpoint_id,
            coalesce(caldera_operations__arrangements.arrangement_id, 0) AS arrangement_id,
            coalesce(endpoints.name, '') AS endpoint_hostname,
            caldera_operations.created_at,
            caldera_operations.updated_at
        FROM
            caldera_operations
        LEFT OUTER JOIN
            caldera_operations__arrangements
        ON
            caldera_operations.id = caldera_operations__arrangements.operation_id
        LEFT OUTER JOIN
            endpoints
        ON
            endpoints.id = caldera_operations__arrangements.endpoint_id  WHERE caldera_operations__arrangements.arrangement_id = :arrangement_id ORDER BY created_at DESC OFFSET :offset ROWS FETCH NEXT :limit ROWS ONLY

So only difference between these codes is that, I use buf.WriteString to include joins. The second one gives the following error:
{"time":"2024-09-10T14:36:31.523814521+05:00","level":"ERROR","msg":"ERROR: missing FROM-clause entry for table \"caldera_operations__arrangements\" (SQLSTATE 42P01)"}

Can someone explain WTf is happening o_o