JDBC not mapping properly in 4.5.0

[harispdev]

Curl request on the database:

/usr/bin/curl -on/json' -sSXPOST '127.0.0.1:4200/_sql?pretty' -d@- << EOF
 {"stmt":"SELECT allowed_users FROM myproject.projects WHERE allowed_users != [NULL]"}
EOF

Result:

{"cols":["allowed_users"],"rows":[[[{"accepted":false,"role":"developer","email":"email@gmail.com"}]]],
"rowcount":1,"duration":50.122208}

Java line on which error occurs:

Object[] array = (Object[]) rs.getArray("allowed_users").getArray();

Java model AllowedUser:

public class AllowedUser {

    @JsonProperty
    String email;
    @JsonProperty
    String role;
    @JsonProperty
    Boolean accepted;

    public String getEmail() {
        return email;
    }

    public void setEmail(String email) {
        this.email = email;
    }

    public String getRole() {
        return role;
    }

    public void setRole(String role) {
        this.role = role;
    }

    public Boolean getAccepted() {
        return accepted;
    }

    public void setAccepted(Boolean accepted) {
        this.accepted = accepted;
    }
}

Error that Crate returns:

Unrecognized field "type" (class models.AllowedUser), not marked as ignorable 
(3 known properties: "email", "role", "accepted"]) at [Source: (String)"[{"type":"json","value":
"{\"accepted\":false,\"email\":\"email@gmail.com\",\"role\":\"developer\"}"}]"; line: 1, column: 11] 
(through reference chain: java.util.ArrayList[0]->models.AllowedUser["type"])

Column allowed_users defined as:

allowed_users ARRAY(OBJECT(IGNORED))

Locally and on the QA server Crate doesn't return any errors and works as expected. The issue occurs only on the production server. The dataset is identical on all envs.

Local and QA Crate version: 4.0.10
Production Crate version: 4.5.0

[proddata]

My suspicion is that this is connected to the change how Object arrays are handled from 4.2

Remap CrateDB object array data type from the PostgreSQL JSON to JSON array type. That might effect some drivers that use the PostgreSQL wire protocol to insert data into tables with object array typed columns. For instance, when using the Npgsql driver, it is not longer possible to insert an array of objects into a column of the object array data type by using the parameter of a SQL statement that has the JSON data type and an array of CLR as its value. Instead, use a string array with JSON strings that represent the objects. See the Npgsql documentation for more details.
https://crate.io/docs/crate/reference/en/4.5/appendices/release-notes/4.2.0.html

The query

SELECT allowed_users FROM myproject.projects WHERE allowed_users != [NULL]

returns 1 row in 4.0.10 and 4.1.8
but 0 rows in 4.2.7 / 4.3.x / 4.5.0

Steps do reproduce

CREATE TABLE myproject.projects (
  allowed_users ARRAY(OBJECT(IGNORED))
 );

INSERT INTO myproject.projects (
  allowed_users) VALUES (['{"accepted":false,"role":"developer","email":"email@gmail.com"}']);
 
SELECT allowed_users FROM myproject.projects WHERE allowed_users != [NULL]

[proddata]

btw ... using OBJECT(DYNAMIC) also works with 4.5

CREATE TABLE myproject.projects (
  allowed_users ARRAY(OBJECT(DYNAMIC))
 );

SELECT allowed_users FROM myproject.projects WHERE allowed_users != [NULL]

returns 1 row

[proddata]

could be related to #10746

[harispdev]

Hi, thanks for the reply. I forgot to mention, I'm using io.crate.client.jdbc.CrateDriver not the PostgreSQL one.

btw ... using OBJECT(DYNAMIC) also works with 4.5

ARRAY(OBJECT(DYNAMIC)) did not solve the issue, the same error still occurs. The error occurs when trying to read the data, insertion works perfectly. If I try and query the data via curl it always returns the same number of rows as the 4.0.10 version.

The error is dropped in Java from CrateDriver when I try to read the inserted data.

When preparing a statement for the insertion the following line was used:

