Gorm Bug in array unpacking in PostgreSQL

GORM Playground Link

https://github.com/go-gorm/playground/pull/1

Description

Consider the following query:

arrValue := []string{"a","b","c"}
query.Where("test= any(array[?]::varchar[])", arrValue)

What happens is that gorm unpacks the single value into multiple values and morphs the query into this SQL:

test= any(array[($1,$2,$3)]::varchar[])

and sends each of the values as a different variables.

Unfortunately, this is not the way PG works or the driver (pgx) works. This produces the following error: ERROR: could not determine data type of parameter $1 (SQLSTATE 42P18)

If gorm produced:

test= any(array[($1)]::varchar[])

and sent the array as a single var, it would have worked.

See https://github.com/jackc/pgx/issues/629

Comment From: github-actions[bot]

The issue has been automatically marked as stale as it missing playground pull request link, which is important to help others understand your issue effectively and make sure the issue hasn't been fixed on latest master, checkout https://github.com/go-gorm/playground for details. it will be closed in 2 days if no further activity occurs. if you are asking question, please use the Question template, most likely your question already answered https://github.com/go-gorm/gorm/issues or described in the document https://gorm.io ✨ Search Before Asking ✨

Comment From: AdallomRoy

Remark: In gorm1 this exact query works

Comment From: github-actions[bot]

Comment From: AdallomRoy

Any news?

Comment From: github-actions[bot]

Comment From: nzlov

Any news?

Comment From: AdallomRoy

@jinzhu closed the issue as stale even though this is definitely a bug

Comment From: uded

I can confirm - this is a bug

Comment From: amurchick

@AdallomRoy I am have same issue. My workaround:

package types

import (
    "database/sql/driver"
    "encoding/json"
    "fmt"
)

type StringArray []string

func (a *StringArray) Scan(src interface{}) error {
    switch src := src.(type) {
    case []byte:
        return a.scanBytes(src)
    case string:
        return a.scanBytes([]byte(src))
    case nil:
        *a = nil
        return nil
    default:
        return fmt.Errorf("cannot convert %T to StringArray", src)
    }
}

func (a *StringArray) scanBytes(src []byte) error {
    *a = nil
    if len(src) == 0 {
        return nil
    }
    if len(src) < 2 {
        return fmt.Errorf("bad json: %q", src)
    }
    src[0] = '['
    src[len(src)-1] = ']'

    cnt := len(src) / 4
    if cnt == 0 {
        cnt = 4
    }
    *a = make([]string, 0, cnt)
    err := json.Unmarshal(src, a)
    return err
}

func (a StringArray) Value() (driver.Value, error) {
    if a == nil {
        return nil, nil
    }
    bytes, err := json.Marshal(&a)
    if err != nil {
        return nil, err
    }
    if len(bytes) < 2 {
        return nil, fmt.Errorf("bad json: %q", bytes)
    }
    bytes[0] = '{'
    bytes[len(bytes)-1] = '}'
    return string(bytes), nil
}

With this type you example will looks as:

arrValue := StringArray{"a","b","c"}
query.Where("test= any(?::varchar[])", arrValue)