KB483657: Table Prefix with Multiple Segments is Incorrectly Quoted

Symptoms

• User uses a database that uses a multi-segmented table prefix, e.g, Snowflake, and specified the prefix in Database Instances
  

• Incorrect quoting around leads to a SQL error.
  
• Ex. tablespace.namespace. is not separated and is incorrectly quoted as "tablespace.namespace".

Glossary

• Unified Quoting
• Changing the Data Engine Version

Steps to Reproduce

• Set the table prefix to a multi-segmented value. There are multiple ways to do this; we changed the table prefix for the YR_CATEGORY_SLS table to "tablespace.namespace.": 

• Restart the Intelligence Server. This is necessary to see the changes.
• Run a report that uses the warehouse or table that you set the table prefix for. For example, create a report of "Year", "Cost".
• Execute the report and View SQL:

select    a11.YEAR_ID  YEAR_ID,
    sum(a11.TOT_COST)  WJXBFS1
from    "tablespace.namespace"."YR_CATEGORY_SLS"   a11
group by    a11.YEAR_ID
Note that because the entire prefix is quoted by double quotes, instead of each segment is quoted separately, the syntax of this query is wrong and the execution will fail.​​

Cause

This is a known issue in Strategy 2019 and earlier versions:

• When table names contains special characters, Data Engine will quote them based on Database specific pattern, in order for the query to execute correctly in the warehouse. For some database types, the quotation setting is by default turned on.
• Data Engine is always able to quote the table names and its prefix separately but did not support quoting of multi-segment table prefixes, so the entire string is quoted as a single entity. In other words, strings with segments separated by dots are not quoted by segment, but altogether.

Solution

• Upgrade to Strategy 2020 or above. We recommend the latest version.

In the new fix, multi-segment table prefixes are correctly separated and quoted.For example, tablespace.namespace. will become "tablespace"."namespace".
We provide an example of how incorrect SQL can change after the fix:

select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"

select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"

select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"

select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

select    YEAR_ID    YEAR_ID,
...
from     "tablespace.namespace"."YR_CATEGORY_SLS"

select   YEAR_ID  YEAR_ID,
...
from    "tablespace"."namespace"."YR_CATEGORY_SLS"

Strategy Internal Reference Number for this technical note is KB483657 and DE131186.