You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@superset.apache.org by ar...@apache.org on 2024/03/11 18:04:39 UTC
(superset) branch table-time-comparison updated: Table with Time Comparison:
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
The following commit(s) were added to refs/heads/table-time-comparison by this push:
new e4772faad0 Table with Time Comparison:
e4772faad0 is described below
commit e4772faad0e5d700877f38c1456e31a0441949ee
Author: Antonio Rivero <an...@gmail.com>
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()