Author: jolee Date: 2012-06-11 15:39:16 -0400 (Mon, 11 Jun 2012) New Revision: 4172 Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java Log: TEIID-2069 Optimze add LIMIT clause to SELECT within a Virtual Procedure using Oracle backport from trunk, slimmed to not include api changes. Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-06-11 17:52:05 UTC (rev 4171) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-06-11 19:39:16 UTC (rev 4172) @@ -36,6 +36,7 @@ import java.util.List; import org.teiid.language.*; +import org.teiid.language.Comparison.Operator; import org.teiid.language.SQLConstants.Tokens; import org.teiid.language.SetQuery.Operation; import org.teiid.language.visitor.CollectorVisitor; @@ -282,6 +283,20 @@ } Limit limit = queryCommand.getLimit(); queryCommand.setLimit(null); + + if (command instanceof Select) { + Select select = (Select)command; + TableReference tr = select.getFrom().get(0); + if (tr instanceof NamedTable && isDual((NamedTable)tr)) { + if (limit.getRowOffset() > 0 || limit.getRowLimit() == 0) { + //no data + select.setWhere(new Comparison(new Literal(1, TypeFacility.RUNTIME_TYPES.INTEGER), new Literal(0, TypeFacility.RUNTIME_TYPES.INTEGER), Operator.EQ)); + return null; + } + return null; //dual does not allow a limit + } + } + List<Object> parts = new ArrayList<Object>(); parts.add("SELECT "); //$NON-NLS-1$ /* @@ -324,6 +339,20 @@ } return parts; } + + private boolean isDual(NamedTable table) { + String groupName = null; + AbstractMetadataRecord groupID = table.getMetadataObject(); + if(groupID != null) { + String nameInSource = groupID.getNameInSource(); + if(nameInSource != null && nameInSource.length() > 0) { + groupName = nameInSource; + } + } else { + groupName = table.getName(); + } + return DUAL.equalsIgnoreCase(groupName); + } @Override public boolean useAsInGroupAlias(){ @@ -557,16 +586,9 @@ private void stripDualAlias(NamedTable table) { if (table.getCorrelationName() != null) { - String groupName = null; - AbstractMetadataRecord groupID = table.getMetadataObject(); - if(groupID != null) { - groupName = getName(groupID); - } else { - groupName = table.getName(); - } - if (DUAL.equalsIgnoreCase(groupName)) { - table.setCorrelationName(null); - } + if (isDual(table)) { + table.setCorrelationName(null); + } } } Modified: branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-06-11 17:52:05 UTC (rev 4171) +++ branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-06-11 19:39:16 UTC (rev 4172) @@ -664,6 +664,28 @@ output); } + @Test public void testDUALLimit() throws Exception { + String input = "SELECT something FROM DUAL as g0 LIMIT 2"; //$NON-NLS-1$ + String output = "SELECT seq.nextval FROM DUAL"; //$NON-NLS-1$ + + helpTestVisitor(getOracleSpecificMetadata(), + input, + EMPTY_CONTEXT, + null, + output); + } + + @Test public void testDUALLimit1() throws Exception { + String input = "SELECT something FROM DUAL as g0 LIMIT 1, 2"; //$NON-NLS-1$ + String output = "SELECT seq.nextval FROM DUAL WHERE 1 = 0"; //$NON-NLS-1$ + + helpTestVisitor(getOracleSpecificMetadata(), + input, + EMPTY_CONTEXT, + null, + output); + } + /** * Test Oracle's rownum pseudo-column. Not a real column, so it can't * be fully-qualified with a table name. MM requires this column to be