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

Certain OR expressions are not passed to Table indexes or Table Function initScan()

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1, 10.8.1.2, 10.8.2.2, 10.8.3.0, 10.9.1.0, 10.10.1.1
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Bug behavior facts:
      Performance, Seen in production

      Description

      Issue originally posted here:
      ====================
      http://apache-database.10148.n7.nabble.com/RestrictedVTI-initScan-does-not-pass-certain-Table-Functions-predicate-expressions-td128229.html

      Note by Rick Hillegas:
      ================
      Hi David,

      I think it's worth filing a JIRA for this issue. If the defect is shared
      by VTIs and table functions then there's a possibility that ordinary
      table scans suffer from it too. That would raise the problem's urgency.

      Thanks,
      -Rick

      Summary Description:
      ================
      Basically some WHERE clause expressions do not get passed through via RestrictedVTI.initScan().
      This can have a severe impact on memory/performance.

      (I suspect the issue may be related to logic which tries to move AND nodes to the top of the tree...?)

      Examples (I have a few more here than in the post above):

      These get passed ok in the Restriction object:

      • C1>6
      • C1>1 AND C2<'d'
      • C1>6 OR C2<'d'
      • C1>1 AND (C1<6 OR C2<'d')

      This one gets passed partially by initScan():
      C1>1 AND (C1<6 OR (C2>'e' AND C2<'d')) ===> initScan() passes only: "C1" > 1

      These do not get passed at all (initScan() Restriction argument object is null):

      • C1>6 OR (C1>1 AND C2<'d')
      • C1>6 OR ((C1>1 AND C2<'d') AND C2>'b')
      • C1 in ( 1, 4 )
      • C1 in ( 1, 4 ) OR C2>'f' – Can Derby resolve in() clauses to a list of '=' conditions ? This would be useful!

      My table function is defined as follows:
      CREATE FUNCTION TF_TEST1() RETURNS TABLE(C1 INT, C2 VARCHAR(32672)) PARAMETER STYLE DERBY_JDBC_RESULT_SET LANGUAGE JAVA NOT DETERMINISTIC READS SQL DATA EXTERNAL NAME 'core.TestTableFunctions.TF_TEST1'

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                drv David Vyvyan
              • Votes:
                2 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated: