Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-4357

TableFunctions provide no information to limit underlying query

    Details

    • Type: Improvement
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None
    • Environment:
      ALL
    • Urgency:
      Normal
    • Issue & fix info:
      Newcomer, Patch Available
    • Bug behavior facts:
      Performance

      Description

      The API specification for TableFunctions cannot provide information to the implementer of the TableFunction about the details of the query. For example:

      (a) I defined a table function named MyFunction with columns a,b, & c
      (b) I bind the table function properly using the CREATE FUNCTION SQL.

      User executes the following SQL:

      select a,b from table ( MyFunction() ) where c = 123

      Without passing the column list and/or where clause as arguments to the table function, my implementation can not know that it only needs two of the three columns, and only rows where c = 123.

      For TableFunctions that are built to integrate distant/legacy data, the cost of the query can be prohibitive. It would be better if information regarding the columns in the select and restrictions from the where clause could be passed to the developer.

        Attachments

        1. RestrictedTableFunctions.html
          68 kB
          Rick Hillegas
        2. RestrictedTableFunctions.html
          71 kB
          Rick Hillegas
        3. RestrictedTableFunctions.html
          73 kB
          Rick Hillegas
        4. RestrictedTableFunctions.html
          73 kB
          Rick Hillegas
        5. derby-4357-01-aa-publicAPI.diff
          14 kB
          Rick Hillegas
        6. RestrictedTableFunctions.html
          80 kB
          Rick Hillegas
        7. derby-4357-02-ac-passThrough.diff
          48 kB
          Rick Hillegas
        8. derby-4357-02-ad-passThrough.diff
          56 kB
          Rick Hillegas
        9. derby-4357-03-aa-hashjoin.diff
          16 kB
          Rick Hillegas
        10. derby-4357-04-aa-moreTests.diff
          2 kB
          Rick Hillegas

          Issue Links

            Activity

              People

              • Assignee:
                rhillegas Rick Hillegas
                Reporter:
                cgoodacre Chris Goodacre
              • Votes:
                1 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: