TEIID24005 exception while federating data between SimpleDB and any other source

[vj007]

Hi Ramesh/Steven,

I'm using Teiid embedded, and facing an exception while trying to create a Virtual Model (Type: DDL) by inner-joining a Amazon SimpleDB table with a H2 (or any other database) table. Following is the exception stack trace:

Caused by: org.teiid.translator.TranslatorException: TEIID24005 ORDER BY is only supported when Order By column is also part of the WHERE clause
    at org.teiid.translator.simpledb.SimpleDBSQLVisitor.validateOrderBy(SimpleDBSQLVisitor.java:116)
    at org.teiid.translator.simpledb.SimpleDBSQLVisitor.visit(SimpleDBSQLVisitor.java:93)
    at org.teiid.language.Select.acceptVisitor(Select.java:103)
    at org.teiid.language.visitor.AbstractLanguageVisitor.visitNode(AbstractLanguageVisitor.java:51)
    at org.teiid.language.visitor.SQLStringVisitor.append(SQLStringVisitor.java:90)
    at org.teiid.translator.simpledb.SimpleDBQueryExecution.<init>(SimpleDBQueryExecution.java:59)
    at org.teiid.translator.simpledb.SimpleDBExecutionFactory.createResultSetExecution(SimpleDBExecutionFactory.java:79)
    at org.teiid.translator.simpledb.SimpleDBExecutionFactory.createResultSetExecution(SimpleDBExecutionFactory.java:34)
    at org.teiid.translator.ExecutionFactory.createExecution(ExecutionFactory.java:300)
    at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:317)
    at org.teiid.dqp.internal.process.DataTierTupleSource.getResults(DataTierTupleSource.java:298)
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:110)
    at org.teiid.dqp.internal.process.DataTierTupleSource$1.call(DataTierTupleSource.java:107)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:334)
    at java.util.concurrent.FutureTask.run(FutureTask.java:166)
    at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
    at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:274)
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
    at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:214)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
    at java.lang.Thread.run(Thread.java:722)

I do understand that Amazon SimpleDB itself poses this restriction of having the sort attribute present in at least one of the predicates (as mentioned in https://aws.amazon.com/articles/1231), but I'm wondering why this error comes up only when when processing an INNER JOIN between SimpleDB table's and another table's column from other DB (e.g. H2)?

The schema text of my DDL model is:

create view \"portfolio\" as select PUBLIC.PRODUCT.SYMBOL, COMPANY_NAME FROM PUBLIC.PRODUCT INNER JOIN WATCH ON PUBLIC.PRODUCT.SYMBOL = WATCH.SYMBOL

Attached is the log file from Teiid Engine (TRACE level enabled).

Please help.

[rareddy]

Vijay,

To avoid sorting inside the Teiid engine, Teiid is sending the ORDER BY on the statement. However the issue is, as you pointed out SimpleDB comes with restrictions on usage of the ORDER BY. So, either we need to turn of the ORDER BY on Simple DB (I think you can do that using translator override) or make an enhancement to SimpleDB. Although, I am not sure if there is a capability to define on translator API, that says use ORDER BY only when WHERE is present.

Ramesh..