Author: shawkins Date: 2011-06-28 10:21:51 -0400 (Tue, 28 Jun 2011) New Revision: 3281 Modified: branches/7.4.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java branches/7.4.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java branches/7.4.x/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestElementImpl.java Log: TEIID-1616 switching teradata to use positional ordering Modified: branches/7.4.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java =================================================================== --- branches/7.4.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/api/src/main/java/org/teiid/language/visitor/SQLStringVisitor.java 2011-06-28 14:21:51 UTC (rev 3281) @@ -272,8 +272,7 @@ if(elementID != null) { elemShortName = getName(elementID); } else { - String elementName = obj.getName(); - elemShortName = getShortName(elementName); + elemShortName = obj.getName(); } // Check whether a subclass wants to replace the element name to use in special circumstances Modified: branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html =================================================================== --- branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/build/kits/jboss-container/teiid-releasenotes.html 2011-06-28 14:21:51 UTC (rev 3281) @@ -71,6 +71,11 @@ <li>Support for using the FROM clause post item hints MAKEDEP/MAKENOTDEP has been deprecated. Use the pre item comment hint syntax instead, e.g. /*+ MAKEDEP */ tbl </ul> +<h4>from 7.4</h4> +<ul> + <li>ColumnReference.getName will always return just the element name. Previously it inconsistently returned the qualified and unqualified form depending upon where the ColumnReference appeared. +</ul> + <h4>from 7.3</h4> <ul> <li>SYS.PROPERTIES has a new column, ClobValue, to get values exceeding the max string length Modified: branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/connectors/translator-jdbc/src/main/java/org/teiid/translator/jdbc/teradata/TeradataExecutionFactory.java 2011-06-28 14:21:51 UTC (rev 3281) @@ -29,13 +29,21 @@ import java.sql.Timestamp; import java.util.ArrayList; import java.util.Arrays; +import java.util.HashMap; import java.util.List; +import java.util.Map; import org.teiid.language.ColumnReference; +import org.teiid.language.Command; +import org.teiid.language.DerivedColumn; import org.teiid.language.Expression; import org.teiid.language.Function; import org.teiid.language.LanguageFactory; import org.teiid.language.Literal; +import org.teiid.language.QueryExpression; +import org.teiid.language.Select; +import org.teiid.language.SortSpecification; +import org.teiid.translator.ExecutionContext; import org.teiid.translator.SourceSystemFunctions; import org.teiid.translator.Translator; import org.teiid.translator.TranslatorException; @@ -292,8 +300,37 @@ } @Override - public boolean supportsSetQueryOrderBy() { - return false; + public List<?> translateCommand(Command command, ExecutionContext context) { + if (command instanceof QueryExpression) { + QueryExpression qe = (QueryExpression)command; + //teradata prefers positional ordering + if (qe.getOrderBy() != null) { + Select select = qe.getProjectedQuery(); + List<DerivedColumn> derivedColumns = select.getDerivedColumns(); + Map<String, Integer> positions = new HashMap<String, Integer>(); + int i = 1; + for (DerivedColumn derivedColumn : derivedColumns) { + String name = derivedColumn.getAlias(); + if (name == null && derivedColumn.getExpression() instanceof ColumnReference) { + ColumnReference cr = (ColumnReference)derivedColumn.getExpression(); + name = cr.toString(); + } + positions.put(name, i++); + } + for (SortSpecification ss : qe.getOrderBy().getSortSpecifications()) { + Expression ex = ss.getExpression(); + if (!(ex instanceof ColumnReference)) { + continue; + } + ColumnReference cr = (ColumnReference)ex; + Integer position = positions.get(cr.toString()); + if (position != null) { + ss.setExpression(new Literal(position, TypeFacility.RUNTIME_TYPES.INTEGER)); + } + } + } + } + return super.translateCommand(command, context); } public static class LocateModifier extends FunctionModifier { Modified: branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java =================================================================== --- branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/connectors/translator-jdbc/src/test/java/org/teiid/translator/jdbc/teradata/TestTeradataTranslator.java 2011-06-28 14:21:51 UTC (rev 3281) @@ -21,7 +21,7 @@ */ package org.teiid.translator.jdbc.teradata; -import static org.junit.Assert.assertEquals; +import static org.junit.Assert.*; import java.sql.Date; import java.sql.Timestamp; @@ -31,6 +31,8 @@ import org.junit.BeforeClass; import org.junit.Test; +import org.teiid.cdk.api.TranslationUtility; +import org.teiid.language.Command; import org.teiid.language.Expression; import org.teiid.language.Function; import org.teiid.language.In; @@ -88,29 +90,55 @@ @Test public void testSubString() throws Exception { String input = "SELECT intkey FROM BQT1.SmallA WHERE SUBSTRING(BQT1.SmallA.IntKey, 1) = '1' ORDER BY intkey"; - String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1) = '1' ORDER BY SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1) = '1' ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); } @Test public void testSubString2() throws Exception { String input = "SELECT intkey FROM BQT1.SmallA WHERE SUBSTRING(BQT1.SmallA.IntKey, 1, 2) = '1' ORDER BY intkey"; - String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1,2) = '1' ORDER BY SmallA.IntKey"; + String output = "SELECT SmallA.IntKey FROM SmallA WHERE substr(cast(SmallA.IntKey AS varchar(100)),1,2) = '1' ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); } @Test public void testDateToString() throws Exception { String input = "SELECT intkey, UPPER(timevalue) AS UPPER FROM BQT1.SmallA ORDER BY intkey"; - String output = "SELECT SmallA.IntKey, UPPER(cast(cast(SmallA.TimeValue AS FORMAT 'HH:MI:SS') AS VARCHAR(9))) AS UPPER FROM SmallA ORDER BY SmallA.IntKey"; + String output = "SELECT SmallA.IntKey, UPPER(cast(cast(SmallA.TimeValue AS FORMAT 'HH:MI:SS') AS VARCHAR(9))) AS UPPER FROM SmallA ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); } @Test public void testLocate() throws Exception { String input = "SELECT INTKEY, BIGDECIMALVALUE FROM BQT1.SmallA WHERE LOCATE('-', BIGDECIMALVALUE) = 1 ORDER BY intkey"; - String output = "SELECT SmallA.IntKey, SmallA.BigDecimalValue FROM SmallA WHERE position('-' in cast(SmallA.BigDecimalValue AS varchar(100))) = 1 ORDER BY SmallA.IntKey"; + String output = "SELECT SmallA.IntKey, SmallA.BigDecimalValue FROM SmallA WHERE position('-' in cast(SmallA.BigDecimalValue AS varchar(100))) = 1 ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, input, output, TRANSLATOR); } + @Test public void testPositionalOrderBy() throws Exception { + String input = "SELECT INTKEY, BIGDECIMALVALUE FROM BQT1.SmallA ORDER BY intkey"; + String output = "SELECT g_0.IntKey AS c_0, g_0.BigDecimalValue AS c_1 FROM SmallA AS g_0 ORDER BY 1"; + + TranslationUtility tu = TranslationHelper.getTranslationUtility(TranslationHelper.BQT_VDB, null); + Command command = tu.parseCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, command); + } + @Test public void testPositionalOrderByUnion() throws Exception { + String input = "SELECT a as b, b as a from (SELECT intkey as a, stringkey as b from BQT1.smalla union SELECT intkey as a, stringkey as b from BQT1.smalla) as x order by a"; + String output = "SELECT v_0.c_0, v_0.c_1 FROM (SELECT g_1.IntKey AS c_0, g_1.StringKey AS c_1 FROM SmallA AS g_1 UNION SELECT g_0.IntKey AS c_0, g_0.StringKey AS c_1 FROM SmallA AS g_0) AS v_0 ORDER BY 2"; + + TranslationUtility tu = TranslationHelper.getTranslationUtility(TranslationHelper.BQT_VDB, null); + Command command = tu.parseCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, command); + } + + @Test public void testPositionalOrderByUnion1() throws Exception { + String input = "SELECT a as b from (SELECT intkey as a, stringkey as b from BQT1.smalla union SELECT intkey as a, stringkey as b from BQT1.smalla) as x order by x.b"; + String output = "SELECT v_0.c_0 FROM (SELECT g_1.IntKey AS c_0, g_1.StringKey AS c_1 FROM SmallA AS g_1 UNION SELECT g_0.IntKey AS c_0, g_0.StringKey AS c_1 FROM SmallA AS g_0) AS v_0 ORDER BY v_0.c_1"; + + TranslationUtility tu = TranslationHelper.getTranslationUtility(TranslationHelper.BQT_VDB, null); + Command command = tu.parseCommand(input, true, true); + TranslationHelper.helpTestVisitor(output, TRANSLATOR, command); + } + @Test public void testByteToString() throws Exception { helpTest(LANG_FACTORY.createLiteral(new Byte((byte)1), Byte.class), "string", "cast(1 AS varchar(4000))"); } @@ -194,13 +222,13 @@ @Test public void testRightFunction() throws Exception { String input = "SELECT INTKEY, FLOATNUM FROM BQT1.SmallA WHERE right(FLOATNUM, 2) <> 0 ORDER BY INTKEY"; - String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE substr(cast(SmallA.FloatNum AS varchar(100)),(character_length(cast(SmallA.FloatNum AS varchar(100)))-2+1)) <> '0' ORDER BY SmallA.IntKey"; + String out = "SELECT SmallA.IntKey, SmallA.FloatNum FROM SmallA WHERE substr(cast(SmallA.FloatNum AS varchar(100)),(character_length(cast(SmallA.FloatNum AS varchar(100)))-2+1)) <> '0' ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); } @Test public void testLocateFunction() throws Exception { String input = "SELECT INTKEY, STRINGKEY, SHORTVALUE FROM BQT1.SmallA WHERE (LOCATE(0, STRINGKEY) = 2) OR (LOCATE(2, SHORTVALUE, 4) = 6) ORDER BY intkey"; - String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.ShortValue FROM SmallA WHERE position('0' in SmallA.StringKey) = 2 OR position('2' in substr(cast(SmallA.ShortValue AS varchar(100)),4)) = 6 ORDER BY SmallA.IntKey"; + String out = "SELECT SmallA.IntKey, SmallA.StringKey, SmallA.ShortValue FROM SmallA WHERE position('0' in SmallA.StringKey) = 2 OR position('2' in substr(cast(SmallA.ShortValue AS varchar(100)),4)) = 6 ORDER BY 1"; TranslationHelper.helpTestVisitor(TranslationHelper.BQT_VDB, null, input, out, TRANSLATOR); } } Modified: branches/7.4.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java =================================================================== --- branches/7.4.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/engine/src/main/java/org/teiid/dqp/internal/datamgr/LanguageBridgeFactory.java 2011-06-28 14:21:51 UTC (rev 3281) @@ -115,6 +115,7 @@ import org.teiid.query.sql.symbol.GroupSymbol; import org.teiid.query.sql.symbol.ScalarSubquery; import org.teiid.query.sql.symbol.SearchedCaseExpression; +import org.teiid.query.sql.symbol.SelectSymbol; import org.teiid.query.sql.symbol.SingleElementSymbol; import org.teiid.translator.TranslatorException; @@ -179,9 +180,9 @@ /* Query */ Select translate(Query query) { - List symbols = query.getSelect().getSymbols(); + List<SelectSymbol> symbols = query.getSelect().getSymbols(); List<DerivedColumn> translatedSymbols = new ArrayList<DerivedColumn>(symbols.size()); - for (Iterator i = symbols.iterator(); i.hasNext();) { + for (Iterator<SelectSymbol> i = symbols.iterator(); i.hasNext();) { SingleElementSymbol symbol = (SingleElementSymbol)i.next(); String alias = null; if(symbol instanceof AliasSymbol) { @@ -375,7 +376,7 @@ In translate(SetCriteria criteria) { Collection expressions = criteria.getValues(); - List translatedExpressions = new ArrayList(); + List<org.teiid.language.Expression> translatedExpressions = new ArrayList<org.teiid.language.Expression>(); for (Iterator i = expressions.iterator(); i.hasNext();) { translatedExpressions.add(translate((Expression)i.next())); } @@ -423,13 +424,13 @@ return new SubqueryComparison(translate(criteria.getLeftExpression()), operator, quantifier, - translate((QueryCommand)criteria.getCommand())); + translate(criteria.getCommand())); } SubqueryIn translate(SubquerySetCriteria criteria) { return new SubqueryIn(translate(criteria.getExpression()), criteria.isNegated(), - translate((QueryCommand)criteria.getCommand())); + translate(criteria.getCommand())); } Not translate(NotCriteria criteria) { @@ -462,7 +463,7 @@ if(items.get(i).isUnrelated() || (!set && symbol instanceof ElementSymbol)){ orderByItem = new SortSpecification(direction, translate(symbol)); } else { - orderByItem = new SortSpecification(direction, new ColumnReference(null, symbol.getOutputName(), null, symbol.getType())); + orderByItem = new SortSpecification(direction, new ColumnReference(null, SingleElementSymbol.getShortName(symbol.getOutputName()), null, symbol.getType())); } orderByItem.setNullOrdering(items.get(i).getNullOrdering()); translatedItems.add(orderByItem); @@ -558,7 +559,7 @@ } ColumnReference translate(ElementSymbol symbol) { - ColumnReference element = new ColumnReference(translate(symbol.getGroupSymbol()), symbol.getOutputName(), null, symbol.getType()); + ColumnReference element = new ColumnReference(translate(symbol.getGroupSymbol()), SingleElementSymbol.getShortName(symbol.getOutputName()), null, symbol.getType()); if (element.getTable().getMetadataObject() == null) { return element; } @@ -744,10 +745,10 @@ /* Batched Updates */ BatchedUpdates translate(BatchedUpdateCommand command) { - List updates = command.getUpdateCommands(); + List<Command> updates = command.getUpdateCommands(); List<org.teiid.language.Command> translatedUpdates = new ArrayList<org.teiid.language.Command>(updates.size()); - for (Iterator i = updates.iterator(); i.hasNext();) { - translatedUpdates.add(translate((Command)i.next())); + for (Iterator<Command> i = updates.iterator(); i.hasNext();) { + translatedUpdates.add(translate(i.next())); } return new BatchedUpdates(translatedUpdates); } Modified: branches/7.4.x/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestElementImpl.java =================================================================== --- branches/7.4.x/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestElementImpl.java 2011-06-28 03:47:29 UTC (rev 3280) +++ branches/7.4.x/engine/src/test/java/org/teiid/dqp/internal/datamgr/TestElementImpl.java 2011-06-28 14:21:51 UTC (rev 3281) @@ -72,7 +72,7 @@ public void testGetName() throws Exception { Object metadataID = TstLanguageBridgeFactory.metadata.getElementID("pm1.g1.e1"); //$NON-NLS-1$ - assertEquals("pm1.g1.e1", example("pm1.g1", "e1", metadataID).getName()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ + assertEquals("e1", example("pm1.g1", "e1", metadataID).getName()); //$NON-NLS-1$ //$NON-NLS-2$ //$NON-NLS-3$ } public void testGetGroup() throws Exception {