Skip to content
This repository has been archived by the owner on Aug 2, 2022. It is now read-only.

JOINs with aggregations in SELECT clause will return duplicate columns for each joined table #236

Open
jordanw-bq opened this issue Oct 18, 2019 · 0 comments
Assignees
Labels
BI integration Issues for integration with BI tools bug Something isn't working low SQL TABLEAU-SUPPORT

Comments

@jordanw-bq
Copy link
Contributor

Using Docker image amazon/opendistro-for-elasticsearch:1.1.0
Dataset: Flights sample data (loaded through Kibana)
SQL Plugin version: 1.1.0.1

Example Query:
SELECT SUM(e.day_of_week_i), AVG(f.AvgTicketPrice) AS Average FROM kibana_sample_data_flights f INNER JOIN kibana_sample_data_ecommerce e ON (f.dayOfWeek = e.day_of_week_i) WHERE f.OriginWeather IN ('Sunny','Rain') OR f.DestWeather IN ('Sunny','Rain')

Results:

{
  "schema": [
    {
      "name": "f.SUM(e.day_of_week_i)",
      "type": "double"
    },
    {
      "name": "f.Average",
      "type": "double"
    },
    {
      "name": "e.SUM(day_of_week_i)",
      "type": "double"
    },
    {
      "name": "e.Average",
      "type": "double"
    }
  ],
  "total": 200,
  "datarows": [
    [
      null,
      null,
      null,
      null
    ],
    [
      null,
      null,
      null,
      null
    ],
    [
      null,
      null,
      null,
      null
    ],
    ...

Expectation:
Query should only return columns as listed, instead of what seems to be the cartesian product of the joins and the selected columns.
Example results (query run with MySQL on small subset of Flights data):

| SUM(e.day_of_week_i) | Average | 
| 0.0 | 560.6304932110273 | 
@dai-chen dai-chen added BI integration Issues for integration with BI tools bug Something isn't working labels Oct 18, 2019
@chloe-zh chloe-zh self-assigned this Nov 14, 2019
@chloe-zh chloe-zh changed the title JOINs with functions in SELECT clause will return duplicate columns for each joined table JOINs with aggregations in SELECT clause will return duplicate columns for each joined table Nov 15, 2019
@dai-chen dai-chen added the SQL label Sep 11, 2020
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.
Labels
BI integration Issues for integration with BI tools bug Something isn't working low SQL TABLEAU-SUPPORT
Projects
None yet
Development

No branches or pull requests

4 participants