Array type not annotated when array type cannot be inferred in postgres

[prakol16]

Description

When an array appears in an expression but its type cannot be inferred, postgres will throw an error. SeaQuery should make sure to annotate all arrays with their type to ensure this does not happen.

Steps to Reproduce

First, create a postgresql table:

 CREATE TABLE tbl (uid text PRIMARY KEY);

Now, consider the following sea query code:

use sea_query::{SimpleExpr, ArrayType, Value, PostgresQueryBuilder, Query, ColumnRef, Alias};

#[test]
fn test_empty_array() {
    let empty_array: SimpleExpr = Value::Array(ArrayType::BigInt, Some(Box::new(vec![]))).into();
    let equality_check = empty_array.clone().eq(empty_array);
    let query = Query::select()
        .column(ColumnRef::Asterisk)
        .from(Alias::new("tbl"))
        .and_where(equality_check)
        .to_owned();
    assert_eq!(query.to_string(PostgresQueryBuilder), r#"SELECT * FROM "tbl" WHERE ARRAY [] = ARRAY []"#);
}

This test passes. However, if we try to run the resulting SQL query, we get an error:

> SELECT * FROM "tbl" WHERE ARRAY [] = ARRAY [];
ERROR:  cannot determine type of empty array
LINE 1: SELECT * FROM "tbl" WHERE ARRAY [] = ARRAY [];

Note: this bug is important because postgresql does not seem to infer the type of an empty array when inserting values, even if the column has an associated type.

Expected Behavior

The following query would work:

SELECT * FROM "tbl" WHERE ARRAY []::bigint[] = ARRAY []::bigint[]

Alternatively, sea query could introduce an explicit cast:

SELECT * FROM "tbl" WHERE CAST(ARRAY [] as bigint[]) = CAST(ARRAY [] as bigint[]);

Actual Behavior

Instead, sea query returns a query that causes an error by not annotating the type of the empty array. It is possible there are other kinds of arrays that need type annotations as well, though I can't think of any.

Reproduces How Often

Deterministic

Versions

sea-query v0.30.1

Additional Information

[prakol16]

It is possible there are other kinds of arrays that need type annotations as well, though I can't think of any.

I just thought of an example where there is a non-empty array that needs annotations: Array['{}']::jsonb[] is different from Array['{}'] (the latter is a text array). Alternatively, all json strings could be annotated as such.