Request: Data-Modifying CTEs

[successfulmike]

Request:
I'd like to request the connection.execute_query function permit CTEs (Common Table Expressions) with DML operations as noted in below examples on Postgre's main webpage (see section 7.8.2. Data-Modifying Statements in WITH)
https://www.postgresql.org/docs/13/queries-with.html#QUERIES-WITH-MODIFYING

Purpose:
We'd like to have the ability to persist the original records before they're updated or deleted within the same query using the RETURNING clause. This seems to also be allowed in existing Tableau Hyper API document (https://help.tableau.com/current/api/hyper_api/en-us/reference/sql/sql-delete.html); trying this today (tableauhyperapi 0.0.14265) throws an exception

tableauhyperapi.hyperexception.HyperException: syntax error: got "delete", expected "explain", "select", "table", "values", "with", "with ", '(:'

Alternative (in case of technical limitation):
First call the connection.execute_query function that returns the rows that will be updated or deleted and persist them in a Results object (dataframe), then call the connection.execute_command function that performs the update or delete operation (using same filter from step one above).

[rferraton]

MS SQL OUTPUT Clause is an équivalent of RETURNING postgresql clause.
It is often use for SCD loading in combinaison with the MERGE statement : https://www.mssqltips.com/sqlservertip/1704/using-merge-in-sql-server-to-insert-update-and-delete-at-the-same-time/

I agree with you, this kind of clause (RETURNING or OUTPUT) can be usefull

[vogelsgesang]

This seems to also be allowed in existing Tableau Hyper API document (https://help.tableau.com/current/api/hyper_api/en-us/reference/sql/sql-delete.html); trying this today (tableauhyperapi 0.0.14265) throws an exception

Both the RETURNING clause and the WITH clause can definitely be combined with a DELETE statement in Hyper.

The queries below are part of our test suite, and Hyper is able to execute them succesfully:

Can you please provide a complete, self-contained repro of your issue?

---

RETURNING clause:

CREATE TABLE FOO ( A INT, B INT, C INT, D INT);

INSERT INTO FOO SELECT INDEX, INDEX + 1, INDEX + 2, INDEX + 3 FROM SEQUENCE(1,100);

DELETE FROM FOO WHERE A = 1 RETURNING A;

DELETE FROM FOO WHERE A = 11 RETURNING A,B,C;

WITH clause and RETURNING clause:

CREATE TABLE t1 AS SELECT i AS a FROM generate_series(1,7) s(i)

--- We can refer to a CTE defined by a WITH clause in the WHERE clause
WITH v(x) AS (VALUES(1),(3))
DELETE FROM t1
WHERE EXISTS(SELECT * FROM v WHERE x = a)
RETURNING a
-- result: 1, 3

-- We can also refer to it from the RETURNING clause
query tsv XX rowsort
WITH v(x) AS (VALUES(2),(3))
DELETE FROM t1
WHERE a > 5
RETURNING a, (SELECT MAX(x) FROM v WHERE a%x = 0)
-- result:
-- 6   3
-- 7   NULL

-- We can even use recursive CTEs
WITH RECURSIVE v(x) AS (SELECT 4 UNION ALL SELECT x+1 FROM v WHERE x < 5)
DELETE FROM t1
WHERE EXISTS(SELECT * FROM v WHERE x = a)
RETURNING a
-- result: 4, 5

[successfulmike]

@vogelsgesang , can you specify which version of the API you confirmed this with? I'm also specifically creating endpoint requests via Python so I'm leveraging the connection.execute_query method. Does your test do the same?

[vogelsgesang]

I verified this using the latest internal HyperAPI-build, but the test case in question was unmodified for a couple of years already, so the HyperAPI version doesn't matter. I am using execute_query through C++ instead of Python, but that doesn't change how Hyper interprets SQL