[DRILL-2087] Some planner option setting might produce a wrong plan and wrong query result for join query.

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 0.8.0
• Component/s: Query Planning & Optimization
• Assignee: Jinfeng Ni
• Reporter: Jinfeng Ni
• Created: 28/Jan/15 01:27
• Updated: 12/Feb/15 23:44
• Resolved: 12/Feb/15 23:44

Description

I have the following parquet data ( essentially tpch's nation/region tables, but break into multiple parts).

ls -l nation
total 24
-rw-r--r--@ 1 jni  staff  1376 Jan 27 16:47 01.parquet
-rw-r--r--@ 1 jni  staff  1400 Jan 27 16:47 02.parquet
-rw-r--r--@ 1 jni  staff  1279 Jan 27 16:47 03.parquet

ls -l region
total 24
-rw-r--r--@ 1 jni  staff  564 Jan 27 16:54 01.parquet
-rw-r--r--@ 1 jni  staff  491 Jan 27 16:54 02.parquet
-rw-r--r--@ 1 jni  staff  506 Jan 27 16:54 03.parquet

With the default planner setting, the following query produce the correct result :

0: jdbc:drill:zk=local> select t1.n_nationkey from dfs.`/Users/jni/work/data/parquet/region` t2 join dfs.`/Users/jni/work/data/parquet/nation` t1 on t1.n_regionkey = t2.r_regionkey;
SLF4J: Failed to load class "org.slf4j.impl.StaticLoggerBinder".
SLF4J: Defaulting to no-operation (NOP) logger implementation
SLF4J: See http://www.slf4j.org/codes.html#StaticLoggerBinder for further details.
+-------------+
| n_nationkey |
+-------------+
| 18          |
| 12          |
| 9           |
| 8           |
| 21          |
| 19          |
| 7           |
| 6           |
| 23          |
| 22          |
| 20          |
| 13          |
| 11          |
| 10          |
| 4           |
| 0           |
| 16          |
| 15          |
| 14          |
| 5           |
| 24          |
| 17          |
| 3           |
| 2           |
| 1           |
+-------------+
25 rows selected (2.057 seconds)

However, if I modify the planner option (`planner.slice_target` and `planner.join.row_count_estimate_factor` ) as following, then the same query would return 0 row, which is not correct.

0: jdbc:drill:zk=local> alter session set `planner.slice_target` = 10;
+------------+------------+
|     ok     |  summary   |
+------------+------------+
| true       | planner.slice_target updated. |
+------------+------------+
1 row selected (0.093 seconds)
0: jdbc:drill:zk=local> alter session set `planner.join.row_count_estimate_factor` = 0.1;
+------------+------------+
|     ok     |  summary   |
+------------+------------+
| true       | planner.join.row_count_estimate_factor updated. |
+------------+------------+
1 row selected (0.041 seconds)
0: jdbc:drill:zk=local> select t1.n_nationkey from dfs.`/Users/jni/work/data/parquet/region` t2 join dfs.`/Users/jni/work/data/parquet/nation` t1 on t1.n_regionkey = t2.r_regionkey;
+-------------+
| n_nationkey |
+-------------+
+-------------+
No rows selected (0.71 seconds)

If we look at explain plan result, after changing the planner option, we will see for the JOIN operator, the LEFT does not have any EXCHANGE operator, while the RIGHT has HashToRandomExchange operator. That seems to be not a valid plan.

00-00    Screen
00-01      UnionExchange
01-01        Project(n_nationkey=[$2])
01-02          HashJoin(condition=[=($1, $0)], joinType=[inner])
01-04            Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/Users/jni/work/data/parquet/region]], selectionRoot=/Users/jni/work/data/parquet/region, numFiles=1, columns=[`r_regionkey`]]])
01-03            HashToRandomExchange(dist0=[[$0]])
02-01              Project(n_regionkey=[$1], n_nationkey=[$0])
02-02                Scan(groupscan=[ParquetGroupScan [entries=[ReadEntryWithPath [path=file:/Users/jni/work/data/parquet/nation]], selectionRoot=/Users/jni/work/data/parquet/nation, numFiles=1, columns=[`n_regionkey`, `n_nationkey`]]])

The cause of this problem seems to be that Drill will remove EXCHANGE operator under some conditions. That condition does not guarantee that a JOIN operator always either has EXCHANGE on both sides, or none.