Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-1917

Support column reference in "FOR SYSTEM_TIME AS OF"

    XMLWordPrintableJSON

    Details

    • Type: New Feature
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      As discussed in mailing list[1], the standard says QSTPS can’t contain a column reference. So when joining the Orders to the Products table for the price as of the time the order was placed is impossible using "FOR SYSTEM_TIME AS OF". But can be expressed using a subquery, such as:

       SELECT  *
          FROM Orders AS o
          JOIN LATERAL (SELECT * FROM ProductPrices WHERE sysStart <= O.orderTime AND sysEnd > O.orderTime) AS P
            ON o.productId = p.productId
      

      But subquery is too complex for users. We know that the standard says it can’t contain a column reference. We initialize this discuss as we would like to "extend" the standard to simplify such query:

       SELECT  *
          FROM Orders AS o
          JOIN LATERAL ProductPrices FOR SYSTEM_TIME AS OF O.orderTime AS P
            ON o.productId = p.productId
      

      [1] https://lists.apache.org/thread.html/f877f356a8365bf74ea7d8e4a171224104d653cf73861afb2901a58f@%3Cdev.calcite.apache.org%3E

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                jark Jark Wu
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated: