Details

    • Type: Improvement Improvement
    • Status: Closed
    • Priority: Minor Minor
    • Resolution: Fixed
    • Affects Version/s: 10.6.1.0
    • Fix Version/s: 10.6.1.0
    • Component/s: SQL
    • Labels:
      None

      Description

      It would be nice if Derby supported the NATURAL JOIN syntax (feature F401-01 in SQL:2003).

      1. natural_preview.diff
        9 kB
        Knut Anders Hatlen
      2. natural_preview_v2.diff
        15 kB
        Knut Anders Hatlen
      3. derby-4495-1a.stat
        0.2 kB
        Knut Anders Hatlen
      4. derby-4495-1a.diff
        22 kB
        Knut Anders Hatlen

        Issue Links

          Activity

          Hide
          Knut Anders Hatlen added a comment -

          I committed the patch to trunk with revision 897190 so that people can start testing the new syntax. Problems could be reported in separate JIRA issues. Closing this issue.

          Show
          Knut Anders Hatlen added a comment - I committed the patch to trunk with revision 897190 so that people can start testing the new syntax. Problems could be reported in separate JIRA issues. Closing this issue.
          Hide
          Knut Anders Hatlen added a comment -

          The NPE when combining join operators was caused by JoinNode's late
          building of the RCL (it's not done in bindExpressions(), but in
          deferredBindExpressions()). However, the column names are available if
          you call getAllResultColumns(null) instead of getResultColumns(), so
          the solution to that problem was straightforward.

          I'm uploading a new patch (derby-4495-1a.diff) with that change, as
          well as more comments and tests. All the regression tests ran cleanly,
          so the patch is ready for review.

          Here's a description of the changes in the patch:

          JoinNode.java:

          • Added a flag for natural join.
          • In the bind phase, generate a USING clause containing all the common
            columns in the natural join.
          • In the code that transforms USING to ON (deferredBindExpressions),
            remove the implicit assumption that USING clauses contain at least
            one column name (the USING syntax requires at least one column, but
            natural joins between tables with no common columns can make us
            generate empty USING clauses internally, breaking that assumption)

          sqlgrammar.jj:

          • Factored out existing code to generate a new join node to avoid
            having to duplicate that code for natural join.
          • Added new rule naturalJoin and wired it into joinedTableExpression.

          JoinTest.java:

          • Added test cases for the new syntax. I've verified that the results
            are the same as with PostgreSQL (with the exception of the
            statements that use asterisked identifier chains - see discussion on
            DERBY-4370). There's always room for more tests. Suggestions are
            welcome.
          Show
          Knut Anders Hatlen added a comment - The NPE when combining join operators was caused by JoinNode's late building of the RCL (it's not done in bindExpressions(), but in deferredBindExpressions()). However, the column names are available if you call getAllResultColumns(null) instead of getResultColumns(), so the solution to that problem was straightforward. I'm uploading a new patch (derby-4495-1a.diff) with that change, as well as more comments and tests. All the regression tests ran cleanly, so the patch is ready for review. Here's a description of the changes in the patch: JoinNode.java: Added a flag for natural join. In the bind phase, generate a USING clause containing all the common columns in the natural join. In the code that transforms USING to ON (deferredBindExpressions), remove the implicit assumption that USING clauses contain at least one column name (the USING syntax requires at least one column, but natural joins between tables with no common columns can make us generate empty USING clauses internally, breaking that assumption) sqlgrammar.jj: Factored out existing code to generate a new join node to avoid having to duplicate that code for natural join. Added new rule naturalJoin and wired it into joinedTableExpression. JoinTest.java: Added test cases for the new syntax. I've verified that the results are the same as with PostgreSQL (with the exception of the statements that use asterisked identifier chains - see discussion on DERBY-4370 ). There's always room for more tests. Suggestions are welcome.
          Hide
          Knut Anders Hatlen added a comment -

          Uploading a new preview patch that addresses the known issues with the first patch and also adds some test cases. To get the correct expansion of asterisks, the natural join was transformed to a USING join earlier, at the end of bindExpression(). Also, the transformation of a USING clause to a join condition was modified so that it generates a valid join clause if there are no common columns (otherwise it would generate an AndNode with no children and cause a NullPointerException).

          One new problem was found: Combining NATURAL with other join operators may fail with a NullPointerException. For example, this query fails:

          SELECT * FROM T1 CROSS JOIN T2 NATURAL JOIN T3

          The problem appears to be that the RCL of the CROSS JOIN operation has not been instantiated when the USING clause for the NATURAL JOIN operation is generated. The RCL is actually still null when the expansion of asterisks happens, so simply delaying the generation of the USING clause until we need it doesn't prevent the NPE (we need to know the common columns to expand asterisks correctly).

          Show
          Knut Anders Hatlen added a comment - Uploading a new preview patch that addresses the known issues with the first patch and also adds some test cases. To get the correct expansion of asterisks, the natural join was transformed to a USING join earlier, at the end of bindExpression(). Also, the transformation of a USING clause to a join condition was modified so that it generates a valid join clause if there are no common columns (otherwise it would generate an AndNode with no children and cause a NullPointerException). One new problem was found: Combining NATURAL with other join operators may fail with a NullPointerException. For example, this query fails: SELECT * FROM T1 CROSS JOIN T2 NATURAL JOIN T3 The problem appears to be that the RCL of the CROSS JOIN operation has not been instantiated when the USING clause for the NATURAL JOIN operation is generated. The RCL is actually still null when the expansion of asterisks happens, so simply delaying the generation of the USING clause until we need it doesn't prevent the NPE (we need to know the common columns to expand asterisks correctly).
          Hide
          Knut Anders Hatlen added a comment -

          Things that are known not to work with the preview patch, are:

          • expansion of asterisks should follow rules similar to the ones for the USING clause (see DERBY-4370)
          • if there are no common columns, NATURAL JOIN should behave like CROSS JOIN, but currently throws a NullPointerException
          Show
          Knut Anders Hatlen added a comment - Things that are known not to work with the preview patch, are: expansion of asterisks should follow rules similar to the ones for the USING clause (see DERBY-4370 ) if there are no common columns, NATURAL JOIN should behave like CROSS JOIN, but currently throws a NullPointerException
          Hide
          Knut Anders Hatlen added a comment -

          Here's a quick attempt to add NATURAL JOIN. No sanity/error checking or tests, but it seems to work in the simplest case, at least:

          ij> select cast(schemaname as char(5)), cast(tablename as char(5))
          > from sys.sysschemas natural join sys.systables where schemaname = 'APP';
          1 |2
          -----------
          APP |T
          APP |T1
          APP |T2

          3 rows selected

          Show
          Knut Anders Hatlen added a comment - Here's a quick attempt to add NATURAL JOIN. No sanity/error checking or tests, but it seems to work in the simplest case, at least: ij> select cast(schemaname as char(5)), cast(tablename as char(5)) > from sys.sysschemas natural join sys.systables where schemaname = 'APP'; 1 |2 ----------- APP |T APP |T1 APP |T2 3 rows selected

            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