0

I am executing fairly large SQL select queries against a redshift DB using teiid. I have optimized my query to give a better response time by avoiding using inner queries and inner select statements in my query. How ever when I execute the query, the teiid query engine changes my query to a different version which uses inner queries and inner select statements. Is there any way of bypassing this behavior and directly use the query which i provide.

Her is the original teiid query that I execute

        CREATE VIRTUAL PROCEDURE GetTop() RETURNS (json clob) OPTIONS (UPDATECOUNT 0, "REST:METHOD" 'GET', "REST:URI" 'GetTop')
        AS
        /*+ cache(pref_mem ttl:14400000) */
        BEGIN
            execute immediate
            'SELECT  JSONOBJECT(
                JSONARRAY_AGG(
                    JSONOBJECT(
                       total_purchases,
                       total_invoice,
                       total_records,
                       period
                    )
                )
                AS "dd"

            ) as json FROM(
              SELECT SUM((CASE


            GROUP BY period

Teiid query engine converts above query into bellow version which has an inner SELECT Statement

SELECT SUM(v_0.c_1),
       COUNT(DISTINCT v_0.c_2),
       COUNT(v_0.c_2),
       v_0.c_0
FROM (SELECT CASE

GROUP BY v_0.c_0

I would like to knowhHow can I by pass this behavior and execute my original query?

Sanjewa Ranasinghe
  • 15
  • 1
  • 8

1 Answers1

0

Teiid will only create inline views for specific purposes - it generally removes them when possible. You'll need to provide more context of the user query, the processor plan, or debug plan so that we can understand why the inline views are needed.

Steven Hawkins
  • 538
  • 1
  • 4
  • 7
  • This appears to be an issue with the pg/redshift translator. supportsFunctionsInGroupBy should be true. As false the engine is creating an inline view to compensate. That should be corrected in the code base - can you log an issue? There are workarounds such as creating you own translator extension translator, using a Delegating Translator to override this capability, or even using a direct/native query if you want to send the sql directly to the source. – Steven Hawkins Dec 14 '17 at 19:14
  • Ok thanks. I'll log an issue. I may need some time to reproduce this issue properly. Once I'm done I'll edit the Question above with the sample queries. And I'll log an issue. – Sanjewa Ranasinghe Dec 18 '17 at 06:07