Author: shawkins Date: 2009-07-30 12:53:28 -0400 (Thu, 30 Jul 2009) New Revision: 1205 Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCMetdataProcessor.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/BindValueVisitor.java trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/DropFunctionModifier.java trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java Log: TEIID-700 fix for oracle bug in union order by with a nested limit. also adding logging to the metadata import Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCConnector.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -326,7 +326,7 @@ xaConn = xaDs.getXAConnection(); conn = xaConn.getConnection(); } - JDBCMetdataProcessor metadataProcessor = new JDBCMetdataProcessor(); + JDBCMetdataProcessor metadataProcessor = new JDBCMetdataProcessor(this.logger); PropertiesUtils.setBeanProperties(metadataProcessor, metadataFactory.getImportProperties(), "importer"); //$NON-NLS-1$ PropertiesUtils.setBeanProperties(metadataProcessor, this.environment.getProperties(), "importer"); //$NON-NLS-1$ metadataProcessor.getConnectorMetadata(conn, metadataFactory); Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCMetdataProcessor.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCMetdataProcessor.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/JDBCMetdataProcessor.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -32,6 +32,7 @@ import java.util.TreeMap; import org.teiid.connector.api.ConnectorException; +import org.teiid.connector.api.ConnectorLogger; import org.teiid.connector.api.TypeFacility; import org.teiid.connector.metadata.runtime.AbstractMetadataRecord; import org.teiid.connector.metadata.runtime.BaseColumn; @@ -75,6 +76,12 @@ private boolean importApproximateIndexes = true; private boolean importProcedures = true; + private ConnectorLogger logger; + + public JDBCMetdataProcessor(ConnectorLogger logger) { + this.logger = logger; + } + public void getConnectorMetadata(Connection conn, MetadataFactory metadataFactory) throws SQLException, ConnectorException { DatabaseMetaData metadata = conn.getMetaData(); @@ -98,6 +105,7 @@ private void getProcedures(MetadataFactory metadataFactory, DatabaseMetaData metadata) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing procedures"); //$NON-NLS-1$ ResultSet procedures = metadata.getProcedures(catalog, schemaPattern, procedureNamePattern); while (procedures.next()) { String procedureCatalog = procedures.getString(1); @@ -152,6 +160,7 @@ private Map<String, TableInfo> getTables(MetadataFactory metadataFactory, DatabaseMetaData metadata) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing tables"); //$NON-NLS-1$ ResultSet tables = metadata.getTables(catalog, schemaPattern, tableNamePattern, tableTypes); Map<String, TableInfo> tableMap = new HashMap<String, TableInfo>(); while (tables.next()) { @@ -177,6 +186,7 @@ private void getColumns(MetadataFactory metadataFactory, DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing columns"); //$NON-NLS-1$ ResultSet columns = metadata.getColumns(catalog, schemaPattern, tableNamePattern, null); int rsColumns = columns.getMetaData().getColumnCount(); while (columns.next()) { @@ -208,9 +218,10 @@ columns.close(); } - private static void getPrimaryKeys(MetadataFactory metadataFactory, + private void getPrimaryKeys(MetadataFactory metadataFactory, DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing primary keys"); //$NON-NLS-1$ for (TableInfo tableInfo : tableMap.values()) { ResultSet pks = metadata.getPrimaryKeys(tableInfo.catalog, tableInfo.schema, tableInfo.name); TreeMap<Short, String> keyColumns = null; @@ -238,6 +249,7 @@ private void getForeignKeys(MetadataFactory metadataFactory, DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing foreign keys"); //$NON-NLS-1$ for (TableInfo tableInfo : tableMap.values()) { ResultSet fks = metadata.getImportedKeys(tableInfo.catalog, tableInfo.schema, tableInfo.name); TreeMap<Short, String> keyColumns = null; @@ -280,6 +292,7 @@ private void getIndexes(MetadataFactory metadataFactory, DatabaseMetaData metadata, Map<String, TableInfo> tableMap) throws SQLException, ConnectorException { + logger.logDetail("JDBCMetadataProcessor - Importing index info"); //$NON-NLS-1$ for (TableInfo tableInfo : tableMap.values()) { ResultSet indexInfo = metadata.getIndexInfo(tableInfo.catalog, tableInfo.schema, tableInfo.name, false, importApproximateIndexes); TreeMap<Short, String> indexColumns = null; Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/oracle/OracleSQLTranslator.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -49,6 +49,7 @@ import org.teiid.connector.language.IInsertExpressionValueSource; import org.teiid.connector.language.ILimit; import org.teiid.connector.language.IQueryCommand; +import org.teiid.connector.language.ISelectSymbol; import org.teiid.connector.language.ISetQuery.Operation; import org.teiid.connector.metadata.runtime.Element; import org.teiid.connector.visitor.util.SQLReservedWords; @@ -166,10 +167,34 @@ ILimit limit = queryCommand.getLimit(); queryCommand.setLimit(null); List<Object> parts = new ArrayList<Object>(); + parts.add("SELECT "); //$NON-NLS-1$ + /* + * if all of the columns are aliased, assume that names matter - it actually only seems to matter for + * the first query of a set op when there is a order by. Rather than adding logic to traverse up, + * we just use the projected names + */ + boolean allAliased = true; + for (ISelectSymbol selectSymbol : queryCommand.getProjectedQuery().getSelect().getSelectSymbols()) { + if (!selectSymbol.hasAlias()) { + allAliased = false; + break; + } + } + if (allAliased) { + String[] columnNames = queryCommand.getColumnNames(); + for (int i = 0; i < columnNames.length; i++) { + if (i > 0) { + parts.add(", "); //$NON-NLS-1$ + } + parts.add(columnNames[i]); + } + } else { + parts.add("*"); //$NON-NLS-1$ + } if (limit.getRowOffset() > 0) { - parts.add("SELECT * FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM ("); //$NON-NLS-1$ + parts.add(" FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM ("); //$NON-NLS-1$ } else { - parts.add("SELECT * FROM ("); //$NON-NLS-1$ + parts.add(" FROM ("); //$NON-NLS-1$ } parts.add(queryCommand); if (limit.getRowOffset() > 0) { Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/BindValueVisitor.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/BindValueVisitor.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/BindValueVisitor.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -27,7 +27,6 @@ import org.teiid.connector.language.IFunction; import org.teiid.connector.language.IInCriteria; import org.teiid.connector.language.IInlineView; -import org.teiid.connector.language.IInsert; import org.teiid.connector.language.IInsertExpressionValueSource; import org.teiid.connector.language.ILanguageObject; import org.teiid.connector.language.ILikeCriteria; Modified: trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/DropFunctionModifier.java =================================================================== --- trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/DropFunctionModifier.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/connectors/connector-jdbc/src/main/java/org/teiid/connector/jdbc/translator/DropFunctionModifier.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -34,7 +34,7 @@ * can be overridden. This modifier should only be used with functions having the * minimum or more number of arguments. */ -public class DropFunctionModifier extends BasicFunctionModifier implements FunctionModifier { +public class DropFunctionModifier extends BasicFunctionModifier { private int replaceIndex = 0; Modified: trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java =================================================================== --- trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-07-29 14:26:05 UTC (rev 1204) +++ trunk/test-integration/src/test/java/com/metamatrix/connector/jdbc/oracle/TestOracleSQLConversionVisitor.java 2009-07-30 16:53:28 UTC (rev 1205) @@ -457,8 +457,8 @@ } @Test public void testLimitWithNestedInlineView() throws Exception { - String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by intkey"; //$NON-NLS-1$ - String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey) WHERE ROWNUM <= 100) x GROUP BY x.intkey"; //$NON-NLS-1$ + String input = "select max(intkey), stringkey from (select intkey, stringkey from bqt1.smalla order by intkey limit 100) x group by stringkey"; //$NON-NLS-1$ + String output = "SELECT MAX(x.intkey), x.stringkey FROM (SELECT * FROM (SELECT SmallA.IntKey, SmallA.StringKey FROM SmallA ORDER BY intkey) WHERE ROWNUM <= 100) x GROUP BY x.stringkey"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), input, @@ -496,6 +496,15 @@ String sql = "select stringnum || '1' from BQT1.Smalla"; //$NON-NLS-1$ String expected = "SELECT CASE WHEN SmallA.StringNum IS NULL THEN NULL ELSE concat(SmallA.StringNum, '1') END FROM SmallA"; //$NON-NLS-1$ helpTestVisitor(FakeMetadataFactory.exampleBQTCached(), sql, EMPTY_CONTEXT, null, expected); + } + + @Test public void testRowLimitWithUnionOrderBy() throws Exception { + String input = "(select intkey from bqt1.smalla limit 50, 100) union select intnum from bqt1.smalla order by intkey"; //$NON-NLS-1$ + String output = "SELECT c_0 FROM (SELECT VIEW_FOR_LIMIT.*, ROWNUM ROWNUM_ FROM (SELECT g_1.IntKey AS c_0 FROM SmallA g_1) VIEW_FOR_LIMIT WHERE ROWNUM <= 150) WHERE ROWNUM_ > 50 UNION SELECT g_0.IntNum AS c_0 FROM SmallA g_0 ORDER BY c_0"; //$NON-NLS-1$ + + CommandBuilder commandBuilder = new CommandBuilder(FakeMetadataFactory.exampleBQTCached()); + ICommand obj = commandBuilder.getCommand(input, true, true); + this.helpTestVisitor(obj, EMPTY_CONTEXT, null, output); } }