Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1050

Avatica can't serialize java.sql.Array

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 1.5.0
    • Fix Version/s: avatica-1.10.0
    • Component/s: avatica
    • Labels:
      None

      Description

      As far as I can see, there is no way to serialize arrays in the Avatica RPC.

        Issue Links

          Activity

          Hide
          elserj Josh Elser added a comment -

          This is done by Frame for JSON and ColumnValue for Protobufs. For JSON, each value in the row attribute of a frame might be an array instead of a scalar value.

          I have noticed in CALCITE-1040, however, that the parsing of scalar or array values is busted for arrays of length 1 with protobuf. Apparently I never wrote up a test for this. I'm currently working on adding an array_value and scalar_value to ColumnValue for protobuf to handle this conversion correctly.

          Does this make sense to you?

          Show
          elserj Josh Elser added a comment - This is done by Frame for JSON and ColumnValue for Protobufs. For JSON, each value in the row attribute of a frame might be an array instead of a scalar value. I have noticed in CALCITE-1040 , however, that the parsing of scalar or array values is busted for arrays of length 1 with protobuf. Apparently I never wrote up a test for this. I'm currently working on adding an array_value and scalar_value to ColumnValue for protobuf to handle this conversion correctly. Does this make sense to you?
          Hide
          lukaslalinsky Lukas Lalinsky added a comment -

          Unfortunately, it doesn't. In order to submit ExecuteRequest, I need to get a list of TypedValue and I don't see anything in the code that would be parsing or serializing arrays. Do you have an example how to send an array to TypedValue in JSON?

          I can't really use the protobuf interface, because it's using the beta version of proto3, which would make my code very hard to install.

          Show
          lukaslalinsky Lukas Lalinsky added a comment - Unfortunately, it doesn't. In order to submit ExecuteRequest , I need to get a list of TypedValue and I don't see anything in the code that would be parsing or serializing arrays. Do you have an example how to send an array to TypedValue in JSON? I can't really use the protobuf interface, because it's using the beta version of proto3, which would make my code very hard to install.
          Hide
          elserj Josh Elser added a comment -

          In order to submit ExecuteRequest, I need to get a list of TypedValue

          So, you're saying that you cannot execute a prepared query with an array substituted in? I'd like to make sure I understand exactly what you're trying to do before digging into this. The more clarification you can provide on what exactly you are trying to do would be extremely helpful.

          I can't really use the protobuf interface, because it's using the beta version of proto3, which would make my code very hard to install.

          Sorry to hear that. There are published tarballs for the protobuf releases, but I guess you're lamenting packages provided by your operating system? Even so, you could build the python libs and include them in your python module, no? It seems like something that could be solved in the packaging of your phoenixdb project. Am I misrepresenting this problem? Protobuf is the way of the future as far as Avatica is concerned. I'd like to make sure the path forward isn't unnecessarily difficult.

          Show
          elserj Josh Elser added a comment - In order to submit ExecuteRequest, I need to get a list of TypedValue So, you're saying that you cannot execute a prepared query with an array substituted in? I'd like to make sure I understand exactly what you're trying to do before digging into this. The more clarification you can provide on what exactly you are trying to do would be extremely helpful. I can't really use the protobuf interface, because it's using the beta version of proto3, which would make my code very hard to install. Sorry to hear that. There are published tarballs for the protobuf releases, but I guess you're lamenting packages provided by your operating system? Even so, you could build the python libs and include them in your python module, no? It seems like something that could be solved in the packaging of your phoenixdb project. Am I misrepresenting this problem? Protobuf is the way of the future as far as Avatica is concerned. I'd like to make sure the path forward isn't unnecessarily difficult.
          Hide
          lukaslalinsky Lukas Lalinsky added a comment - - edited

          Let's say I have a prepared statement, created like this:

          {
            "request": "prepare",
            "connectionId": "...",
            "sql": "UPSERT INTO foo (id, array_column) VALUES (?, ?)",
            "maxRowCount": 0
          }
          

          Now I need to execute the statement like this:

          {
            "request": "execute",
            "statementHandle": { ... },
            "parameterValues": [
              {"type": "INTEGER", "value": "1"},
              {"type": "???", "value": ???}
            ],
            "maxRowCount": 0
          }
          

          I don't know what to put instead of ??? for the array parameter to work. ColumnMetaData.Rep does have an ARRAY type, but it's either not used in TypedValue or I'm submitting the data in the wrong format. Initially I expected "type": "ARRAY", "value": [1,2,3] to work, but it doesn't.

          Show
          lukaslalinsky Lukas Lalinsky added a comment - - edited Let's say I have a prepared statement, created like this: { "request" : "prepare" , "connectionId" : "..." , "sql" : "UPSERT INTO foo (id, array_column) VALUES (?, ?)" , "maxRowCount" : 0 } Now I need to execute the statement like this: { "request" : "execute" , "statementHandle" : { ... }, "parameterValues" : [ { "type" : "INTEGER" , "value" : "1" }, { "type" : "???" , "value" : ???} ], "maxRowCount" : 0 } I don't know what to put instead of ??? for the array parameter to work. ColumnMetaData.Rep does have an ARRAY type, but it's either not used in TypedValue or I'm submitting the data in the wrong format. Initially I expected "type": "ARRAY", "value": [1,2,3] to work, but it doesn't.
          Hide
          elserj Josh Elser added a comment -

          Thanks for writing this up Lukas Lalinsky. This is a great example. I see what you mean now.

          On the "query results" side of things, we have Frame which which contains a List<Object> of rows. For an array, the element in the rows would be a List itself, which triggers it. I'm guessing that it was just missing on this side.

          Let me start by writing some tests to work through this. Thanks again for the bug report.

          Show
          elserj Josh Elser added a comment - Thanks for writing this up Lukas Lalinsky . This is a great example. I see what you mean now. On the "query results" side of things, we have Frame which which contains a List<Object> of rows . For an array, the element in the rows would be a List itself, which triggers it. I'm guessing that it was just missing on this side. Let me start by writing some tests to work through this. Thanks again for the bug report.
          Hide
          elserj Josh Elser added a comment -

          Ok, this is most assuredly missing the implementation. Calling setArray on the AvaticaPrepareStatement, ends up calling AvaticaSite#setArray:

            public void setArray(Array x) {
            }
          

          I need to give this some thought about how to implement, but I'm a bit scared about trying to tackle this so close to the 1.6.0 release. I may punt this to 1.7.0 since it would require a bit of code-change and testing.

          Show
          elserj Josh Elser added a comment - Ok, this is most assuredly missing the implementation. Calling setArray on the AvaticaPrepareStatement, ends up calling AvaticaSite#setArray : public void setArray(Array x) { } I need to give this some thought about how to implement, but I'm a bit scared about trying to tackle this so close to the 1.6.0 release. I may punt this to 1.7.0 since it would require a bit of code-change and testing.
          Hide
          jamestaylor James Taylor added a comment -

          Josh Elser - is this one still on your radar? Over in Phoenix/Calcite integration land, this one would help a lot.

          Show
          jamestaylor James Taylor added a comment - Josh Elser - is this one still on your radar? Over in Phoenix/Calcite integration land, this one would help a lot.
          Hide
          elserj Josh Elser added a comment -

          Yes, it is James Taylor. Sorry I haven't gotten to finishing it yet..

          Show
          elserj Josh Elser added a comment - Yes, it is James Taylor . Sorry I haven't gotten to finishing it yet..

            People

            • Assignee:
              elserj Josh Elser
              Reporter:
              lukaslalinsky Lukas Lalinsky
            • Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

              • Created:
                Updated:

                Development