Skip to content

[Java][FlightRPC] FlightSQL error: 'Parameter ordinal out of range' executing a prepared stmt with params #33475

Closed

Description

@asfimport

Hey again :)

I'm getting a 'parameter ordinal 1 out of range' error trying to set a parameter on the returned AvaticaPreparedStatement. Repro:

  • Open a FlightSQL JDBC connection

  • conn.prepareStatement with a SQL query containing params (e.g. INSERT INTO users (id, name) VALUES (?, ?))

  • ps.setString(1, "foo") -> above error, thrown from AvaticaPreparedStatement.getParameter(int)

    I had a bit of a dig to try to identify a potential cause:

  • the Meta.Signature passed to the AvaticaPreparedStatement on creation has an empty parameter list - this is what causes the out-of-bounds error.

  • in ArrowFlightMetaImpl.prepare, it calls newSignature, but this only takes the SQL query, and so newSignature creates the signature with the empty list. The call to ArrowFlightSqlClientHandler.prepare happens on the line after - could we pass the param Schema from this result to newSignature?

Let me know if I can help narrow this down further or help with the fix :)

James

Reporter: James Henderson / @jarohen
Assignee: David Li / @lidavidm

PRs and other links:

Note: This issue was originally created as ARROW-18300. Please see the migration documentation for further details.

Activity

  1. asfimport commented on Nov 10, 2022

    @asfimport
    Author

    David Li / @lidavidm:
    Thanks for the detailed reproduction.

    This is going to take me a bit to fix, because it looks like parameters are straight up not implemented (ArrowFlightMetaImpl#execute ignores typedValues). So it'll take me a bit to put this together and ensure it's thoroughly tested.

  2. asfimport commented on Nov 10, 2022

    @asfimport
    Author

    James Henderson / @jarohen:
    Thanks - yep, no worries, of course.

    FWIW we can't return accurate parameter types at the point of creating a prepared statement - partly because XT is a dynamic database, but also partly because we're not sure it's possible in the general case:

    • For us, we can't accurately type the params in INSERT INTO users (id, name) VALUES (?, ?) because a user is allowed to put any value in those columns - aware we might be the exception to the rule on this one. We currently say that the param Schema has two columns, both of which are 0-leg dense unions, and then refine it when the user binds some parameters, but I'm not sure how viable that workaround might be long-term.
    • More generally, even in statically typed databases, (admittedly quite contrived) queries like SELECT ? FROM foo have untypable params - Postgres, for example, throws an error if you try to get the parameter metadata through JDBC for this one.
  3. asfimport commented on Nov 10, 2022

    @asfimport
    Author

    David Li / @lidavidm:
    Yeah, and I think that's not been well-defined in the standard. ADBC, for what it's worth, uses NullType/NA to indicate that the parameter type is not known (though this is somewhat abusing the type system), and the SQLite example implementation of Flight SQL returns a dense union type (that's not really feasible if your type system is extensible like Postgres's, though).

    @jduo any thoughts here? We should clarify this in the protocol specification.

  4. asfimport commented on Nov 13, 2022

    @asfimport
    Author

    David Li / @lidavidm:
    I put up a draft PR: #14627

    It doesn't yet handle the NullType/NA convention proposed above or any types besides String.

  5. asfimport commented on Nov 28, 2022

    @asfimport
    Author

    David Li / @lidavidm:
    It looks like I'm not going to get any time to work on this in the near future. Let me know if you want to pick it up.

  6. avantgardnerio commented on Jan 26, 2023

    @avantgardnerio

    If it helps, I think it wouldn't be too hard to get Apache Ballista to return the correct schema to test against. We could definitely use this feature.

    AFAICT, this:

    Would need to call this:

    And I have some code in a private repo that is providing the schema to be deserialized (DataFusion recently implemented type inference and a method to collect the inferred schema from the parameters)

    CC @alamb

  7. avantgardnerio commented on Jan 31, 2023

    @avantgardnerio

    PTAL at #33961

  8. removed their assignment
    on Aug 10, 2023
  9. added a commit that references this issue on Nov 4, 2023
  10. added this to the 15.0.0 milestone on Nov 4, 2023
  11. lidavidm commented on Nov 4, 2023

    @lidavidm
    Member

    @jarohen in case this interests you, @aiguofer pushed this across the finish line! However it currently depends on the server providing accurate types for the parameters, so there's still more that could be improved

  12. added a commit that references this issue on Nov 13, 2023
  13. added a commit that references this issue on Feb 19, 2024
  14. added a commit that references this issue on Oct 24, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions