JIRA API returns 200 but query fails with java.lang.NoSuchMethodError

[saurabh-shinde]

We are trying to run following query on rest_api_3_issue table i.e. rest/api/3/issue/<issue_id> but getting following error.

2024-01-12T10:12:14.255Z    ERROR   SplitRunner-14-67   io.trino.execution.executor.TaskExecutor    Error processing Split 20240112_101210_00087_5i47d.1.0.0-0 OpenApi split (start = 3939636.608237, wall = 710 ms, cpu = 0 ms, wait = 0 ms, calls = 1)
java.lang.NoSuchMethodError: 'io.trino.spi.block.SqlMap io.trino.spi.type.MapType.getObject(io.trino.spi.block.Block, int)'
    at pl.net.was.JsonTrinoConverter.buildMap(JsonTrinoConverter.java:167)
    at pl.net.was.JsonTrinoConverter.convert(JsonTrinoConverter.java:118)
    at pl.net.was.OpenApiClient.convertJsonToRecord(OpenApiClient.java:381)
    at pl.net.was.OpenApiClient.convertJson(OpenApiClient.java:355)
    at pl.net.was.OpenApiClient$JsonResponseHandler.handle(OpenApiClient.java:336)
    at pl.net.was.OpenApiClient$JsonResponseHandler.handle(OpenApiClient.java:293)
    at io.airlift.http.client.jetty.JettyHttpClient.doExecute(JettyHttpClient.java:739)
    at io.airlift.http.client.jetty.JettyHttpClient.execute(JettyHttpClient.java:646)
    at pl.net.was.OpenApiClient.makeRequest(OpenApiClient.java:152)
    at pl.net.was.OpenApiClient.getRows(OpenApiClient.java:96)
    at pl.net.was.OpenApiRecordSetProvider.getRecordSet(OpenApiRecordSetProvider.java:74)
    at io.trino.split.RecordPageSourceProvider.createPageSource(RecordPageSourceProvider.java:50)
    at io.trino.split.PageSourceManager.createPageSource(PageSourceManager.java:61)
    at io.trino.operator.TableScanOperator.getOutput(TableScanOperator.java:296)
    at io.trino.operator.Driver.processInternal(Driver.java:395)
    at io.trino.operator.Driver.lambda$process$8(Driver.java:298)
    at io.trino.operator.Driver.tryWithLock(Driver.java:694)
    at io.trino.operator.Driver.process(Driver.java:290)
    at io.trino.operator.Driver.processForDuration(Driver.java:261)
    at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:887)
    at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:187)
    at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:555)
    at io.trino.$gen.Trino_423_e_7____20240112_090839_2.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)

From DEBUG logs, we see that API call is successfully executed (DEBUG 20240112_101210_00087_5i47d.1.0.0-0-67 pl.net.was.OpenApiRecordSetProvider Received response code 200) but query is failing.

[nineinchnick]

Which version of the plugin are you using? Can you try the latest one?

[saurabh-shinde]

We were using 1.25. Upgraded to latest version & I am getting below error now.

java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode (com.fasterxml.jackson.databind.node.ObjectNode and com.fasterxml.jackson.databind.node.ArrayNode are in unnamed module of loader io.trino.server.PluginClassLoader @1b35cd25)
    at pl.net.was.JsonTrinoConverter.convert(JsonTrinoConverter.java:127)
    at pl.net.was.OpenApiClient.convertJsonToRecord(OpenApiClient.java:404)
    at pl.net.was.OpenApiClient.convertJson(OpenApiClient.java:378)
    at pl.net.was.OpenApiClient$JsonResponseHandler.handle(OpenApiClient.java:359)
    at pl.net.was.OpenApiClient$JsonResponseHandler.handle(OpenApiClient.java:320)
    at io.airlift.http.client.jetty.JettyHttpClient.doExecute(JettyHttpClient.java:739)
    at io.airlift.http.client.jetty.JettyHttpClient.execute(JettyHttpClient.java:646)
    at pl.net.was.OpenApiClient.makeRequest(OpenApiClient.java:163)
    at pl.net.was.OpenApiClient.getRows(OpenApiClient.java:109)
    at pl.net.was.OpenApiRecordSetProvider.getRecordSet(OpenApiRecordSetProvider.java:74)
    at io.trino.split.RecordPageSourceProvider.createPageSource(RecordPageSourceProvider.java:50)
    at io.trino.split.PageSourceManager.createPageSource(PageSourceManager.java:61)
    at io.trino.operator.TableScanOperator.getOutput(TableScanOperator.java:296)
    at io.trino.operator.Driver.processInternal(Driver.java:395)
    at io.trino.operator.Driver.lambda$process$8(Driver.java:298)
    at io.trino.operator.Driver.tryWithLock(Driver.java:694)
    at io.trino.operator.Driver.process(Driver.java:290)
    at io.trino.operator.Driver.processForDuration(Driver.java:261)
    at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:887)
    at io.trino.execution.executor.PrioritizedSplitRunner.process(PrioritizedSplitRunner.java:187)
    at io.trino.execution.executor.TaskExecutor$TaskRunner.run(TaskExecutor.java:555)
    at io.trino.$gen.Trino_423_e_7____20240112_112758_2.run(Unknown Source)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635)
    at java.base/java.lang.Thread.run(Thread.java:833)

