Derby
  1. Derby
  2. DERBY-4357

TableFunctions provide no information to limit underlying query

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Major 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.

      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

          Chris Goodacre created issue -
          Rick Hillegas made changes -
          Field Original Value New Value
          Bug behavior facts [Performance]
          Fix Version/s 10.6.0.0 [ 12313727 ]
          Affects Version/s 10.5.3.0 [ 12314117 ]
          Affects Version/s 10.5.2.0 [ 12314116 ]
          Affects Version/s 10.5.1.1 [ 12313771 ]
          Affects Version/s 10.4.2.0 [ 12313345 ]
          Affects Version/s 10.4.1.3 [ 12313111 ]
          Rick Hillegas made changes -
          Attachment RestrictedTableFunctions.html [ 12418167 ]
          Rick Hillegas made changes -
          Attachment RestrictedTableFunctions.html [ 12418665 ]
          Rick Hillegas made changes -
          Attachment RestrictedTableFunctions.html [ 12419310 ]
          Rick Hillegas made changes -
          Attachment RestrictedTableFunctions.html [ 12420372 ]
          Rick Hillegas made changes -
          Attachment derby-4357-01-aa-publicAPI.diff [ 12422380 ]
          Rick Hillegas made changes -
          Assignee Rick Hillegas [ rhillegas ]
          Rick Hillegas made changes -
          Attachment RestrictedTableFunctions.html [ 12423473 ]
          Rick Hillegas made changes -
          Attachment derby-4357-02-ac-passThrough.diff [ 12423480 ]
          Rick Hillegas made changes -
          Issue & fix info [Newcomer] [Newcomer, Patch Available]
          Rick Hillegas made changes -
          Attachment derby-4357-02-ad-passThrough.diff [ 12423609 ]
          Rick Hillegas made changes -
          Attachment derby-4357-03-aa-hashjoin.diff [ 12423701 ]
          Rick Hillegas made changes -
          Attachment derby-4357-04-aa-moreTests.diff [ 12423920 ]
          Rick Hillegas made changes -
          Link This issue incorporates DERBY-4507 [ DERBY-4507 ]
          Rick Hillegas made changes -
          Status Open [ 1 ] Resolved [ 5 ]
          Resolution Fixed [ 1 ]
          Kathey Marsden made changes -
          Fix Version/s 10.6.1.0 [ 12313727 ]
          Knut Anders Hatlen made changes -
          Status Resolved [ 5 ] Closed [ 6 ]
          Gavin made changes -
          Workflow jira [ 12474011 ] Default workflow, editable Closed status [ 12802688 ]

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                Development