Uploaded image for project: 'Apache Drill'
  1. Apache Drill
  2. DRILL-1986

Natural join query returns wrong result

    XMLWordPrintableJSON

Details

    Description

      Natural join returns wrong result:

      0: jdbc:drill:schema=dfs> select * from `t1.json`;
      +------------+------------+------------+
      |     a1     |     b1     |     c1     |
      +------------+------------+------------+
      | 1          | 1          | 2015-01-01 |
      | 2          | 2          | 2015-01-02 |
      +------------+------------+------------+
      2 rows selected (0.087 seconds)
      
      0: jdbc:drill:schema=dfs> select * from `t2.json`;
      +------------+------------+------------+
      |     a1     |     b1     |     c1     |
      +------------+------------+------------+
      | 1          | 1          | 2015-01-01 |
      +------------+------------+------------+
      1 row selected (0.112 seconds)
      
      0: jdbc:drill:schema=dfs> select * from `t1.json` natural join `t2.json`;
      +------------+------------+------------+------------+------------+------------+
      |     a1     |     b1     |     c1     |    a10     |    b10     |    c10     |
      +------------+------------+------------+------------+------------+------------+
      +------------+------------+------------+------------+------------+------------+
      No rows selected (0.223 seconds)
      

      Equivalent inner join query returns one row:

      0: jdbc:drill:schema=dfs> select * from `t1.json` t1, `t2.json` t2 where t1.a1=t2.a1 and t1.b1=t2.b1 and t1.c1=t2.c1;
      +------------+------------+------------+------------+------------+------------+
      |     a1     |     b1     |     c1     |    a10     |    b10     |    c10     |
      +------------+------------+------------+------------+------------+------------+
      | 1          | 1          | 2015-01-01 | 1          | 1          | 2015-01-01 |
      +------------+------------+------------+------------+------------+------------+
      1 row selected (0.732 seconds)
      

      Natural join is listed as supported in our documentation.
      If we decide not to support it, we need to make sure to remove it from docs as well.

      Attachments

        Issue Links

          Activity

            People

              seanhychu Sean Hsuan-Yi Chu
              vicky Victoria Markman
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: