Uploaded image for project: 'Ignite'
  1. Ignite
  2. IGNITE-14040

NATURAL JOIN not implemented correctly

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.9.1
    • Fix Version/s: None
    • Component/s: sql
    • Labels:
      None
    • Ignite Flags:
      Docs Required, Release Notes Required

      Description

      Try this:

       

      CREATE TABLE t1 (i int PRIMARY KEY, j int);
      CREATE TABLE t2 (i int PRIMARY KEY, k int);
      INSERT INTO t1 VALUES (1, 1), (2, 2);
      INSERT INTO t2 VALUES (2, 2), (3, 3);
      SELECT * FROM t1 NATURAL JOIN t2;
      

       

      It doesn't yield any results, when we should be getting:

      |I |J |K |
      |--|--|--|
      |2 |2 |2 |

      The explain plan on the SELECT seems to hint at keys and values being compared

      SELECT
       __Z0.I AS __C0_0,
       __Z0.J AS __C0_1,
       __Z1.K AS __C0_2
      FROM PUBLIC.T1 __Z0
       /* PUBLIC.T1.__SCAN_ */
      INNER JOIN PUBLIC.T2 __Z1
       /* PUBLIC."_key_PK": _KEY = PUBLIC.__Z0._KEY */
       ON 1=1
      WHERE (PUBLIC.__Z0.I = PUBLIC.__Z1.I)
       AND ((PUBLIC.__Z0._KEY = PUBLIC.__Z1._KEY)
       AND (PUBLIC.__Z0._VAL = PUBLIC.__Z1._VAL))

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              lukas.eder Lukas Eder
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: