Understanding CAP_SELECT_INTO and CAP_CREATE_TEMP_TABLES

[hab6]

About You:
Name: Michael Habiger
Company: Actian Corporation

Explanation of Issue

When running the TDVT suite with our JDBC custom dialect, several test cases fail because the generated SQL tries to use a temporary table in a SELECT ... INTO statement which is not supported by the Actian database.

TDVT tests that fail due to the error
Query.Sort_Alphabetic_DESC_Top1
lod.calc.1.TopN
BUGS.B340
BUGS.B1713
Filter.TopN-context

Example 1

manifest.xml

<customization name="CAP_CREATE_TEMP_TABLES" value="yes"/>
<customization name="CAP_SELECT_INTO" value="yes"/>

Generated SQL

SELECT TOP 2 "Calcs"."str0" AS "str0", SUM("Calcs"."int2") AS "z__alias__0"
INTO "zTableau_1_AC3EAD5A_0573_4A73_8051_2C156A2640FD_4_TempTable"
FROM "actian"."Calcs" "Calcs" GROUP BY 1 ORDER BY 2 DESC NULLS LAST

Result: Error

Unable to materialize temporary table: A SELECT statement with an INTO clause after
the target list may only be used by host language SQL, or in a stored (database) procedure.

Example 2

manifest.xml

<customization name="CAP_CREATE_TEMP_TABLES" value="yes"/>
<customization name="CAP_SELECT_INTO" value="no"/>

Generated SQL

SELECT TOP 2 "Calcs"."str0" AS "str0", SUM("Calcs"."int2") AS "z__alias__0"
INTO "zTableau_1_B617B611_E37F_445C_97E3_6B3419F1620D_3_TempTable"
FROM "actian"."Calcs" "Calcs" GROUP BY 1 ORDER BY 2 DESC NULLS LAST

Result: Error

Unable to materialize temporary table: A SELECT statement with an INTO clause after
the target list may only be used by host language SQL, or in a stored (database) procedure.

Example 3

manifest.xml

<customization name="CAP_CREATE_TEMP_TABLES" value="no"/>
<customization name="CAP_SELECT_INTO" value="yes"/>

Generated SQL

SELECT TOP 2 "Calcs"."str0" AS "str0", SUM("Calcs"."int2") AS "z__alias__0"
FROM "actian"."Calcs" "Calcs" GROUP BY 1 ORDER BY 2 DESC NULLS LAST

Result: Success - With only setting CAP_CREATE_TEMP_TABLES to no, all 5 of the above listed test cases now succeed since no temp table is used in the SELECT statement.

Questions

We don't understand the CAP_SELECT_INTO setting and would like know whether it should apply to this situation, i.e. preventing the generation of a SELECT ... INTO (temp table) ... statement.

We are also concerned that setting CAP_CREATE_TEMP_TABLES to no might cause problems for other complex queries.

Is the Tableau SQL generation code able to handle this differently, such as creating a temporary table and then using it with INSERT INTO (temp table) SELECT FROM (existing table) ... ?

References

Tableau Capabilities
Tableau JDBC Capability Customizations

[lukewrites]

Internal tracking: W-14212439