...
CASE statements - We do not currently support CASE statements in the Synapse SQL. We would need to extend the SQL parser to correctly handle such cases.
Jira Legacy server System JIRA serverId ba6fb084-9827-3160-8067-8ac7470f78b2 key PLFM-7797 CTE - We do not currently support common table expressions (CTE). Note: CTE can be more complex than the simple CTEs used in above examples. For example, it is possible for a CTE to include many tables, joins, unions, and sub-queries. Supporting complex CTE that includes joins, unions or sub-queries would potentially allow users to execute system debilitating queries. However, all of the examples above are simple an only involve a single table with aggregation. Such a simple CTE poses little to no risk. Therefore, we would like to add CTE support to runtime queries. This means a materialized view would not be needed to use a CTE.
Jira Legacy server System JIRA serverId ba6fb084-9827-3160-8067-8ac7470f78b2 key PLFM-7798 Optimize Materialized Views - Currently, when we rebuild a materialized view (MV), we lock users from querying it while we rebuild it. For small MVs that only rebuild occasionally, this is not a problem. However, this use case will require potentially large MVs (millions of rows). If it takes 30 mins to rebuild the MV used for a cohort builder, after a file updates, it is unlikely that we want users to be unable to use the cohort builder during a rebuild. On the other hand users are unlikely to notices if the data in the cohort builder is slightly stale. Therefore, we propose adding an optimization to MVs that would allow the existing MV to remain query-able during the rebuild process. After the rebuild is finished, the stale MV could be atomically, swapped with the newly build MV.
Jira Legacy server System JIRA serverId ba6fb084-9827-3160-8067-8ac7470f78b2 key PLFM-7799