This is an automated email from the ASF dual-hosted git repository. arivero pushed a commit to branch table-time-comparison in repository https://gitbox.apache.org/repos/asf/superset.git
commit 10c7ffdad23b5f94bfedf21a70d40cb9d0396756 Author: Antonio Rivero <[email protected]> AuthorDate: Mon Mar 11 19:04:20 2024 +0100 Table with Time Comparison: - Using left outer join instead of inner joins - Updating tests --- superset/connectors/sqla/models.py | 8 +++++--- tests/unit_tests/connectors/test_models.py | 30 +++++++++++++++--------------- 2 files changed, 20 insertions(+), 18 deletions(-) diff --git a/superset/connectors/sqla/models.py b/superset/connectors/sqla/models.py index a872f75553..c41cdf90bc 100644 --- a/superset/connectors/sqla/models.py +++ b/superset/connectors/sqla/models.py @@ -1509,15 +1509,17 @@ class SqlaTable( if col in shifted_query_b_subquery.c and col in query_a_cte.c ] final_query = sa.select(*final_selected_columns).select_from( - shifted_query_b_subquery.join(query_a_cte, sa.and_(*join_conditions)) + query_a_cte.outerjoin( + shifted_query_b_subquery, sa.and_(*join_conditions) + ) ) else: # When dealing with queries that have no columns or that are totals, # rowcounts etc we join with the 1 = 1 to create a result set that have # both sets (original and prev) final_query = sa.select(*final_selected_columns).select_from( - shifted_query_b_subquery.join( - query_a_cte, sa.literal(True) == sa.literal(True) + query_a_cte.outerjoin( + shifted_query_b_subquery, sa.literal(True) == sa.literal(True) ) ) # Transform the query as you would within get_query_str_extended diff --git a/tests/unit_tests/connectors/test_models.py b/tests/unit_tests/connectors/test_models.py index 1a176f4860..a601440e64 100644 --- a/tests/unit_tests/connectors/test_models.py +++ b/tests/unit_tests/connectors/test_models.py @@ -162,7 +162,8 @@ class TestInstantTimeComparisonQueryGeneration: query_a_results."SUM(num_girls)" AS "SUM(num_girls)", anon_1."SUM(num_boys)" AS "prev_SUM(num_boys)", anon_1."SUM(num_girls)" AS "prev_SUM(num_girls)" - FROM + FROM query_a_results + LEFT OUTER JOIN (SELECT name AS name, sum(num_boys) AS "SUM(num_boys)", sum(num_girls) AS "SUM(num_girls)" @@ -170,8 +171,7 @@ class TestInstantTimeComparisonQueryGeneration: WHERE ds >= '1983-01-01 00:00:00' AND ds < '2023-02-14 00:00:00' GROUP BY name - ORDER BY "SUM(num_boys)" DESC) AS anon_1 - JOIN query_a_results ON anon_1.name = query_a_results.name + ORDER BY "SUM(num_boys)" DESC) AS anon_1 ON anon_1.name = query_a_results.name """ simplified_query1 = " ".join(str.sql.split()).lower() simplified_query2 = " ".join(expected_str.split()).lower() @@ -201,14 +201,14 @@ class TestInstantTimeComparisonQueryGeneration: query_a_results."SUM(num_girls)" AS "SUM(num_girls)", anon_1."SUM(num_boys)" AS "prev_SUM(num_boys)", anon_1."SUM(num_girls)" AS "prev_SUM(num_girls)" - FROM + FROM query_a_results + LEFT OUTER JOIN (SELECT sum(num_boys) AS "SUM(num_boys)", sum(num_girls) AS "SUM(num_girls)" FROM my_schema.my_table WHERE ds >= '1983-01-01 00:00:00' AND ds < '2023-02-14 00:00:00' - ORDER BY "SUM(num_boys)" DESC) AS anon_1 - JOIN query_a_results ON 1 = 1 + ORDER BY "SUM(num_boys)" DESC) AS anon_1 ON 1 = 1 """ simplified_query1 = " ".join(str.sql.split()).lower() simplified_query2 = " ".join(expected_str.split()).lower() @@ -237,7 +237,8 @@ class TestInstantTimeComparisonQueryGeneration: OFFSET 0) AS rowcount_qry) SELECT query_a_results.rowcount AS rowcount, anon_1.rowcount AS prev_rowcount - FROM + FROM query_a_results + LEFT OUTER JOIN (SELECT COUNT(*) AS rowcount FROM (SELECT name AS name, @@ -247,8 +248,7 @@ class TestInstantTimeComparisonQueryGeneration: WHERE ds >= '1983-01-01 00:00:00' AND ds < '2023-02-14 00:00:00' GROUP BY name - ORDER BY "SUM(num_boys)" DESC) AS rowcount_qry) AS anon_1 - JOIN query_a_results ON 1 = 1 + ORDER BY "SUM(num_boys)" DESC) AS rowcount_qry) AS anon_1 ON 1 = 1 """ simplified_query1 = " ".join(str.sql.split()).lower() simplified_query2 = " ".join(expected_str.split()).lower() @@ -308,7 +308,8 @@ class TestInstantTimeComparisonQueryGeneration: query_a_results."SUM(num_girls)" AS "SUM(num_girls)", anon_1."SUM(num_boys)" AS "prev_SUM(num_boys)", anon_1."SUM(num_girls)" AS "prev_SUM(num_girls)" - FROM + FROM query_a_results + LEFT OUTER JOIN (SELECT name AS name, sum(num_boys) AS "SUM(num_boys)", sum(num_girls) AS "SUM(num_girls)" @@ -316,8 +317,7 @@ class TestInstantTimeComparisonQueryGeneration: WHERE ds >= '1900-01-01 00:00:00' AND ds < '1950-02-14 00:00:00' GROUP BY name - ORDER BY "SUM(num_boys)" DESC) AS anon_1 - JOIN query_a_results ON anon_1.name = query_a_results.name + ORDER BY "SUM(num_boys)" DESC) AS anon_1 ON anon_1.name = query_a_results.name """ simplified_query1 = " ".join(str.sql.split()).lower() simplified_query2 = " ".join(expected_str.split()).lower() @@ -347,7 +347,8 @@ class TestInstantTimeComparisonQueryGeneration: query_a_results."SUM(num_girls)" AS "SUM(num_girls)", anon_1."SUM(num_boys)" AS "prev_SUM(num_boys)", anon_1."SUM(num_girls)" AS "prev_SUM(num_girls)" - FROM + FROM query_a_results + LEFT OUTER JOIN (SELECT name AS name, sum(num_boys) AS "SUM(num_boys)", sum(num_girls) AS "SUM(num_girls)" @@ -355,8 +356,7 @@ class TestInstantTimeComparisonQueryGeneration: WHERE ds >= '1983-01-01 00:00:00' AND ds < '2023-02-14 00:00:00' GROUP BY name - ORDER BY "SUM(num_boys)" DESC) AS anon_1 - JOIN query_a_results ON anon_1.name = query_a_results.name + ORDER BY "SUM(num_boys)" DESC) AS anon_1 ON anon_1.name = query_a_results.name """ simplified_query1 = " ".join(str.sql.split()).lower() simplified_query2 = " ".join(expected_str.split()).lower()
