def test_build_query_with_rollup_multiple_dimensions(self):
queries = (
mock_dataset.query()
.widget(f.ReactTable(mock_dataset.fields.votes))
.dimension(
f.day(mock_dataset.fields.timestamp),
f.Rollup(mock_dataset.fields["candidate-id"]),
f.Rollup(mock_dataset.fields.political_party),
)
.sql
)
self.assertEqual(len(queries), 3)
with self.subTest("base query is same as without references or rollup"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'"candidate_id" "$candidate-id",'
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$candidate-id","$political_party" '
'ORDER BY "$timestamp","$candidate-id","$political_party"',
str(queries[0]),
)
with self.subTest(
"in first rollup dimension's query, the dimension is replaced with NULL"
):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'"candidate_id" "$candidate-id",'
'NULL "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$candidate-id" '
'ORDER BY "$timestamp","$candidate-id","$political_party"',
str(queries[1]),
)
with self.subTest(
"in the second rollup dimension's query, rollup dimension and all following dimensions "
"are replaced with NULL"
):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'NULL "$candidate-id",'
'NULL "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$candidate-id","$political_party"',
str(queries[2]),
)
def test_build_query_with_rollup_dimensions_and_filter_applied_on_correct_rollup_dimension(
self, ):
queries = (mock_dataset.query().widget(
f.ReactTable(mock_dataset.fields.votes)).dimension(
f.Rollup(mock_dataset.fields.political_party),
f.Rollup(f.day(mock_dataset.fields.timestamp)),
).filter(
f.OmitFromRollup(
mock_dataset.fields.timestamp.between(
date(2018, 1, 1), date(2019, 1, 1)))).sql)
self.assertEqual(len(queries), 3)
with self.subTest("base query is same as without rollup"):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'TRUNC("timestamp",\'DD\') "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' "
'GROUP BY "$political_party","$timestamp" '
'ORDER BY "$political_party","$timestamp" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
"base rollup query is same as base query minus the timestamp rollup dimension"
):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
"'_FIREANT_ROLLUP_VALUE_' \"$timestamp\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$political_party","$timestamp" '
'ORDER BY "$political_party","$timestamp" '
'LIMIT 200000',
str(queries[1]),
)
with self.subTest(
"base rollup query is same as base query minus the political party rollup dimension"
):
self.assertEqual(
"SELECT "
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
"'_FIREANT_ROLLUP_VALUE_' \"$timestamp\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$political_party","$timestamp" '
'ORDER BY "$political_party","$timestamp" '
'LIMIT 200000',
str(queries[2]),
)
def test_build_query_with_single_rollup_dimension(self):
queries = (mock_dataset.query().widget(
f.ReactTable(mock_dataset.fields.votes)).dimension(
f.Rollup(mock_dataset.fields.political_party)).sql)
self.assertEqual(len(queries), 2)
with self.subTest(
"base query is same as without references or totals"):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$political_party" '
'ORDER BY "$political_party" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
"totals dimension is replaced with _FIREANT_ROLLUP_VALUE_"):
self.assertEqual(
"SELECT "
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'ORDER BY "$political_party" '
'LIMIT 200000',
str(queries[1]),
)
def test_dimension_with_dimension_modifier_is_replaced_by_default_when_result_set_filter_is_present(
self, ):
queries = (ds.query.widget(f.Pandas(ds.fields.aggr_number)).dimension(
ds.fields.date).dimension(f.Rollup(ds.fields.boolean)).filter(
f.ResultSet(ds.fields.boolean == True)).sql)
self.assertEqual(len(queries), 2)
with self.subTest('base query is the same as without totals'):
self.assertEqual(
"SELECT "
'"date" "$date",'
"CASE WHEN \"boolean\"=true THEN 'set(boolean=true)' ELSE 'complement(boolean=true)' END \"$boolean\","
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date","$boolean" '
'ORDER BY "$date","$boolean" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
'totals dimension is replaced with _FIREANT_ROLLUP_VALUE_'):
self.assertEqual(
"SELECT "
'"date" "$date",'
'\'_FIREANT_ROLLUP_VALUE_\' "$boolean",'
'SUM("number") "$aggr_number" '
'FROM "test" '
'GROUP BY "$date" '
'ORDER BY "$date","$boolean" '
'LIMIT 200000',
str(queries[1]),
)
def test_build_query_with_rollup_dimension_and_two_filters_only_one_applied_to_rollup(
self,
):
queries = (
mock_dataset.query()
.widget(f.ReactTable(mock_dataset.fields.votes))
.dimension(
mock_dataset.fields.political_party,
f.Rollup(f.day(mock_dataset.fields.timestamp)),
)
.filter(
f.OmitFromRollup(
mock_dataset.fields.timestamp.between(
date(2018, 1, 1), date(2019, 1, 1)
)
),
mock_dataset.fields.timestamp.between(
date(2018, 3, 1), date(2019, 9, 1)
),
)
.sql
)
self.assertEqual(len(queries), 2)
with self.subTest("base query is same as without rollup with both filters"):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'TRUNC("timestamp",\'DD\') "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' "
"AND \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' "
'GROUP BY "$political_party","$timestamp" '
'ORDER BY "$political_party","$timestamp"',
str(queries[0]),
)
with self.subTest(
"base rollup query is same as base query minus the rollup dimension with one filter"
):
self.assertEqual(
"SELECT "
'"political_party" "$political_party",'
'NULL "$timestamp",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-03-01' AND '2019-09-01' "
'GROUP BY "$political_party" '
'ORDER BY "$political_party","$timestamp"',
str(queries[1]),
)
def test_does_not_group_rollup_column(self):
mssql_mock_dataset = copy.deepcopy(mock_dataset)
mssql_mock_dataset.database = MSSQLDatabase()
queries = (mssql_mock_dataset.query().widget(
f.ReactTable(mock_dataset.fields.votes)).dimension(
f.Rollup(mock_dataset.fields.political_party)).sql)
self.assertEqual(
"SELECT "
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'ORDER BY "$political_party" '
'OFFSET 0 ROWS '
'FETCH NEXT 200000 ROWS ONLY',
str(queries[1]),
)
def test_build_query_with_totals_on_dimension_and_subsequent_dimensions(
self):
queries = (mock_dataset.query().widget(
f.ReactTable(mock_dataset.fields.votes)).dimension(
f.day(mock_dataset.fields.timestamp),
f.Rollup(mock_dataset.fields["candidate-id"]),
mock_dataset.fields.political_party,
).sql)
self.assertEqual(len(queries), 2)
with self.subTest(
"base query is same as without references or totals"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'"candidate_id" "$candidate-id",'
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$candidate-id","$political_party" '
'ORDER BY "$timestamp","$candidate-id","$political_party" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest(
"all dimensions after the rolled up dimension are _FIREANT_ROLLUP_VALUE_"
):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
"'_FIREANT_ROLLUP_VALUE_' \"$candidate-id\","
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$candidate-id","$political_party" '
'LIMIT 200000',
str(queries[1]),
)
def test_build_query_with_rollup_cat_dimension_with_references_and_date_filters(
self, ):
queries = (mock_dataset.query().widget(
f.ReactTable(mock_dataset.fields.votes)).dimension(
f.day(mock_dataset.fields.timestamp)).dimension(
f.Rollup(mock_dataset.fields.political_party)).reference(
f.DayOverDay(mock_dataset.fields.timestamp)).filter(
mock_dataset.fields.timestamp.between(
date(2018, 1, 1), date(2019, 1, 1))).sql)
self.assertEqual(len(queries), 4)
with self.subTest(
"base query is same as without references or rollup"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' "
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[0]),
)
with self.subTest("reference query is shifted"):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'"political_party" "$political_party",'
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') "
"AND TIMESTAMPADD(day,-1,'2019-01-01') "
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[1]),
)
with self.subTest(
"base rollup query is same as base query minus the rollup dimension"
):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN '2018-01-01' AND '2019-01-01' "
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[2]),
)
with self.subTest(
"reference total query is shifted without the rollup dimension"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD(day,1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
"'_FIREANT_ROLLUP_VALUE_' \"$political_party\","
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
"WHERE \"timestamp\" BETWEEN TIMESTAMPADD(day,-1,'2018-01-01') "
"AND TIMESTAMPADD(day,-1,'2019-01-01') "
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$political_party" '
'LIMIT 200000',
str(queries[3]),
)
def test_build_query_with_rollup_dimension_and_a_reference(self):
queries = (
mock_dataset.query()
.widget(f.ReactTable(mock_dataset.fields.votes))
.dimension(
f.day(mock_dataset.fields.timestamp),
f.Rollup(mock_dataset.fields.political_party),
)
.reference(f.DayOverDay(mock_dataset.fields.timestamp))
.sql
)
self.assertEqual(len(queries), 4)
with self.subTest("base query is same as without references or rollup"):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'"political_party" "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party"',
str(queries[0]),
)
with self.subTest("reference query is shifted"):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'"political_party" "$political_party",'
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp","$political_party" '
'ORDER BY "$timestamp","$political_party"',
str(queries[1]),
)
with self.subTest(
"base rollup query is same as base query minus the rollup dimension"
):
self.assertEqual(
"SELECT "
'TRUNC("timestamp",\'DD\') "$timestamp",'
'NULL "$political_party",'
'SUM("votes") "$votes" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$political_party"',
str(queries[2]),
)
with self.subTest(
"reference total query is shifted without the rollup dimension"
):
self.assertEqual(
"SELECT "
"TRUNC(TIMESTAMPADD('day',1,TRUNC(\"timestamp\",'DD')),'DD') \"$timestamp\","
'NULL "$political_party",'
'SUM("votes") "$votes_dod" '
'FROM "politics"."politician" '
'GROUP BY "$timestamp" '
'ORDER BY "$timestamp","$political_party"',
str(queries[3]),
)
