S02006

Error Code

S02006

Error Message

When joining multiple tables, only the first table to be joined can be a partitioned table. RefId:S02006

Probable Causes

This error occurs when joining DFS partitioned tables. Join clause containing the partitioned table(s) cannot be nested within another join clause.

For example:

dbName = "dfs://test_multi_table_join"
if(existsDatabase(dbName)){
	dropDatabase(dbName)
}
db = database(dbName, VALUE, 1..3)

t1 = table(1..3 as id)
t2 = table(1..3 as id)
t3 = table(1..3 as id)

pt1 = db.createPartitionedTable(t1, `pt1, `id)
pt2 = db.createPartitionedTable(t1, `pt2, `id)
pt3 = db.createPartitionedTable(t1, `pt3, `id)
pt1.append!(t1)
pt2.append!(t2)
pt3.append!(t3)

// the following queries containing partitioned tables in the nested ej will throw an error
select * from ej(pt1, ej(pt2, pt3, `id), `id)
select * from ej(t1, ej(pt2, t3, `id), `id)
select * from ej(ej(pt1, pt2, `id), pt3, `id)
select * from ej(ej(pt1, pt2, `id), t3, `id)

Solutions

For versions prior to 2.00.10, use a SQL statement to load the DFS partitioned table into memory before performing the join operation, which avoids the issue of nesting the join clause on partitioned tables.

select * from pt1 inner join (select * from pt2 inner join pt3 on pt2.id=pt3.id) tmp on pt1.id=tmp.id
select * from t1 inner join (select * from pt2 inner join t3 on pt2.id=t3.id) tmp on t1.id=tmp.id

Since version 2.00.10, JOIN keywords are supported for joining multiple partitioned tables. You can also use ANSI SQL syntax as follows:

select * from pt1 inner join pt2 on pt1.id=pt2.id inner join pt3 on pt2.id=pt3.id
select * from pt1 inner join pt2 on pt1.id=pt2.id inner join t3 on pt2.id=t3.id