[teiid-issues] [JBoss JIRA] (TEIID-3384) SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference

SQLServerException: Each GROUP BY expression must contain at least one column that is not an outer reference ------------------------------------------------------------------------------------------------------------ Key: TEIID-3384 URL: https://issues.jboss.org/browse/TEIID-3384 Project: Teiid Issue Type: Bug Components: Query Engine Affects Versions: 8.7 Reporter: Salvatore R Assignee: Steven Hawkins Fix For: 8.11 Running this query (where "test_a" is a table in MS SQL Server): {code:sql} select test2 from (select 'aaa' as test2 from ms.test_a) b group by test2 {code} the following exception is thrown: {code:sql} 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 {code} The connection to the datasource is defined in standalone-teiid.xml as: {code:sql} <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>com.microsoft.sqlserver.jdbc.SQLServerDriver</driver-class> <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> {code} Driver and translator definitions are respectively: {code:sql} ... <driver name="com.microsoft.sqlserver" module="com.microsoft.sqlserver"/> ... <translator name="sqlserver" module="org.jboss.teiid.translator.jdbc"/> ... {code} and the "ms" model is defined in a dynamic vdb as: {code:sql} <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> {code} I could reproduce this error in Teiid-8.10.0.Beta2 running SQL Server Express 2012 - 64bit (11.0.3128.0).