Details

    • Type: New Feature
    • Status: Closed
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 1.11.0
    • Component/s: None
    • Labels:
      None

      Description

      The CROSS/OUTER APPLY is very similar to CROSS JOIN and OUTER JOIN. The difference is that the APPLY operator is used to invoke a table-valued function. This is not a standard SQL syntax , but introduced from MS SQL Server [1].

      The APPLY operator can be expressed by Calcite’s LATERAL TABLE . That means the

      SELECT MyTable.*, t.s FROM MyTable CROSS APPLY split(MyTable.a)) AS t(s)

      corresponds to :

      SELECT MyTable.*, t.s FROM MyTable, LATERAL TABLE(split(MyTable.a)) AS t(s)

      and

      SELECT MyTable.*, t.s FROM MyTable OUTER APPLY split(MyTable.a)) AS t(s)

      corresponds to:

      SELECT MyTable.*, t.s FROM MyTable LEFT JOIN LATERAL TABLE(split(MyTable.a)) AS t(s) ON TRUE

      The ON TRUE part is necessary for LEFT JOIN, but it's trivial for users. That's why I'm introducing "CROSS/OUTER APPLY" which will simplify the SQL a lot.

      As the APPLY can be expressed by LATERAL, so the only thing we need to touch is the Parser (see [2] and search FLINK to find the modification).

      [1] https://technet.microsoft.com/en-us/library/ms175156(v=sql.105).aspx
      [2] https://github.com/wuchong/flink/blob/60812e51156ec9fa6088154d2f6dea8c1ff9ac17/flink-libraries/flink-table/src/main/codegen/templates/Parser.jj

        Issue Links

          Activity

          Hide
          julianhyde Julian Hyde added a comment -

          APPLY needs to be a keyword, but does it need to be reserved? I think not (and hope not).

          Show
          julianhyde Julian Hyde added a comment - APPLY needs to be a keyword, but does it need to be reserved? I think not (and hope not).
          Hide
          jark Jark Wu added a comment -

          Yes, you are right. APPLY should be a non-reserved keyword.

          Show
          jark Jark Wu added a comment - Yes, you are right. APPLY should be a non-reserved keyword.
          Hide
          julianhyde Julian Hyde added a comment -

          As I said in the email thread, this functionality should be enabled by a new SqlConformance setting.

          Show
          julianhyde Julian Hyde added a comment - As I said in the email thread, this functionality should be enabled by a new SqlConformance setting.
          Hide
          jark Jark Wu added a comment -

          I'm not sure I understand correctly. Could you explain more about How it could be enabled by a SqlConformance setting ?

          Show
          jark Jark Wu added a comment - I'm not sure I understand correctly. Could you explain more about How it could be enabled by a SqlConformance setting ?
          Hide
          julianhyde Julian Hyde added a comment - - edited

          See https://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html. If you execute the statement select * from emp where deptno != 10 and you are not running with conformance=ORACLE_10, the method isBangEqualAllowed() will return false, and the validator will throw an error. We do this because we want people to use <> rather than != in vanilla Calcite.

          So, you'll need to add a new method to SqlConformance for this functionality.

          Show
          julianhyde Julian Hyde added a comment - - edited See https://calcite.apache.org/apidocs/org/apache/calcite/sql/validate/SqlConformance.html . If you execute the statement select * from emp where deptno != 10 and you are not running with conformance=ORACLE_10, the method isBangEqualAllowed() will return false, and the validator will throw an error. We do this because we want people to use <> rather than != in vanilla Calcite. So, you'll need to add a new method to SqlConformance for this functionality.
          Hide
          julianhyde Julian Hyde added a comment - - edited

          I notice that Oracle 12i also supports CROSS APPLY and OUTER APPLY. The right-hand side can be a sub-query or a table function, but if a table function, you have to use the usual (TABLE(...)) syntax. Thoughts on that?

          Show
          julianhyde Julian Hyde added a comment - - edited I notice that Oracle 12i also supports CROSS APPLY and OUTER APPLY . The right-hand side can be a sub-query or a table function, but if a table function, you have to use the usual (TABLE(...)) syntax. Thoughts on that?
          Hide
          jark Jark Wu added a comment -

          Thank you for the detailed explanation.

          Show
          jark Jark Wu added a comment - Thank you for the detailed explanation.
          Hide
          julianhyde Julian Hyde added a comment -

          By the way, here is a great example that we should include in the test suite:

          SELECT number,
                 doubled_number,
                 doubled_number_plus_one
          FROM master..spt_values
          CROSS APPLY (
            SELECT 2 * CAST(number AS BIGINT)
          ) CA1(doubled_number)
          CROSS APPLY (
            SELECT doubled_number + 1
          ) CA2(doubled_number_plus_one)
          
          Show
          julianhyde Julian Hyde added a comment - By the way, here is a great example that we should include in the test suite: SELECT number, doubled_number, doubled_number_plus_one FROM master..spt_values CROSS APPLY ( SELECT 2 * CAST(number AS BIGINT) ) CA1(doubled_number) CROSS APPLY ( SELECT doubled_number + 1 ) CA2(doubled_number_plus_one)
          Hide
          jark Jark Wu added a comment -

          Hi Julian Hyde, sorry for the late response. Could we support MS SQL SERVER's syntax first ? Because SQL SERVER's syntax is simpler and only support table function which will be easy to implement I think.

          Show
          jark Jark Wu added a comment - Hi Julian Hyde , sorry for the late response. Could we support MS SQL SERVER's syntax first ? Because SQL SERVER's syntax is simpler and only support table function which will be easy to implement I think.
          Hide
          julianhyde Julian Hyde added a comment -

          Am I correct that SQL Server's supports using table functions anywhere (not just in CROSS/OUTER APPLY) without the TABLE keyword? If so, yes, their syntax is simpler, but their validation is more complex, because tables and table-functions share a namespace and they have to distinguish between them. For example, if you write SELECT * FROM EMP(1, 2) it has to deduce that EMP is a table function.

          I like the idea of supporting SQL Server's more concise syntax, but let's do it for all table references, not just CROSS/OUTER APPLY, and let's add a SqlConformance setting so that people can turn that behavior on/off.

          Show
          julianhyde Julian Hyde added a comment - Am I correct that SQL Server's supports using table functions anywhere (not just in CROSS / OUTER APPLY ) without the TABLE keyword? If so, yes, their syntax is simpler, but their validation is more complex, because tables and table-functions share a namespace and they have to distinguish between them. For example, if you write SELECT * FROM EMP(1, 2) it has to deduce that EMP is a table function. I like the idea of supporting SQL Server's more concise syntax, but let's do it for all table references, not just CROSS / OUTER APPLY , and let's add a SqlConformance setting so that people can turn that behavior on/off.
          Hide
          jark Jark Wu added a comment -

          Yes, you are right, see this link.

          It seems that it conflicts with the current `LATERAL TABLE`. So we have to support the syntax of SELECT * FROM EMP(1, 2) for SQL Server's SqlConformance, right ?

          Show
          jark Jark Wu added a comment - Yes, you are right, see this link . It seems that it conflicts with the current `LATERAL TABLE`. So we have to support the syntax of SELECT * FROM EMP(1, 2) for SQL Server's SqlConformance, right ?
          Hide
          julianhyde Julian Hyde added a comment -

          Yes.

          I'm saying that you can have what you want, but let's treat it as two orthogonal features: support CROSS/OUTER APPLY, and support table functions without explicit TABLE. I have created CALCITE-1490 for the latter. Ironically, both would be enabled in the SQL Server SqlConformance. But in principle someone could enable one and not the other in their custom SqlConformance.

          Show
          julianhyde Julian Hyde added a comment - Yes. I'm saying that you can have what you want, but let's treat it as two orthogonal features: support CROSS/OUTER APPLY, and support table functions without explicit TABLE. I have created CALCITE-1490 for the latter. Ironically, both would be enabled in the SQL Server SqlConformance. But in principle someone could enable one and not the other in their custom SqlConformance.
          Hide
          julianhyde Julian Hyde added a comment -

          Reviewing the pull request, you've not kept the features orthogonal: you have implemented SQL Server table-function syntax, but only on the right-hand side of APPLY. In SQL Server you can include any table expression on the right-hand side of APPLY, including table names, sub-queries, and standard table-function syntax. We should do the same. In the parser, the right-hand size of APPLY should be TableRef(), not TableFunctionRef().

          If you want to add TableFunctionRef(), do it as part of CALCITE-1490.

          Show
          julianhyde Julian Hyde added a comment - Reviewing the pull request, you've not kept the features orthogonal: you have implemented SQL Server table-function syntax, but only on the right-hand side of APPLY. In SQL Server you can include any table expression on the right-hand side of APPLY, including table names, sub-queries, and standard table-function syntax. We should do the same. In the parser, the right-hand size of APPLY should be TableRef() , not TableFunctionRef() . If you want to add TableFunctionRef() , do it as part of CALCITE-1490 .
          Hide
          julianhyde Julian Hyde added a comment -

          Please review my branch https://github.com/julianhyde/calcite/tree/1472-apply, which documents the APPLY syntax but removes support for concise table function calls. Also adds tests for APPLY with various other relational expressions.

          Show
          julianhyde Julian Hyde added a comment - Please review my branch https://github.com/julianhyde/calcite/tree/1472-apply , which documents the APPLY syntax but removes support for concise table function calls. Also adds tests for APPLY with various other relational expressions.
          Hide
          jark Jark Wu added a comment -

          Hi Julian Hyde , I think the change is very good.

          Show
          jark Jark Wu added a comment - Hi Julian Hyde , I think the change is very good.
          Hide
          julianhyde Julian Hyde added a comment -

          Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/8355a252 (not including concise table function syntax, which is covered by CALCITE-1490). Thanks for the PR, Jark Wu!

          Show
          julianhyde Julian Hyde added a comment - Fixed in http://git-wip-us.apache.org/repos/asf/calcite/commit/8355a252 (not including concise table function syntax, which is covered by CALCITE-1490 ). Thanks for the PR, Jark Wu !
          Hide
          julianhyde Julian Hyde added a comment -

          Resolved in release 1.11.0 (2017-01-11).

          Show
          julianhyde Julian Hyde added a comment - Resolved in release 1.11.0 (2017-01-11).

            People

            • Assignee:
              julianhyde Julian Hyde
              Reporter:
              jark Jark Wu
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved:

                Development