Pushdown of joins in Apache Drill against DB2 with schemas

[Magnus Pierre <mp...@maprtech.com>]

Pushdown of joins in Apache Drill against DB2 is not done correctly if querying tables stored in schemas:

The query:
SELECT
	c.COUNTRY_CODE 
FROM
	gs_db2.GOSALES.COUNTRY as c
INNER JOIN
	gs_db2.GOSALES.SALES_TARGET t
ON 	c.COUNTRY_CODE = t.COUNTRY_CODE_RETAILER

is converted to:

SELECT 
	*
FROM 
	"GOSALES"."COUNTRY"
INNER JOIN 
	"GOSALES"."SALES_TARGET” 
ON 	"COUNTRY"."COUNTRY_CODE" = "SALES_TARGET".”COUNTRY_CODE_RETAILER"

When pushed down by Drill, which is not correct since:

Any table name references must contain schema name if schemas is used and no alias for the table exists i.e. ”GOSALES”.”COUNTRY”,”COUNTRY_CODE” instead of just ”COUNTRY”.”COUNTRY_CODE"

Since I am a database guy with close to 20 years with SQL as my primary work tool, I can not help to provide some small hints regarding the SQL generated: (Slightly OT but might be worth considering)

Quite many databases does not like to have fully qualified table names for the same table multiple times in the query. Some optimizers can actually decide to bring in the table multiple times (I’ve seen this happen on TD for instance) since it is strictly not the same table reference, it just refers to the same table which means you leave the choice to the optimizer and optimizers are often stupid. If you want to be strictly by the book in SQL and help the DB optimizer to understand the query, you define an alias for the tables and reference the alias wherever using that instance of the table to ensure it is the ”right instance” of the table you refer to.

Example:
SELECT 
	*
FROM 
	"GOSALES".”COUNTRY” AS "COUNTRY"
INNER JOIN 
	"GOSALES".”SALES_TARGET” AS ”SALES_TARGET" 
ON 	"COUNTRY"."COUNTRY_CODE" = "SALES_TARGET".”COUNTRY_CODE_RETAILER”

This is a valid query and there’s no risk of the optimizer believing it can opt to include the table N times.

Another limit by not using aliases for tables in the pushed down query, is that you can only use a table once and only once in a query which is not very good since many times you refer to the same table multiple times but with different constraints or filters for different perspectives to be combined.

Last:
The * reference to columns of the table, and with two or more tables is not really a good option since column names might be duplicated and cause strange errors esp if they have different types. Maybe drill is good at this, but many databases are not good at dealing with duplicated column names in the same query, and you retrieve far more data than necessary to Apache Drill. 
IMHO: A possible solution to this since metadata is available about the tables and the columns to be retrieved by the query, and since most SQL knowledgeable persons always provide aliases for columns when retrieving same column twice to distinguish between them, the aliases are also available to be used in the pushed-down query, but this of course also means that join push down possibly need to rewrite the SELECT part of the query as well as pushing down fields in order to fully support pushdown of joins without errors and that might be hard.

Regards,
Magnus