Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-2501

window functions in view return incorrect results

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Blocker
    • Resolution: Done
    • Affects Version/s: 7.5
    • Fix Version/s: 8.4, 7.7.8
    • Component/s: Query Engine
    • Labels:
      None

      Description

      conditions and limits above a view are too broadly allowed to be pushed through a view layer containing window functions.

      From the forum posting for example:

      SELECT
              "StateProvinceID"
              , COUNT(*) OVER (PARTITION BY a."CountryRegionCode") AS num
          FROM salestaxrate a
          WHERE "TaxType" = 3

      returns the correct counts, where as

      SELECT *
      FROM
      (
          SELECT
              "StateProvinceID"
              , COUNT(*) OVER (PARTITION BY a."CountryRegionCode") AS num
          FROM salestaxrate a
          WHERE "TaxType" = 3
      ) x
      WHERE "StateProvinceID" = 45
      

      returns counts where the state province id condition is applied before the windowing.

      The currently logic will only prevent the criteria from being pushed if it is directly applied against a computed window value.

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                shawkins Steven Hawkins
              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: