Author: jolee Date: 2012-06-12 10:05:05 -0400 (Tue, 12 Jun 2012) New Revision: 4175 Modified: branches/7.7.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java branches/7.7.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 Modified: branches/7.7.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- branches/7.7.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-06-11 21:04:27 UTC (rev 4174) +++ branches/7.7.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2012-06-12 14:05:05 UTC (rev 4175) @@ -105,7 +105,11 @@ * @return the name of that element or group as defined in the source */ protected String getName(AbstractMetadataRecord object) { - String nameInSource = object.getNameInSource(); + return getRecordName(object); + } + + public static String getRecordName(AbstractMetadataRecord object) { + String nameInSource = object.getNameInSource(); if(nameInSource != null && nameInSource.length() > 0) { return nameInSource; } Modified: branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-06-11 21:04:27 UTC (rev 4174) +++ branches/7.7.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/oracle/OracleExecutionFactory.java 2012-06-12 14:05:05 UTC (rev 4175) @@ -36,9 +36,11 @@ 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; +import org.teiid.language.visitor.SQLStringVisitor; import org.teiid.logging.LogConstants; import org.teiid.logging.LogManager; import org.teiid.metadata.AbstractMetadataRecord; @@ -285,6 +287,21 @@ } 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$ /* @@ -327,7 +344,18 @@ } return parts; } - + + private boolean isDual(NamedTable table) { + String groupName = null; + AbstractMetadataRecord groupID = table.getMetadataObject(); + if(groupID != null) { + groupName = SQLStringVisitor.getRecordName(groupID); + } else { + groupName = table.getName(); + } + return DUAL.equalsIgnoreCase(groupName); + } + @Override public boolean useAsInGroupAlias(){ return false; @@ -506,14 +534,7 @@ 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)) { + if (isDual(table)) { table.setCorrelationName(null); } } Modified: branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java =================================================================== --- branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-06-11 21:04:27 UTC (rev 4174) +++ branches/7.7.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/oracle/TestOracleTranslator.java 2012-06-12 14:05:05 UTC (rev 4175) @@ -664,6 +664,28 @@ null, 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