Currently running Denodo 8 and using some views connected to a Postgres 12.9 database.
I found that when running a query with an analytical function sum() and a REGEXP_LIKE function, causes the following error:
Finished with error: Error executing view: Function sum is not executable
SELECT ticket_id, owner, title, ticket_updated_date, ticket_submitted_date,
SUM(CASE WHEN ticket_update_date >= ticket_submitted_date THEN 1 ELSE 0 END) OVER (PARTITION BY ticket_id ORDER BY ticket_submitted_date DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS num_approvals
FROM tickets
where ticket_subject REGEXP_LIKE('^testcase$')
group by 1, 2, 3, 4, 5
order by 5 desc
When we comment out the WHERE clause in the previous statement, the query executes successfully and returns results. Why would REGEXP_LIKE cause the analytical function to fail with this error? Is this a bug?
When looking at the Execution Trace of the successful query with the REGEXP_LIKE commented out, the entire query is translated properly to Postgres language and pushed to the data source. I do see that REGEXP_LIKE is not supported in Postgres 12 and will be available in the future Postgres 15. A simple query using the REGEXP_LIKE function without an analytical function executes successfully, but is unable to translate to Postgres language and pushed to the source, as expected. The function is then applied at the Denodo layer in the Projection Plan. Both the analytical function and REGEXP_LIKE function seem to work as expected, but not together for some reason.
