Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-3384

SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 8.11
    • 8.7
    • Query Engine
    • None

    Description

      Running this query (where "test_a" is a table in MS SQL Server):

      select test2
      from
       (select 'aaa' as test2 from ms.test_a) b
      group by test2
      

      the following exception is thrown:

      01:36:55,198 WARN  [org.teiid.CONNECTOR] (Worker1_QueryProcessorQueue4) ks49tw3LLVqR Connector worker process failed for atomic-request=ks49tw3LLVqR.1.1.1: org.teiid.translator.jdbc.JDBCExecutionException: 164 TEIID11008:TEIID11004 Error executing statement(s): [Prepared Values: [] SQL: SELECT 'aaa' FROM "test_tables"."dbo"."test_a" g_0 GROUP BY 'aaa']
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:131) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
              at org.teiid.dqp.internal.datamgr.ConnectorWorkItem.execute(ConnectorWorkItem.java:335)
              at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.7.0_51]
              at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) [rt.jar:1.7.0_51]
              at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.7.0_51]
              at java.lang.reflect.Method.invoke(Method.java:606) [rt.jar:1.7.0_51]
              at org.teiid.dqp.internal.datamgr.ConnectorManager$1.invoke(ConnectorManager.java:209)
              at com.sun.proxy.$Proxy47.execute(Unknown Source)
              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.run(FutureTask.java:262) [rt.jar:1.7.0_51]
              at org.teiid.dqp.internal.process.FutureWork.run(FutureWork.java:58)
              at org.teiid.dqp.internal.process.DQPWorkContext.runInContext(DQPWorkContext.java:276)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$RunnableWrapper.run(ThreadReuseExecutor.java:119)
              at org.teiid.dqp.internal.process.ThreadReuseExecutor$3.run(ThreadReuseExecutor.java:210)
              at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) [rt.jar:1.7.0_51]
              at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) [rt.jar:1.7.0_51]
              at java.lang.Thread.run(Thread.java:744) [rt.jar:1.7.0_51]
      Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference.
              at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)
              at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)
              at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)
              at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)
              at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeQuery(SQLServerPreparedStatement.java:285)
              at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:462)
              at org.teiid.translator.jdbc.JDBCQueryExecution.execute(JDBCQueryExecution.java:123) [translator-jdbc-8.10.0.Beta2.jar:8.10.0.Beta2]
              ... 18 more
      

      The connection to the datasource is defined in standalone-teiid.xml as:

      <datasource jndi-name="java:/mssql-test-tables" pool-name="sqlserverDS1" enabled="true">
      	<connection-url>jdbc:sqlserver://localhost:1433;databaseName=test_tables</connection-url>
      	<driver-class&gt;com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class&gt;
      	<driver>com.microsoft.sqlserver</driver>
      	<pool>
      		<prefill>false</prefill>
      		<use-strict-min>false</use-strict-min>
      		<flush-strategy>FailingConnectionOnly</flush-strategy>
      	</pool>
      	<security>
      		<user-name>test</user-name>
      		<password>test</password>
      	</security>
      	<validation>
      		<check-valid-connection-sql>select 1</check-valid-connection-sql>
      	</validation>
      </datasource>
      

      Driver and translator definitions are respectively:

      ...
      <driver name="com.microsoft.sqlserver" module="com.microsoft.sqlserver"/>
      ...
      <translator name="sqlserver" module="org.jboss.teiid.translator.jdbc"/>
      ...
      

      and the "ms" model is defined in a dynamic vdb as:

         <model name="ms">
              <property name="importer.useFullSchemaName" value="false"/>
              <property name="importer.tableTypes" value="TABLE,VIEW"/>
      		<property name="importer.importKeys" value="false"/>
      		<property name="importer.schemaPattern" value="dbo"/>
              <source name="test_tables_ms" translator-name="sqlserver" connection-jndi-name="java:/mssql-test-tables"/>
          </model>
      

      I could reproduce this error in Teiid-8.10.0.Beta2 running SQL Server Express 2012 - 64bit (11.0.3128.0).

      Attachments

        Activity

          People

            rhn-engineering-shawkins Steven Hawkins
            redfox999 Salvatore R. (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: