8 Replies Latest reply on Jun 8, 2012 2:18 AM by arisarnado

PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

arisarnado Jun 5, 2012 2:31 AM

Hello guys. Sorry I'm kind of new to Teiid and I encountered this problem.

Please see below for the details:

Input PostgreSQL statements:

a) SELECT <column> FROM <table> ORDER BY <column> OFFSET <offset> ROW FETCH NEXT <limit> ROWS ONLY

b) SELECT <column> FROM <table> ORDER BY <column> LIMIT <limit>

Output from Teiid Driver (after translation/optimization):

a) & b) SELECT <column> FROM <table>

Questions:

(1) I would like to know what could be the possible causes why LIMIT, OFFSET and ORDER BY are removed.

(2) Is there a way to "skip" optimization of SQL statements?

I saw one post (not JBoss site) that adding NON_STRICT would do the trick but it didn't work for me.

Additional Information:

Teiid Driver Version: teiid-7.7.0.Final-client.jar

PostgreSQL Version: 9.0.4

Thank you very much guys.

1. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

shawkins Jun 5, 2012 6:37 AM (in response to arisarnado)

1) Check the SHOWPLAN DEBUG output. See https://docs.jboss.org/author/display/TEIID/SHOW+Statement and https://docs.jboss.org/author/display/TEIID/SET+Statement
Stating with 8.1 the planning annotations wil also state why many planning decissions were made. Also make sure that you're using the postgresql translator, which does support limit/offset.
The NON_STRICT limit hint is to push the limit below it's logical position, such as when used above an order by.

2) No, there are currently just hints to help guide the process. https://issues.jboss.org/browse/TEIID-669 is a long standing issue covering some possible approaches.
Actions
2. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

arisarnado Jun 6, 2012 1:55 AM (in response to shawkins)

Hello Steven. Thanks for the fast response. I greatly appreciate it.

I checked the logs to check is the correct translator is used and yes, it was already set to "postgresql".
Actions
3. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

rareddy Jun 6, 2012 9:37 AM (in response to arisarnado)

Can you post the OPTION DEBUG log as Steve suggested?
Actions
4. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

arisarnado Jun 6, 2012 8:02 PM (in response to arisarnado)

Hello again.

I checked my SQL statement again and noticed that the 0 is passed as parameter to
the OFFSET clause. Looking at the translateLimit() method of PostgreSQLExecutionFactory class,
there is a checking that offset should be > 0 or else it will return null.

So I think this maybe the reason why the OFFSET clause was removed during translation.

However, I'm wondering why the translateLimit() method checks for the Row Offset and why not
only Limit.

Current Code for Teiid 7.7 (just pseudocode version)
public List<?> translateLimit(...){
    if (limit.getRowOffset() >0) {
        return Arrays.asList( Limit, Offset );
    }
    return null;
}

So, my understanding is that OFFSET clause should/needs exist so that LIMIT clause
will be translated? Because if LIMIT clause only exists, the method above will just
return null.

Is my understanding correct?

Thank you very much.
Actions
5. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

shawkins Jun 6, 2012 9:12 PM (in response to arisarnado)

Aris,

With the translateXXX methods on the ExecutionFactory if null is returned then the default translation used, which would be LIMIT x. The override exists for pg since LIMIT x OFFSET y differs from the default Teiid form or LIMIT y, x

The processor plan or the debug log will show the query pushed to the source. The final source translation by the ExecutionFactory will produce the query string sent to the source. I would suspect that the push down query in the plan does not contain the limit clause.

Steve
Actions
6. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

arisarnado Jun 7, 2012 4:27 AM (in response to rareddy)

Hello Ramesh.

Currently, the application is using iBatis 2.3 and I'm not sure how to
enable SET SHOW PLAN DEBUG since the SQL statement is in XML format.
Actions
7. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

rareddy Jun 7, 2012 9:09 AM (in response to arisarnado)

Aris,

Since you know the query iBatis is issuing, you can use any SQL like Squirrel or a simple JDBC client (look under Teiid examples for SimpleClient.java) and issue the same query to replicate the behavior.

Ramesh..
1 of 1 people found this helpful
Actions
8. Re: PostgreSQL LIMIT, OFFSET and ORDER BY clauses omitted by Teiid during translation

arisarnado Jun 8, 2012 2:18 AM (in response to rareddy)

Hello Ramesh,

OK. I'll try your suggestion (re: SimpleClient.java) and post feedback here.

Thanks.
Actions

Go to original post