KB485576: Error Message: The SQL query generated exceeds the maximum length

Description

An error message is seen that a SQL has exceeded the maximum length supported when a intelligent cube/report is executed having 2 metrics from same base table with same metric qualification and also has at least another metric from same base table with different metric qualification and VLDB settings at the project database instance is set as below.
 

Steps to reproduce

• Set the below VLDB setting at the database instance to 4th option. "No Count Distinct"

•  Project Configuration > Database Instance > SQL Data warehouses >  VLDB properties > Metrics > Separate COUNT DISTINCT
• Create a report/cube having 2 metrics which has same metric qualification and based on same fact, there are also other metrics which has different metric qualification and based on different fact, and for some of the metrics, the join type settings are inner join, for others the join type settings are outer join.
  
• 4 metrics: 2 with same fact and same metric qualification, 1 with different fact and same metric qualification, 1 with same fact and different metric qualification
  
• Inner join/outer join
• Compound metric with the above 4 metrics defined inside.
• The resultant SQL can have massive number of intermediate passes causing it to exceed the limit.

Solution

To fix the data issue, upgrade to Strategy 2021 Update 5 and set the Data Engine Version to be 2021. To set Data Engine Version, refer to How to set Date Engine Version.