[DRILL-5713] Doing joins on tables that share column names in a JDBC store returns incorrect results

• Type: Bug
• Status: Resolved
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 1.11.0
• Fix Version/s: 1.16.0
• Component/s: Execution - Relational Operators, Storage - JDBC
• Labels: newbie

Environment

My mac running the latest drill in embedded mode.

• Assignee: Timothy Farkas
• Reporter: Timothy Farkas
• Created: 10/Aug/17 00:32
• Updated: 03/Mar/19 12:19
• Resolved: 03/Mar/19 12:19

Description

If there are two tables in Postgres that share column names, incorrect results are returned when a join is done between the two tables.

For example if we have two tables: categories and categories2 with the following contents:

+----------------+--------------------++---------------
|categoryguid|categoryparentguid|categoryname|

+----------------+--------------------++---------------
|id1|null|restaurants|
|null|id1|food&Dining|
|id2|null|Coffee Shops|
|null|id2|food&Dining|

+----------------+--------------------++---------------

Then the following join query returns incorrectly names columns and incorrect null values:



select cat.categoryname, cat2.categoryname from postgres.public.categories cat join postgres.public.categories2 cat2 on (cat.categoryguid = cat2.categoryguid) where cat.categoryguid IS NOT NULL;

+----------------+---------------+
|categoryname|categoryname0|

+----------------+---------------+
|restaurants|null|
|Coffee Shops|null|

+----------------+---------------+