Database.getSQLConnection().createArrayOf("object", project.getAllowedUsers().toArray()

The getSQLConnection method is defined as:

public static Connection getSQLConnection() throws ClassNotFoundException, SQLException {

        Class.forName("io.crate.client.jdbc.CrateDriver");
        Connection connection = DriverManager.getConnection("crate://127.0.0.1:5432/?user=crate");
        connection.setSchema("myproject");

        return connection;
 }

I used the not NULL query to simply filter out the other data, to keep the question as short as possible. In the actual project that part is never used, I just query all the data like so:

SELECT * FROM projects ORDER BY created DESC

[proddata]

@harispdev
You are using crate-jdbc 2.6.0?

I can't really reproduce this unfortunately.

Object[] array = (Object[]) rs.getArray("allowed_users").getArray();

works as intended

import java.sql.*;
import java.util.Properties;

public class Main {

    public static void main(String[] args) {

        try {
            Properties props = new Properties();
            props.put("user","crate");
            Connection conn = DriverManager.getConnection("crate://localhost:5432/",props);
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT allowed_users FROM myproject.projects3 where allowed_users != [null];");
            resultSet.next();
            Object[] array = (Object[]) resultSet.getArray("allowed_users").getArray();

            System.out.println(array.length);

        } catch (SQLException e) {
            System.out.println( e);
        }

    }
}

returns 1
without any exception

---

This seems to me more like a problem with the mapping, but not crate / jdbc.

Could you maybe share a minimal example to reproduce this and the complete exception?

[harispdev]

@proddata Georg you seem to be right, after some additional tests, it looks like the error actually occurs indeed when trying to map the data:

ObjectMapper mapper = new ObjectMapper();
String jsonArray = mapper.writeValueAsString(array);
List<AllowedUser> allowedUsers = mapper.readValue(jsonArray, new TypeReference<List<AllowedUser>>() {});

[proddata]

CrateDB 4.0.10:

CrateDB 4.1.8:

[proddata]

So this is indeed this to be related to this change to make CrateDB more compatible with standard PostgreSQL

Remap CrateDB object array data type from the PostgreSQL JSON to JSON array type. That might effect some drivers that use the PostgreSQL wire protocol to insert data into tables with object array typed columns.

[proddata]

I'm sorry, perhaps I'm missing something obvious, but how can I map this with Jackson?

Something like that:

public class Main {

    public static void main(String[] args) {

        try {
            Properties props = new Properties();
            props.put("user","crate");
            Connection conn = DriverManager.getConnection("crate://localhost:5432/",props);
            Statement statement = conn.createStatement();
            ResultSet resultSet = statement.executeQuery("SELECT allowed_users FROM myproject.projects where allowed_users != [null];");
            resultSet.next();
            Object[] array = (Object[]) resultSet.getArray("allowed_users").getArray();

            ObjectMapper objectMapper = new ObjectMapper();
            objectMapper.disable(DeserializationFeature.FAIL_ON_UNKNOWN_PROPERTIES);
            AllowedUser p = objectMapper.readValue( array[0].toString(), AllowedUser.class);

            System.out.println("Email: "+p.email);
            System.out.println("Role: "+p.role);
            System.out.println("Accepted: "+p.accepted);

        } catch (SQLException e) {
            System.out.println( e);
        } catch (JsonParseException e) {
            e.printStackTrace();
        } catch (JsonMappingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}

class AllowedUser {
    @JsonProperty
    String email;
    @JsonProperty
    String role;
    @JsonProperty
    Boolean accepted;
}

---

I will close this issue now, as this is neither a bug report, nor feature request for CrateDB.
I would kindly as you to open a new discussion on https://community.crate.io/ for that matter

[harispdev]

@proddata thank you so much for your patience and your assistance Georg. I've managed to solve the issue and
If anyone comes across the same issue, you need to loop your data and parse the Object from the (Object[] array) to a method similar to this:

public static AllowedUser mapPGToModel(Object object) throws JsonProcessingException {
        PGobject pGobject = (PGobject) object;

        ObjectMapper mapper = new ObjectMapper();
        return mapper.readValue(pGobject.getValue(), AllowedUser.class);
}