Derby
  1. Derby
  2. DERBY-2642

Convert lang/dynamicLikeOptimization.sql to JUnit

    Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.3.1.4
    • Fix Version/s: 10.3.1.4
    • Component/s: Test
    • Labels:
      None
    1. derby-2642-2.stat
      0.7 kB
      Knut Anders Hatlen
    2. derby-2642-2.diff
      63 kB
      Knut Anders Hatlen
    3. derby-2642-1.stat
      0.2 kB
      Knut Anders Hatlen
    4. derby-2642-1.diff
      21 kB
      Knut Anders Hatlen

      Activity

      Hide
      Knut Anders Hatlen added a comment -

      This test contains one call to SYSCS_GET_RUNTIMESTATISTICS with the following comment:

      – verify that like optimization being performed
      execute p2 using 'values ''%'' ';
      values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS();

      Does anyone know which part of the query plan that shows that like optimization has been performed?

      This is what the query plan in question looks like:

      Statement Name:
      null
      Statement Text:
      select id from test where vc10 like ?
      Parse Time: 0
      Bind Time: 0
      Optimize Time: 0
      Generate Time: 0
      Compile Time: 0
      Execute Time: 0
      Begin Compilation Timestamp : null
      End Compilation Timestamp : null
      Begin Execution Timestamp : null
      End Execution Timestamp : null
      Statement Execution Plan Text:
      Project-Restrict ResultSet (3):
      Number of opens = 1
      Rows seen = 7
      Rows filtered = 0
      restriction = false
      projection = true
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      Source result set:
      Project-Restrict ResultSet (2):
      Number of opens = 1
      Rows seen = 7
      Rows filtered = 0
      restriction = true
      projection = false
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      restriction time (milliseconds) = 0
      projection time (milliseconds) = 0
      Source result set:
      Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer
      Number of opens = 1
      Rows seen = 7
      Rows filtered = 0
      Fetch Size = 16
      constructor time (milliseconds) = 0
      open time (milliseconds) = 0
      next time (milliseconds) = 0
      close time (milliseconds) = 0
      next time in milliseconds/row = 0
      scan information:
      Bit set of columns fetched=

      {0, 2}

      Number of columns fetched=2
      Number of pages visited=1
      Number of rows qualified=7
      Number of rows visited=8
      Scan type=heap
      start position:
      null stop position:
      null qualifiers:
      Column[0][0] Id: 2
      Operator: <
      Ordered nulls: false
      Unknown return value: true
      Negate comparison result: true
      Column[0][1] Id: 2
      Operator: <
      Order&

      Show
      Knut Anders Hatlen added a comment - This test contains one call to SYSCS_GET_RUNTIMESTATISTICS with the following comment: – verify that like optimization being performed execute p2 using 'values ''%'' '; values SYSCS_UTIL.SYSCS_GET_RUNTIMESTATISTICS(); Does anyone know which part of the query plan that shows that like optimization has been performed? This is what the query plan in question looks like: Statement Name: null Statement Text: select id from test where vc10 like ? Parse Time: 0 Bind Time: 0 Optimize Time: 0 Generate Time: 0 Compile Time: 0 Execute Time: 0 Begin Compilation Timestamp : null End Compilation Timestamp : null Begin Execution Timestamp : null End Execution Timestamp : null Statement Execution Plan Text: Project-Restrict ResultSet (3): Number of opens = 1 Rows seen = 7 Rows filtered = 0 restriction = false projection = true constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Project-Restrict ResultSet (2): Number of opens = 1 Rows seen = 7 Rows filtered = 0 restriction = true projection = false constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 restriction time (milliseconds) = 0 projection time (milliseconds) = 0 Source result set: Table Scan ResultSet for TEST at read committed isolation level using instantaneous share row locking chosen by the optimizer Number of opens = 1 Rows seen = 7 Rows filtered = 0 Fetch Size = 16 constructor time (milliseconds) = 0 open time (milliseconds) = 0 next time (milliseconds) = 0 close time (milliseconds) = 0 next time in milliseconds/row = 0 scan information: Bit set of columns fetched= {0, 2} Number of columns fetched=2 Number of pages visited=1 Number of rows qualified=7 Number of rows visited=8 Scan type=heap start position: null stop position: null qualifiers: Column [0] [0] Id: 2 Operator: < Ordered nulls: false Unknown return value: true Negate comparison result: true Column [0] [1] Id: 2 Operator: < Order&
      Hide
      Knut Anders Hatlen added a comment -

      The last part of the query plan (truncated in the output above because the test sets maximumdisplaywidth too low) looks like this:

      qualifiers:
      Column[0][0] Id: 2
      Operator: <
      Ordered nulls: false
      Unknown return value: true
      Negate comparison result: true
      Column[0][1] Id: 2
      Operator: <
      Ordered nulls: false
      Unknown return value: false
      Negate comparison result: false

      I assume this means that the scan has two qualifiers >= (that is, < negated) and <. Is this what identifies the query plan as optimized for like?

      Show
      Knut Anders Hatlen added a comment - The last part of the query plan (truncated in the output above because the test sets maximumdisplaywidth too low) looks like this: qualifiers: Column [0] [0] Id: 2 Operator: < Ordered nulls: false Unknown return value: true Negate comparison result: true Column [0] [1] Id: 2 Operator: < Ordered nulls: false Unknown return value: false Negate comparison result: false I assume this means that the scan has two qualifiers >= (that is, < negated) and <. Is this what identifies the query plan as optimized for like?
      Hide
      A B added a comment -

      I think the optimization in question here is described:

      http://db.apache.org/derby/docs/dev/tuning/rtuntransform472.html

      In particular, note the following line:

      "In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate,
      one predicate using the >= operator, and one predicate using the < operator."

      This correlates to what you have noted about the qualifiers, so my guess is that Yes, this is what demonstrates the "dynamic like optimization".

      Show
      A B added a comment - I think the optimization in question here is described: http://db.apache.org/derby/docs/dev/tuning/rtuntransform472.html In particular, note the following line: "In this situation, the LIKE predicate is likewise transformed into three predicates: one LIKE predicate, one predicate using the >= operator, and one predicate using the < operator." This correlates to what you have noted about the qualifiers, so my guess is that Yes, this is what demonstrates the "dynamic like optimization".
      Hide
      Knut Anders Hatlen added a comment -

      Thanks Army! I'll add an isDynamicLikeOptimized() method to the RuntimeStatisticsParser class and try to convert the test to JUnit.

      Show
      Knut Anders Hatlen added a comment - Thanks Army! I'll add an isDynamicLikeOptimized() method to the RuntimeStatisticsParser class and try to convert the test to JUnit.
      Hide
      Knut Anders Hatlen added a comment -

      Attaching the first version of DynamicLikeOptimizationTest. It covers most of what dynamicLikeOptimization.sql tests, except that it's still missing a couple of queries which cast the pattern or escape character to VARCHAR, and it doesn't check the query plans. I will add tests for that later.

      DynamicLikeOptimizationTest runs in both embedded mode and client/server mode (the old test only runs in embedded).

      Show
      Knut Anders Hatlen added a comment - Attaching the first version of DynamicLikeOptimizationTest. It covers most of what dynamicLikeOptimization.sql tests, except that it's still missing a couple of queries which cast the pattern or escape character to VARCHAR, and it doesn't check the query plans. I will add tests for that later. DynamicLikeOptimizationTest runs in both embedded mode and client/server mode (the old test only runs in embedded).
      Hide
      Knut Anders Hatlen added a comment -

      Committed revision 539744.

      Show
      Knut Anders Hatlen added a comment - Committed revision 539744.
      Hide
      Knut Anders Hatlen added a comment -

      Attaching patch which adds a test which casts the pattern or escape character, and a test which checks the query plan. The patch also removes the old test.

      Show
      Knut Anders Hatlen added a comment - Attaching patch which adds a test which casts the pattern or escape character, and a test which checks the query plan. The patch also removes the old test.
      Hide
      Knut Anders Hatlen added a comment -

      Committed revision 540856.

      Show
      Knut Anders Hatlen added a comment - Committed revision 540856.

        People

        • Assignee:
          Knut Anders Hatlen
          Reporter:
          Knut Anders Hatlen
        • Votes:
          0 Vote for this issue
          Watchers:
          0 Start watching this issue

          Dates

          • Created:
            Updated:
            Resolved:

            Development