[nineinchnick]

There were some SPI changes, so the latest version probably won't work with older Trino versions. You'd have to build the latest version with Trino 423 yourself.

[saurabh-shinde]

I have rebuilt openapi connector with trino-version set to 423 & set version for dependency trino-spi to 435.

I was able to package & create connector zip. But still seeing same error.

java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode (com.fasterxml.jackson.databind.node.ObjectNode and com.fasterxml.jackson.databind.node.ArrayNode are in unnamed module of loader io.trino.server.PluginClassLoader @2378e4ba)

[nineinchnick]

I think you should use the same version for all Trino dependencies. Can you push your changes to a public fork?

[saurabh-shinde]

I tried setting trino-version to 423, but that was causing compilation errors as SqlRow & SqlMap are not present in that version. So tried this WA to see if things work.

I have created a fork & pushed changes to it as well. Link

[nineinchnick]

Yeah, it's expected that there would be some changes required to make it work with an older Trino version (or other projects based on Trino, like SEP). I checked your fork, and you haven't made any changes like that yet.

[saurabh-shinde]

Hi, So after making some changes to convert method in class JsonTrinoConverter, I was able to get past query failure due to JsonNode to ArrayNode conversion. But issue with it is that data in nested JSON is lost. Need to check & figure out how I can fix this.

[nineinchnick]

If you need any help with that, reach out to me on the Trino Slack, and remember to push any changes to your fork.

[saurabh-shinde]

I have pushed changes to fork.

[saurabh-shinde]

Hi @nineinchnick , How are columns of a "table" decided in this case? I am seeing a mismatch in 'keys' of JSON response and columns present in query result.

[nineinchnick]

Camel case gets converted to snake case, fields from request body are added with the _req suffix, fields from multiple methods for the same path can be added. Parameters are stripped from paths and fields are concatenated. Fields with same names but different types are disambiguated by adding numbers suffixes.

What differences are you seeing?

[saurabh-shinde]

Hi, Columns I see in query result:

"schema" ,"fields_to_include" ,"fields_by_keys" ,"override_screen_security" ,"expand_req" ,"update_history" ,"expand" ,"names" ,"notify_users" ,"override_editable_flag" ,"self" ,"id" ,"return_issue" ,"delete_subtasks" ,"fields" ,"issue_id_or_key" ,"key" ,"properties" ,"properties_2"

Keys in JSON response, value for fields is a nested json:

"expand" ,"id" ,"self" ,"key" ,"fields"

Reference to API

[nineinchnick]

Other columns are request parameters, so you can add predicates with them, for example to fetch a specific issue

[saurabh-shinde]

I think issue in this case is not ObjectNode to ArrayNode conversion. Even if we convert it successfully, we will be losing data.

This column/field should not be considered of type ARRAY(varchar) . It is a nested JSON & should be of type MAP

[nineinchnick]

Can you provide more details? What's the exact OpenAPI spec for that field and how it's getting converted?

[saurabh-shinde]

We are trying to onboard JIRA instance as a catalog. We are using a connector zip built from v1.28 code. We had to change trino-version to 423. After which we are seeing error java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode.

In order to get past it I had added below snippet to convert method in JsonTrinoConverter class:

if (type instanceof ArrayType arrayType) {
            if (jsonNode.isArray()) {
                return buildArray((ArrayNode) jsonNode, arrayType, schemaType);
            }
            ArrayNode arrayNode = new ArrayNode(JsonNodeFactory.instance);
            arrayNode = arrayNode.add(jsonNode);
            return buildArray(arrayNode, arrayType, schemaType);
        }

After this, query completed successfully, but fields column had no data.

On further analysis, I realised that fields column should not be defined as ARRAY(varchar) but as MAP to preserve nested JSON from response data.

Just to see if we can see ARRAY data, I tweaked above snippet to something like this:

