[GitHub] fabianmenges opened a new pull request #3892: [Bugfix]: Subquery time filter logic for sqla datasources (partitions)

[GitBox <gi...@apache.org>]

fabianmenges opened a new pull request #3892: [Bugfix]: Subquery time filter logic for sqla datasources (partitions)
URL: https://github.com/apache/incubator-superset/pull/3892
 
 
   When you create a group-by query using the UI it will create a subquery which will contain the time filter of the outer query. If you configured Superset to automatically align queries to partitions (e.g. using `time_secondary_columns` of overriding `append_timefilter` of `BaseEngineSpec` in `db_engine_spec.py`) the inner time filter will not receive the partition alignment logic from the outer filter. This fixes this.
   
   ## Example:
   We are using `append_timefilter` to automatically add a filter on the partition column `_PARTITIONTIME` when we query for the column `timestamp`. This works great for the outer query but not for the subquery.
   With this fix the time filter will get correctly passed down to the subquery.
   
   ### Before
   ```
   SELECT `accountName` AS accountName,
          timestamp AS __timestamp,
                       COUNT(*) AS count
   FROM social_metrics_service.`FlattenedSocialMetricsRecords` AS `FlattenedSocialMetricsRecords_1`
   JOIN
     (SELECT `accountName` AS accountName__,
             COUNT(*) AS mme_inner__
      FROM social_metrics_service.`FlattenedSocialMetricsRecords` AS `FlattenedSocialMetricsRecords_1`
      WHERE timestamp >= 1510185600000
        AND timestamp <= 1510866016000
      GROUP BY accountName__
      ORDER BY mme_inner__ DESC
      LIMIT 50) AS anon_1 ON `accountName` = `accountName__`
   WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2017-11-09') AND TIMESTAMP('2017-11-16')
     AND timestamp >= 1510185600000
     AND timestamp <= 1510866016000
   GROUP BY accountName,
            __timestamp
   ORDER BY count DESC
   LIMIT 50000
   ```
   ### After
   Note that the subquery also has the `_PARTITIONTIME` included
   ```
   SELECT `accountName` AS accountName,
          timestamp AS __timestamp,
                       COUNT(*) AS count
   FROM social_metrics_service.`FlattenedSocialMetricsRecords` AS `FlattenedSocialMetricsRecords_1`
   JOIN
     (SELECT `accountName` AS accountName__,
             COUNT(*) AS mme_inner__
      FROM social_metrics_service.`FlattenedSocialMetricsRecords` AS `FlattenedSocialMetricsRecords_1`
      WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2017-11-09') AND TIMESTAMP('2017-11-16')
        AND timestamp >= 1510185600000
        AND timestamp <= 1510865767000
      GROUP BY accountName__
      ORDER BY mme_inner__ DESC
      LIMIT 50) AS anon_1 ON `accountName` = `accountName__`
   WHERE _PARTITIONTIME BETWEEN TIMESTAMP('2017-11-09') AND TIMESTAMP('2017-11-16')
     AND timestamp >= 1510185600000
     AND timestamp <= 1510865767000
   GROUP BY accountName,
            __timestamp
   ORDER BY count DESC
   LIMIT 50000
   ```

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services