Allow common table expression in reports' SQL queries

[thenor]

The trac reporting systems performs some rewriting of SQL queries in reports. The consequence is that the query must be a SELECT.

However, for instance ​th:SubticketsPlugin needs a way to perform recursive queries in order to report on ticket hierarchies - and that is not possible in a simple SELECT. This is currently solved by means of a manually installed stored procedure that in turn is called through a normal SELECT. It is described in more detail here: ​th:SubticketsPlugin/Reporting.

The technique used in the plugin is a Common Table Expression; it is described here: ​https://www.postgresql.org/docs/9.1/static/queries-with.html

I suspect that e.g. ​th:MasterTicketsPlugin and other plugins that deal with ticket relations could benefit from a similar improvement. Plus, use of WITH can simplify many "ordinary" queries.

If that turns out to be unrealistic, it would be helpful if there was a nice way to install stored procedures in a reasonably database-independent manner through plugins' initialisation code.

[Jun Omae]

I tried the following query on Trac 1.0.13 and 1.2. It seems to be worked.

WITH RECURSIVE t(n) AS (
    VALUES (1)
  UNION ALL
    SELECT n+1 FROM t WHERE n < 100
)
SELECT sum(n) FROM t

Also, I think you could wrap with SELECT * FROM (WITH ...) if it doesn't work.

I'd like to close as wontfix.

[Jun Omae]

According to ​this entry, CTE query would be available since MySQL 8.0.

[theodor.norup@…]

Sorry, I feel a bit stupid, works for me as well. Wonder, what I've been mixing up…

Maybe it is relevant to mention this possibility explicitly in the report documentation?

[Ryan J Ollos]

Replying to theodor.norup@…:

Maybe it is relevant to mention this possibility explicitly in the report documentation?

Sure, feel free to edit TracReports. A statement after the last sentence in TracReports#CreatingCustomReports might be appropriate.