...
        if (type instanceof ArrayType arrayType) {
            if (jsonNode.isArray()) {
                return buildArray((ArrayNode) jsonNode, arrayType, schemaType);
            }
            ArrayNode arrayNode = convertToArrayNode((ObjectNode) jsonNode);
            return buildArray(arrayNode, arrayType, schemaType);
        }
...
    private static ArrayNode convertToArrayNode(ObjectNode objectNode)
    {
        ArrayNode arrayNode = new ArrayNode(JsonNodeFactory.instance);

        // Iterate over the fields of the ObjectNode
        objectNode.fields().forEachRemaining(entry -> {
            // Add each field's value to the ArrayNode
            arrayNode.add(entry.getKey());
        });

After this, fields column had array of keys for fields nested JSON in response.

I hope this explains the issue we are seeing right now.

Pasting section for GET here. JIRA OpenAPI json

"get": {
        "deprecated": false,
        "description": "Returns the details for an issue.\n\nThe issue is identified by its ID or key, however, if the identifier doesn't match an issue, a case-insensitive search and check for moved issues is performed. If a matching issue is found its details are returned, a 302 or other redirect is **not** returned. The issue key returned in the response is the key of the issue found.\n\nThis operation can be accessed anonymously.\n\n**[Permissions](#permissions) required:**\n\n *  *Browse projects* [project permission](https://confluence.atlassian.com/x/yodKLg) for the project that the issue is in.\n *  If [issue-level security](https://confluence.atlassian.com/x/J4lKLg) is configured, issue-level security permission to view the issue.",
        "operationId": "getIssue",
        "parameters": [
          {
            "description": "The ID or key of the issue.",
            "in": "path",
            "name": "issueIdOrKey",
            "required": true,
            "schema": { "type": "string" }
          },
          {
            "description": "A list of fields to return for the issue. This parameter accepts a comma-separated list. Use it to retrieve a subset of fields. Allowed values:\n\n *  `*all` Returns all fields.\n *  `*navigable` Returns navigable fields.\n *  Any issue field, prefixed with a minus to exclude.\n\nExamples:\n\n *  `summary,comment` Returns only the summary and comments fields.\n *  `-description` Returns all (default) fields except description.\n *  `*navigable,-comment` Returns all navigable fields except comment.\n\nThis parameter may be specified multiple times. For example, `fields=field1,field2& fields=field3`.\n\nNote: All fields are returned by default. This differs from [Search for issues using JQL (GET)](#api-rest-api-3-search-get) and [Search for issues using JQL (POST)](#api-rest-api-3-search-post) where the default is all navigable fields.",
            "in": "query",
            "name": "fields",
            "schema": {
              "items": { "default": "*all", "type": "string" },
              "type": "array"
            }
          },
          {
            "description": "Whether fields in `fields` are referenced by keys rather than IDs. This parameter is useful where fields have been added by a connect app and a field's key may differ from its ID.",
            "in": "query",
            "name": "fieldsByKeys",
            "schema": { "default": false, "type": "boolean" }
          },
          {
            "description": "Use [expand](#expansion) to include additional information about the issues in the response. This parameter accepts a comma-separated list. Expand options include:\n\n *  `renderedFields` Returns field values rendered in HTML format.\n *  `names` Returns the display name of each field.\n *  `schema` Returns the schema describing a field type.\n *  `transitions` Returns all possible transitions for the issue.\n *  `editmeta` Returns information about how each field can be edited.\n *  `changelog` Returns a list of recent updates to an issue, sorted by date, starting from the most recent.\n *  `versionedRepresentations` Returns a JSON array for each version of a field's value, with the highest number representing the most recent version. Note: When included in the request, the `fields` parameter is ignored.",
            "in": "query",
            "name": "expand",
            "schema": { "type": "string" }
          },
          {
            "description": "A list of issue properties to return for the issue. This parameter accepts a comma-separated list. Allowed values:\n\n *  `*all` Returns all issue properties.\n *  Any issue property key, prefixed with a minus to exclude.\n\nExamples:\n\n *  `*all` Returns all properties.\n *  `*all,-prop1` Returns all properties except `prop1`.\n *  `prop1,prop2` Returns `prop1` and `prop2` properties.\n\nThis parameter may be specified multiple times. For example, `properties=prop1,prop2& properties=prop3`.",
            "in": "query",
            "name": "properties",
            "schema": {
              "items": { "default": "null", "type": "string" },
              "type": "array"
            }
          },
          {
            "description": "Whether the project in which the issue is created is added to the user's **Recently viewed** project list, as shown under **Projects** in Jira. This also populates the [JQL issues search](#api-rest-api-3-search-get) `lastViewed` field.",
            "in": "query",
            "name": "updateHistory",
            "schema": { "default": false, "type": "boolean" }
          }
        ],
        "responses": {
          "200": {
            "content": {
              "application/json": {
                "example": "{\"id\":\"10002\",\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/10002\",\"key\":\"ED-1\",\"fields\":{\"watcher\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/EX-1/watchers\",\"isWatching\":false,\"watchCount\":1,\"watchers\":[{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"accountId\":\"5b10a2844c20165700ede21g\",\"displayName\":\"Mia Krystof\",\"active\":false}]},\"attachment\":[{\"id\":10000,\"self\":\"https://your-domain.atlassian.net/rest/api/3/attachments/10000\",\"filename\":\"picture.jpg\",\"author\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"key\":\"\",\"accountId\":\"5b10a2844c20165700ede21g\",\"accountType\":\"atlassian\",\"name\":\"\",\"avatarUrls\":{\"48x48\":\"https://avatar-management--avatars.server-location.prod.public.atl-paas.net/initials/MK-5.png?size=48&s=48\",\"24x24\":\"https://avatar-management--avatars.server-location.prod.public.atl-paas.net/initials/MK-5.png?size=24&s=24\",\"16x16\":\"https://avatar-management--avatars.server-location.prod.public.atl-paas.net/initials/MK-5.png?size=16&s=16\",\"32x32\":\"https://avatar-management--avatars.server-location.prod.public.atl-paas.net/initials/MK-5.png?size=32&s=32\"},\"displayName\":\"Mia Krystof\",\"active\":false},\"created\":\"2024-01-18T00:44:58.335+0000\",\"size\":23123,\"mimeType\":\"image/jpeg\",\"content\":\"https://your-domain.atlassian.net/jira/rest/api/3/attachment/content/10000\",\"thumbnail\":\"https://your-domain.atlassian.net/jira/rest/api/3/attachment/thumbnail/10000\"}],\"sub-tasks\":[{\"id\":\"10000\",\"type\":{\"id\":\"10000\",\"name\":\"\",\"inward\":\"Parent\",\"outward\":\"Sub-task\"},\"outwardIssue\":{\"id\":\"10003\",\"key\":\"ED-2\",\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/ED-2\",\"fields\":{\"status\":{\"iconUrl\":\"https://your-domain.atlassian.net/images/icons/statuses/open.png\",\"name\":\"Open\"}}}}],\"description\":{\"type\":\"doc\",\"version\":1,\"content\":[{\"type\":\"paragraph\",\"content\":[{\"type\":\"text\",\"text\":\"Main order flow broken\"}]}]},\"project\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/project/EX\",\"id\":\"10000\",\"key\":\"EX\",\"name\":\"Example\",\"avatarUrls\":{\"48x48\":\"https://your-domain.atlassian.net/secure/projectavatar?size=large&pid=10000\",\"24x24\":\"https://your-domain.atlassian.net/secure/projectavatar?size=small&pid=10000\",\"16x16\":\"https://your-domain.atlassian.net/secure/projectavatar?size=xsmall&pid=10000\",\"32x32\":\"https://your-domain.atlassian.net/secure/projectavatar?size=medium&pid=10000\"},\"projectCategory\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/projectCategory/10000\",\"id\":\"10000\",\"name\":\"FIRST\",\"description\":\"First Project Category\"},\"simplified\":false,\"style\":\"classic\",\"insight\":{\"totalIssueCount\":100,\"lastIssueUpdateTime\":\"2024-01-18T00:44:53.818+0000\"}},\"comment\":[{\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/10010/comment/10000\",\"id\":\"10000\",\"author\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"accountId\":\"5b10a2844c20165700ede21g\",\"displayName\":\"Mia Krystof\",\"active\":false},\"body\":{\"type\":\"doc\",\"version\":1,\"content\":[{\"type\":\"paragraph\",\"content\":[{\"type\":\"text\",\"text\":\"Lorem ipsum dolor sit amet, consectetur adipiscing elit. Pellentesque eget venenatis elit. Duis eu justo eget augue iaculis fermentum. Sed semper quam laoreet nisi egestas at posuere augue semper.\"}]}]},\"updateAuthor\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"accountId\":\"5b10a2844c20165700ede21g\",\"displayName\":\"Mia Krystof\",\"active\":false},\"created\":\"2021-01-17T12:34:00.000+0000\",\"updated\":\"2021-01-18T23:45:00.000+0000\",\"visibility\":{\"type\":\"role\",\"value\":\"Administrators\",\"identifier\":\"Administrators\"}}],\"issuelinks\":[{\"id\":\"10001\",\"type\":{\"id\":\"10000\",\"name\":\"Dependent\",\"inward\":\"depends on\",\"outward\":\"is depended by\"},\"outwardIssue\":{\"id\":\"10004L\",\"key\":\"PR-2\",\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/PR-2\",\"fields\":{\"status\":{\"iconUrl\":\"https://your-domain.atlassian.net/images/icons/statuses/open.png\",\"name\":\"Open\"}}}},{\"id\":\"10002\",\"type\":{\"id\":\"10000\",\"name\":\"Dependent\",\"inward\":\"depends on\",\"outward\":\"is depended by\"},\"inwardIssue\":{\"id\":\"10004\",\"key\":\"PR-3\",\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/PR-3\",\"fields\":{\"status\":{\"iconUrl\":\"https://your-domain.atlassian.net/images/icons/statuses/open.png\",\"name\":\"Open\"}}}}],\"worklog\":[{\"self\":\"https://your-domain.atlassian.net/rest/api/3/issue/10010/worklog/10000\",\"author\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"accountId\":\"5b10a2844c20165700ede21g\",\"displayName\":\"Mia Krystof\",\"active\":false},\"updateAuthor\":{\"self\":\"https://your-domain.atlassian.net/rest/api/3/user?accountId=5b10a2844c20165700ede21g\",\"accountId\":\"5b10a2844c20165700ede21g\",\"displayName\":\"Mia Krystof\",\"active\":false},\"comment\":{\"type\":\"doc\",\"version\":1,\"content\":[{\"type\":\"paragraph\",\"content\":[{\"type\":\"text\",\"text\":\"I did some work here.\"}]}]},\"updated\":\"2021-01-18T23:45:00.000+0000\",\"visibility\":{\"type\":\"group\",\"value\":\"jira-developers\",\"identifier\":\"276f955c-63d7-42c8-9520-92d01dca0625\"},\"started\":\"2021-01-17T12:34:00.000+0000\",\"timeSpent\":\"3h 20m\",\"timeSpentSeconds\":12000,\"id\":\"100028\",\"issueId\":\"10002\"}],\"updated\":1,\"timetracking\":{\"originalEstimate\":\"10m\",\"remainingEstimate\":\"3m\",\"timeSpent\":\"6m\",\"originalEstimateSeconds\":600,\"remainingEstimateSeconds\":200,\"timeSpentSeconds\":400}}}",
                "schema": { "$ref": "#/components/schemas/IssueBean" }
              }
            },
            "description": "Returned if the request is successful."
          },
          "401": {
            "description": "Returned if the authentication credentials are incorrect or missing."
          },
          "404": {
            "description": "Returned if the issue is not found or the user does not have permission to view it."
          }
        },
        "security": [{ "basicAuth": [] }, { "OAuth2": ["read:jira-work"] }, {}],
        "summary": "Get issue",
        "tags": ["Issues"],
        "x-atlassian-oauth2-scopes": [
          {
            "scheme": "OAuth2",
            "scopes": ["read:jira-work"],
            "state": "Current"
          },
          {
            "scheme": "OAuth2",
            "scopes": [
              "read:issue-meta:jira",
              "read:issue-security-level:jira",
              "read:issue.vote:jira",
              "read:issue.changelog:jira",
              "read:avatar:jira",
              "read:issue:jira",
              "read:status:jira",
              "read:user:jira",
              "read:field-configuration:jira"
            ],
            "state": "Beta"
          }
        ],
        "x-atlassian-connect-scope": "READ"
      }

[nineinchnick]

Sorry, I don't understand the issue and since you applied changes to the source code, I can't help with that.

[saurabh-shinde]

Without changes, error at hand is : java.lang.ClassCastException: class com.fasterxml.jackson.databind.node.ObjectNode cannot be cast to class com.fasterxml.jackson.databind.node.ArrayNode .

As part of debugging, I was going through error & tried changes to see if we are able to get past it.

[saurabh-shinde]

I am seeing same issue with trino version 435 & connector version 1.28. Please note, no code changes were done this time. I will create another issue with all details in it, including steps performed.

[nineinchnick]

Fixed in be3741888d4d5938184bcf7db2cf4bd1668f4399

The issue was caused by an object type for the fields field that was parsed as a MapType but didn't have any actual fields. Now such types are processed as text, so it'll map to VARCHAR and will need to be parsed in the query using the SQL json functions.