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

Use setObject(int, Object, int) when binding parameters

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: avatica-1.19.0
    • Component/s: avatica
    • Labels:
      None

      Description

      Trying to capture some discussion from a recent pull request: https://github.com/apache/calcite/pull/209#issuecomment-195025402

      In a few places (such as https://github.com/apache/calcite/blob/master/avatica/server/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java#L795-L800), we perform:

      TypedValue o = parameterValues.get(i);
      preparedStatement.setObject(i + 1, o.toJdbc(calendar));
      

      Vladimir stated that this is ambiguous (stored procedures differing by argument list and differentiating between the actual type when the value is null) and would be remedied by passing along the desired type when setting the object.

      We may also have to invoke setNull explicitly? This is unclear to me.

      Reasons why "explicit sql type" is important
      Calling the proper function

      Consider database has two functions that differ in type of argument only.
      For instance compute(varchar), compute(numeric), and compute(user_defined_struct)

      Which one should be executed if calling with just preparedStatement.setObject(i, null)?
      There is not enough information for the database to choose between varchar and numeric function.

      Performance

      Execution plan depends on the types of bind parameters. For instance, in PostgreSQL, you must tell all the datatypes of the bind variables right in PREPARE message.
      That basically means, if you flip between datatypes, you have to use different prepared statement IDs.

      If just String val = ...; ps.setObject(1, val) is used, then for non-null it can result in String execution plan, while for null it can flip to unknown.
      Same for batched statement execution. PostgreSQL just cannot handle datatype flips right in the middle of the batch. It is handled in the pgjdbc driver, so it cuts batch in several sub batches, so it becomes less efficient.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              elserj Josh Elser
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: