[DRILL-1986] Natural join query returns wrong result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Duplicate
• Affects Version/s: 0.8.0
• Component/s: Query Planning & Optimization
• Labels: no_verified_test
• Assignee: Sean Hsuan-Yi Chu
• Reporter: Victoria Markman
• Created: 12/Jan/15 22:08
• Updated: 08/May/15 22:15
• Resolved: 29/Jan/15 22:38

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.

Issue Links

• depends upon: DRILL-1921 Throw unsupported error message some set operators that are not currently supported